Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Adding Drop Down Menus To A Spreadsheet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How do I add drop down menus at the top of each column that will both store
and allow for easy retrieval of any entries in the cells beneath?

View Answers     

Similar Excel Tutorials

Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allo ...
Display the Print Window in Excel
This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear ...
Remove All Data Validation from a Cell in Excel
Remove all data validation from a cell in Excel with this free Excel macro. This is a great macro to use when you ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...

Helpful Excel Macros

Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
- Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allows yo
Remove All Data Validation from a Cell in Excel
- Remove all data validation from a cell in Excel with this free Excel macro. This is a great macro to use when you need
Display the Print Window in Excel
- This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w

Similar Topics

I put together a form with drop down menus in. No macro's in the workbook, just some drop down menus in a work book with a few hidden columns and some basic protection. The protection is not on the drop down menu cells.

I designed the workbook using Excel 2007, but have shared it with a number of people since who are using Office 2003, and Office 20007. No problems. Everyone can see the drop down menus and is able to populate them.

There's one colleague who opens the workbook and doesn't see any of the drop downs. The guy is using Office 2003 SP2. I have no idea why he doesn't see them. Does anyone have any thoughts?

Hi there,

I have a number of drop-down menus on my spreadsheet that I've drawn using the Forms toolbar. I currently have a macro that clears the sheet so that it's blank for the next user. Does anybody know how I can get the macro to clear these drop-down menus (or get them back to the first selection in the drop-down menu), as at the moment they still contain the selection that the last user made.

Many thanks!


I have a problem. I have in excel 07 created a document which contains drop-down menus, and they work perfectly. When i send the document to my collegue, who use excel 10, the drop-down menus (and everything else) works perfect, but when he makes changes and saves the document, some of the drop-down menus dissapear when i open it in 07.
The changes my collegue make are NOT in the drop-down menus.

Any ideas of what goes wrong?

I learnt how to do drop down menus for cells, but I would like the drop down
menu to display one word but enter in a different character in the cell when
it is selected.

I have drop down menus (on several sheets), all offering the same list of choices, and I would like to synchronize them, i.e. if I change the choice on any of the drop down menus, also the choices on all the other drop down menus should change accordingly.

How do I do that?

Thanks for any suggestions!



I'm still pretty inexperienced with Excel and I'm trying to figure out how if I change entries on a drop down menu it will clear out all other drop down menus based on that selection. IE, if I change the city from Houston to Dallas, the sub drop down menus would all be cleared out. I've attached a very dumbed down version of what I'm trying to accomplish. I'm pretty sure I will have to write something into macros and I have no idea how to do that. Any help would be greatly be appreciated. Thanks!

I have an excel sheet that is comprised of 20+ questions, each connected to a seperate drop down menu by an IF Statement. The drop down menus are sometimes connected to another question or they are connected to a solution (sort of like "Choose your own adventure" books).

