Dropdown menus can be extremely useful tools for many applications, from status updates to location or product specification and more. Learning how to create a dropdown in Google Sheets and Excel can save you time and effort when managing spreadsheets that involve a large amount of data.
Though the process is different for each program, you’ll need to edit the data validation settings to create a dropdown in Google Sheets and Excel. These settings allow you to create a designated list of valid data entries to apply to a certain cell or range of cells. They can be edited and customized from their respective menus and are fairly easy to troubleshoot.
Here’s how to create a dropdown in Google Sheets and Excel, as well as the solutions to a few common issues with data validation in both programs.
How to Create a Dropdown in Google Sheets
In your Google Sheets spreadsheet, select the cell or cells for which you’d like the drop-down list to apply. Select “Data” from the tab menu at the top of the page, then select “Data validation.” A popup will appear with options for
- Changing the cell range selected
- The criteria in the dropdown list
- Whether or not to show an Invalid Data warning
- The appearance of the text
Under “Criteria,” select either “List from a range” to choose a number of cells to include on the list or “List of items” to enter a list of customized items. If you choose “List of items,” separate your items with commas but no spaces. Click “Save” to apply this formatting.
To change or delete an item, reselect the cell or cells you wish to change, then return to the “Data validation” menu. You can change your dropdown menu’s options, edit the “Criteria” items. To delete the list completely, click “Remove validation” at the bottom of the popup box.
How to Create a Dropdown in Excel
Create a new worksheet in your Excel spreadsheet. Type your items as a list in individual cells, then select all of the applicable cells and press “Ctrl” and “T” at the same time. This will convert them into a table.
Return to your main worksheet, and select the cell or cells for which you’d like the dropdown list to apply. Next, click on the “Data” tab in the menu bar at the top of the program, and select “Data validation.” This will bring up a popup menu with three tabs.
- Settings – where you will set up the dropdown menu’s items and display
- Input Message – where you can set a message to display when a cell is selected
- Error Alert – where you can set a message to display when invalid data is entered into the cell
In the “Settings” tab of the popup menu, under “Allow,” select “List”. Then, under “Source”, select the table range you created earlier. Next, make sure that the option “In-cell dropdown” is selected, then press “Ok”.
To edit the list, simply select the cell or cells included in the dropdown list and return to the “Data validation” menu. From there, you can either edit the source, allow or disallow blank cells, or remove the validation by pressing “Clear All”.
Troubleshooting A Dropdown in Google Sheets and Excel
Creating a dropdown menu is one thing, but getting it to function exactly as you need it to can be quite another. So here are some ways to troubleshoot common issues with both Google Sheets and Excel data validation.
Unable to access dropdown in Google Sheets
If you can’t access a dropdown menu in a Google Sheets spreadsheet, you may not have the correct administrative rights for the page. If someone shared the link with you, contact the spreadsheet owner and ask to be given editing rights. Likewise, if you are trying to share a spreadsheet with other people, be sure to give them editing rights.
Issues with porting data validation from Excel to Google Sheets and vice versa
Since Google Sheets and Excel use different formulas to calibrate dropdown menus, it is difficult to directly port data validation from one program to another. Because of this, you can either use customized community-built applications or re-enter your data validation criteria manually to solve the issue. Unfortunately, there isn’t a much easier way to do this at this time.
Unable to select “Data validation” on an Excel spreadsheet
If you’re unable to select the “Data validation” menu on an Excel spreadsheet, you may not have permission to access all of its functions due to an administrative block. Ask the owner of the spreadsheet to grant you permissions, then try again. You will also be unable to access this menu if the spreadsheet is being shared; simply stop sharing to access it.
Information in an Excel spreadsheet dropdown menu isn’t displaying correctly
If you discover that some of your items aren’t appearing or you notice that your list doesn’t appear at all, you should refresh the spreadsheet first and see if that fixes it. If this does not work, you may be dealing with a corrupted workbook. If this is the case, you’ll need to repair the file. First, from the base Excel program, click the “File” menu, then hover over “Open”.
Select “Open and Repair…” then select your file. This should start the automatic recovery process for the workbook. If this doesn’t work, there are a few more options you can try, including extracting the data from your workbook to create a new, uncorrupted file.
Conclusion
Creating and using dropdown menus in your spreadsheets can help you organize and analyze data much more effectively. By creating and managing these menus, you’re restricting inputs to exactly what’s needed and preventing unnecessary confusion and overcrowding that duplicate entries can cause.
As long as you understand how these menus are created and what to do when they don’t function properly, you can use them to make your spreadsheets more streamlined and effective. This can make your work days significantly easier and improve your document workflow.
