Data Validation in Excel Enforces Consistency

Have you ever sorted or filtered a spreadsheet only to find that some of the entries have the state of Florida listed as FL whereas others used FLA and some even typed the whole F-L-O-R-I-D-A?  Whether it is a project name, dates, or simply an inconsistent labeling, these types of poor practice happen all the time, especially when multiple people use the same spreadsheet. 

Part of the problem is that Excel really is not a great database and does not by default ask the spreadsheet user to declare what type of data each cell will contain.  Also, people tend to use the office suite only to the extent that they are comfortable with each application - have you ever received a letter from someone in Excel instead of Word?!!  We really should be asking ourselves if the document we are working on is in the appropriate format.  A good rule of thumb is to ask whether any calculations are being performed.  So in other words, are we adding column D + E and then dividing by column F and storing the results in column G?  Are we using any formulas at all?  If not, then we probably should be using Access instead of Excel.  If it is a contact list - that's an Access project, not Excel.

Assuming that we must use Excel for the task, let's look at ways to make sure we have everyone in lock-step.  For our example, we're going to think of a project-based firm that has customer names and project numbers.  First, we should make sure that we are conceptually thinking about how we set up the spreadsheet.  Think of a painter with a palette full of different colors of paint in one hand, and a canvas that is going to have the paint applied to it in the other.  Instead of colors, we're dealing with information, and instead of palette and canvas we have different worksheets.  Our palette will be named "Names+Formulas" and the canvas will be named "Data."  

 (Note I have colored the "Names" tab grey)

In the Names worksheet create two lists in columns A.  Label the column in Row 1 as Customer_Name.  Never use Spaces in headers.  For this example add fictitious customers: Zoe, Blackberry, Trump, and Newcastle.  Now that we have our current customer list we can name this range.  Let's assume that we expect to have some more customers in the near future so we will include a couple of blanks in the range. 

 

1. Select A1:A11 making sure to include the header name as well as the blanks for future names. 
2. On the insert menu select Names > Create
3. Ensure that the default Top Row is selected and click OK
   

This has trained Excel that in this worksheet, the list Customer_Name = worksheet (names+formulas) range A2:A11.  Now switch over to the Data sheet.  Lets assume we have a nicely formatted document set up that we are using to track our customer orders.  Column B will have the customer name.  To add a pulldown:

1. Select column B then off the Data menu select Data > Validation...
2. In the settings tab under Allow, select List
3. In the source field type: =Customer_Name
4. To prevent people from entering anything not on the customer list, uncheck the box beside Ignore Blank.

If you click OK and click in cell B3, a drop down arrow will appear in the corner of the cell.  Click this arrow to drill down and show the list of customer names to choose from.  A user can either select from this list or type in the name directly.  If you have unchecked Ignore Blank, then only the names from the list can be entered.  If you have checked Ignore Blank then the drop down is only a convenient reference instead of an absolute restriction.

The other two tabs in the Data Validation dialog box allow you set an error and input message.  If you are not ignoring blanks then it is good practice to set an error message explaining what to do if the user needs to add a name to the list.  Much like when you are using a program and a message pops up saying 'you do not have access to this location, contact your system admistrator' we can inform our users about what the proper method to add a new customer will be.  If everyone who has access to the spreadsheet should be allowed to add to the customer list it is simple.  Just reference the list on 'names+formulas' something like below:

Simple enough?  Great.  Repeat the same process for project numbers or other important items that can no longer be entered willy nilly.  In a future post I'll show you how to set it so that if you select customer A, then under project number only the projects (or order numbers) of that particular customer are available.  Now that's using data validation!!!

Now for some caveats:

• Don't overuse your new favorite feature.  Remember that simpler is usually better, and overconstraining anything is annoying for the users.
• Think hard about whether you need to use the input message or not. Most of the time it will be pretty obvious what needs to go where. And remember what is helpful the first time can rapidly become uneccesary and annoying if it is frequently used.
• Constrain only as far as is necessary and consider all your options. In the introduction I mentioned the example of state names. Instead of creating a list in Names+Formulas of all 50 states or their abbreviations consider some of the other validation options like text length instead of list. If you set the textlength | equal to | 2 it will have the same effect as having a long list in a dropdown of abbreviations.

• Remember to update your named ranges. If you add a customer name beyond A11 it will not appear in the drop down, and will trigger the error message until the named range is updated. Be proactive and maintain your spreadsheets when other people have to use them. Most people are not going to care why it doesn't work, they just want it fixed!
• Consider who should have access to setting up standards and maintaining lists. I typically will set up the names+formulas sheet, and then either hide it or protect it with a password. Your situation and both the number and skills of your users will inform this decision for you.
  • Test, test, and then test some more before you release your fancy new workbooks. Instead of assuming that you have all the angles, try actually inputting 20 or 30 records and see:
    
  • Have I overconstrained to where it is impossible to enter a record?  
  • Is it annoying or unintuitive to use?  
• Get help when you need it.