If they go back to the beginning and choose a different adventure (I'll continue with that theme), the previous selections from the drop down menus are still there. I created a macro to clear the contents of the menus, but it is only if the user wants to start completely over. So if the user just wants to take a couple steps back, is there a macro to clear the contents of the drop down menu if the cell next to it is blank?

Thank you!

Hi there,
I'm stuck on a puzzling formula I'm creating for a client, and am in desperate need of help.
What I want to do (and have done) is set up 2 drop down menus.
My drop down menus are in column A, Row 1 and 2.

Drop down 1 - looks up a list of publications (Column C)
Drop down 2 - looks up months (Row 1, Column D-O)

What I want to do is when my client drops down the publication they want, and the month they want, it will tell me the cost of that publication in A3.

I have attached my example of what I'm trying to do.
All help would be greatly appreciated.
Kind regards,

I need to create drop down menus based on the previous fields. So, for example, The first drop down would have the option for 1-8. Then, based on what they select there, the next field would have a drop down for a-y. Then to finalize it, based on what the user selects in the first two fields, a final drop down menu would appear with 1-99. The issue is I need it specific. So, for example, if a user selects 6 from the first drop down menu, they will only see m-p in the second drop down. If the user then selects o from the 2nd drop down, they will only see 80-84 in the last drop down.

To throw one more wrench into it, some selections for the a-y and 1-99 may be the same. So 1.b may be Operations-Invoices which also may be the same for 5.s but under a different preceding drop down selection like Customer Service-Invoices.

I am trying to make a series of drop-down menus wherein the list used for the
second drop-down changes based on what is selected in the first drop-down.
Because there are more than seven options in the first menu, I can't just do
an =IF() function. Is there any way to force the calculation of a reference
within another formula? For example, if I have named lists A, B, and C and
the first drop-down menu in A1 contains the options A, B, and C, how do I get
the drop-down menu in A2 to read A1 as a list name instead of a text item?

If there is no way to do this, does anyone have alternate ideas (other than
the obvious making one giant list that is sorted but not actually separated)?

-Lisa Fox

Good morning,
I have an excel worksheet that has a column of drop down menus. Each row has a drop down menu and the cell link is a cell in the same row but different column. Is there anyway I can copy the drop down menus down for each row and have the cell link change, as well, so it follows the rows down.

I hope I explained what I need well.
Thank you!

Apologies firstly to the forum as i am not an excel expert at by any means, but have taken it upon myself to go about designing an estimate spreadsheet for my printing business, now I wish I hadn't.

The main problem is I have created alot of cells with drop down lists using data validation in them, to speed up the quoting process! Firstly i have to say the drop down menus work fine as they are.

These drop down lists include all the variants for the printing business ie: quantity, size, paper cost, printing cost, finishing costs etc...

Each of these drop down menus items needs a value and or the ability to store a formula so i can generate the final price, including profit margins. This is proving very hard. (i can do the formula) but each drop down item has a different value. i am stuck and a newbie, please help, i can email the file and even pay someone to do this for me, its taken me all weekend.

please help. or if somebody has a spreadsheet to work out printing companies estimates i would love to see it or anything similar.

thanks in advance

Can somebody please point me in the right direction, i can email the file

I recently updated a rather complicated spreadsheet to include many drop down menus to eliminate the need for repetitive typing. The spreadsheet's formulas worked great across each worksheet, but since I've decided to add in these drop down menus all of the formulas do not update automatically. I have to go to each cell with a formula and refresh the cell for the formula to work, and this would take way too long to refresh each cell after each week. I also have MoreFunc installed, FYI. Please, any help on troubleshooting this problem would be great. Thanks.

Hi all

Is it possible in Excel 2003 to have drop-down menus that appear/disappear on mouseover similar to a web-page?
They would be part of a worksheet not a userform if that helps?
I'm looking to redesign a rather clunky workbook that uses a load of buttons for navigation and would prefer something like this.


I'm completely new to using excel but am getting the hang of it. I just have two question:

I have three drop down menus in lets say a1 a2 a3. Each going from 0-200% by 10%. In lets say cell a4 I have another drop down menu. Would it be possible to choose something from the forth drop down menu that would change the first 3 drop down menus. For example: a4 has a drop down menu of 1-4. If I choose 1, a1-a3 would change to 50%, change a4 to 2 and a1-a3 changes to 100% and so on.

I'm somewhat familiar with conditional formatting but I have a small question. Lets say I have a check box with a true and false cell (lets call it b1) that goes with it. Would it be possible to have another cell (b2) become visible when b1 is true? What I mean is that if b1 is true then b2 appears but if b1 is false then b2 doesn't exist.

Please let me know and thanks in advance for your help.

I'm looking for some help on trying to have more than one type of validation in a cell. I have created drop down menus using lists to create available class dates. I would like to also have validation to give me a warning if the the drop down date chosen is less than two weeks away from today as a reminder to me. I can make the formula work and the warning comes up, but then I lose the drop down menus.

Any ideas?


Can someone help me with creating drop down menus in excel? I know how to do a standard one, but I'm working on a project where I've created a drop-down menu for a category, but now need to create a drop down menu for sub-category. The issue is this: I need the drop down menu for the sub-category to be determined by what is chosen in the main category section. For instance: If "EQUIPMENT" is the selection in the main category drop down menu, then I need a drop down menu in the sub category column to be "treadmills, ellipticals, bikes", etc. But, if "ACCESSORIES" is the selection chosen in the main category, then I need the sub-category drop down menu to be "mats, stretch tubes, etc.". I have over 1500 rows to do this, so I really want some kind of a formula to do this.

Does that make sense? Can anyone help? Many thanks! - Dan

I have a pivot table and as expected from a pivot table the user can use the row drop down menus to select to see partial data.

Problem is that even though the pivot table has refreshed old options which no longer exist still appear in the drop down menus.

Is there a way to clear down these drop downs so the only possibilities are from the most recent refresh?

I have a database in sheet 1 ranging around 500 rows with about 20 columns. Sheet 2 pulls out the filtered data from each column of Sheet 1 removing the duplicates. Sheet 3 has drop down menus, where the filtered data in sheet 2 is displayed thru drop down menus. There are about 10 drop down menus in sheet 3. On the basis of the criteria selected from drop down, the quantity is calculated and displayed in a cell in Sheet 3. For eg: drop down menu - customer name and job name. Job name menu is based on the selection made in customer name. Meaning job name menu should display all the jobs associated to the customer name selected in the customer name menu. It does so but leaves the last job name from displaying. If there are 6 job names associated ABC customer, the drop down of job name would display only 5 though it is filtered properly in Sheet 2. Now how do I get the job name menu display all 6 job names. This happens with each customer. Inshort, it displays 1 less. If 10 job names associated, it will display only 9. How do i get it to display all 10..

I hope my question is clear. let me know if need more info..

I desperately need some help in creating progressively filtering drop down menus.

basically i have a list of people with assigned job descriptions. these job descriptions can then be broken down further into 2 tiers but the information for these teirs is in another tab (parent tab)

i want excel to use the description provided, then go to the parent tab and in a drop down menu present all the 1st level tiers for that job, and then once a selection is made, for that selection show the resulting 3rd level tiers available.

so in the attached file...the data im given is in tab1 (column B)

and i want to use the above data to filter and create drop down menus from Parent tab columns A,B,C. the drop down menus should be in tab1.

also the data in tab1 column B is the same as parent tab column A.

pleeeeeeeeeeeeeeeease help!

I have figured out creating drop down menus using the validation option but
what I would like to do is have a scrollable calendar in that field. Is there
a way to put a calendar in pull down menus or would someone recommend another
way to approach this?


Hello! I'm having a problem when I sort data columns that contain drop-down menus. My spreadsheet has several columns: "Patient name", "Room #", and "Phone #". Each cell within the "Phone #" column should have a drop-down validation list containing two possible entries: the room's designated telephone number, or NONE. Whenever I add a new patient to a room, these three columns need to be sorted alphabetically by patient name. But when I sort all three lists simultaneously, it looks great- but then I realize that the drop-down lists associated with each "Phone #" cell haven't sorted! They stay in their original location and don't sort with their cell. So the designated telephone numbers for each room are all screwed up. How do fix this? I saw this same question posted on another Excel forum, but no-one knew the answer. Help!

I am looking for advice on how to create an interactive chart that changes based on the selections in two different drop down menus containing ranges. Any help is greatly appreciated.


My book contains 2 sheets. The second sheet is the following format:


And the list continues with text in column A and values in Column B for many, many values.

In the first sheet, I want to have 2 drop down menus. They would be side by side for the sake of comparison. In each menu, one is able to see all of the different texts that are in column A in sheet 2. Therefore, one may choose 'abc' in one drop down menu, and 'def' in the other. Then, I would like the respective value from column B (sheet 2) to display below the drop down menu.

So in Sheet 1, if I chose 'abc' in one menu, and 'def' in the other, i would get something like this in sheet 1:


Hello. I have an excel spreadsheet that I use to create pivot table reports. Once the reports are refreshed they are copied to a new workbook to send to each individual salesperson to evaluate. The problem I am having is the drop down menus that are originally in the reports before copying to a new work book are lost once they are moved to a new workbook. I understand that this is because the cell reference is in the original workbook but is there a way to have these drop downs stay in the work sheet?