Category: Excel Data Validation

Garbage in, garbage out, am I right?

Valid Data is Crucial

You depend on Excel spreadsheets to show accurate data and analysis.

Enter the wrong data and the results will be incorrect, and that could have a negative impact on your business or reputation.

Safeguard Data Entry

Here are 3 ways to help safeguard your data entry, and I’m sure you use a few other methods in your worksheets too.

Here’s a screen shot of a data entry worksheet, where I’ve used each of those technique, to help people enter valid data.

But remember, these techniques help make your Excel files idiot resistant, not idiot proof!

You’ve met people, right? They’re very creative at finding ways around your helpful Excel data entry safeguards.

1) Use Keyboard Shortcuts

Keyboard shortcuts save you time and can prevent errors. Quickly enter the current date and time, or copy the cell above, instead of re-typing.

• To enter the current date: Ctrl + ;
• To enter the current time: Ctrl + :
• To copy from the cell above: Ctrl + D

2) Use Drop Down Lists

Instead of allowing free-form data entry, you can add drop down lists to the worksheet using the Excel data validation feature.

For example, create a list of product names, and select from that list.

This prevents typos, and only valid entries are allowed. Then, if a product is discontinued, remove it from the list, and it won’t appear in the drop down for new entries.

To create a drop down list, follow these steps:

1. On a blank worksheet, create a list of items for the drop down list.
2. To name the list, select all the items, then click in the Name Box, at the left of the formula bar. Type a one word name for the list, such as Product_List then press the Enter key.
3. On the data entry sheet, select the cells where you want the drop down list.
4. On the Excel Ribbon, click the Data tab, then click Data Validation.
5. In the Data Validation dialog box, for Allow, select List.
6. In the Source box, type an equal sign, then the name of your list: =Product_List
7. Click OK.

3) Highlight Missing Data With Colour

Use a bright colour to highlight data entry cells that are empty. This makes it easy to spot the cells that have to be filled in.

To highlight an empty cell:

1. Select the cells that you want to highlight (to select non-adjacent cells, press the Ctrl key, then click on cells)
2. On the Excel Ribbon, click the Home tab, click Conditional Formatting, and click New Rule
3. In the Select a Rule Type section, click Use a Formula to Determine Which Cells to Format
4. In the Format Values Where This Formula is True box, type a formula that checks the active cell (Name Box shows active cell address), using double quote marks (“”) to test for an empty string. For example: =A3=””
5. Click Format, on the Fill tab select a color, then click OK, twice.

What Are Your Data Entry Safeguards?

What techniques do you use in your Excel files, to help people enter valid data?

Do you use the 3 techniques that I listed?

___________

You know how to create a drop down list in Excel, by using the Data Validation feature.

Sometimes you get extra fancy, and make a drop down list that depends on the selection made in another list, using dependent data validation.

Dependent Drop-Down List Problem

Dependent data validation works well, but there’s a loophole.

In the screen shot below, I can change the category to Fruit, even though a vegetable, Cabbage, is selected as the Item in that row.

With this loophole, you could end up with some strange data on your worksheet!

Lock the First List

Excel users are very resourceful, and can find ways around almost every safeguard that you set up.

However, you can slow them down a bit, by making the first list dependent on the second.

They’ll have to clear the Item selection before they can change the Category.

Currently, the Category cells have a data validation list that’s based on the named range, Produce.

Change Data Validation Formula

You can change the formula in the Source box, so it only uses the Produce range if the Item cell is empty. In the screen shot below, cell B2 is active, and the Data Validation Source formula is:

=IF(C2=””,Produce,B2)

Cannot Change Category

If cell C2 is empty, the Produce list will show in the drop down list.

If an Item was selected in cell C2, then the drop down list will show contents of cell B2.

Highlight Any Inconsistencies

It’s still not foolproof. Users could clear the Category cell, then enter any value. Or they could cut and paste from another cell.

As an extra precaution, you could add conditional formatting in column C, to turn the cells yellow if the selected Item is not in the selected Category.

With cell C2 active, the conditional formatting formula is:

