How to create drop down list to select in excel

This article will guide you how to create a drop down list - a drop down list to select data in excel extremely useful and handy.

In the process of working, accountants have to enter a lot of data, especially those who do warehouse, when entering the detailed sales list, you will have to type each item, so it takes time, sometimes Left many mistakes. Therefore, within the framework of today's article, TipsMake will guide you how to create drop down lists - drop down lists to select data in excel. It will be very convenient, faster and more accurate.

This function is called creating a drop-down list by Data validation, it will provide a lot of utilities for us in the working process. For example, creating a dropdown list for customers to select the item, choose yes or no, the accountant chooses the item code, select a date from the list, etc., etc. useful.

For example, in the picture, TipsMake has a detailed list of sales invoices including product code, product name, . The item code we will have to get from the list of goods, and the goods name, you can use the Vlookup function in Excel to reference projection out. Now instead of typing each code one by one, it is very likely to be confused, you can apply the following method:

How to create drop down list to select in excel Picture 1How to create drop down list to select in excel Picture 1

1. You put your mouse in the first cell of the column of product code, then on the Data tab on the menu bar select Data Validation.

2. After the Data Validation table appears, select Tab Settings, in the Allow section select List. Other parameters remain unchanged.


How to create drop down list to select in excel Picture 2How to create drop down list to select in excel Picture 2

3. In the Source section, scan the column of product code in the List of goods like TipsMake as shown in this picture. Or you can name the data area first by using the Define Name function. Then click OK.

How to create drop down list to select in excel Picture 3How to create drop down list to select in excel Picture 3

And here is the result, as you can see, there is now a drop down arrow for you to choose.

How to create drop down list to select in excel Picture 4How to create drop down list to select in excel Picture 4

- Note: In case you can not perform the data validation function, it could be due to a number of reasons as follows:

+ Drop-down lists cannot be created from a table but linked to Office SharePoint tools. If you have a link to this tool, remember to unlink and delete the format, and you do not use it.

+ Because your spreadsheet is in protected mode, for example, the spreadsheet that has been applied the 'password protection for excel' function is not editable or manipulative. The solution is to remove that protection mode and continue to perform the above steps.

- After creating the drop-down list, make sure it works the way you want. Also make sure that the width of the cells in the data validation drop-down box is wide enough to display all the information. If you want to prevent others from impacting on this data. Find a way to hide excel data.

Good luck!

5 ★ | 1 Vote