3 Types of Excel Lists to Ease Data Entry
Do you need a list that’s based on spreadsheet data ? Excel is your friend!
An Excel list makes it easier to enter and track your data. Today we’ll cover three different types of lists you can add to your Excel worksheets: custom lists, dropdown lists, and checklists.
1. Custom Lists
If you frequently use the same sets of data in your worksheets, you can create custom lists for them. Custom lists can include sets of data like department names, clients, and age ranges.
Once you’ve added a custom list to a specific workbook, it’s also available in all new and existing workbooks. You can enter your list directly on the Custom Lists dialog box, import your list from cells on a worksheet, or import a list from cells in a named range.
Our article about creating a custom list in Excel covers how to create custom lists using each of these methods.
You can use custom lists to fill a range of cells with your own personal list or to create dropdown lists. In this Excel file, we created four custom lists for use in dependent dropdown lists. The first Excel list, named Food, determines which of the other custom lists populates the second dropdown list.
When you select an option from the first dropdown list on Sheet1, the second dropdown list populates with the custom list associated with that name.
2. Dropdown Lists
We’ve all used dropdown lists before, whether you’re changing settings in a software program or filling out a form on a website . You can also add dropdown lists to your Excel worksheets to make entering data quicker and easier.
Drop-down lists also help reduce errors in data entry by limiting the entries available for an input field. A user entering data simply selects an option instead of typing one, preventing typos and misspellings in the data.
You can also have multiple dropdown lists that depend on each other. When you select an item in one dropdown list, the items available in another dropdown list change.
Our article about creating a dropdown list in Excel covers how to create single and dependent dropdown lists.
Here’s our example Excel file where we created two dropdown lists on Sheet1. The dropdown list on the right is dependent on the one on the left for its options.
The To-Do List Template on Vertex42’s site contains several dropdown lists and uses conditional formatting for actions like changing the color of text when a task is completed or when the Priority changes. It also shows a scroll bar in a dropdown list when there are more than six options.
Do you use to-do lists to keep yourself organized? If you use Excel often, you don’t need another app for your to-do lists. You can create checklists in Excel and check off items. A checklist is also useful for keeping track of what you still need to do in your workbook without using another program or app.
You can even keep track of how many items are checked off and whether the checklist is complete. Our article about creating a checklist in Excel covers how to create one that gives you a visual indication telling you whether all the items on your Excel list are complete or not.
Here is the file we used for our checklist below. You can study what we did and add to it as you see fit.
Vertex42 has a checklist template for a vacation packing list where they used dropdown lists for checking off items. Each checkbox has a blank option and a check mark option.
Another way to create a checklist using dropdown lists is to add a blank box and a check mark to each one. The blank box is selected by default. When the task is complete, select the check mark from the dropdown list. This makes all the text for that item gray and crossed out.
Vertex42’s Task Checklist Template is a good example of this.
Another way to check things off in an Excel spreadsheet is to create a dropdown list with a check mark as the only option. Select the check mark to show the item as completed. To uncheck the item, select the cell with the check mark in the dropdown list and press Delete. Only the check mark is removed. The dropdown list remains, and you can select the check mark again to mark the item as completed.
Vertex42 demonstrates this method in their Action Items Template.
The Options Are Endless
The different types of lists in Excel can make your life easier. Use custom lists for sets of data you use repeatedly, dropdown lists when creating forms to collect data, and checklists as to-do lists.
How do you use lists in your worksheets? Share your Excel list ideas and suggestions in the comments below.
Image Credit: Rawpixel/Depositphotos