Understand Arrays + more Excel tips
April 21, 2015
Understand array formulas, speed up macros, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
In case you missed them, here are the articles that I posted recently:
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 to get more information.
Level - Intermediate
Array formulas can be confusing and intimidating. To help us understand them, 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.
Level - Intermediate
To make it easy to enter data on a worksheet, you can create a drop down list of items in a cell. When you click on that cell, the arrow appears -- click it to see the list, then click on an item to select it. If you're new to Excel, and haven't tried this trick yet, there are instructions, and a video tutorial, on my website.
Level - Basic
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. You might be using some of these already
In a related post, Mike Woodhouse suggests some improvements to the tips.
Level - Advanced/VBA
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.
Level - Intermediate
If you're starting to work with pivot tables, or trying to improve your skills, it's nice to have a variety of data 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 sure send a lot of alcoholic beverages to Canada!
Level - Basic/Intermediate. (There is a pivot table introduction on my website)
If you'd like to modify the right-click menus in Excel, but don't want to dive 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.
Level - Intermediate
Last week, I was very happy to see this year's first crocus, blooming in our garden. The rhubarb is bravely poking its nose above ground too, and there are buds on some of the trees. What a wonderful time of year! I hope you're enjoying a lovely spring or fall, wherever you are.
That's it for this week! If you have any comments or questions, send me an email.
Debra Dalgleish
dsd@ contextures.com
P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.
Last updated: January 2, 2020 3:53 PM