Your Turn Again – Data Validation
The other night at home, I was doing some work at home, I ran across a situation where I wished that our “Data Validation” functionality and “Pick From List” were integrated – specifically, it would have been really nice if, after I set up Data Validation on a range, Excel recognized when I was typing an entry from the list of valid possible entries.
Here’s an example. Say I was categorizing some expenses in this Table, and I have set up Data Validation on the last column to make sure I do not mis-categorize any expenses.
If I start typing an entry that is already present above or below the cell I am currently editing (I think Excel looks 25 rows above and below or something like that), like “B” in this case, Excel fills in the rest of the word with the part I didn’t type highlighted in black. If that’s the word I want, I just have to press Enter, and I am off to the races. Fewer keystrokes, faster data entry.
However, if I start typing something that is defined in Data Validation as a valid entry, but is not already typed (“Dogs” in this case), I don’t get the highlighting, and I have to type in the entire value before pressing enter.
This could be a bit smoother. Many of the Data Validation Lists I define have a lot of items (30-50) that can be multiple words, so it would be really handy to get Pick-From-List auto complete – it would make things faster (less typing) and easier (since I wouldn’t have to remember every word of all 50, just the first words).
That led to my next thought about Data Validation – after you type an entry, if the entry is invalid, it erases everything you typed. For example, if a valid entry was “Home Improvements” and you typed “Home Improvement” and pressed Enter, Excel would alert you that the value was not valid and then delete everything in the cell, leaving you to have to re-type those characters. It would be nice to be able to just press Cancel and type an “s” instead.
So, while I am at it, here are a number of other things we have heard about Data Validation over the years:
- It would be nice to have controll over whether things like Copy/Paste, CTRL+Enter, and Fill Down over-wrote data validation.
- It should be possible to specify data types for the range having Data Validation applied.
- It should be possible to specify unique values only in a range (rank everything 1-20 …).
- Two columns can be linked in hierarchical lists (if I choose WA in column 1, only cities in WA should appear in the Data Validation list in column 2).
- Have an option to sort entries in the drop down.
- Allow me to reference cells on another sheet without resorting to hacks.
- Better user control over the error message shown.
So now it is your turn. What would you like to see added/changed/done to Data Validation?
