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?