=COUNTIF(INDIRECT(B2),C2)=0

The bright yellow colour will alert users if there’s an inconsistency in the selections, and make it easier for you to spot any problems.

Highlighted Items

For example, in the following screen shot, Cabbage and Rutabaga are highlighted in yellow, because the Category has been changed to Fruit.

Those items don’t appear in the range named Fruit, so the COUNTIF formula returns a zero.

[Update] Video: Block Dependent Drop Down Changes

This short video shows another way to block people from changing the first drop down list, if there’s a selection in the second drop down.

There are written steps, and a sample file on the Advanced Dependent Drop Down Lists page, on my Contextures site.

_________________

Roger Govier has created a sample workbook and instructions that show you how to create dynamic dependent data validation lists.

His technique lets you create multiple levels of dependent data validation, without defining a named range for each list.

INDEX and MATCH Functions

Instead, Roger’s formula uses the INDEX function, with MATCH, to find a column heading, then shows the list from that column.

For example, on one sheet you can list regions, countries, areas and cities.

Data Entry Sheet Drop Downs

Then, on another sheet, select a region in column A, and see only the related countries in column B’s data validation drop down.

In column C, you’d see only the areas in the selected column, then only the applicable cities in column D.

How It Works

With Roger’s technique, you’ll create four defined ranges, then use two of those ranges as the source for data validation drop down lists.

The ranges are dynamic, so you can add more items to any list, or add new lists, and the defined ranges will automatically adjust.

There are full instructions for Data Validation – Dynamic Dependent Lists, and a sample workbook that you can download.

Also, Roger Govier‘s contact information is at the bottom of that page.

____________________

Today I heard from someone who was having problems with data validation, in cells that were formatted in Percent Style.

Enable automatic percent entry

In the workbook, Enable automatic percent entry is turned on (Office button, Excel Options, Advanced, Editing options).

Data Entry Cells

In the cells that are formatted as Percent Style, the percent sign automatically appears as you start typing a number.

Data Validation Rule for Decimals

The data validation allows Decimals between zero and 100.

Invalid Entries

All goes well if you enter a valid number in the formatted cells. However, if you enter text, or an invalid number, the Data Validation error message appears.

Data Validation Retry

If you click Cancel, the cell is cleared. You can type another value in the cell, and the percent sign is automatically added.

However, if you click the Retry button, the cell isn’t cleared. The existing entry, including the percent sign, is highlighted.

When you type a new number, it replaces the existing entry, and Excel doesn’t automatically add a percent sign.

The result is a percentage much higher than what you intended. Here, it’s 5500% instead of 55%

Retry Percentage Workaround

If you use the Retry button, remember to type the percent sign yourself.

Or, click Cancel, to start a new entry.

As a reminder, you could add those instructions to your Data Validation error message.

Or, turn off the Enable automatic percent entry option, and always add a percent sign if typing a whole number in these cells.

There’s more information on the Enable automatic percent entry option in the Microsoft Knowledgebase.
__________________

You can use Excel’s Data Validation to limit the total amount that users enter in a group of cells.

For example, if budget is $3500, you can prevent entries that will go over that total amount.

Budget Sheet Setup

Here’s what is on the Budget data entry sheet:

• Cell F1 contains the total amount allowed for the budget, and the cell is named BudgetTotal.
• The user can enter budget amounts in cells C3:C7.
• Cell C8 sums the amounts entered in cells C3:C7.
• Cell F3 shows the amount remaining (BudgetTotal minus total budgeted in C8).

Add the Data Validation

On the Budget Data Entry Sheet, data validation is applied to cells C3:C7, because this is where the data entry occurs.

• Select cells C3:C7
• On the Ribbon, click the Data tab, then click Data Validation. (In Excel 2003, choose Data|Data Validation)

• Choose Allow: Custom
• For the formula, use SUM to total the values in the range $C$3:$C$7. The result must be less than or equal to the amount in the BudgetTotal cell.
  =SUM($C$3:$C$7)<=BudgetTotal
• Click OK to close the Data Validation dialog box.

