How to create drop-down menus is Google Sheets to limit the content of a cell

With Google sheets drop-down menus, you can limit the content of a cell only to a few entries, so that nobody can type in whatever they want, into the cell. This can be useful if you know, what the contents of a cell can be.

Working with financial or any other type of data is easy on spreadsheets, and most of us use it almost every day for various needs. While we all use spreadsheets only for entering data, and do some basic calculations, it isn’t just limited to that. We can actually carry out a lot of other activities on spreadsheet programs, and some of the unknown functions can actually turn out to be useful for us, under different circumstances. While most people find it easy to work with Microsoft Excel, when it comes to dealing with spreadsheets, Google Sheets can be equally mighty, as well. It comes with everything that you might need.

One of the most common tasks, which most people don’t know about Google Sheets, and most other spreadsheet programs, is the ability to create and deal with drop-down menus. For example, a cell will contain either true or false, credit or debit, any number between 1 and 10, or any other limited set of values. Creating drop-down menus in Google Sheets is quite easy, and I will discuss how to do that here.

So, without any further delay, let’s get started with the tutorial.

The steps to follow

Open a new spreadsheet in Google Sheets, where you want to create a drop-down menu, or your existing sheet, whichever is convenient for you, and create a list of items cellwise that will be available in the drop-down menu.

So I have created a list with the 12 months. Now select the cell, where the drop-down menu will be generated.

Now click on ‘Data’ in the menu-bar and click on ‘Data validation’.

Data validation

In the ‘Data validation’ window, select ‘List from a range’ as the criteria, and tap on the ‘Select data range’ icon, as shown in the image below.

List from a range

Now select the range of cells, where the list is present. For me, it is the list of 12 months that I have entered. Alternatively, you can manually enter the range of cells where the list is present. After selecting the range, click on ‘OK’.

Drop down menu in Sheets 3

Now mark ‘Show dropdown list in cell’, which should, however, be marked by default. 

Depending upon the type of data, you can even reject any invalid data or show warning for the same. Finally, click on ‘Save’.

Show Google sheets drop-down menus list in the cell

Now, click on the small down arrow beside the cell to see the list of valid items for that particular cell.

Drop down menu in Sheets 5

If you doubt, somebody might change the contents in the list, there is another method, as well.

Just choose the cell, where the drop-down menu will be generated, and open ‘Data validation’ in the same way.

Select ‘List of items’ as the criteria, and enter the list of items that will be present in the drop-down menu, in text-box corresponding to it, each separated by a comma. Once done, change the other necessary settings, and finally click on ‘Save’.

Save Google sheets drop-down menus in Sheets 6

Now just click on the down arrow corresponding to the cell, to find the list of valid items for the cell.

Google sheets drop-down menus

If you want to remove data validation and revert to the original state of the cell, just select the cell, open ‘Data validation’ once again, the usual way, and click on ‘Remove validation’.

Remove validation

The cell is back to its original state. Besides the two data validation criteria that I have discussed here, there are other criteria, as well. But the two criteria for data validation that I have mentioned here, are the most-used ones, and chances are there, you will frequently use it to make your everyday tasks a lot easier.

The data validation feature is not just limited to Google Sheets. The same feature is available in Microsoft Excel, LibreOffice, and other popular Office productivity apps, as well.

So that all in this tutorial. Do you have any questions in mind? Feel free to write it in the comment section below.

Other Articles to Read: