Contextures

Contextures News 2021-01-26

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.

TEXTJOIN Line Breaks

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.

  • =SUBSTITUTE(TEXTJOIN(", ",FALSE, FILTER(CHOOSE({1,2,3,4}, Sales_Data[Category], Sales_Data[Product], Sales_Data[Grams], Sales_Data[Quantity]&CHAR(10)), Sales_Data[Order]=B3)), CHAR(10) & ", ",CHAR(10))

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.

TEXTJOIN and FILTER with line breaks

Drop Down Trick

To make it easy to find items in a long drop-down list, add single-letter headings in the source list.

source list with headings

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:

  • Type a letter in the drop down cell, and click the drop down arrow
  • The list opens at the letter you typed, so you can quickly find the item you need
  • No macros are needed to use the drop down list with letter headings

drop down list with headings

Excel Articles

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)

Also see: My Excel Products || Previous Issues

Big Font Keyboard

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.

weekly photo

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.

Debra Dalgleish
dsd @contextures.com
ctxdebra @gmail.com

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 4, 2021 8:53 PM