Many users of Microsoft's standard office suite clearly underestimate the Excel spreadsheet editor. A drop-down list in a cell, saving time spent on entering or searching data, is created in it 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 dropdown lists for?
Lists of this type themselves imply a wide scope. In one cell, you can enter not one value, as is done by default, but several.
There are two input options:
- Only the values ​​from the generated list.
- Values ​​from the list and any other data.
So, we begin work with Excel. Creating a drop-down list here is quite simple. But first, you need to perform a few preliminary steps, so that ultimately the whole procedure comes down only to the choice of the method of entering the necessary data.
How to make drop-down lists in Excel: general technique
The initial stage provides access to the 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, select one or several cells in which it is supposed to enter some range of values, and right-click on the context menu. We use the “Data” section first, then the “Data Check” item. And go to the options tab.
It has a test condition (criterion) with a data type string, where we select the “List” value. In principle, the remaining fields can still be left unchanged. A little lower is the line where the source of values ​​will be entered. Here he also interests us.
Drop-down list in Excel 2007: manual data entry
Let's start by manually entering values. If we talk about how to make drop-down lists in Excel using this method, the solution is to write 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 an English assignment, only a comma is used. The disadvantage of this method is obvious: it takes too much time.
Source out of range
In the second case, you can use a faster solution. For example, we need to enter all the values ​​in the cells: starting from 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 of "(A2: A4)", as is done in the corresponding line of formulas, but in the option "= $ A $ 2: $ A $ 4".
The method is really good for its speed of inserting values. But as the most important drawback, it is possible to note the impossibility of using the ranges of cells located on sheets other than the currently active one.
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. And after that, in the source line you just need to enter its name, putting an equal sign in front of it. This technique allows you to create lists using data and values ​​that are located on different sheets of the book. And just such an option is mainly used in versions of the Excel 2007 editor and below, since these modifications do not know how to work with ranges in which data from different sheets is indicated.
Manage List Options
As for list management, the Excel editor has many useful features. But the most, perhaps, the most interesting is 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, in the data verification section, which was used in all previous methods, we turn to the "Messages for input" tab. Here you can enter a hint that will be displayed when the corresponding cell is activated. You can turn off the message by simply unchecking the item to display it. On the error tab, you can enter a warning about incorrect data entry. This is useful in the absence of stringent conditions for monitoring values ​​or in case of partial changes.
Conclusion
As you can see from the above, the problem of how to make drop-down lists in Excel is not so difficult to solve. The question is different: which method to use? If there is not much data, the simplest option is suitable. The second method is more common. For the average user, it is best suited. But if the user’s knowledge of the program is not limited only to the use of the simplest functions, here it will be optimal to create named lists with the subsequent input of data ranges from them. Because in the most named list, changing is quick and easy. In general, you can choose the most convenient way for yourself without any problems.