Modern spreadsheet programs, like Microsoft Excel or Google Sheets, carry a plethora of functions under the surface that—if used correctly—can take their utility to another level. For instance, the cells in your spreadsheets can be turned into dropdown menus. Let’s go over how to do so in both Excel and Sheets.

Creating a Dropdown Menu in Microsoft Excel

First, you need to create the list that your dropdown will reference as its options. On an Excel sheet, create a new table that contains the various options you want included in your dropdown. Once that’s completed, select the cell where you want your drop-down menu to appear.

Once the right cell is selected, navigate to the Ribbon and find the Data tab, and from there, find Data Validation. In the window that appears, go to the Settings tab and—under the Allow box—select List.

In the Source box, identify the range that is populated by the table you created with your options, omitting the cell that contains the title. If it will be okay for people to leave the selection blank, make sure that the Ignore blank box is checked, and of course the In-cell dropdown box is as well.

Under the Input Message tab, you can set your cell to display a message when it is selected by checking the Show input message when cell is selected box. You can then add a title and the message you want displayed.

The Error Alert tab allows you to notify someone when they’ve entered something that isn’t included in your predetermined list. Check the Show error alert after invalid data is entered box and provide your desired title and message in the appropriate boxes. You also need to pick the Style of the alert.

• Information or Warning won’t stop someone from putting data not included in the list in, and will display a blue circle with an “I” in it or the yellow warning triangle respectively.
• Stop will prevent someone from entering data not included in your list at all.

Creating a Dropdown Menu in Google Sheets

Google Sheets presents you with a few methods to create a dropdown menu.

If you want to use specific words or numbers, you should first select the cell or range of cells you want to use for your dropdown and select Data from the toolbar, followed by Data validation. A panel will open, where you can select List from a range to define the Criteria. A menu will open, where you should select List of items.

In the box next to the selection, type out all the options you want your dropdown list to present, only separated by commas. For instance: banana,orange,apple,grape

A small downwards arrow will appear by default in the cells used for your dropdown, but you can disable this by unchecking Show dropdown list in cell. Click Save, and you have your dropdown list.

However, if you want to base your dropdown off of data already in your spreadsheet, you should again select the range of cells you want populated by your list. Again, select Data and Data validation, and leave Criteria set to List from a range. In the box, provide the range of cells that already have your list populated. So, if your list ranges from F5 to F12, type in F5:F12. You can also click on the small grid pattern that appears in the entry box to manually select the cells to include. Click Save, and once again, you have your dropdown list.

Hopefully, this tip helps you organize data that much more effectively. Reach out to Apex Technology to find out how else we can make your technology work harder for you, more conveniently. Give us a call at (704) 895-0010.