SubscribeExcel Basics: How to add drop down list to validate data
Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. In-cell drop-down lists or combo boxes can assist you in picking up a value from a valid list to enter in a cell. Here is a short how-to guide to get you started on data validation in excel.
-
Select the cell where you want validation, go to Data > Validation
-
Specify the validation criteria
In the data validation dialog box, select “list” for type of content to allow in the cell (as you can see, you can also validate data based on other conditions like length of text, data type, formula results, more on this in another post)
In the source field start typing valid values separated by comma, for eg. if you want to allow only first 6 months to be entered in the field, type
January, February, March, April, May, June -
See the validation in action
When you press ok, your cell will now look like this.
-
Doing more: Fetching the validation list from another range of cells
Instead of entering all the values that can go in to the cell in the data validation source field, you can maintain the data in the spreadsheet and refer to in the validation criteria. See below:
More Excel Tips: How you can copy validation criteria from one cell to another, Master IF functions in Excel - 5 tips, 15 fun things you can do with excel now
| Delicious | Stumble it |
« Prev | Home | 5 Infographics that can WOW you [Aug 7] »
Have an Excel Question?
Trackbacks & Pingbacks
- Pingback by Advanced Data Validation Techniques in Excel - Switch lists, change lists etc. | Pointy Haired Dilbert - Chandoo.org on November 25, 2008 @ 7:02 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
Join Our Community
Chandoo,
In step 4,make sure that the data cells from where data is fetched for the drop down, is far far away/hidden/font color same as background.
Jus’ a cosmetic tip.
I create drop downs like this and thought there was a better way.
This fetching data does not seem impressive, what say?
-Nikhil
(May be a duplicate comment)
I like to use the first rows in the sheet for the drop down options and then hide the rows. This way all of the source data is located in one spot and easy to find.
I find this function very useful and apply it all the time.
Another simple approach can be to use the below function….
=INDIRECT(”info!E2:E” & COUNTA(INDIRECT(”Info!$E:$E”)))
here Info is the sheet name, containing the values that you want in the drop down in the Column E, starting from 2 row onwards…
the only catch is…..if afterwards you add / remove a column in the Info sheet, you will be requried to update the above to use the correct referencing.
HTH
~Vijay~
@Nikhil: you are right, often I use another sheet to have the lists, just to be safe that no one meddles with those.
@Tony: yeah, this saves a ton of time when you are creating sheets to circulate to others..
@Vijay, thanks for that tip, it is useful if you work on lots of lists that also change the values. Welcome to PHD, hope you liked my site…
Like Chandoo, I am always using another sheet to store the input lists for reasons of security and stability.
You only have to keep in mind that references to other sheets in data validation lists are only possible by giving the cell range a name and refering to this name in the source field of the data validation dialogue.
Thanks Chandoo…
indeed i am delighted a see a website (blog) where ideas are shared freely like this…kudos to you for setting up the platform….
great sutff…..cheers
~Vijay~
It would be more useful if it found entries “as you type”. Similar to the awesome bar in Firefox.
First I want to say this website is awesome! I haven’t spent this long on a site reading just about everything in a long time. I have a problem that after hours of web research I can’t find the answer. I want to create a list from a range of cells that are filled in by an If() statement, If(A2=”F”,B2,”") Because I can’t find a way to leave a blank cell from the If() the Ignore blank box in the validation window doesn’t work. Is there a way around this? Maybe a formula in the list box that ignores the “” cells?