In an earlier blog, I showed you how to create drop down lists and rules to control what people enter into your spreadsheets using DATA VALIDATION rules. These are a great tool to use and dramatically reduce the number of user input errors.
Going back to our timesheet example, we set up a number of drop down lists and rules, but there is still margin for error. For example, not every employee might work on every project and task combination. There are chargeable and non-chargeable activities but these are all in a single list. In both these examples, a user could correctly select their name (if they can’t do that then there is no hope!) or identify an activity as non-chargeable but select totally the wrong project/task combination or assign a chargeable activity. So although we have limited their choices, there is still lots of potential for messing things up.
To get around this problem we need to create linked or dependent validation drop down lists, where a choice made in one cell affects the choices available in the next.
To make this work, we need lots of NAMED RANGES.
Let’s take the chargeable/non-chargeable options as an example.
At the moment our drop down list in the Details column is using the named range Reasons but this contains every option – both chargeable and non-chargeable. First we need to split this into two separate lists – chargeable reasons and non-chargeable reasons.
Now, we need to name our two lists. When you name them you MUST use the values from the drop down list that will determine the options in list 2. In this example, the options in the first drop down are “Chargeable” and “Non-Chargeable”. Our two lists must therefore use those names for the linking to work.
Highlight the cells containing the chargeable items and then click into the NAME BOX and type in Chargeable. Repeat for the Non-Chargeable items. At this point however, you may get an error message.
Remember that when naming ranges you cannot have spaces or certain characters including dash (-) which we have here. This means we have to adjust our values in the first drop down to remove any spaces or dashes etc. If you don’t do this then you can’t link the two validation lists. Mildly irritating, but necessary.
In this example I will opt for an underscore to give us a value and matching named range of Non_Chargeable.
Once you have all your named ranges, all that’s required is to make it work!
Go back to the Timesheet and edit the existing validation rule in the Details column. Instead of referring to the full list of options stored in the named range “Reasons” we enter a formula…
Column D contains our drop down with the Chargeable/Non_Chargeable options. What the INDIRECT function does is look in cell D5 and then goes to find whatever it finds in that cell. In this instance, the only things called “Chargeable” or “Non_Chargeable” are named ranges. The values that make up the named range are then displayed in the drop down list.
So now when you make a choice in column D it determines what appears in column E.
Or if you select Non_Chargeable…
This has now added a new level of control to the worksheet, preventing users from making an incorrect selection following a selection in another column.
This method can be continued by creating new named ranges using the values from the named ranges we just created. Let’s say that after choosing “Chargeable” as my first option, I am then given the limited list of options that are chargeable. From that list I choose “Training”. Assuming we have another column, I would want a list of training courses to appear so that I can select from that list. Similar to what I did above, I would create a list of training courses and name it “Training” – an exact match for the value in my details drop down list. Repeat this process for each and every option in your drop down list. You could end up with dozens of named ranges but at least you will know that what you get back from users will be correct.
In my blog “Using Data Validation to Control Data Entry”, we looked at how to set up DATA VALIDATION rules, add prompts and customise alert messages.
Now, let’s look at a few other features you might want to use when working with VALIDATION rules;
- Finding cells that have DATA VALIDATION rules applied
- Checking for data that no longer meets your rules
- Preventing duplicate entries
Finding cells that have DATA VALIDATION rules applied
So, how do you find cells in a worksheet that have validation rules applied to them? You could click in each cell, one at a time and see if a drop down arrow appears. That’s fine as long as the validation involves a drop down list. But what if it has another type of rule or even a custom formula? No hints there!
For this, we need Go To Special. You can find this on the HOME tab under FIND & SELECT.
Click on DATA VALIDATION and any cells that have rules of any sort applied to them will be highlighted.
The grey cells are those with rules applied (the labels in this example also happen to be grey – but it should be pretty obvious when you run this)
If you like your keyboard shortcuts, you can access this by the following combination:
Ctrl + G followed by Alt + S, followed by V and then press ENTER.
Checking for data that no longer meets your rules
As you create your rules, naturally you will test them and make sure they work. But sometimes you may make changes to rules after data has been entered. Unfortunately, Excel will not automatically highlight data that no longer meets your new rules. If you want to check how your data is affected by a new rule and whether it breaks it you need to run CIRCLE INVALID DATA.
If you love your keyboard shortcuts, then you’ll need:
Alt followed by A, followed by V, followed by I.
So make a change to your rule(s) first. Continuing our timesheet example, I set up a rule that limited the comments to 150 characters. I’ve now decided that even that is too much and want to limit it to only 100 characters. I make the necessary change to my rule, but there is already some data in my worksheet. In this example there is a comment that is 126 characters long. This was fine under the old rule but breaks my new one.
Now turn on your CIRCLE INVALID DATA.
When you turn this on, any cells that contain data that breaks the edited rule will be highlighted by a red circle.
Unfortunately, Excel does not do this automatically for you, so you need to remember to do this after you have edited an existing rule. If applicable, make changes to the data in the cell(s) that are circled. Once they meet the new criteria, the circle will disappear.
Preventing duplicate entries
As well as controlling data entry through a set of simple rules you can use formulas to create complex rules with multiple conditions. One example where a formula can come in handy is preventing duplicate entries.
For this we need to use the CUSTOM option when creating our new rule within DATA VALIDATION.
In the FORMULA box enter the following formula:
What this does is check the selected range, in this example A1 to A50. Note the use of absolute references as you have to fix the range that is being checked.
NOTE: if you are applying the rule to an existing list make sure you apply the validation rule BEYOND the end of the list otherwise as you add new records no rule will have been applied to the blank cells and therefore allowing you to potentially add duplicate values.
The next bit A1 says look in cell A1 and check it against every cell in the selected range and count how many times it appears. This is left as a relative reference so that each cell is checked in turn when the formula is copied down the column.
The rule then states that when counted, each cell value must add up to 1 or less which means that if you add a duplicate value the count will be greater than 1 and therefore block it.
Let’s take this one step further…..
What if I want exceptions to the rule?
For that, all I need to do is modify my formula slightly….
Essentially it is the same rule as before but with the addition of an exception. In this example I am allowing the name “Dave” to appear more than once. Note the use of brackets around the first part of the formula.
You can add further exceptions by adding more +(A1=”text”) on the end of the formula.
And there you have a few useful additional features from data validation!
They say that two things in life are unavoidable – tax and death. There is, however, one more you can add to the list, and that is people mucking up your spreadsheets! Send out a spreadsheet to five different people and you can guarantee you will get at least one person filling it out in some strange and obscure manner, and some bright spark won’t like your layout so they decide to re-arrange your table to suit them.
So how do you stop all of that? There are two basic things you can do:
- Apply DATA VALIDATION to your tables
- Apply varying degrees of PROTECTION to your worksheets and workbooks
Here, I am going to tackle DATA VALIDATION.
DATA VALIDATION allows you create drop down lists in cells as well as create rules that control what people can and cannot enter into a cell.
Let’s take a timesheet as an example…
Each week I want team members to fill out their own timesheet. They need to fill out all times for one week starting on a Monday through to Friday. Work is done on projects, and more specifically to tasks within a project. Time can be chargeable or non-chargeable to the project and different activities will fall into each of those categories. Team members are also required to add a justification where necessary, such as overtime. And finally, the timesheet needs to be approved by the manager.
So with all the different bits of information required, the possibilities for errors and creative form filling are endless.
A lot of this timesheet relies on specific project numbers and category descriptions. This sort of data entry is prone to errors, so use DATA VALIDATION to create drop down lists forcing users to select from a specific list of options, with no allowance for users to add their own values.
First, let’s create a list of team members so people can’t even get that wrong! Create a list (ideally on another sheet in the workbook – makes hiding the lists easier afterwards). If you want, name your list e.g Team. This is not strictly necessary if you are using Excel 2010/2013 but makes life easier and is a MUST if using 2007 or earlier if your lists are on a separate sheet from the sheet where the drop downs will be used as you cannot select cells on another sheet unless they are part of a named range.
In your timesheet, click on the cell(s) that will have the drop down list. Assuming we have already created a list, go to the DATA tab and select DATA VALIDATION.
From the ALLOW options, select LIST.
In the SOURCE box use the range picker button to select the cells containing the list values, or if you have created a named range type = followed by the range name i.e. =Team (note: no speech marks).
You should now have a fully working drop down list to choose from.
To help your team use the drop down properly, you can add an optional prompt. Go to the INPUT MESSAGE tab, add a title of some sort and a useful message to assist (rather than confuse) the users.
Click on OK to confirm. You should now see this next your cell.
You also have the option to control whether people are restricted to using only the values in the list, or whether they can type in their own values. The latter option is potentially dangerous, but this may be necessary so a good user prompt here is a plus.
Go to the ERROR ALERT tab.
Under STYLE, you have 3 options;
- Stop – limits values to those in the drop down list ONLY (Good choice!)
- Warning – warns you are about to enter a value that is not in the list and asks if you wish to continue. To which you can say ‘yes’, therefore ignoring the list completely.
- Information – even less harsh than Warning. Tells you the value you are entering is not in the list. Click on OK to ignore and continue regardless.
There may well be times when you do want to give people the option to enter other values, which is fine, but if you want to cut out any form of creativity with your spreadsheet go for STOP!
If you don’t want the standard pop up message from Excel, type in your own title and message, so if someone enters something outside the list they get a custom message;
Continue to apply drop down lists with input and error messages where appropriate.
DATA VALIDATION though is not limited to just creating lists. Create rules to control any of the following:
- Whole numbers
- Text length
- Custom (create your own rules using formulas)
In each case you will have a variety of options: between, greater than, less than etc. Pick an option and enter values to suit your rules.
Most of the options are self-explanatory but CUSTOM is where you can create formulas to create complex rules that may involve multiple cells. When using the CUSTOM function just be careful with your relative and absolute cell references especially when applying your validation rules to a block of cells. Although you apply the rule to a range of cells you are actually writing the formula for the first cell in the selected range and Excel copies the formula down/across to the other cells.
Eventually, you will get a complete timesheet looking something like this…
So that is DATA VALIDATION. We now have a working spreadsheet where users have limited options on how to fill it out. However, there are still a number of issues that need to be dealt with here to further improve the reliability of the timesheet;
- Incorrect project/task combinations
- Incorrect status/details combinations
- Late submission of the timesheet
- Authorisation access
I will cover these in “Creating Linked Validation Drop Down Lists” and “Protecting Your Workbooks”. Further enhancements could be added using VBA…so by no means a done deal yet!
Also see “Data Validation Error Checking & other useful tips”
Do you require people to fill out a sheet such as expenses in a simple double entry format, but find that people add the costs to all the wrong columns? There is a simple way to control this with a combination of a formula, data validation and a bit of protection.
Let’s take a simple example;
People put in expense claims for the following categories: Travel, Hotel, Food, Misc
For each expense they need to add a short description and a value. The value then needs to be added to the correct column across the page, in effect creating a double entry table.
The problem of course, is getting people to correctly add the value to the appropriate column and to make sure that all the columns add up correctly to match the grand total in column C.
First of all, control the category column. Add a data validation list based on the column headings and limit the list to those values.
Add some standard currency formatting to your various monetary cells.
Now add a formula to auto-populate the correct columns once they have selected a category and entered a cost.
The formula to use here would be: =IF(EXACT($A2,D$1)=TRUE,$C2,””)
This will test to see if the category selected in column A matches the column heading in columns D to G. If they match then it displays the value in column C, otherwise leaves the cell blank.
Note, that until a value is entered in column C it will display £0.00, which is fine as it flags an incomplete cost and should prompt the user to enter a value.
The final step is to protect and lock columns D to G to prevent users randomly entering data in these cells. Make sure the cells in columns A to C are unlocked to allow editing once protection is applied.
Make sure that the cells in columns D to G are both locked and hidden so that users cannot see the formulas (not essential but adds to the magic of the worksheet). Add a password to prevent users making changes and make sure you protect the workbook too to prevent anyone from removing the sheet or making changes to data validation rules etc.
Now when a user adds in their expenses, the values are automatically added to the correct column and because people cannot randomly add or edit values the totals for each column will add up to the grand total of all the expenses claimed.
From here you can extend your formulas to calculate net cost and VAT where applicable, and get those to automatically calculate based on selections I’ve now added to columns D and E.
When working with lists of information it is sometimes nice to be able to highlight rows of related information such as all rows for a specific product or salesperson. If you have the latest version of Excel then you have the benefit of SLICERS, but if not then you need a different approach to achieve this. By combining DATA VALIDATION and CONDITIONAL FORMATTING this can be done quite easily.
Let’s take this simple table showing regional sales by product line.
I could use a filter and select the information I need but using a combination of DATA VALIDATION and CONDITIONAL FORMATTING will display it in the table itself whilst still leaving other data visible.
Create a couple of drop down lists using DATA VALIDATION using the values from your list to generate the values to select from. I have left a blank cell at the top of each list so that I can have an option to select ‘nothing’.
Once I have those set up I can set about applying some CONDITIONAL FORMATTING based on the selections made in the two drop downs.
I have set up three rules, one for each drop down list and one to highlight values that meet both selections. Note the use of partial absolute referencing in the formulas to make sure that when the rules are copied across the three columns the reference points remain constant i.e. always look in columns A and/or C.
Formulas are as follows:
=AND($A6=$B$1,$C6=$B$2) to highlight when both product & region criteria match
=$C6=$B$2 to highlight where the product line is matched
=$C6=$B$1 to highlight where the region is matched
And there you have a simple way to highlight data in a list based on the user’s selections in drop down lists.