How to use Data Validation in Microsoft Excel to create dropdown lists
Posted on May 13, 2007 at 10:04 pm
Make sure to read other posts from the Excel Tutorials series!
- Microsoft Excel Basics Tutorial – Learning how to use Excel
- Excel Tips & Tricks – How to unprotect only a portion of an Excel spreadsheet
- Excel Basics – How to use the Conditional Sum wizard
- How to use Data Validation in Microsoft Excel to create dropdown lists
- Excel Tutorial – How to make a simple graph or chart in Excel
- Excel Tutorial – Example of how to use the VLOOKUP function
If you’re a big Excel user, then you may have found yourself in a situation where you wished you could have simply picked a value for a cell from a dropdown list of choices rather than having to copy and paste text between cells. For example, let’s say you have an Excel sheet for your own personal budget and every time you enter a transaction on a new row, you type in whether it’s an expense or it’s income.
Well sometimes you can type it in incorrectly or you might have a long list of different types of expenses and incomes, so this can be a pain to maintain and remember. An easy solution in Excel is to create your own dropdown list of choices pulled from a particular column on the same sheet or another sheet. This can be very useful because now you can use one sheet to store all of your reference lists and use them on any other sheet in the workbook.
So how do we do this? It’s actually not very difficult! Here’s how:
1. First, create a new workbook and on Sheet1 start off in row 1, column 1 (A1) and type in a few expense categories in the column. For example, here’s what I came up with:
2. Now that you got your reference list, go ahead and select the cells with the data in them. In our case, we’ll select the cells from A1 to A7. Left click on A1 and hold the mouse button down and drag until you get to A7.
3. Now in the box next to the formula textbox on the left (also called the Name box), type in a name for your list. I typed in Expenses for our list. Doing this basically gives the cells from A1 to A7 a name so that the group of cells can now be referenced by a name rather than saying A1 through A7.
4. Now we can use our list to create a dropdown menu with those values. First select the cell where you want your dropdown list to be created. This can be on the same sheet or on a differnet sheet. I went ahead and selected cell A1 on Sheet2 for example.
Next, go to the top menu and choose Data and then Validation. On the Settings tab choose List under the Allow heading. Make sure In-cell dropdown is selected and then type in the name that we had just created earlier (Expenses in this case) with an = sign in the front. So it should be “=Expenses“.
Click OK and you’ll now see that the current cell you had selected has a little arrow pointing down next to it. Click on the arrow and you’ll see the list of items from that we have typed into Sheet1.
That’s it! Remember, this can be used on any Excel sheet where you have some kind of lookup list that needs to be typed in over and over again. Using Data Validation, you can avoid human errors and increase efficiency.
[tags]microsoft excel, excel data validation, excel[/tags]
» Filed Under MS Office Tips
Related Posts
- How to use data validation in Excel
- Excel Tutorial – Example of how to use the VLOOKUP function
- Create and Use Basic Formulas in MS Excel 2010
- How to filter data in Excel
- Link Cells Between Tabs and Workbooks and In Excel 2010
Comments
15 Responses to “How to use Data Validation in Microsoft Excel to create dropdown lists”
-
Pingbacks
-
How to quickly sort data in Excel Says:
[...] tip helps you get more productive with Excel. If you want more Excel help, read my posts on how to use data validation in Excel, how to use the Conditional Sum Wizard, how to make a chart or graph in Excel, how to use VLOOKUP, [...]
May 3rd, 2009 at 5:48 am
Pingbacks
-
How to create multiple linked dropdown lists in Excel Says:
[...] on Friday, June 26th 2009 26Jun So previously I wrote about how you can create a simple dropdown list in Excel, which is perfect for any kind of data validation. If you have a range of values that people are [...]
June 26th, 2009 at 5:12 am
Very good stuff
it helps alot but the dropdown menu only visible if the cell which we put data validation on it is selected, is there any way to make the dropdown menu visiable all the time so that we know the cell is dropdown menu.
hey its nice tutorial. thanx
Thanks a lot!!!!!! This has helped me and i was done in 5 miinutes…..tried for hours…..
Again many thanks and keep posting your excel wisdom!!
Cheers,
Patrick
Is anyone able to tel me if it is possible to use symbols (wingdings) in a dropdown list. I want to use differnet coloured arrows up down sideways etc in Gold Silver Bronze yellow Red (A B C D E) basically rather than pass fail or gradings as above.
As I am very new to Excel, can anyone tell me how to change the value in a drop down list in Excel? For example, if my drop down list contain 3 values a,b,c and if I click “a”, it will show the value “X” (example), and if i click b it will show “Y’ (example) and so on…thanks!
wow..thanks a lottttttttt….this is what I’ve been looking to do in Excel for a long time!
Is there anyway that you can have multipule colours in the drop down list. The cells that I am drawing from have a different colour eg- March(red), April(blue), May (green) for each month. Is there anyway to carry these colours into the drop down box rather than it just defaulting to black or the colour selected for that cell?
I was just wondering if you can display objects/images/little icons (e.g. a check mark representing ‘achieved’; a half filled circle representing ‘in progress’; a triangle representing ‘problematic’; etc.) instead of text in the drop down list. I would like to learn how to do that.
Thank you for the very useful Excel tip. However, I also wanted to know if this was possible with colors? Basically, I want the user to have a drop down list of color choices and only let them choose from the colors I specify. Can’t figure out how to do that! Thanks.
This a very useful tutorial. Nicely presented easy to understand.
Hi,
How to find the source for the dropdown list in an existing file. so that we can edit it, if the file is not created by us?
thanks
Gayathri
Hi.
Does anybody know how to create a drop list from an existing one by selecting any value? VLOOKUP didn’t work as it’s giving only one choice, not a list.
Please advise.
Thanks.