Drop-Down List

Let`s now understand the issues you may face when your worksheet does not contain a drop-down list and how to correctly insert one in your file.

Example Background:

In this example imagine you work for a company which exports a certain product to different countries around the world. You are now working with a dataset which provides you a breakdown of 60 export sales that took place this month, and which shows the country of destination, the value of the sale and if there was any delay in the export or not.

Your task is then to calculate through a single function (in cell H5) the total amount of revenues for each country by taking into consideration any delays you had while exporting the goods. The selected “Country” must be displayed in cell H3 and the status for “Export Delays” must be displayed in cell H4. For sake of example, let`s type “China” in cell H3 and the word “No” in cell H4.

To calculate the total sales to China without any delays, you basically need to work with the function SUMIFS for these 2 conditions. Follow the steps below:

1. First, insert the function SUMIFS in cell H5.

2. For the argument “Sum Range”, select the range E3:E62.

3. After that, for the argument “Criteria Range 1”, select the range C3:C62.

4. Now select cell H3 for the argument “Criteria 1”.

5. For the argument “Criteria Range 2”, select cell D3:D62.

6. Finally, for the argument “Criteria 2”, select cell H4 and then close brackets and press ENTER.

The result shows that the total sales to China without any delays was $ 38,044.

Typical issues you may face before inserting a drop-down list: 

You can then easily change the conditions set in cells H3 and H4 and check alternative results. To calculate total export with delays to United States, you then basically need to type “USA” in cell H3 and the word “Yes” in cell H4 and the result $ 157,705 will show up.

Even though you can keep making these changes manually, you will face 2 issues:

a) You might have a typo in the country word you insert (e.g. “Jappan” instead of “Japan”)

b) Or you might use a correct word but not in the format available in the dataset (e.g., “United States” instead of “USA”).

On both occasions Excel will return you the value 0 as it cannot find the exact correspondent values in your data source to perform the sum calculation you need. To make sure you work only with the variables “Country” and “Delay Status” exactly as they are defined in your dataset, you need then to work with Drop-Down lists.

Inserting a Drop-Down List:

There are 3 steps required to build a statical drop-down list.

Step 1 – Identify your Unique Values:

So, follow the steps below:

1. First, select the range C3:D62.

2. Then, copy the selection and paste it as “values” in cell K4.

3. Now select the range K4:K63 and go to the menu “Data” and choose the option “Remove Duplicates”. Choose the option “Continue with current selection”, click on “Remove Duplicates” and click on OK in the menu that comes next.

4. A message will pop up saying you have a list of 13 unique values which are all the possibilities of countries available for you to choose at this stage.

Step 2 – Insert a drop-down list through Data Validation:

Let`s start by creating a drop-down list for all the unique “countries” identified. Follow the steps below:

Variable “Country”:

1. Select cell H3 then go to the menu Data and choose the option “Data Validation”. The following menu will pop:

2. In the field “Allow”, select the option “List” and for the field “Source” select the range K4:K16 and then click on OK. Your dataset has been set for cell H3.

3. Press the shortcut ALT + Arrow Down to expand the drop-down list of cell H3. There you can see all the unique country names you identified previously.

By taking this approach you are making sure it`s impossible for users of this spreadsheet to make mistakes such as typos or names not matching strictly the original dataset, as the list generated comes directly from the dataset itself.

Variable “Export Delays”:

Let`s now insert a drop-down list for the variable “Export Delays”. Follow the steps below:

1. Select the range L4:L63, go to the menu Data and click on “Remove Duplicates”. Keep the current selection in place and click on OK in the next 2 message boxes that follow. As expected, 2 unique values remain (i.e., “Yes” or “No”).

2. Now select cell H4, then go to the menu Data and choose the option “Data Validation” again. Select one more time the option “List” in the field “Allow”, and insert the range L4:L5 in the field “Source” and press ENTER. Your drop-down list for the variable “Export Delays” is also done.

Step 3 – Set your drop-down list in the correct order:

Finally, make sure you always keep your drop-down list in the correct order as that makes the selection much easier. In our example we are working with a long list of country names, so displaying on alphabetical order might be a good idea. To do that, you basically need to sort your data directly in your “Unique Data List” which can either be done with the tool “Sort” or “Filter”. Let`s use the tool “Sort” as per steps below:

1. Select the range K4:K16 containing your unique list of countries.

2. Right click and select the option “Sort” and the option “Sort A to Z”.

3. Mark the option “Continue with current selection” and click on OK.

Your entire list will be sorted on alphabetical order from Argentina to USA. If you expand your drop-down list from cell H3 by using the shortcut ALT + Arrow Down, you will also notice the new correct order is displayed there.