Excel Create List

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

Create a List in Excel

When we need to collect data from others, they may write different things from their perspective. Still, we need to make all the related stories under one. Also, it is common that while entering the data, they make mistakes because of typo errors. For example, assume in certain cells, if we ask users to enter either “YES” or “NO,” one will enter “Y,” someone will insert “YES” like this, and we may end up getting a different kind of results. So in such cases, creating a list of values as pre-determined values allows the users only to choose from the list instead of users entering their values. Therefore, in this article, we will show you how to create a list of values in Excel.

Create List in Excel

#1 - Create a Drop-Down List in Excel

We can create a drop-down list in Excel using the “Data Validation in excel” tool, so as the word itself says, data will be validated even before the user decides to enter. So, all the values that need to be entered are pre-validated by creating a drop-down list in Excel. For example, assume we need to allow the user to choose only "Agree" and "Not Agree," so we will create a list of values in the drop-down list.

In the Excel worksheet under the "Data" tab, we have an option called "Data Validation" from this again, choose "Data Validation."

As a result, this will open the "Data Validation" tool window.

The "Settings" tab will be shown by default, and now we need to create validation criteria. Since we are creating a list of values, choose "List" as the option from the "Allow" drop-down list.

For this "List," we can give a list of values to be validated in the following way, i.e., by directly entering the values in the "Source" list.

Enter the first value as "Agree."

Once the first value to be validated is entered, we need to enter "comma" (,) as the list separator before entering the next value. So, enter “comma” and enter the following values as "Not Agree."

After that, click on "Ok," and the list of values may appear in the form of the "drop-down" list.

#2 - Create a List of Values from Cells

The above method is to get started, but imagine the scenario of creating a long list of values or your list of values changing now and then. Then, it may get difficult to return and edit the list of values manually. So, by entering values in the cell, we can easily create a list of values in Excel.

Follow the steps to create a list from cell values.

We must first insert all the values in the cells.
Then, open "Data Validation" and choose the validation type as "List."
Next, in the "Source" box, we need to place the cursor and select the list of values from the range of cells A1 to A5.
Click on "OK," and we will have the list ready in cell C2.

So values to this list are supplied from the range of cells A1 to A5. Any changes in these referenced cells will also impact the drop-down list.

For example, in cell A4, we have a value as “Apr,” but now we will change that to “Jun” and see what happens in the drop-down list.

Now, look at the result of the drop-down list. Instead of “Apr,” we see “Jun” because we had given the list source as cell range, not manual entries.

#3 - Create List through Named Manager

There is another way to create a list of values, i.e., through named ranges in excel.

We have values from A1 to A5 in the above example, naming this range “Months.”

Now, select the cell where we need to create a list and open the drop-down list.

Now place the cursor in the "Source" box and press the F3 key to an open list of named ranges.

As we can see above, we have a list of names, choose the name "Months" and click on "OK" to get the name to the "Source" box.

Click on "OK," and the drop-down list is ready.

Things to Remember

The shortcut key to open data validation is “ALT + A + V + V."
We must always create a list of values in the cells so that it may impact the drop-down list if any change happens in the referenced cells.