Excel Drop Down Trick
February 5, 2019
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
To make it easy for people to enter data on a worksheet, you can use data validation to create drop down lists. For example, make a table of employee, and show all those names in the drop down.
Someone asked me how to make the drop down show a shorter list, if the employee list is filtered. To do that, I used a formula workaround (no macros).
First, I added another column in the employee table, with a formula to number the visible rows:
The SUBTOTAL function ignores the hidden rows, and numbers the visible rows only 1 to 6.
Next, I created another small table, with numbers typed in the first column, and this formula in the second column:
The final steps were to create a dynamic named range, based on the new table, and change the drop downs to use that list, instead of the full list of names.
To see all the details for setting this up, go to the Drop Down from Filter List article on my blog.
It's easy to change the summary function in a pivot table, from Sum to Count or Average, and several other functions. And, if you need to, you can create your own pivot table formulas, with calculated items and calculated fields.
If you're not sure how these formulas work, watch my new video -- it shows a simple example of each formula type, and also shows how to make a quick list of all the formulas you created.
These formulas only work with normal pivot tables though, not OLAP-based ones. So, if you checked the box to "Add to Data Model", the formula commands aren't available.
Go to my Contextures site to learn more about pivot table Calculated Items and Calculated Fields.
Here are a couple of Excel articles that you might find useful or interesting.
Personal Finance - Len Penzo uses Excel for his personal finances, because it's more hands-on than sites like Mint. He shares a few tips, and there's a good discussion in the comments. (Level - All)
Spreadsheets - You'll see familiar items in Ariel Fischman's spreadsheet collection. He's got VisiCalc, Multiplan, beta copies of Excel, and much more. He can't find Excel 2001 for the Mac though, if you have a copy of that. (Level - All)
There was a deep freeze here last week, and lots of snow, so it was perfect weather to stay inside and work with Excel. There's a digital weather station in our front hall, and it shows the outdoor temperature (that was a "warm" day), and other info.
I also have a Galileo thermometer that shows the indoor temperature. It's not as accurate, but it's much prettier than the digital one.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190205ctx.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.
Last updated: February 7, 2019 12:16 PM