Technology

How to Create a Drop List in Excel – Easy Steps for Office Workers

Knowing how to create a Drop List in Excel can significantly improve your work productivity. In this article, FPT Shop will provide you with a detailed guide on how to create a Drop List in Excel that is extremely easy to follow. Let's find out now!

Administrator

8 January, 2024

153

If you’re a regular user of Excel on your computer, you’ve probably created drop-down lists before. But do you know how to use Drop Lists or have trouble creating them? Let’s take a look at how to create Drop Lists in Excel in a super simple way in the following article from FPT Shop!

What are Drop Lists? Benefits of creating Drop Lists in Excel

Drop Lists (or commonly known as dropdown lists) are a function that creates a dropdown list in a column with repetitive objects. The Drop List feature is widely used by Excel users due to its convenience and accuracy. Here are some prominent benefits of Drop Lists:

Easily create necessary input categories. Save time retyping data.
Appropriate for locations that need information about names, professions, places of residence, etc.
Support businesses in declaration, timekeeping.
Help manage inventory and warehouse tasks.
Reduce errors in information input and spelling.

How to create Drop Lists in Excel

Case when there is existing data in the spreadsheet

Step 1: In the Excel file, select the cell where you want to create the Drop List. Then, select the Data tab. Next, select Data Validation.

Step 2: On the Settings tab, select List in the Allow section. Go to the Sources section, select the data you want to create the Drop List from. Press OK to complete the process of creating a Drop List in Excel.

Case when creating Drop List by manually entering data

Step 1: Select the cell where you want to create the Drop List. Then, select the Data tab. Next, select Data Validation.

Step 2: Select the Settings tab in the dialog box. In the Allow section, select List. Go to the Sources section, enter the names of the components you want to create the Drop List. Finally, press OK to complete the process of creating a Drop List in Excel.

Case when creating Drop List dependent on another list

Step 1: Select the data range in the Product Name column. Then, select the Formulas tab and select Define Name.

Step 2: At this point, the New Name dialog box will appear on the screen. In the Name field, enter a name for the data range. Then, select OK to save the changes.

Note: The data range name must match the column header, but without spaces.

In this example, the Name will be PRODUCT NAME.

Step 3: Select the cell where you want to create the list. Then, press the Data tab and select Data Validation.

Step 4: In the dialog box that appears, select the Settings tab. Select List in the Allow section.

Step 5: In the Sources section, enter the following formula: =INDIRECT(SUBSTITUTE(F3,” “,””)). Finally, press OK.

How to customize the data of a created list in Excel

So now you have learned about how to create Drop Lists in Excel. However, you may need to customize or change the data in the list. Here are some instructions for making some other customizations once you have created a list.

Edit or delete a created data range name

Step 1: First, select the Formula tab. Then, click on Name Manager.

Step 2: When the dialog box appears, click on the data range name you want to select. Finally, choose the desired customizations as follows:

Select Edit: If you want to edit the data range name.
Select Delete: If you want to delete this data range name.

Copy and paste a Drop List

Step 1: Click on the cell that has the created Drop List. Then, right-click to select Copy (or press the combination of keys Ctrl + C).

Step 2: Select the cell where you want to paste the Drop List. Then, right-click and select Paste Special.

Step 3: In the dialog box that appears, select Validation in the Paste section. Finally, press OK to complete the process of copying and pasting a Drop List.

How to delete a Drop List in Excel

In case you create a Drop List incorrectly or no longer want to use it, you can delete the created Drop List with just 2 simple steps as follows.

Step 1: Select the cell with the created Drop List. Then, click on the Data tab and select Data Validation.

Step 2: When the dialog box appears, select the Clear All button and then press OK to delete the Drop List in Excel.

Conclusion

Above are the detailed information about how to create Drop Lists in Excel that FPT Shop wants to share with you. Hopefully, this article will help you apply Excel effectively and quickly, save time in work and study, and create professionalism in file management, avoiding unnecessary errors.

Guide on 4 ways to transfer data from one table to another in Excel in detail
How to detect and fix number formatting in Excel quickly in just 5 minutes

FPT Shop is a reputable retail system for modern technology products such as laptops, phones, tablets,… and a range of other quality electrical appliances. If you are looking for a good value laptop to use Excel and other office computing software, visit FPT Shop. Check out great deals on laptops here.