How to Make Drop-Down Choice Buttons on an Excel Spreadsheet
Microsoft Excel is a spreadsheet program that is part of the Microsoft Office Suite of office productivity programs. While this program is primarily used for data analysis, it is also good for building lists of information for later use. When building a list, oftentimes there will be a field in each entry which will always be one of several finite options. If this is the case, Excel has a function that allows you to input the options once and then place those options into a pull-down menu.
Things You'll Need
- Microsoft Excel
- List of options
Open Excel. Click on the Windows icon at the top of the the window and scroll down to "Open." Click on the command and browse through the folders on your computer to find your list. Click on it once to select it and click on "Open."
Click on a blank sheet in the workbook (use the tabs on the bottom to select a blank sheet). Type the entries you would like to see in your drop-down list in a column with no spaces in between the entries.
Highlight the list so it is all selected. Click in the name box (this is a text box next to the function bar) and give your list a name (for example, you can name it which column you will be using the list in).
Go back to your list and click in the first cell you want the drop-down list to occur. Click on the "Data" menu at the top of the screen and click on "Validation." Click on the "Settings" tab when the "Validation" window opens.
Click on "List" in the "Allow" box. Type "=" and your list name in the "Source" box. Click on the box next to "In-Cell drop down." Click on the "Ignore Blank" box if the cell can be left blank. Once it is complete, click "OK."
Right-click the cell you have just created the list and click on "Copy." Highlight the entire column, right-click, and click "Paste" to make every cell in that column into that drop-down list.
Tips & Warnings
- Make sure the column is wide enough to show all of the choices in your list. You must manually size the columns to make sure everything fits.
- Considering hiding the list or placing a password on the cell in order to make sure people can't change the data in the list.
- The best lists of this type are ones that have five to 10 finite selections. Even though a list can have more than 32,000 different choices, this will give a data glut and make the list unusable. Be sensible with your data..