Guide: How to Create an Excel Drop-down List in 4 Steps

By Indeed Editorial Team

Published May 29, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Thanks to its user-friendly and easily customizable features, Excel is one of the most common productivity tools companies from various industries use. Businesses can efficiently calculate, model, and project financial data through Excel via drop-down lists. You can advance your career by learning and understanding how these lists can help streamline work processes and improve your work output. In this article, we define an excel drop-down list, provide steps on how to create one, and explore some of the careers that use this feature.

What is an Excel drop-down list?

An excel drop-down list is a function that enables users to select an option from a list of choices within a spreadsheet. This feature can help improve collaboration between you and your coworkers by simplifying data entry processes. Instead of asking every team member to manually input data, which may extend workload or create confusion, a drop-down list enables them to add already designated information with a simple click. It promotes data accuracy and ensures that only relevant data is in the spreadsheet.

For example, suppose you're preparing a project with numerous tasks. You can create a spreadsheet in excel with column one detailing the assignments and due dates, column two with the headline "team members," and column three has the headline "completed." Column two has a drop-down list featuring the names of your coworkers, and the drop-down list in column three features a yes or no option. These two lists ease your ability to assign or reassign duties while also offering your team the chance to update the spreadsheet upon completing their tasks.

Related: 5 Basic Excel Skills and How to Include Them in Your Resume

How to create an Excel drop-down list

Excel offers a couple of methods to create drop-down lists, but the most common one is using data validation. Data validation is a feature that can help manage the type of information you can input into a worksheet by restricting entries and offering custom rules. Here are the four steps to creating an excel drop-down list:

1. Define the content of your list

Open the Excel spreadsheet where you want to work and select a sheet. A helpful method of defining and maintaining the options for your drop-down list is to create a table of content either within the sheet containing the list or a separate sheet. You can start by typing the heading for your table, then below it within a single column, list the data or entries you want to see within the drop-down list. To convert your table into a named Excel table, first select the heading cell, then on the "Insert" tab, click "Table," check the option "My table has headers," and click "OK."

2. Assign a name to your list

Hover your cursor slightly above the table's header until your cursor turns into a black arrow pointing down. Once you see the arrow, click it to select all the cells from your table. To the left side of the formula bar, click the name box and write a descriptor, which can be the same as the title of your table. By assigning a descriptor to your cells, you create a name in formulas that is quick and easy to access. This option saves time and prevents you from having to go back and forth to select the content of your drop-down list.

3. Start your drop-down list

You can select the cells where you want to add a drop-down list. Select "Data" on your toolbar and click the "Data Validation" icon, opening a dialogue box with three tabs for settings, input message, and error alert. If the "Data Validation" icon is missing from your toolbar, you can input "Data Validation" in the search bar and click enter after selecting the cells.

In the setting tab, the "Allow" box defaults to "Any value" and features several options you can choose. Select "List" and ensure the "Ignore blank" and "In-cell drop-down" options next to the box have a check mark. Then in the "Source" box, you can manually input the source by typing the descriptor name of your table, like this "=YourDescriptorName." You can also press "F3" to open a smaller dialogue box displaying a list of all named cells or cell ranges in your document.

4. Finish setting up your drop-down list

The "Input message" tab lets you add a message that appears over every cell of the drop-down list when the cursor is on it. Depending on the purpose of your list or the number of people using it, an input message can provide helpful information. You may also choose to deselect or untick the box that states, "Show input message when a cell is selected."

Lastly, select the "Error alert" tab, which helps ensure only the data you designate is available and warns others if they attempt to input an entry outside the scope of options. Excel offers a default alert message, but you can customize it by writing a title and the content of the message. There are three error styles you can choose, such as stop, warning, and information. The "Stop" and "Information" styles are similar because they prevent you from inputting any value, while the "Warning" style allows you to write something unlisted in the drop-down list.

Related: A List of Intermediate Skills in Excel to Include in Your Resume

6 careers that use excel drop-down list

Numerous companies from various industries rely on Excel and drop-down lists to aggregate and assemble data efficiently. Understanding the software's various customizable and user-friendly features can help advance your career and gain an advantage when searching for employment. Here are some professions that benefit from using Excel:

1. Administrative assistant

National average salary: $20.69 per hour

Primary duties: Administrative assistants help administrators and other employees to organize files, create correspondence, and prepare reports or documents. Their responsibilities include setting up meetings, event planning, and clerical tasks like managing calendars, sorting mail, or preparing invoices. Administrative assistants create and maintain electronic and physical filing systems, manage accounts, and perform bookkeeping.

2. Data analyst

National average salary: $58,461 per year

Primary duties: Data analysts help company executives discover ways to maximize efficient operations and other initiatives. They review company databases and external sources to make inferences about data figures and complete statistical calculations. Data analysts use graphs, infographics, and other methods to visualize data, creating presentations and reports based on recommendations and findings.

Related: Advance Excel Skills: Definitions and Examples

3. Accountant

National average salary: $54,057 per year

Primary duties: Accountants are responsible for the management and reporting of the financial data of an organization. Their responsibilities include reconciling the company's bank statements and bookkeeping ledgers, managing income and expenditure accounts, and filing and remitting taxes and other financial obligations. Accountants examine and analyze a company's financial statements to ensure compliance with financial reporting and accounting principles. They typically work for an accounting firm or as part of a corporation, but they can also complete freelance work for small businesses and individuals.

4. Cost estimator

National average salary: $62,246 per year

Primary duties: Cost estimators are responsible for determining the projected time frame to complete a project, accounting for additional factors, such as labour needs, materials, budgets, and potential setbacks. They analyze what aspects of production influence a service or product's cost and use that data to prepare a detailed cost analysis. Cost estimators develop and maintain relationships with company vendors and contractors, and present prepared estimates to management and other stakeholders.

5. Retail manager

National average salary: $26.84 per hour

Primary duties: Retail managers lead sales associates or representatives teams to assist customers' shopping needs. They are responsible for creating employee schedules, assigning schedules and responsibilities to employees, and ensuring employees perform these duties efficiently, such as restocking shelves, checking out customers, and reorganizing displays. Retail managers present financial goals and sales targets for their staff to meet on a scheduled basis, train new employees and provide ongoing education for employees, and monitor the store's spending budget.

6. Financial analyst

National average salary: $58,034 per year

Primary duties: Financial analysts analyze economic conditions, stock market performance, and other details to provide expert advice to company leadership before making financial decisions. They prepare reports and projections based on financial data using metrics, such as yearly growth, return on assets, equity, and earnings per share. Financial analysts can find employment in consulting firms, banks, mutual funds, and corporations to generate wise investment strategies and support overall economic growth and stability.

Salary figures reflect data listed on Indeed Salaries at the time of writing. Salaries‌ ‌may‌ ‌‌vary‌‌ ‌depending‌ ‌on‌ ‌the‌ ‌hiring‌ ‌organization‌ ‌and‌ ‌a‌ ‌candidate's‌ ‌experience,‌ ‌academic‌ background‌, ‌and‌ ‌location.‌ Please note that none of the products or companies mentioned in this article are affiliated with Indeed.

Explore more articles