I recently received an email from a reader asking how to add a hidden
drop down menu inside a cell. I was unable to respond back to the
reader (email address was invalid), but I thought it was a great
question and wanted to add it to Excel Hints. In just a couple steps,
you’ll be able to add this feature to your spreadsheet and improve the
validity of many spreadsheets you create.
How To Add the Hidden Drop Down Menu
Unlike using a drop down menu from the forms toolbar (there will eventually be a post about that), this drop down menu is not seen until that particular cell is selected. Within your particular form you are creating, select the cell where you like the drop down menu to appear. After selecting the cell, select Data–>Validation and a pop up box will appear ingeniously called “Data Validation”.
Under the ‘Settings’ Tab, select ‘list’ under the allow pull-down menu. Then under Source either select the range of cell you want included in the drop down menu, or type in the value with a comma to separate them. When you are finished entering the range or numbers, hit OK. That’s it. Check out the screenshots below which will walk you through the steps.
In Cell Drop Down Menu Example
In this example, we have created a form where the user types in their personal information, but instead of entering an age, we want them to select an age range. This is where an in cell drop down menu can come in handy.
1. Select the cell where you want to place the drop down menu.
2. Select Data–>Validation from the top menu. The box that pops up should look like this.
3. Select “list” from the Allow Menu, and enter (or select) the strings you want in your drop down menu.
4. You should now see the drop down menu you have created with the options you entered in step 3.
5. You’re done. You can now use the form you just created.
How To Add the Hidden Drop Down Menu
Unlike using a drop down menu from the forms toolbar (there will eventually be a post about that), this drop down menu is not seen until that particular cell is selected. Within your particular form you are creating, select the cell where you like the drop down menu to appear. After selecting the cell, select Data–>Validation and a pop up box will appear ingeniously called “Data Validation”.
Under the ‘Settings’ Tab, select ‘list’ under the allow pull-down menu. Then under Source either select the range of cell you want included in the drop down menu, or type in the value with a comma to separate them. When you are finished entering the range or numbers, hit OK. That’s it. Check out the screenshots below which will walk you through the steps.
In Cell Drop Down Menu Example
In this example, we have created a form where the user types in their personal information, but instead of entering an age, we want them to select an age range. This is where an in cell drop down menu can come in handy.
1. Select the cell where you want to place the drop down menu.
2. Select Data–>Validation from the top menu. The box that pops up should look like this.
3. Select “list” from the Allow Menu, and enter (or select) the strings you want in your drop down menu.
4. You should now see the drop down menu you have created with the options you entered in step 3.
5. You’re done. You can now use the form you just created.
No comments:
Post a Comment