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:
In the example above, we could use the range A2:A14 to drive the dropdown list and cell A2 would be the first match for a blank cell, rather than A12.
That's it for now, and 2016! May you and your family have a great, safe and Merry Christmas (or whatever holiday you celebrate) and a fantastic 2017.
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".
