Excel Create List

Create List in Excel

Often time when we need to collect data from others, they may write different things from their perspective, but at the end of the day, we need to make all the related stories under one and also it is the common thing 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 enter “YES” like this, we may end up getting a different kind of results. So in these cases, creating a list of values as pre-determined values and allow the users only to choose from the list instead of users enter their own values, so in this article, we will show you how to create a list of values in excel.

You can download this Create List Excel Template here – Create List Excel Template

#1 – Create a Drop-Down List in Excel

We can create a drop-down list in excel using the “Data Validation in excelData Validation In ExcelThe data validation in excel helps control the kind of input entered by a user in the worksheet.read more” tool, so as the word itself says, data will be validated even before the user decided 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 to choose “Data Validation.”
Using Drop Down List 1
  • This will open the data validation tool window.
Using Drop Down List 1-1
  • By default, the “Settings” tab will be shown, 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.
Using Drop Down List 1-2
  • For this list, we can give a list of values to be validated in two ways; one is by directly entering the values in the “Source” list.
Using Drop Down List 1-3
  • Enter the first value as “Agree.”
Using Drop Down List 1-4
  • 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 next values as “Not Agree.”
Using Drop Down List 1-5
  • Click on “Ok,” and the list of values will appear in the form of the “drop-down” list.
Using Drop Down List 1-6

#2 – Create List of Values from Cells

The above method is just fine to get started but imagine the scenario of creating a long list of value or your list of values changing every now and then, and then it will get difficult to go back 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 list from cell values.

  1. Enter all the values in the cells.

    Create List Example 2

  2. Open data validation and chose the validation type as “List”.

    Create List Example 2-1

  3. In the source box, places your cursor and select the list of values from the range of cells A1 to A5.

    Create List Example 2-3

  4. Click on “Ok”, and we will have the list ready in cell C2.

    Create List Example 2-4
    So values to this list are supplied from the range of cell A1 to A5; any changes happen in these referenced cells will impact the drop-down list as well.

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

Create List Example 2-5

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

#3 – Create List through Named Manager

There is another way we can create a list of values in excel, i.e., through named ranges in excelNamed Ranges In ExcelName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window.read more.

  • In the above example, we have values from A1 to A5, so we will name this range as “Months.”
Create List Example 3
  • Now select the cell where we need to create a list and open drop-down list.
Create List Example 3-1
  • Now place your cursor in the “Source” box and press the F3 key to an open list of named ranges.
Create List Example 3-2
  • As you 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.
Create List Example 3-3
  • Click on “Ok,” and the drop-down list is ready.
Create List Example 3-4

Things to Remember

  • Shortcut key to open data validation is “ALT + A + V + V”.
  • Always create a list of values in the cells so that it will impact the drop-down list if any change happens in the referenced cells.

Recommended Articles

This has been a guide to Excel Create List. Here we learn how to create a list of values in excel also create a simple drop drown method and create a list through name manager along with examples and downloadable excel templates. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion