Family Encyclopedia >> Work

3 types of Excel lists to facilitate data entry

Do you need a list based on spreadsheet data? 5 Excel document settings. You should never forget to review. 5 Excel document settings. You should never forget to check. I don't want anyone to see. Always check these settings. Read more ? Excel is your friend!

An Excel list makes it easy to enter and track your data. Today we'll cover three different types of lists that you can add to your Excel spreadsheets:custom lists, dropdown lists, and checklists.

1. Custom Lists

If you frequently use the same data sets in your worksheets, you can create custom lists for them. Custom lists can include data sets such as department names, clients, and age ranges.

Once you've added a custom list to a specific book, it's also available to all new and existing books. You can enter your list directly into the Custom Lists dialog, import your list from cells in a spreadsheet, or import a list from cells in a named range.

3 types of Excel lists to facilitate data entry

Our article on 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 data sets in your Excel spreadsheets? Simply create a custom list and use AutoComplete 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 create four custom lists to use in dependent dropdown lists. The first list in Excel, called Food , determines which of the other custom lists fills the second dropdown.

When you select an option from the first dropdown list on Sheet1 , the second dropdown list is populated with the custom list associated with that name.

3 types of Excel lists to facilitate data entry

2. Drop down lists

We've all used drop-down 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 Using Excel How to Create a Free Survey and Collect Data Using Excel Are you tired? Having to manually collect and consolidate data from multiple people into your Excel spreadsheet? If so, chances are you haven't discovered Excel Survey yet. Read more . You can also add dropdown lists to your Excel spreadsheets to make data entry faster and easier.

Drop-down lists also help reduce data entry errors by limiting the available entries for an input field. A user entering data simply selects an option instead of typing one, which prevents 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, the available items in another dropdown change.

Our article on how to create a drop down list in Excel How to Create a Drop Down List in Excel How to Create a Drop Down List in Excel Excel supports versatile drop down 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 individual and dependent dropdown lists.

Here is our example excel file where we create two drop down lists on Sheet1 . The dropdown on the right depends on the one on the left for its options.

3 types of Excel lists to facilitate data entry

The Vertex42 Site Task List template contains multiple dropdown lists and uses conditional formatting for actions like changing the text color when a task is completed or when Priority changes Also displays a scroll bar in a dropdown list when there are more than six options.

3 types of Excel lists to facilitate data entry

3. Checklists

Do you use to-do lists to stay 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 application.

You can even keep track of how many items are checked off and whether the checklist is complete. Our article on how to create 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 step at a time. Read More explains how to create one that gives you a visual indication that all the items in 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 it as you see fit.

3 types of Excel lists to facilitate data entry

Vertex42 has a checklist template for a vacation packing list where they used drop down lists to check off items. Each checkbox has a blank option and a checkmark option.

3 types of Excel lists to facilitate data entry

Another way to create a checklist using dropdown lists is to add a blank box and a checkmark to each one. The blank box is selected by default. When the task is complete, select the check mark from the dropdown list. This causes all text in that element to appear gray and strikethrough.

The Vertex42 Task Checklist Template is a good example of this.

3 types of Excel lists to facilitate data entry

Another way to check things off in an Excel spreadsheet is to create a dropdown list with a checkmark as the only option. Select the check mark to show the item as completed. To uncheck the item, select the cell with the checkmark in the dropdown list and press Clear . Only the check mark is removed. The dropdown remains and you can select the checkmark again to mark the item as complete.

Vertex42 demonstrates this method in its action element template.

The options are endless

The different types of lists in Excel can make your life easier. Use custom lists for data sets 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 ideas and suggestions for your Excel list in the comments below.