Contextures News 2020-08-25

Excel Formula Quick Tricks

August 25, 2020

Table drop downs, formula quick tricks, 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.

Thank you for reading the news, and you'll get the next email in two weeks, on September 8th.

• Table Drop Downs
• Formula Tricks
• Excel Articles
• Weekly Photo

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Table Drop Downs

Drop downs make it easy to enter data in a table, and it's more helpful if each list is based on your selections in other drop downs.

My latest example has 3 levels of dependent drop downs - 1) select a region, 2) pick a sales rep from that region, 3) choose a customer for that region and sales rep.

This sheet has the lists for the Customer column drop down. The list for East/Dee starts in cell E7.

This is the formula in cell B7, and copied across to column J.

=IFERROR(SORT(FILTER(CustCol, (RegCol=B3) * (RepCol=B4))),"")

• FILTER function returns the customers for the Region in B3, and the Rep in B4
• SORT puts the results in A-Z order

To see all the details, and to get the sample file, go to the Dependent Drop Downs in Excel Table page.

NOTE: If your version of Excel doesn't have the new functions yet, try the technique on this page.

Formula Tricks

Here a couple of quick tips, to help when you're entering a complex formula.

Argument Names

After you type the function name and bracket, press Ctrl+Shift+A to put all the arguments into the cell.

• With the first argument highlighted, click on the range that you want to refer to.
• Then, click the next argument name in the tooltip, and select its range

Move the ToolTip

If the function argument tooltip gets in your way, you can move it.

• Point to an empty part of the tooltip, so the pointer changes to a 4-headed arrow
• Drag the tooltip to a different spot in the Excel window

Or, if you never use the tooltip, turn it off:

• Go to File>Options>Advanced.
• In the Display section, remove the check mark for Show function ScreenTips.

Excel Articles

Here are a couple of Excel-related articles that you might find useful or interesting.

Learning: Whatever you're trying to learn, there's good advice from Gordon Shotwell in his article, Advice for non-traditional data scientists. My favourite tip - "Follow kind experts" (Level - All)

Power BI: Watch session recordings from the recent Power BI conference, including Create Beautiful Yet Effective Data Visualizations, by Grace Teoh. Note: You'll have to register (free). (Level - All)

Also see: My Excel Products || Previous Issues

Land Shark

My son got a puppy this spring, and they've come for a couple of safe-distance visits this summer. Last week, they brought rafting gear, to test it in our back yard. No, we don't have a pool or waterfront property, but there's enough space to inflate a raft! The puppy tested his life jacket too, and it reminded me of those old sketches on Saturday Night Live, when the Land Shark knocked on apartment doors.

You can find similar life jackets for dogs on Amazon.

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200825ctx.html
I'll also post any article updates or corrections there.

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.