Kiến thức

How to work with drop down lists in MS Excel-Master Data Analysis

6 Comments

How to work with drop down lists in MS Excel

Have you ever wanted to create a drop down list in Excel to avoid data entry errors or to make the data entry process easier?

In this post I’ll cover a whole range of tips on how to work with drop down lists in Excel including:

Selections from dropdowns - Excel

  • How to create/modify drop down lists

  • Create lists using information from other worksheets

  • How to remove lists from a range

  • How to copy lists from one cell to another

  • Display a tooltip when a cell with a list is selected and when a wrong value is entered

  • How to deal with a workbook with multiple different lists

  • Create an expandable list

  • Create dependent drop down lists

Excel has a feature called Data Validation that you can use to make sure the users of your spreadsheets enter certain values in the cells. The lists (or drop down lists) are a type of data validation used to make sure the users enter a value from a set of options.

Bạn đang xem: How to work with drop down lists in MS Excel-Master Data Analysis

How to create/modify drop down lists

To create/modify a drop down list:

  • Select the cell(s) where you want to create/modify the drop down list
  • Go to the Data tab and click on the data validation button

Data Validation Tab - Excel

In the Data Validation dialog box click in the validation criteria selection button and select ‘List’:

Excel Data Validation Criteria - List

If you’re modifying an existing list, the validation criteria type will be preselected with the existing type.

  • Enter the source for the list:
    1. The source can be entered manually, using commas to separate the options:

Dropdown - Manual List

or it can be entered by selecting a range that contains the options you want in your list.

Dropdown - Select Range

Back to top

Create lists using information from other worksheets

If you’re using Excel 2007 or later you can create a drop down from a source range that is located in another worksheet.

To create a list from a range in another worksheet, in the Data Validation dialog box press the range selection button range selection excel,  and go to the desired range in the other worksheet.

Excel Data Validation - Select from another worksheet

Back to top

Xem thêm: Cách Dùng Giới Từ AT, ON, IN Để Chỉ Nơi Bạn Sống-featured

How to remove drop down lists

  • Select the cell(s) where you want to create/modify the drop down list
  • Go to the Data tab and click on the data validation button
  • Click on Clear All

Excel Data Validation - Clear All

Back to top

How to copy lists from one cell to another

  • Select a cell that contains the drop down list you want to copy
  • Copy the cell by pressing Ctrl + C or Right-click -> Copy
  • Select the cells where you want to paste the drop down list
  • Right-click, select paste special, click on Validation and press OK.

Note: You can also do a regular Copy/Paste operation to paste the list. However, if you copy a cell with a value or formula they will be pasted along the drop down.

Back to top

Xem thêm: TÓM TẮT LÝ THUYẾT VÀ BÀI TẬP CHUYỂN ĐỘNG CƠ-CHUYỂN ĐỘNG THẲNG ĐỀU

How to deal with a workbook with multiple different lists

When creating a workbook with multiple different list one of the recommended approaches is to create a worksheet specifically for all the list sources. For example, you can create a worksheet called ‘List Sources’ and place there all the sources for your lists.

In addition, is a good practice to name the lists so you can easily refer to them using their name. To name a list, select the range that contains the list elements and enter the corresponding name in the name box.

Excel Data Validation - naming ranges

Then you can use the names of the lists in the Data Validation dialog box

Excel Dropdown - Referencing Range names

Back to top

To help the users of your worksheets you can display a tooltip when they select the cells that contain a drop down list or any other type of data validation.

Once you’re in the data validation dialog box, click on the Input Message and enter the title and message you want to display.

Excel Data Validation - Input Message

The tooltip will look like this

Data Validation Tooltip

To show a message when the user enters a wrong value in the cell:

Go to the Error Alert tab within the Data Validation dialog box
Enter the title and error message you want to display

Excel Data Validation - Wrong Entry Dialog Box

The error message will look like this:

Excel Data Validation - Wrong Entry Message

Back to top

Xem thêm: Thức mệnh lệnh (Imperative Mood) trong tiếng Anh-Ucan.vn

Create an expandable drop down list

If you work with lists that constantly need to be updated and every time you add a new value to the list you need to go the Data Validation dialog box and select the new range, there’s a workaround to make your life easier.

The trick is very simple, just convert the list source to a table and you’ll be good to go. Here are the steps:

1) Select the range that contains the source for the list

2) Convert the source range to a table: Go to Insert tab, click on Table and then click OK.

Dropdown - Create table

A faster way of create tables is to select the desired range and press Ctrl + T, then press Enter.

3)  Create the drop down list and use as a source the table you just created.

Dropdown - Select table

And that’s all!

See below how the drop down list automatically captures new values when you add them below the list.

Dropdown - Add new items to the table

Back to top

Create dependent drop down lists

Another thing you can do is to limit the choices in a drop down list based on the selections of another list. For example, if you select ‘beverages’ in column A, then only beverages appear in column B; but if you select ‘Dairy’ in column A then only dairy products appear in column B. See example below:

Dropdown - Indirect Same cell

1) The first step is to create a named range for each option of the main list.

For example, if the main list is composed by Beverages, Dairy, Bakery, and Frozen, then you should create a named range for each option.

Notice that each list MUST be named exactly as the corresponding option in the main list.

Dropdown - Naming Ranges

2) Then create the drop down for the main list

3) Then create the drop down for the sublist using the INDIRECT function

Type =INDIRECT(<Address of cell with main list>)

In the following example the main list is in cell A5, therefore you have to go to the cell B5 and create the drop down list using =INDIRECT(A5) as the source. See example below:

Dropdown - Dependent list

Back to top

Please share

If you liked this or know someone who could use it please click the buttons at the

top

to share it with your friends on Facebook, Twitter, and LinkedIn.

<!– –>

MS Excel

Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Check Also
Close
Back to top button