Excel Drop Down List Trick
April 6, 2021
Hide used items in drop down list, currency symbol switch, 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 on April 20th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
If you have customers in multiple countries, you might need to show product prices in different currencies. With Excel's conditional formatting, you can automatically change the number formatting, to show the correct currency symbol for the selected country - no macro needed.
How It Works
For more details, go to the Conditional Format Currency page on my Contextures site. There are step-by-step instructions, and this short video, with the full transcript and timeline.
Instead of showing all the items in a data validation drop down list, you can hide items that were already selected. That little trick can prevent problems, like assigning an employee to multiple shifts on the same day.
In this screen shot, Cam has been assigned already, so his name was removed from the drop downs for Monday.
In the original version of this workbook, there were two sets of "helper" columns, and OFFSET formulas to create the short list of names.
I just made a new version, using Excel's spill functions, and it only needs one set of "helper" columns, with this formula.
To get the sample file, and read more about hiding used items, go to the Hide Used Items in Drop Down page on my Contextures site.
Here are a few Excel-related links that you might find useful or interesting.
Charts: Carsten Sandtner shows how to make a better-looking chart in Excel, and create a chart template. That will save you time when making another chart later. (Level - All)
Programming: On the Analyst Cave blog, Tom looks at the pros and cons of creating your own user-defined functions (UDFs) in Excel, and shows UDF examples. (Level - Int/Adv)
Also see: Previous Newsletter Issues
A Canadian cleaning company is running a spring cleaning challenge on Instagram, so I downloaded their free checklist. It looks easy when they do it, and I'd like a clean house!
However, my enthusiasm didn't last long, and now the duster has a new job, as a "home accessory." Do you think the spring cleaning would go better if I made my own checklist, in Excel?
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210406ctx.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: April 5, 2021 4:34 PM