IN this week’s roundup, you’ll learn about arrays and array formulas, see how to speed up macros, and many more tips.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
1. Contextures Posts
In case you missed them, here are the articles that I posted recently:
- If you leave numbers in the default General format, Excel will automatically round them. For more control, apply a different number format, or a custom format.
- For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
2. Excel Dashboard Course
Registration is open for Mynda Treacy’s Excel Dashboard Course. and you can save 20% if you sign up by Thursday (April 23rd). The course is a great investment, and you can read my review for the highlights, and more information.
3. Understanding Array Formulas
Array formulas can be confusing and intimidating, if you don’t use them often (or ever!). On the Model Off blog, Dan Mayoh explains what arrays are, and how to use array formulas in financial models. There is a homework assignment too, for extra practice, with a workbook to download.
4. Talking With Jon Peltier
On the Visualoop blog, Tiago Veloso talks with Excel charting expert, Jon Peltier. Jon explains how he got started in Excel, and gradually built his own business, where he sells his time-saving Excel charting utilities.
5. Speed up Your VBA Code
Last week, we saw complaints about slow VBA code in Excel 2013. This week, learn how to speed up your code, with ten simple tricks from Mike Alexander.
In a related post, Mike Woodhouse suggests some improvements, and warnings, for the tips.
6. Work With a Big Data Set
On the Udemy website, data analyst David Taylor explains how to work with a big data set, using baby names from a social security database. You can download the file, and follow along. The tutorial is below the sales page for an advanced Excel course, so scroll down about halfway, if you end up at the top of the page.
7. Sample Pivot Tables
If you’re learning about pivot tables, you can download an interesting workbook to experiment with. The Australian government has just published an Excel file with a pivot table showing their imports and exports with trade partners. They certainly send a lot of alcoholic beverages to Canada, so apparently I’m not the only one drinking Australian wine!
8. Right-Click Menu Editor
If you’d like to modify the right-click menus in Excel, without diving into the VBA code, you can try Doug Glancy’s MenuRighter add-in. It’s easy to pick items from a list, and click to add them to the menu. Download the current version, or volunteer to test the newest version.
9. New Book: Excel VBA 24-Hour Trainer, by Tom Urtis
“Excel VBA 24-Hour Trainer, 2nd Edition is the quick-start guide to getting more out of Excel, using Visual Basic for Applications…This self-paced tutorial explains Excel VBA from the ground up, demonstrating with each advancing lesson how you can increase your productivity. Clear, concise, step-by-step instructions are combined with illustrations, code examples, and downloadable workbooks to give you a practical, in-depth learning experience and results that apply to real-world scenarios.”