Make Your Excel Spreadsheets Smarter With Dropdown Lists

Dropdown lists are the secret weapon against data inconsistency and typos, making data entry easier and more accurate. Despite their power, they’re often overlooked in Excel’s UI, leaving many users missing out on their benefits. Ready to make your spreadsheets smarter? Here’s how you can use dropdown lists in Excel

How to Create Dropdown Lists in Excel

Excel’s dropdown lists are rightly categorized under the Data Validation tab, as they not only streamline data entry but also enhance the reliability of your formulas.

For this example, we’ll use a task tracker with a Status column that provides dropdown lists for each task. The list items will include Not Started, In Progress, Completed, and Blocked.

Unlike Google Sheets, Excel requires the list values to exist somewhere in your workbook. To avoid cluttering your task tracker, it’s best to create a separate sheet to store these values. Once the dropdown lists are created, you can hide the cells or the entire sheet to keep your tracker tidy.

Here’s how to set up the dropdown list:

1. Prepare your list values by entering the list items (e.g., Not Started, In Progress, Completed, Blocked) in a column on a separate sheet.
2. Highlight the cells where you want the dropdown lists to appear (e.g., B2:B5).
3. Go to the Data tab on the ribbon, then click Data Validation in the Data Tools section.
4. In the Data Validation window, select List under the Allow dropdown.
5. Click the upward arrow in the Source field, and select the range containing your list items.
6. Click OK to apply the dropdown list to the selected cells.

The selected cells should now display a dropdown arrow, allowing you to choose from the predefined items.

How to Customize Excel Dropdown Lists

While your dropdown list is functional, it may not look as visually appealing as you’d like, especially if you’re used to the colorful dropdowns in Google Sheets. Fortunately, with a touch of Excel’s conditional formatting, you can enhance the appearance of your dropdown list in Excel.

To apply conditional formatting to your dropdown list, follow these steps:

1. Highlight the cells with dropdown lists (e.g., B2:B5).
2. Go to the Home tab, click Conditional Formatting, and select New Rule.
3. In the New Formatting Rule window, choose Format only cells that contain.
4. In the rule description, set the first dropdown to Specific Text and leave the second field as is.
5. Enter the text you want to format (e.g., Completed).
6. Click Format next to the preview. In the Format Cells window, set the Font color to white, go to the Fill tab, and choose a deep green color for the background.
7. Click OK twice to apply the rule.

Now, any task marked as Completed will have a green background, making it easy to identify at a glance. Repeat these steps for the other list items and assign each a unique color. This will significantly improve visual clarity.

Dropdown lists eliminate the risk of typos and variations—no more Complete vs. Finished vs. Done. This consistency is crucial when analyzing data with formulas; for instance, if a formula tracks Completed tasks, it will miss tasks marked as Finished.

Now with your dropdown lists in place, your Excel sheet is not only easier to use but also primed for advanced data analysis. You can filter tasks by status, such as showing only In Progress tasks, or use the COUNTIF function to count how many tasks are Blocked.