3 Types of Excel Lists to Ease Data Entry
Pinterest Stumbleupon Whatsapp
Advertisement

Do you need a list that’s based on spreadsheet data 5 Excel Document Settings You Must Never Forget to Check 5 Excel Document Settings You Must Never Forget to Check When you pick an Excel template or share a spreadsheet, it may contain custom settings that you don't want anyone to see. Always check these settings. Read More ? 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.

add a custom excel list

Our article about creating a custom list in Excel How to Create a Custom List in Microsoft Excel How to Create a Custom List in Microsoft Excel Do you often have to fill in the same sets of data into your Excel spreadsheets? Just create a custom list and use AutoFill to apply it. Read More 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.

types of excel lists to ease data entry

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 How to Create a Free Survey and Collect Data with Excel How to Create a Free Survey and Collect Data with Excel Are you tired of having to manually collect and consolidate data, from various people, into your Excel spreadsheet? If so, chances are you have yet to discover Excel Survey. Read More . 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 How to Create a Dropdown List in Excel How to Create a Dropdown List in Excel Excel supports versatile dropdown list options. Use them to control user input. We'll show you how to add dropdown lists to your Excel spreadsheets. Read More 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.

add a dropdown excel list

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.

types of excel lists to ease data entry

3. Checklists

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 How to Create a Checklist in Excel How to Create a Checklist in Excel A checklist is a good way to keep track of tasks or processes. We'll show you how to make a checklist in Excel, one simple step at a time. Read More 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.

add an excel checklist

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.

types of excel lists to ease data entry

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.

types of excel lists to ease data entry

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

Leave a Reply

Your email address will not be published. Required fields are marked *