You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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:
So now it is your turn. What would you like to see added/changed/done to Data Validation?
Comments: (22) Collapse
I really like the Data Validation feature. This list of improvments in your post is really good. One thing that I think is missing and that many of my clients want is an option to always show the drop down arrow in Lists so it is more obvious that the cell contains a listbox. That is, dont just show the arrow when the cell is selected.
It would be really good if there was a worksheet function/UDF that would return an array of unique values from a range. I would be useful in many scenarios, including Validation, Lists.
// Johan
If I use a List and enter Y,N as the possible choices, it is case sensitive, and if the user enters y or n, he gets an alert. To avoid that alert, I need to give the list as Y,y,N,n, which looks a bit stupid. If, on the other hand, I define a range, say xy, which is equal to 2 rows x 1 column, which contains X & Y, then in the validation list I enter =xy, then the case sensitivity is no longer an issue (so I use this way of doing it). I don't think case sensitivity should interfere, or perhaps there could be a checkbox on the DV form for ensuring case sensitivity or not.
For me, the biggest gripe about Data validation is the fact that it can be over-written by pasting data into the cell. The same also goes for Conditional Formatting.
No matter how many times I tell my users to use Paste Special-Values, it's second nature to either hit return to paste a value, or use Ctrl+V.
Features such as validation and CF should be have an option to ensure they can't be pasted over.
Oh, and I'm not sure if this has been revised in Office 07, but the data validation "wizard" in current versions is woeful - it's virtually impossible to edit a custom formula in the 'refers to' box.
I'd like to be able limit things to classes. For example when I want to say only Whole numbers or only Floating Point numbers I have to also specify ranges. It would be nice if those ranges were optional. It would also be nice to be able to specify only one range when I need to so I can say 0 or greater.
I agree with the need to control overwriting of validation, but I would go further...
When a sheet is protected and some cells on that sheet are unlocked there are two things I do not want to have happening to those cells:
1) changing validation, which defeats the purpose, and
2) moving the cells, which breaks formulas.
Some folks might like more granular control over these, but I think protection is the right model. I find it hard to envision a practical scenario where you want to enable these actions on a protected sheet.
Your reference to Washington state reminded me that it would be nice if Excel shipped with additional custom sort lists.
I would like to be able to do a custom sort on a list keyed by one of the following IDs by as many of the others as possible:
- state/province name ("New York)
- postal abbreviation ("NY")
- FIPS/GNIS code (36)
- census regions (1.2)
- federal regions (2 or II)
- EPA regions (2)
These would not require any code changes. You would simply need to add custom sort orders for, say "States by EPA region" or "Postal abbreviations by state."
The same could be done internationally with country names, continents, ISO country 2- and 3-letter codes, and top-level domains; as well as for localized copies of Excel (e.g. provinces.)
This change would make combining and comparing geographic data much easier! Data from different sources often comes keyed and sorted differently, so I end up having to manipulate it to the moon and back or dump it all into a database and run join queries to get each case ("New York") on a line of its own.
Go to a blank cell and type Alt+Down and the DV list expands - very handy. Now start typing "Do" (as a start to Dog in your example) and type Alt+Down - the pick-from-list list drops down rather than the DV list.
I'd like an option to cause the list to expand immediately when the cell is selected, then scroll as the user types. Start typing "pen" and the list scrolls so you can down arrow to "Pennsylvania".
re hierarchical: You should have an "Indirect" choice in the Allow list. The user supplies a range and Excel looks for a named range with the same name as the value in the supplied range. e.g. I enter Indirect and A1. When A1 says "Ford", Excel looks for the range named Ford and that's the DV list. When A1 says Toyota, Excel looks for that named range. Gracefully erroring if the named range doesn't exist, of course.
control of DV would be good, but the most important thing for me would be that it is reliable and consistent - circle invalid is not really robust enough. DV should validate all the time not just on data entry, it should work on pasted in values, and formulas.
Currently you can paste an invalid value into a cell (without deleting the DV) and it will not fire, likewise formula results can change from valid to invalid and it does not fire.
Circle invalid does not work on protected sheets.
All the usability stuf sounds good, the hierarchical stuff is easy to set up with index/match/offsets already.
I would always use a range name for a validation list so the external sheet thing is not an issue. the no duplicates suggestion is a good idea.
Cheers
Simon
A few things I'd like to see:
1) Ability to use UDFs directly in Data Validation formulas - like Conditional Formatting formulas allows.
2) An option to specify a certain input syntax. For example [AY6]-[0346], where the the first two characters must be alphabetical and the third must be a number and so on. Also, add specified fixed characters automatically as the user types the entry. In the preceeding example, the square brackets and the hyphen would be added automatically.
3) An option to use a list box. For very long lists, a drop-down list can be unwieldy. The list box can have an option to allow the user to specify the maximun number entries displayed in the list window. The list box window appears when the user clicks a drop arrow on the side of the cell. If the user types in the cell, an AutoComplete drop down can be used to narrow down and select an entry (AutoComplete can also be used for standard drop-down lists).
4) Allow or prevent duplicate entries in a validation range.
I'm annoyed by the fact that the drop-down arrow button gets drawn in the cell next door, and not in the cell to which the DV is applied. More often than not there will be another column of data there which then gets partially concealed by the arrow and the average end user gets confused as to which cell the arrow applies to.
Regards,
Vic Eldridge
a )Allow List Source to be in another File....
In addition to another sheet (without hacks)
b) Skip Blank Cell Option - When a list range is selected... Blank cells should not appear
c) Remove limit on number of Red Circles (invalid data) on a sheet
d) Improve memory management.
If there are lot of cells having a list validation refereing to the same range of cells - Give an option like the Pivot... "To Optimise memory I will use the same range as in the previos pivot...."
/right now there is a file size bloat if there are too may validated cells...
e) Put a spiner control in addition to the dropdown ... so that the cell displays the next item in the DV list once spinner is clicked
f) Instead of preventing a Copy/Cut PAste on Validated cells...Preserve validation and Run the validation on Paste.
This should let validated data to be pasted and invalid data should be left out...
g)Give Options like - Only Text, Only Numbers, etc...like in Access....rather than having to write a formula in Custom
Dont say .... Thank you for all the wonderful suggestion..we will consider all this in the next version....as we have to ship 2007 in a couple of months....I am sure the world will survive with 2003 for some more time...
Sam
In order of importance:
- Allow the list source to be a UDF (VBA,XLL or COM)
- Fix the bug with the number of entries in the list being set from VBA causing a crash
- Pasting into it should only paste values
- Case-insensitive
- Ignore blanks in the source list
- Ignore duplicates in the source list
- Add an option such that double-clicking the cell cycles through the DV list entries (just like the VBE Properties window)
- Add regular-expression validation
- Add an event model for BeforeDropDown(Cell), AfterValidate(Cell, Result) etc.
- Fix the bug with DV selections not updating grouped sheets.
Thanks for asking
Stephen Bullen
My number 1 request is being able to store the lists on another worksheet. I can workaround this, but that functionality is out of reach for the average user.
My number 2 request is everything you discussed in your post. :-)
Thanks everyone for the suggestions - there are lots of good items here. Sam (and others), I wrote this thinking about the next version, not Excel 2007. Sorry that wasn't more clear in the post.
Hi David..
I am getting really exited about the blog of the next version (after 2007) of Excel...
All the goodies seem to be coming only later...so is 2007 the teaser....
By the way...isnt XP (2002) the version with the shortest life till date...1 year....we had 2003 coming out pretty soon....
I have a feeling that 2007 is going to beat this record and give way to 2007 1/2...in no time at all....