WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to insert drop down list excel

July 25, 2022
9.1K Views

If you are a person who is used to working with very recurring data within Excel, it is of great benefit to you how to insert drop down list excel of all its available forms. These will allow you to establish the corresponding list of the data you will use in several sheets within the same Excel system, also considering repetitive lists.

If you use databases that are constantly repeated or you must pay a list of accounts that is usually the same, knowing how to insert drop down list excel will save you much time. Learn about the available ways to achieve it with any operating system or internet connection platform.

How to insert drop down list excel in Windows?

Insert drop down list excel is one of the simplest and most intuitive procedures, so the following steps are easy to understand:

1. Enter the inputs you want into your dropdown list within a new worksheet. You can quickly convert the list to a table by giving a selection to any cell and combining Ctrl+T.

2. Choose the cells in which you want the dropdown list.

3. Go to the Data tab in the top menu of Excel, then go to Data Validation.

4. Go to Settings, then click Allow and then List.

5. Enter Source to select the list range, placing the name and the ranges it belongs to. Remember to skip the header row. Check the dropdown box in the cell.

How to insert drop down list excel Mac?

While insert drop down list excel with Windows may be simple, Mac is on the same page in creating this list for quick and accurate data entry with the following steps:

1. Make a list of specific entries for the dropdown list, which will be written in a single column and without empty cells.

2. Select the cells you want to restrict the data entry.

3. Enter the Data option in Tools to select Data Validation or Validate.

4. Enter Settings and Allow in the menu presented, then click on list. Select the Source section and select the list of valid entries in the spreadsheet.

5. Press Return or click Expand to expand the data validation while restoring the dialog box, ending by selecting OK.

How to insert drop down list excel online?

You do not necessarily have to have the Microsoft Office package to be able to insert drop down list excel. You can do it online with Google tools in the following way:

1. Go to a new spreadsheet and type the entries you want in the dropdown list. Choose the worksheet you want to have the dropdown list.

3. Go to Data on the top toolbar, then select Data Validation.

4. Go to Settings and click on list in the Allow box / go to Source and drag the cells that have the input cells but don't include the header and check the dropdown box in the cell.

5. Click OK.

Accounts, now, with all the opportunities to insert drop down list excel in creating database sheets that you can send to others who must fill in the blanks. Everything is based on a system of rules, with you having the opportunity to create specific data that will later be filled in the system in a previously established order.

Trustpilot

stars

WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User

avator

Algirdas Jasaitis

Advanced Techniques and Tips

Integrating Dropdown Lists with Other Excel Features

To integrate dropdown lists with other Excel features, follow these steps:

Step 1: Select the cells where you want to create the dropdown list.

Step 2: Navigate to the Data tab in the ribbon and click on Data Validation.

Step 3: In the Data Validation dialog box, choose "List" from the dropdown menu under the "Allow" section.

Step 4: In the "Source" field, enter the items you want to appear in the dropdown list, separated by commas. You can also select a range of cells or a named range as the source.

Step 5: Click OK to close the dialog box.

Once you have created the dropdown list, you can utilize it in various ways within your Excel sheet. For instance, you can create dependent dropdown lists, where the available options in one dropdown list depend on the selection made in another dropdown list. Additionally, you can use the dropdown list to generate dynamic charts and tables that automatically update based on the selected item in the dropdown list.

Edit a Drop-down List in Excel From a Cell Range

Step 1: Open the excel sheet with the drop down list.

Step 2: Go to the Home tab and click on Data Validation

Step 3: visit the Source box and edit the cell range. Users can add or remove cells to edit the drop down list. 

Excel edit drop down list cell range

FAQs

Q1. Why is My Dropdown List Not Working and How Can I Fix it?

Drop-down list might act faulty due the following reasons:

  • Objects are Hidden: Check for any hidden objects that may be covering the dropdown list.

  • In-cell dropdown is Unchecked: Enable the "In-cell dropdown" option for the cells where the dropdown list should appear.

  • Bug in Windows Update: Windows updates can sometimes cause bugs in Excel, affecting features like dropdown lists. Check for Excel updates or restart your computer to resolve any known issues.

Q2. How Do I Copy a Dropdown List to Another Cell or Worksheet?

Select the cell(s) that contain the dropdown list you want to copy. Right-click on the destination cell and choose "Paste Special" from the context menu. In the Paste Special dialog box, select "Validation" and click OK. This will paste the dropdown list to the new cell, retaining the same validation settings.

Q3. How do I add a drop-down list in WPS Office (Excel)?

Step 1: Open WPS Spreadsheet and create a data course for the Drop down list.

Step 2: Visit the Data tab and select “Validation”

Step 3: Select the "settings" tab on the Validation dialogue box, expand the "Allow" drop down menu and select “List”

Step 4: Select the range where the drop down menu is needed and click “Ok”: 

Q4. How do I add a double dependent drop down list excel?

To add a double dependent drop-down list in Excel, you can follow these steps:

  • Create separate tables for each level of dependency. For example, if you have three columns A, B, and C, where B depends on A and C depends on B, create separate tables for each column.

  • Select the data in each table and define a named range for each table. This can be done by selecting the data and going to the "Formulas" tab, clicking on "Define Name," and giving a name to the range.

  • Select the cell where you want the first drop-down list and go to the "Data" tab. Click on "Data Validation" and choose "List" from the "Allow" dropdown. In the "Source" field, enter the named range for the first table.

  • Select the cell where you want the second drop-down list and go to "Data Validation." Choose "List" from the "Allow" dropdown and enter the following formula in the "Source" field: =INDIRECT($E$2). Here, $E$2 refers to the cell where the first drop-down list is located.

Mastering Drop-Down Lists in Excel

Learn how to create drop-down lists in Excel, and then transform the way you handle your data. This comprehensive guide equips you with the essential knowledge and skills to seamlessly add drop-down lists, simplify data entry, enhance accuracy, and boost efficiency in your Excel workflows. To achieve more with Excel sheets, users are recommended to experience the advanced excel functionalities offered by WPS Office. 

With an intuitive interface, advanced tools to solve complex calculations and conduct effective data analysis with ease, WPS Office is the go to Office suite for all your excel needs. Download WPS Office today and excel in your respective fields!

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.