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.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
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))),"")
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.
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.
Move the ToolTip
If the function argument tooltip gets in your way, you can move it.
Or, if you never use the tooltip, turn it off:
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
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.
Last updated: October 7, 2020 4:13 PM