How in Excel to make drop-down lists: the simplest methods

Many users of the standard office suite from Microsoft clearly underestimate the Excel spreadsheet. A drop-down list in the cell that saves time spent on entering or searching for data is created in a couple of seconds, unlike Access, where this procedure is more complicated. Below are three main methods for creating such lists and some important parameters for managing them.

What are drop-down lists for?

The lists of this type assume a wide range of applications. In one cell, you can enter more than one value, as is done by default, but several.

Input options can be two:

• Only values from the created list.
• Values from the list and any other data.

So, we start working with Excel. Create a drop-down list here is simple enough. But first, you need to perform several preliminary actions, in order to ultimately reduce the whole procedure to choosing the method of entering the required data.

How to make drop-down lists in Excel: the general procedure

The initial stage provides access to a function that allows you to perform this procedure. How to make drop-down lists in Excel based on the standard features of the program? Very simple. First we select one cell or several, in which we are supposed to enter a range of values, and right click call the context menu. In it, we first use the "Data" section, then the "Data verification" item. And go to the options tab.

In it there is a test condition (criterion) with a data type string, where we select the value "List". In principle, the remaining fields can still be left unchanged. Below is the line where the source of values will be entered. Here it is he who interests us.

Drop-down list in Excel 2007: manual data entry

Let's start with the manual input of values. If we talk about how to make drop-down lists in Excel in this way, the solution is to assign the values yourself in the above field. It should be noted that for Russian-language localization, you need to enter values through a semicolon. For English assignments, only a comma is used. The inconvenience of such a method is obvious: it takes too much time.

Source from the range

In the second case, you can use a faster solution. For example, we need to enter all the values in the cells: starting with A2 and ending with A4. In the line indicating the source, put the cursor. And then just select the desired range. The program itself will put an equal sign, but will write the value not in the form "(A2: A4)", as it is done in the corresponding line of formulas, but in the variant "= $ A $ 2: $ A $ 4".

The method is really good at its speed of inserting values. But as the most important drawback is the impossibility of using ranges of cells located on other sheets, except for the active at the moment.

Entering named ranges (lists)

Finally, another method to solve the problem of how to make drop-down lists in Excel. First, the range itself is created with the appropriate name assigned. And then in the source line, you just need to enter its name, putting an equal sign before it. This technique allows you to create lists using data and values that are located on different pages of the book. And this option is mainly used in the versions of the Excel 2007 editor and below, because these modifications do not know how to work with ranges in which data from different sheets is specified.

Manage list options

As for managing lists, there are a lot of necessary functions in the Excel editor. But most, perhaps, interesting - the distribution of parameters for input with the same conditions. It is used in cases where there are too many checks on the sheet and their conditions need to be changed. To do this, refer to the "Messages for input" tab in the data verification section that was used in all previous methods. Here you can enter a hint that will be displayed when the corresponding cell is activated. The message can be turned off and simply by removing the check mark from the point of its display. On the error tab, you can enter a warning about the incorrectness of the input data. This is useful in the absence of strict conditions for monitoring values or if they are partially modified.

Conclusion

As you can see from all of the above, the problem of how to make drop-down lists in Excel is not so difficult. The question is: which method to use? If there is not much data, the simplest version will do. The second method is more common. For an ordinary user, it will work best. But if the user's knowledge of the program is not limited only to using the simplest functions, it will be optimal to create named lists with subsequent input of data ranges from them. Since in the named list the change is quick and easy. In general, you can choose the most convenient way for yourself without problems.