How to make a drop down list in Excel

Microsoft Excel

If we talk about spreadsheets, we have to talk about Excel, an application that hit the market in 1985, but did not become a reference in the market until 1993, when it surpassed the almighty Lotus-1-2-3. Today Excel is jointly and inseparably integrated into Office 365.

Over the years, Excel has only improved, offering a large number of solutions, solutions for both companies and individuals. One of the functions that it offers us, both for users and for companies, is the possibility of create dropdown lists, a very useful function that we teach next.

Excel is available for both Windows and macOS and via the Web, in full versions. Although it is true that we have a version available for mobile devices, this it is not so complete like the one we can find in the desktop versions. The steps to follow to create a drop-down list in Excel are the same in Windows, macOS and via the Web versions.

Although they can only be created through the desktop versions of Excel, these they can be consulted and interacted with with any version of Excel, including the reduced version that Microsoft offers us through the Office application for mobile devices, a completely free application.

What are drop-down lists

Excel drop-down list

The drop-down lists, allows us select from a list of options only one option, excluding the rest. This type of list allows us to use default values ​​avoiding the entry of erroneous data or with spelling mistakes (which allows us to carry out specific search filters).

In companies, these lists allow you to organize and manage your day-to-day tasks and management in a much more efficient way, as well as offering a professional touch that never hurts. The number of drop-down lists that we can create is unlimited, so we can create a list box for each of the cells on a sheet.

Rows - pivot tables in Excel
Related article:
How to make a pivot table in Excel without complications

These types of lists are very useful when creating invoices (where each concept is different from the previous one), track visits, create databases to apply custom filters that allows us to control stocks in warehouses ... If you have reached this article, it is most likely that you are clear about the use that you intend to give to this fantastic Excel function.

How to create drop down lists in Excel

The drop-down lists obtain the data from tables that we must previously create to use as a source. If the purpose of the sheet we want to create the drop-down lists is to print it, we must set the data source to another separate sheet, a sheet that we can call Data.

As I have commented above, in the same sheet we can create infinite drop-down lists, so if we do not want to create a sheet for each data source, we can use the same sheet, without eliminating the data that we have served as a source for the lists that we have already created. Once we are clear about how they work, we show you the steps to follow to create drop down lists in Excel.

Create data source

Excel data source

The first thing we must do is create the data source, data that is used to create the drop-down lists. If we did not previously create this data, the drop-down list they will have nothing to show. To create the data source, we open a new sheet in Excel, double-click on the name and we will name it Data.

In order not to get involved with which are the data sources of each of the drop-down lists that we want to create, we must write as first value the name of the list, whether cities, models, countries, clothing ... If we are only going to create a list, it is not necessary to write the name in the first cell.

Next, we have to write all the options that we want are displayed in the drop-down list, one below the other in the same column to make it easier to select the source of the data. Once we have created the source of the data, we can create the drop-down lists.

Create drop down list

Excel drop-down list

  • First we select the cells where we want the dropdown lists to be displayed.
  • Next, click on the Data option (not sheet) on the ribbon. Within options, click on Data validation.

Set up lists in Excel

  • Within the Configuration tab> Validation criteria> Allow we select List.
  • Next we go to the Origin box and click on the icon at the end of the box to select the range of cells where the data is located.

Cells where Excel ranges are found

  • Next, click on the Data sheet and we select the range of cells where the data is located, leaving out the name of the cell that has allowed us to identify this data. Once we have selected the data range, we press Enter.

Excel

  • We have already created our first drop down list on the main Excel sheet. In all the cells that we have selected to show a drop-down list, a downward arrow is now displayed that invites us to press to select from all options that we have previously established in the Data sheet.

Once we have created the first drop-down list, we must perform the same process to create the rest of the drop-down lists that we want or need.


Be the first to comment

Leave a Comment

Your email address will not be published. Required fields are marked with *

*

*

  1. Responsible for the data: Actualidad Blog
  2. Purpose of the data: Control SPAM, comment management.
  3. Legitimation: Your consent
  4. Communication of the data: The data will not be communicated to third parties except by legal obligation.
  5. Data storage: Database hosted by Occentus Networks (EU)
  6. Rights: At any time you can limit, recover and delete your information.