Contextures

Contextures News 2021-07-27

Excel Drop Down List Problem

July 27, 2021

Microsoft survey, drop down problem, 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 August 10th.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Drop Down Blanks

When you click the arrow to open a drop down list, it might select a blank at the bottom of the list, instead of the first item in the list. Why does that happen, and how can you fix that problem?

There's a quick fix below, and detailed steps and a video on my website.

drop down opens with blank selected

Usually, this happens because the source list has blank cells at the bottom, as "holder" cells, where new items can be added later.

  • When a drop down list opens, Excel selects the value in the active cell, if it's in the list.
  • For example, if "Paper" is in the cell, "Paper" is selected in the drop down list
  • And, if the active cell is blank, and there is a blank cell in the list, it selects that blank

To prevent that problem, try one of these quick fixes:

  • Don't leave blank cells in your list--just add new items when needed.
  • Or, put a space character in those "holder" cells, so they look blank, but aren't

NOTE: Blanks can cause another problem too --invalid entries might be allowed in the cells.

Find more Data Validation troubleshooting tips on my Contextures site.

Microsoft Survey

If you use VBA user-defined functions (UDFs) or Excel custom function add-ins, Microsoft's Office Scripts team would appreciate your help. Just fill in their short survey -- it should only take 5 minutes.

  • Note: for question 1, you can select Excel MVP mailing list.

If you're not sure what a UDF is, there are a few examples on my site:

-- ConCat UDF to combine text values

-- HLink UDF to extract the URL from a hyperlink

-- Splitter UDF to split cell text into separate cells

Excel Articles

Here are some Excel-related links that you might find useful or interesting.

Data Analysis: If you love Excel and Orcas, take a look at the resource material on Microsoft's Day of Data: Orcas page. The 2 lessons are designed for 11-14 year old students, but you'll find some interesting ideas and techniques in them too. (Level - All)

Add-ins: If you delete an Excel add-in file, you might see error messages when you open Excel later. Jan Karel Pieterse shows how to fix that problem. (Level - Int/Adv)

Also see: Previous Newsletter Issues

Celebration

My dad turned 90 last week, so we had a small outdoor party to celebrate his big day! It poured rain on our drive to the park, but cleared up a few minutes later. We stayed under the big gazebo, just to be safe, and we weren't attacked by the purple hippo or blue elephant in the background. They were probably afraid of our guard dog!

Here's Dad, with his grandkids and great-grandkids, holding one of the handmade birthday cards he received. It was lovely to get together in person, after so many months of Zoom meetings!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210727ctx.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: August 7, 2021 3:54 PM