Improve Data Consistency In Excel With Data Lists

August 4, 2021 thefreelancerslife 2910 Views Excel, Freelancer's guide to Excel, how to use Excel, Using Excel in your freelance business

When you work with Microsoft Excel, you need to ensure that the data in your worksheets is consistent if you want to create any charts or pivot tables from your data. Just imagine if you had a list of business expenses that you wanted to include in a chart to get an idea of where your money was going. If you didn’t use a specific list of expense categories, your charts would be virtually useless.

In the example below, you’ll see that the business expense categories are not consistent.

*Figure 1 – Inconsistent Category data in a list of business expenses.*

When you create a pivot table from the data, you’ll get something like this:

*Figure 2 – Pivot table created from the inconsistent business expense data.*

And, if you try to create a pivot chart from the data, you’ll end up with something like this:

*Figure 3 – Pivot chart created from inconsistent business expense data.*

Unfortunately, the chart above doesn’t help you understand your business expense data. Each entry in the original data has a different spelling for the category. There is even one misspelled category, the Rent Cst category. Because of this, there is a column in the chart for each entry in the data.

A better view of your data would group the expenses into consistent categories. If you change the original data to use consistent category names, you’ll get much better information in your pivot table and pivot chart.

So, if you change the original data to this:

*Figure 4 – Consistent Category names in a list of business expenses.*

You’ll get a pivot table like this:

*Figure 5 – Pivot table created from business expenses with consistent categories.*

And a pivot chart like this:

*Figure 6 – Pivot chart created from business expenses with consistent categories.*

This information is much more actionable and valuable! You can see how much money you are spending on Marketing and Rent.

This example uses a small amount of data. The data you work with will undoubtedly be a lot more extensive. So, how do you keep your data consistent? One answer would be to use a data list.

What Are Data Lists?

A data list is a group of unique values that you can use to populate a cell in your worksheet. For the sample data in this article, you could create a data list of business expense categories. Then, whenever you wanted to add an entry to your worksheet, you would select an item from the list to ensure that you are using the correct category name.

Why Would You Use A Data List?

By reviewing the example above, you should have a pretty good idea of why you would want to use a data list. Having a data list in place will help you to add the correct category names every time you enter data into your business expenses worksheet. The data list will only allow you to enter a category from the list. If you enter something, not on the list, it can pop up an error message and disallow your entry.

How Do You Create A Data List?

Creating a data list is pretty simple. Enter the values you want to use in your list in a separate location of your workbook. Then, in the cell where you want to type the values, link to the data list with data validation.

Create Your Data List

You can create a data list anywhere within your workbook. I like to place my data lists in separate worksheets within my workbook. If you have several data lists, you can put them all on one worksheet or add each list to a different worksheet.

For the business expenses example, I created the following data list on a Categories worksheet within my workbook:

After I created the list, I sorted it in ascending order. This way, when I use data validation to access the list, the categories will be in alphabetical order.

Link Your Data List To Cells

Once you have a data list created, you can use it to validate cells within your worksheet.

Here’s how to link your data list to a cell:

Select a cell where you want to use a data list.
Select the “Data” tab on Excel’s command ribbon.
Click the “Data Validation” icon in the “Data Tools” section of the ribbon.
Select “List” from the “Allow” dropdown in the “Data Validation” dialog box.
Place your cursor in the “Source” textbox.
Select the worksheet that contains your data list if you’re not already on it.
Select the cells that contain the text you want in your data list.
Click the “OK” button.

Here’s what my Data Validation dialog box looked like after I made my Categories data list selections:

*Figure 8 – Data Validation dialog box for Categories data list.*

Now that you have linked the data list to the cell, you will see a dropdown arrow to the right of the cell when you select it. When you click the dropdown arrow, you will see the list of Categories. You can choose a category from the list to enter it in the cell or type a category.

*Figure 9 – Accessing a data list from a cell.*

After adding the data validation to one cell, you can copy and paste it to other cells to add the same validation to those other cells.

Incorrect Entries

If you enter a value manually in a cell with data validation and that value is not in your list, by default, you will see the following error message:

*Figure 10 – Default error message for incorrect entry.*

Select “Retry” to erase the entry in the cell and type a new one. Or choose “Cancel” to remove the incorrect entry from the cell. You can also click the “Help” button to get more information on the error message.

Customizing Your Data Validation Error Message

If you don’t like the error message Excel displays for an incorrect entry, you can create a custom error message in the Data Validation dialog box.

To do this:

Select a cell that contains the data validation.
Click the “Data Validation” icon.
Click to check the “Apply these settings to all other cells with the same settings.” checkbox.
Switch to the “Error Alert” tab of the dialog box.
Select the style of the error message: Stop, Warning, or Information.
Enter a title for the error message.
Enter the text of the error message.
Click the “OK” button.

Here is how I updated my data validation error message:

*Figure 11 – Customized data validation error message.*

Now, when I make an invalid entry, I’ll get the following error:

*Figure 12 – Custom error message for incorrect entry.*

A few things to be aware of related to setting up the custom error message:

Clicking the Apply these settings to all other cells with the same settings.” checkbox ensures that you update all cells that contain the same data validation. You’ll notice that when you check this checkbox, all the cells with the data validation of the initially selected cell are selected. If you do not check this checkbox, only the cell you initially chose will get the changes.
The Style option only impacts the icon you see in the error message; it does not change how the error behaves.
The text you enter in the Title textbox will show up as the text in the error message title.
The text you enter in the Message textbox will show up in the body of the error message.

Data Validation Tooltip

You may have noticed that the Data Validation dialog box had another tab, the Input Message tab. You can use this tab to add a tooltip to a cell that contains data validation. The tooltip will be displayed when the cell is selected and can help the person updating the cell understand what should go in the cell.

Here’s how you add the tooltip:

Select a cell that contains the data validation.
Click the “Data Validation” icon.
Click to check the “Apply these settings to all other cells with the same settings.” checkbox.
Switch to the “Input Message” tab of the dialog box.
Enter text in the Title textbox.
Enter text in the Message textbox.
Click the “OK” button.

Here’s what the Data Validation dialog box looked like when I made my updates to the Input Message tab:

*Figure 13 – Customized input message for data validation tooltip.*

After you add the Input Message, when you click on a cell that contains the data validation, you’ll see the tooltip next to the cell.

*Figure 14 – Input message for data validation.*

Conclusion

So, those are the basics of creating a data list and using it as validation for data you enter in your Excel documents. It’s simple to put this in place and make your worksheet’s data more accurate.

Will you be trying out data lists and data validation in any of your Excel worksheets? How do you plan on using these features? Let me know if you try it out or if you get stuck at any point.

For more Excel tips and tricks, check out the following articles:
25 Excel Navigation and Selection Shortcuts You May Not Know

5 Excel Shortcuts to Drastically Speed Up Data Entry

3 Ways Excel Can Help You Track Your Freelance Income and Expenses

Top Excel Features Every Freelancer Needs To Know

Make Lookups Easier In Excel With XLOOKUP Function

Brenda Keller is a software coach who helps people feel comfortable with and learn how to use software. She has been an Excel user since it was first introduced in 1985 and has taught the program in various settings. Her new Microsoft Excel: Quick Start course was designed to help those new to Excel or those wanting a refresher to get a handle on Excel’s key features quickly. Check out her course on Udemy here or view her YouTube channel here to get more help with Excel. To learn more about Brenda Keller, check out her official website here.