You added some data validation drop down lists to your Excel worksheet, to make it easier to enter the data. Later, you opened that workbook, and the data validation arrows are missing! What happened, and how do you get those arrows back?
At a client's office last week, I was selecting a pricing option from a data validation drop down list. It had some limitations, so here are 3 types of Excel drop down lists compared.
Garbage in, garbage out. 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.
Here are 3 ways to help safeguard your data entry. I'm sure you use a few other methods in your worksheets.
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
Use Drop Down Lists
Instead of allowing freeform data entry, you can add drop down lists to the worksheet. 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:
- On a blank worksheet, create a list of items for the drop down list.
- 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.
- On the data entry sheet, select the cells where you want the drop down list.
- On the Excel Ribbon, click the Data tab, then click Data Validation.
- In the Data Validation dialog box, for Allow, select List.
- In the Source box, type an equal sign, then the name of your list: =Product_List
- Click OK.
Highlight Missing Data With Color
Use a bright color 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:
- Select the cells that you want to highlight (to select non-adjacent cells, press the Ctrl key, then click on cells)
- On the Excel Ribbon, click the Home tab, click Conditional Formatting, and click New Rule
- In the Select a Rule Type section, click Use a Formula to Determine Which Cells to Format
- 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=""
- Click Format, on the Fill tab select a color, then click OK, twice.
You've probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
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 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.
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 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:
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.
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.
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. 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.
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.
In Excel, you can create a drop down Data Validation list, so it's easy to enter valid items in a cell. Usually, the list of valid items is stored in the same workbook, but it's possible to set up a drop down from a list in another workbook.
Today I heard from someone who was having problems with data validation, in cells that were formatted in Percent Style. In the workbook, Enable automatic percent entry is turned on (Office button, Excel Options, Advanced, Editing options).
In the cells that are formatted as Percent Style, the percent sign automatically appears as you start typing a number.
The data validation allows Decimals between zero and 100.
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.
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.
- 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
The 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.
- 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.
- In cell C7, enter $900, which is higher than the amount remaining.
- Press Enter, and you should see an error message.
- Click Cancel or Retry, and enter a valid amount.