Contextures News 20150421

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.

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. Use another number format, or a custom format, to prevent this. (Level - Basic/Intermediate)

• 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 to get more information.

Level - Intermediate

3. Understanding Array Formulas

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

4. Create a Drop Down List

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

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. You might be using some of these already

In a related post, Mike Woodhouse suggests some improvements to the tips.

Level - Advanced/VBA

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.

Level - Intermediate

7. Sample Pivot Tables

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)

8. Right-Click Menu Editor

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

9. First Flower

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.