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.
#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 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.”
- This will open the data validation tool window.
- 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.
- 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.
- 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 next values as “Not Agree.”
- Click on “Ok,” and the list of values will appear in the form of the “drop-down” list.
#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 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.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
- Enter all the values in the cells.
- Now once again, open data validation and chose the validation type as “List.”
- In the source, the box places your cursor and selects 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 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.
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 excel.
- In the above example, we have values from A1 to A5, so we will name this range as “Months.”
- Now select the cell where we need to create a list and open drop-down list.
- Now place your cursor in the “Source” box and press the F3 key to an open list of named ranges.
- 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.
- Click on “Ok,” and the drop-down list is ready.
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.
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 –