From the Not Just Numbers blog:
Before I start this post, I must first apologise for the lack of posts over recent months. After returning from my summer holidays, my workload hasn’t really eased (which isn’t a bad thing), and my blogging has suffered.
I wanted to get an (albeit short) tip up before Christmas, though, and it is one of my New Year resolutions to post at least once a month in 2017.
Your Christmas tip then is a simple answer to a particularly annoying aspect of data validation dropdown boxes.
A feature of data validation drodpown lists in Excel is that if the cell already contains a value from the list, the dropdown starts with that value selected, and you need to scroll up if you want to select an earlier value.
This is normally fine except for the following (very common) scenario.
It is good practice to have the dropdown list look at a range where its entries can be edited, and to leave space at the bottom of the list to allow the list to be added to.
However, a side-effect of this is that, when your cell is empty, the dropdown will start at the bottom of the list, as it sees the empty cells at the bottom as a match for the current entry (nothing).
The simplest answer I have found to this is to have a blank cell at the top of the list as well. As this matching feature matches the first match it finds, your dropdown list will now start at the top (for an empty cell).
I often find the neatest way to do this is to have a blank row under the headers that doesn’t look like part of the list:
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.