Excel Drop Down List Trick
January 26, 2021
Headings in drop down lists, line breaks with TEXTJOIN, 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 February 9th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
With Excel's new FILTER function, you can pull specific records from a list. The TEXTJOIN function can combine all that data into one cell, with separators between the items. It's all in one string though, and hard to read. To fix that problem, you can use the line break character - CHAR(10).
In the screen shot below, this formula is in cell B4, and pulls all the items for the selected order number. I've colour coded it so you can see where the 4 functions are used - SUBSTITUTE, TEXTJOIN, FILTER, and CHOOSE.
In the formula, CHOOSE lets us pick specific columns for the FILTER. I've add the line break with the 4th column - Sales_Data[Quantity]&CHAR(10)
There are more details, and a sample file on the Combine Text and Numbers page on my Contextures site.
To make it easy to find items in a long drop-down list, add single-letter headings in the source list.
You can set up the list headings manually, or use one of the macros on the Letter Headings in Drop Down List page on my Contextures site.
After you add the headings:
Here are a couple of Excel-related articles that you might find useful or interesting.
Excel Tips - This article is from 2017, but Susan Harkins' advice is still good -- 10 things you should NOT do in Excel. Do your co-workers break any of these rules? I'm sure you don't! (Level - All)
XLOOKUP: To see how the new XLOOKUP function works, take a look at these 6 examples on the Microsoft site. (Level - All)
After countless hours of squinting at my laptop keyboard, I finally gave up, and bought a big font keyboard with backlighting. I love it!
My work is going much faster, now that I can take a quick peek at the keys to find F3 or F9. It's noisier than the laptop keyboard, but a lot quieter than the keyboards I started on, 100 years ago!
You can read more about it on my Debra D Blog, if you'd like one too.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210126ctx.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 28, 2022 11:34 AM