How to Create Drop Down List in Microsoft Excel (Full Guide)

Creating a drop-down list helps you narrow down the results and quickly select an item. For example, it is possible to quickly find people who live in a particular place or children who scored above a certain score through a drop-down list. Today, we’ll see how to create a drop-down list in the Microsoft Excel program.

Microsoft Excel is a popular spreadsheet editor to analyze data and generate reports along with graphical charts, pivot tables, etc. It is best for performing calculations, create budgets, analyze business data, and more.

Let us check how to create a drop-down list in Microsoft Excel.

Contents show

How to Create a Drop down List in Excel

Here’s what you should do,

Step 1: Type your entries that you want to show in a drop-down list. It should appear in a single column without any blank cells in between.

How to Create Drop Down List in Microsoft Excel 1

Convert your data list in to a table. Select all cells and press Ctrl + T keys together to convert them into an Excel table. A pop-up box will appear with cells in the selection.

Click on Ok.

Step 2: On the top, it will show various table styles. Pick one of them if needed.

How to Create Drop Down List in Microsoft Excel 2

Select a cell in the table where you want to insert the dropdown list. Visit Data menu from the top, then Data Validation.

How to Create Drop Down List in Microsoft Excel 3

Step 3: You will get a pop-up box with data validation settings.

How to Create Drop Down List in Microsoft Excel 4

Under Settings tab, pick Allow: List. After selecting List, you will see a new option “Source” on the same window. Click the up arrow that appear next to the Source field to select cells that contain your drop-down list values.

How to Create Drop Down List in Microsoft Excel 5

Click the arrow icon again to maximize the pop-up box. Check ‘Ignore blank’ and ‘In-cell dropdown’ boxes.

Step 4: Switch to the Input message tab.

How to Create Drop Down List in Microsoft Excel 6

Set a message that you can display as a pop-up when a cell is selected. Check ‘Show input message when cell is selected’ option. Enter a title and content for your input message.

How to Create Drop Down List in Microsoft Excel 7

Go to Error Alert tab to configure a warning message that will appear when a user entered invalid data in the cell. There are three types of icons and alert styles: Stop, Warning, and Information. Set a style first. Enter a title and content for your alert message.

Click on OK.

How to Create Drop Down List in Microsoft Excel 8

Read How to Delete Boards, Lists, and Cards in Trello

FAQ

What is Microsoft Excel and why is it important?

Microsoft Excel is a spreadsheet program that allows you to organize data, perform calculations, and create visualizations. It is essential for businesses, data analysis, and personal finance management.

How do I create a new workbook in Excel?

To create a new workbook in Excel, click on the “File” tab, select “New,” and choose “Blank Workbook.” Alternatively, you can use the Ctrl + N keyboard shortcut..

What are cells and how do I select them in Excel?

Cells are the individual rectangular boxes in an Excel spreadsheet. To select a cell, simply click on it. To select multiple cells, hold down the Shift key or drag the mouse over the desired range.

How can I format cells to display numbers as currency?

Select the cells you want to format, click on the “Home” tab, choose “Currency” from the Number Format drop-down list, or press Ctrl + Shift + 4.

What are functions in Excel and how do I use them?

Functions in Excel are predefined formulas that perform specific calculations. You can use them by entering the function name followed by parentheses and providing the required arguments.

How can I create a chart or graph in Excel?

Select the data you want to include in the chart, click on the “Insert” tab, choose the desired chart type, and customize it using the Chart Tools tab that appears.

What is conditional formatting and how can I apply it in Excel?

Conditional formatting allows you to highlight cells based on specific conditions. Select the cells, go to the “Home” tab, click on “Conditional Formatting,” and choose a formatting rule.

Can I protect my Excel workbook with a password?

Yes, you can protect your Excel workbook with a password. Click on the “File” tab, select “Protect Workbook,” and choose “Encrypt with Password.” Enter your desired password to secure the workbook.

How can I find and replace data in Excel?

Press Ctrl + F to open the Find and Replace dialog box. Enter the text you want to find and optionally provide a replacement value. Click on “Find Next” or “Replace” to perform the action.

What are pivot tables and how do I create one in Excel?

Pivot tables summarize and analyze large datasets. Select your data, go to the “Insert” tab, click on “PivotTable,” choose the location for the table, and customize it using the PivotTable Tools tab.

How do I freeze panes in Excel to keep headings visible?

Select the cell below and to the right of where you want to freeze panes, go to the “View” tab, click on “Freeze Panes,” and choose either “Freeze Panes” or “Freeze Top Row.”

How do I sort data in Excel?

Select the range you want to sort, go to the “Data” tab, click on “Sort,” choose the sorting criteria, and specify whether to sort in ascending or descending order.

How can I remove duplicates from a list in Excel?

Select the range containing the list, go to the “Data” tab, click on “Remove Duplicates,” choose the columns to check for duplicates, and click OK to remove them.

Is there a way to recover unsaved work in Excel?

Yes, Excel has an AutoRecover feature that periodically saves your work while you’re editing. In case of a crash or accidental closure, it can help recover unsaved files when you reopen Excel.

Can I use Excel on mobile devices?

Yes, Microsoft offers Excel apps for iOS and Android devices, allowing you to view, edit, and create Excel files on your mobile phone or tablet.