Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for May 2024 for May 2024
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for May 2024 is Open
Wall Street Prep

Excel Drop-Down List

Step-by-Step Guide to Understanding the Excel Drop-Down List

Last Updated July 15, 2023

Learn Online Now

Excel Drop-Down List

How to Create Drop-Down List in Excel?

Creating a drop-down list in an Excel spreadsheet is a quick method to make a financial model more intuitive for the end-user.

Instead of manually needing to enter a value into a cell, with a drop-down list, the user picks the desired value from the provided list.

Since the user is constrained to selecting an option on the list, the chance of a data entry error from an invalid input or typing mistake is reduced.

The steps to create a drop-down list are as follows:

  • Step 1: Select the Cell to Create the Drop-Down List
  • Step 2: Click on Data Validation (“Data” → “Data Tools” → “Data Validation”)
  • Step 3: Select “List” as the Criteria from the Settings Tab
  • Step 4: Enter the Cell Value Options for the List

The cell values contained within the list can be either manual inputs or cell references.

Excel Drop-Down List Keyboard Shortcut

To create the drop-down list more efficiently by using keyboard shortcuts, press the following keys to open the data validation box.

Open Data Validation = Alt A V V

Note that each key must be pressed separately in the order above, as opposed to all at once.

What is an Example of the Drop-Down List in Excel?

One practical use-case of integrating a drop-down list in a financial model is the circularity switch, i.e. “circuit breaker”.

The most common source of circularity stems from modeling a company’s debt schedule, in which the interest expense is calculated.

In short, the incurred interest expense is a function of the average debt balance between the beginning and ending values.

However, the paydown (or drawdown) of a credit facility, such as the revolver, is determined by the company’s levered free cash flow, which is in part affected by the interest expense via net income on the income statement, thus creating “circularity”.

Furthermore, financial models built with intentional circularities can often “break”—i.e. the cells become populated with error messages—which the circularity switch is meant to circumvent, akin to a reset button.

In practice, the circularity switch is most often integrated into the model using the following convention:

  • “0” → ON
  • “1” → OFF

The interest expense formula for each debt tranche will use an “IF” function, where if the circularity switch cell is set to “0”, either a value of zero is returned or the beginning of period debt balance is used in the calculation, rather than the average balance.

If the circularity causes a financial model to “break”, the user can scroll up and toggle the switch to “0” to cut off the circularity, before switching it back to “1”. The model’s error messages should subsequently be removed and the calculations should return to normal as intended, assuming all the relevant affected formulas were enclosed with the “IF” function.

A circularity switch cell should either contain a value of “0” or “1”, and creating a drop-down list here restricts the cell to contain only one of those values. Otherwise, an error message appears stating that the entered value is invalid.

Drop-Down List Calculator – Excel Template

We’ll now move on to a modeling exercise, which you can access by filling out the form below.


By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.


1. Scenario Analysis Using Data Validation

Suppose we’re tasked with creating a drop-down list as part of integrating operating scenarios into a financial model.

The three operating scenarios which we want to restrict the user to select from are as follows:

  1. “Base”
  2. “Upside”
  3. “Downside”

The first step is to select which specific cell to be our operating scenario switch. Once chosen, we’ll name that cell “Case” to make references to the cell easier when entering formulas later on.

2. Create Drop-Down List

In the next step, we’ll open the data validation dialogue box using the keyboard shortcut from earlier and enter our three cases into the “Source:” section.

Data Validation List Example

After pressing enter, our “Case” cell should now display a drop-down box to select one of the three options.

Excel Drop Down List

3. Drop-Down List in Excel Calculation Example

To better conceptualize how an operating case switch can be utilized in a financial model, we’ve included a simple revenue projection section in our quick Excel tutorial.

The company in our hypothetical scenario generated $100 million in Year 0, which will increase (or decrease) each subsequent year by the following growth rate assumptions under each respective operating case.

  • Base Case = +2.5% YoY Growth
  • Upside Case = +5.0% YoY Growth
  • Downside Case = -2.5% YoY Growth

The revenue growth assumption driving our revenue forecast adjusts based on the active selection in our drop-down list, resulting in a more structurally sound financial model.

Drop-Down List in Excel

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.
Learn More
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.