Test the Data Validation

After setting up the data validation, you can test it, to make sure that it works as expected.

1. In cell C7, enter $900, which is higher than the amount remaining.
2. Press Enter, and you should see an error message.
3. Click Cancel or Retry, and enter a valid amount.

_______________________

To help users enter data in a spreadsheet, you can create drop down lists with Excel’s Data Validation feature. For example, in an order form, you could provide drop down lists of customers, products, colours, sizes and shipping methods.

Data Validation Source

Usually, each of these lists would need a different Source in the Data Validation dialog box.

• The Customer list would have =CustList as its source
• The Product drop down would have =ProdList as its source.

Use the Same Source

Instead of using a different source for each data validation list, AlexJ has devised a simple way to use the same source for all the lists. This makes it much easier to create and maintain a set of drop down lists.

In AlexJ’s sample file, he’s recording farm information, with drop down lists for Fruit, Vegetable, Farm Equipment and Farmer. He’s typed these lists in the workbook, and named them: DD.Fruit, DD.Veg, DD.Equip and noDD.

The noDD list is just a blank cell, and it can be used when you want users to be able to type freeform in a column.

In row 2, above the table where users will select from the drop down lists, AlexJ has typed the name of the source range for the column below.

Then, AlexJ selected all the blue cells, where drop down lists will be created. In the Data Validation dialog box, he selected Allow: List. As the Source, he entered: =INDIRECT(C$2)

The column reference (C) is relative, and the row reference ($2) is absolute.

Setup Tips

• AlexJ hides row 2, using Outlining, so users aren’t distracted by the range names.
• In the sample file, the named ranges are on the same sheet as the data entry range. In his actual files, AlexJ would have these on another sheet, hidden from users.
• Instead of selecting noDD, cell F2 could be left blank, so no dropdown list would appear.
• No Error Alerts or Input Messages are used in the sample file, but you could add these to your application, if needed.
• The drop down range names in cells J6:M6 are in a range named DD.Ranges. That range is used to create the drop down lists in row 2.

Download the Sample File

To download AlexJ’s sample file click here: Universal Data Validation Drop Downs (zipped 25 KB)

What Do You Think?

I frequently use the INDIRECT function to create dependent data validation lists. However, I hadn’t seen this idea used before, to create different drop down lists from the same source formula.

To me, it seems like a great way to create several adjacent lists, and makes it easy to maintain them.

AlexJ would appreciate your feedback. What do you think? Would you use this technique? Anything you’d add or change?
_________________________

When you set up a worksheet for other people to use, data validation messages can help them get started. The messages appear, like little ToolTips, when a user clicks on a cell.

Users Show or Hide Messages

After using the Excel workbook for a while, users might not need those messages anymore, and the pop-up messages become annoying, rather than helpful.

AlexJ, who recently shared his technique for hiding rows with Excel outlining, has created another useful sample.

In this file, he lets users turn those data validation messages on or off, by choosing TRUE or FALSE from a drop down list.

Show Messages – TRUE

When TRUE is selected, the label cells, such as Name, are green. Click on a label cell and a data validation input message appears, with instructions for that field.

Choose FALSE for No Messages

Select FALSE and the label cells turn white, and no data validation input message appears when you click on a label cell.

How Show/Hide Messages Works

The TRUE/FALSE drop down list is in a cell named ShowUserMsg, and the list is created with data validation.

The labels cells also have data validation, which is set to allow Any value.

For each label, an Input Message is entered in the Data Validation dialog box.

Colour Cells with Conditional Formatting

The label cells and ShowUserMsg cell are coloured with Conditional Formatting.

Excel VBA Code -Show or Hide the Messages

When you select an option from the TRUE/FALSE list, a Worksheet_Change event runs, and turns the messages on or off.

To see the code in Alex’s sample file, right-click the Show User Messages sheet tab, and click on View Code.

Download the Sample File

You can download the Show or Hide Messages file from AlexJ’s Sample Spreadsheets page on the Contextures website.

In the Data Validation section, look for DV0001 – Show or Hide User Tips
___________________