What is an Excel drop-down list and how to use it?

What is an Excel drop-down list?

An Excel drop-down list is a data validation function that allows users to select an option from a list of choices defined in the code list (list of options). Drop-down lists enable controlled data entry in certain cells in the table, which are related to Data validation with the “data validation” function.

By using code lists in the form of a drop-down list, data entry is unified and all users enter the same records. the values specified in the code list. All other manual entries that would be incorrect are thus prevented.

Advantages of drop-down lists:

  • Allow the entry of data that are on the list and are allowed to be entered, but prevent the entry of data that are not on the list.
  • With this method of data entry, the user can not be mistaken and write something manually
  • With the help of drop-down lists or code lists, we speed up the process of entering data into a dynamic table. All possible options must be on this list, so there is no doubt about what needs to be entered. There is no need to constantly check the correctness of the data

A drop-down list can be formed by limiting a cell value e.g. by date, by words, by list, by character length, by the hour, by an integer or decimal number, and any settings. You can also specify incoming messages in the drop-down list, e.g. a mandatory entry message or an error warning.

Where to use drop-down lists?

You can use Drop-down lists or code lists wherever you want to unify and automate data entry in an Excel table. This feature is relevant for several use cases in different industries. If the data is not unified, you will not be able to produce a correct report from the tables or show the correct results. Therefore the use of code lists is strongly recommended.

Some examples from practice: quality control, sales planning, production, purchasing, controlling, technical drawings and characteristics, various measurements in work processes, planning of marketing campaigns, HR and recruitment planning, education, and obtained certificates….

How to use drop-down lists?

In most use cases, you can create a single-level code list, but you can also create a two-level or multi-level code list. This comes into play in the matrix of qualifications of employees in production because the managers usually want to keep track of which work operations the employee is qualified and allowed to perform.

Drop-down lists can also be designed in a way that users from different departments only see their data and not data from other departments. Since drop-down lists sometimes also have outdated values, you can simply format the list so that old values are automatically eliminated, and only actual values are listed.

A larger, main drop-down list can be compiled from several smaller common lists/code lists. Such an advanced code list is used e.g. in financial controlling, where it is necessary to compile a single code list of accounts, work orders, groups, cost centers, and other variables.

Learn more about drop-down lists and code lists in our Excel in Practice courses. The best course to start with is Projects 1 › while Projects 3 › is the best practice. With Package 1 › courses you can learn also everything you need to connect data on the local network from other systems