Contextures News 2021-04-06

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.

Currency Format

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.

currency selection list

How It Works

  • On a different sheet, there is a list with country information
  • VLOOKUP formula in cell G2 finds the currency for the selected country
  • Conditional formatting rules changes the number format, based on the currency name in cell G2

currency VLOOKUP formula

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.

Hide Used Items

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.

hide used items in drop down list

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.

  • =TRANSPOSE(FILTER(EmpNames,COUNTIF($B2:$F2,EmpNames)=0))

hide used items in drop down list short 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.

Excel Articles

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)

Excel Challenge: Robert Gascon has a new Excel challenge, for a receivable aging formula. You can see his solution here, and get the Excel solution file there too. (Level - Int/Adv)

Also see: Previous Newsletter Issues

Spring (Not) Cleaning

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?

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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

Debra Dalgleish

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.

contextures newsletter info


Last updated: April 5, 2021 4:34 PM