How to create a drop-down list

If we realize that we are using the same words repeatedly in a table, it might be more efficient to use a drop-down list.
Here, we will explain how to use the ‘Data Validation’ to create drop-down lists.

What is a drop-down list?
A drop-down list allows us to select words/values from the inverted triangle▼. It will display the selected word/value in the cell.

Below is a list of expenses in November.
In the ‘Group’ column, the accounting division manually type the corresponding group name every time. The 5 groups are ‘Books and subscription’, ‘Communications’, ‘Utilities’, ‘Meeting expenses’ and ‘Travel expenses’. To save time and prevent errors, they have decided to create a drop-down list in column C.

Basic use 1) Make a drop-down list by typing manually

(1) Select cell/s to set up a drop-down list.

 

(2) Select [Data]→[Data Validation]

① Click [Data] on ribbon.
② Click on [Data Validation] or the following illustration. → 
③ Click on ‘Data Validation’.

 

(3) When the dialog box appears, follow the steps below:

① Check to see that the [Settings] tab is selected.
② Select ‘List’ from ‘Allow:’.
③ In ‘Source:’, type ‘Books and subscription’, ‘Communications’, ‘Utilities’, ‘Meeting expenses’ and ‘Travel expenses’ dividing them with a ‘, (comma)’. ※Spaces are not needed in between the comma and the words.

→ Books and subscription,Communications,Utilities,Meeting expenses,Travel expenses

④ Click [OK] to complete.

 

We managed to create a drop-down list.
Now let’s check what happens when we click cell C4.

 

When clicking on cell C4, a drop-down list is successfully displayed.

Basic use 2) Create a drop-down list by selecting an already existing reference

Other than manually typing the contents of the drop-down list, we can make a ‘list for creating a drop-down list‘ in a different space and use that as a reference in Data validation.
The advantage for this is that it is easier to correct the contents of the drop-down list.

We added a ‘Groups’ list in column H. This time, we will use this as a reference in creating a drop-down list.

 

(1)~(2) Follow the same process as above.

 

(3) When the dialog box appears, select cell range H4:H8 in ‘Source:’.

(※By highlighting cell range with the mouse, the ‘$’ will automatically appear on the range. → For more information on absolute cell reference, click here)

Click [OK] to finish set up.

 

POINT!

If we make an individual table for the purpose of creating a drop-down list, it is useful to ‘define (its) name’.

What does defining a name in Excel mean?
By defining a name in cells and cell ranges, it makes it easier to control complex functions. We can define names in the ‘name box’. For example, let’s name cell range ‘C3:C6’ the ‘Sumrange’. (※We cannot use spaces in the ‘name box’.)
Example)
① Highlight cell range ‘C3:C6’, click the name box on upper left and type ‘Sumrange’.

② Write the formula of the SUM function like below:
=SUM(Sumrange)

 

When the reference of the drop-down list is in another sheet

Even if the reference is in a different sheet, the steps in creating a drop-down list is no harder that the previous cases.

 

(1)~(2) Follow the same process as above.

 

(3) When the dialog box appears, input cell range in ‘Source’ by starting with the sheet name. Begin with ‘Sheet name’ + ‘! (Exclamation mark)’ and then continue with cell range.

=Groups!$B$3:$B$7

 

POINT!

If we define the name of cell range ‘B3:B7’ as ‘Groups’, The sheet name and absolute cell reference is not necessary so it will simply be:

 

 

How to include empty cells in drop-down lists

In methods explained previously, we could not include empty cells in drop-down lists.

Using the table below, we will look at how to create a drop-down list in column E including the ’empty cells’.

(1)~(2) Follow the same process as above.

 

(3) In ‘Source:’, highlight cell range including empty cells, cell range ‘H4:H8’. Click [OK] to complete.

 

(4) Checking the drop-down list

We are able to select the empty spaces from the drop-down list.
(※If the list on the right (i.e. the reference) is changed, or if a new payee is added in those empty cells, it will be reflected in the drop-down list)


 

POINT!

When the reference is written manually, add a space in between the commas to create an empty space in the drop-down list.

→ NY Book center, Maxi’s Lunch Box, ,

 

How to exclude empty cells from drop-down lists

There might be times when we do not want to display the empty cells in drop-down lists even though we included the spaces in the ‘Source:’ in creating drop-down list.

Now we will explain a method to ‘not display empty spaces in drop-down lists‘.

(1) Define a name to create a drop-down list. Here we will explain the process through ‘Name Manager’.

① Click on [Formulas] tab on ribbon.
② Click on [Name Manager (click on the word)].

 

(2) When the dialog box appears, click on [New].

 

(3) When the dialog box appears, set up the name definition as below:

① In ‘Name:’, input any name. (Here it will be ‘Payees’).
② In ‘Refers to:’ we normally highlight reference cell range, but this time, we input the following formula:

=Expenses!$H$4:INDEX(Expenses!$H$4:$H$8,COUNTA(Expenses!$H$4:$H$8))

③ Click [OK] to complete.

We will add an explanation to this formula as it is complex.
First of all, the formula that begins with INDEX~:

INDEX(Expenses!$H$4:$H$8,COUNTA(Expenses!$H$4:$H$8))

The INDEX is a function that returns a value of the reference cell range (returns a value in a cell number like ‘A1’).
Furthermore, by using the COUNTA function after the ‘, (comma)’ (=the place of the [row_num] in the INDEX function), Excel counts how many data there are in cell range ‘H4:H8’.

In other words…

・ The number of counts (=number of rows with data) calculated by the COUNTA function is ‘2’.
The INDEX function returns the value in the 2nd row, cell ‘H5’ from the reference cell range [H4:H8].

If we take the first part of the formula

=Expenses!$H$4:

and combine it, the results we get is this.

=Expenses!$H$4:H5

The cell range that the drop-down list will display will be ‘H4:H5’ from the “Expenses” sheet.
(※Keep in mind that this method does not work when there is an empty space in between the list that we would use as a reference in creating a drop-down list.)

The results)

 

How to deactivate a drop-down list

To deactivate a drop-down list, follow these steps.

(1) Highlight cell to deactivate the drop-down list, and open the ‘Data Validation’ dialog box.

(2) Click ‘Clear All’, and click [OK] to finish.