Collaboration at work is great, isn’t it? Teams acting in sync. Optimized workflow. Everyone on top of their tasks!
But as with anything else, there are also challenges. And one of the toughest of them revolves around data.
In this information age, data is the top driver of business. And for an organization to manage and use data properly, that information must be accessible to all of the employees and teams working on it, adding to it, and adjusting it in real time.
With so many fingers in the data pie, controlling data entry and keeping it accurate is a key priority. It’s a good thing there are many steps that managers can take to ensure all data remains clean and organized — and they don’t need to be complicated to be effective. One of those steps is using dropdown lists in Excel or other table applications.
Read on to learn how to create a dropdown in Excel.
Just so you know
Learn how to make the most of your data with Jotform’s detailed Microsoft Excel tutorial. You can even integrate Jotform with Excel to sync form data to your spreadsheets!
What is a dropdown list?
An Excel table is made up of cells organized into columns and rows. In columns, you can use dropdown lists to provide a selection of values that the user can choose from.
Take a look at the example table below, which displays sales targets for team members. The column “Assigned region” pertains to the areas where the company operates. Rather than allowing free text, a dropdown list can provide a set selection of regions.
Why is a dropdown list useful?
When several employees have access to a table or spreadsheet, getting accurate and consistent data is difficult. For instance, imagine that an employee types in “Florida” as the assigned region. However, in order to calculate sales, the company uses the term “Southeast” to refer to the state. It may seem like a small thing, but this type of inaccuracy can be a major problem when it comes to streamlining data across multiple sheets and departments.
A dropdown list ensures that team members can only enter specific values in the field or cell, which is important because
- It speeds up data entry. Rather than typing out the same answer over and over again, you can click on the dropdown list and select the right value.
- The spreadsheet owner predefines and sets up data values in advance in the dropdown list, making it easier to control from the start.
- It keeps data entry consistent across spreadsheets, projects, and departments with minimal effort.
Create a dropdown in Excel — no fuss
Creating a dropdown list in Excel is simple. Here’s how to do it with a basic table in an Excel sheet.
1. Select the column for the dropdown list
Highlight the cells (without the header) of the column where you want to create a dropdown list.
2. Go to Data > Data Validation
Click on the Data tab and go to the Data Validation button, which looks like this:
A dialog box will now open.
3. Define your dropdown list
Now it’s time to define the validation criteria for the selected cells. Open the Allow menu and choose List. This will open a new field: Source.
Enter the data that you want to include in the dropdown list. It’s important to add a comma between each value. Then click OK.
Now your dropdown menu is ready.
4. Adding new rows
When you add a new row to the table, the dropdown format will automatically carry to the new cell.
There are more advanced features available too, such as adding instructions or error messages to the dropdown list.
5. Adding an input message
Providing short instructions for the dropdown menu can be a big help for staff members working with the spreadsheet, especially when you build the instructions into the user experience.
To add a message to the dropdown, select the dropdown cells and go back to the data validation button. Click on the Input Message tab in the dialog box.
The input message includes a title and body text. Enter your desired message and click OK.
When you click on a dropdown cell, the input title and message appear instantly, letting the staff member know how to use the menu and providing any other relevant notes. The message can be short or long, depending on what you need to convey. However, a shorter, clearer message will be most effective — as well as visually appealing:
6. Setting up an error message
You can set up an error message to display whenever an employee attempts to enter free text instead of using the dropdown list.
Highlight the applicable cells and return to the data validation dialog box. Click on the Error Alert tab. Choose the alert style, add a title and message, and click OK.
Note what happens when you try to enter the text “Florida,” rather than choosing a value from the dropdown list. An error warning appears, providing users with the option to fix the mistake or continue:
The same applies to Google Sheets
Creating a dropdown in Google Sheets is very similar to the process in Excel. Highlight the relevant cells, open the Data dropdown menu, and select Data validation.
Just as in Excel, choose List of items as your criteria and add the sources, each separated by a comma. Click Save, and your dropdown menu is ready.
Using dropdowns in Jotform Tables
Dropdown menus — which are a big help in reducing form response errors — are a popular feature in thousands of digital form templates offered by JotForm. For example, rather than allowing free text for “year of birth,” a dropdown menu with a list of years prevents people from inadvertently typing in the wrong year. This saves a lot of hassle and catch-up down the road.
When you use Jotform Tables with Jotform’s online form templates, you can connect and display all the form data in table format with a few clicks. If a form features a dropdown menu response, the dropdown field is automatically created as a column in the Jotform table — no effort required at all.
You can also create a dropdown in Jotform Tables by adding a new column, choosing the Single Selection option, and then either manually entering the answer options you would like to appear in the dropdown or choosing a source for the answer options.
Drop the effort level and error rate
Dropdown menus are a great option for forms and spreadsheets because they add variety to the user experience and reduce error rates with almost no additional effort.
Save our guide on how to create dropdown in Excel and develop your own in Excel, Google Sheets, or Jotform Tables to start benefiting from this simple and smart data entry function.
Send Comment: