Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

Excel Drop Down List

A data validation function that allows users to select an option from a list of choices

What is an Excel Drop Down List?

An Excel drop down list is a data validation function that allows users to select an option from a list of choices. It can be particularly useful in performing financial modeling and analysis by incorporating scenarios and making a spreadsheet more dynamic.

 

Excel Drop Down List Example

 

Learn to build dropdowns into models in CFI’s Advanced Excel Course.

 

How to Make an Excel Drop Down List

Below are step-by-step instructions on how to build a drop down list in Excel:

 

Step 1: Create a List of Options

Make a vertical list of options you want users to be able to choose from. As you can see in our simple example, we have created a list of cities from around the world.

 

Excel Drop Down List - Step 1

 

Step 2: Pick a Cell and Access Data Validation on the Ribbon

Pick a cell where you want the Excel drop down list to be located and use the ribbon to access the Data Validation function, which is located on the Data ribbon. The keyboard shortcut on a Windows computer is Alt, A, V, V.

 

Excel Drop Down List - Ribbon

 

Step 3: Select “List” and Link the Data

When the data validation dialogue box appears, you will need to select “List” where it asks you what you want to “Allow” in the cell.

Once that is done, you can select the data you want to include as the options that will appear in the list. In our example, the options are the cities listed in cells B5:B13.

Then press OK, and your setup is complete.

Learn more step-by-step lessons in CFI’s Advanced Excel Course.

 

Excel Drop Down List - Step 3

 

Step 4: Format the Excel Drop Down List

You may wish to change the background color of the cell that contains the list or add a border to it so users can easily identify it.

When you’re done, start playing around with the cool new feature you’ve created!

 

Excel Drop Down List - Step 4

 

Financial Modeling Applications

In financial modeling and valuation, an Excel drop down list can be very useful for creating scenarios that run through the model.

In CFI’s financial modeling courses, there is commonly a set of scenarios that a financial analyst will use to compare different alternatives for a business.

In the example below, you can see how using a list makes it easy to toggle between the base case, upside case and downside case. The model then uses the CHOOSE function to flow the numbers through the model.

Learn more step-by-step lessons in CFI’s Advanced Excel Course.

 

Additional Resources

Thank you for reading this guide to making your Excel models more dynamic. To continue advancing your career as a financial analyst these additional resources will be helpful.

  • Excel Modeling Best Practices
  • Excel Shortcuts for PC and Mac
  • Guide to Excel Functions
  • VLOOKUP Function

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!