Data Validation: Part 2 of 3

Using named ranges is a very useful trick and can be applied in a lot of ways, including data validation.

In part one of this Data Validation series, I showed you how simple it is to add validation to your spreadsheets, increasing the ease and accuracy of data entry. In that post, I showed two ways of limiting the allowed values for any particular cell. The most straightforward method involves entering the values directly into the source box. Alternatively, you can enter the allowed values in a column somewhere else on the spreadsheet and then refer to that range of cells in the validation window. The downside to this method is that you must list the values on the same spreadsheet as the field on which you are applying validation, adding unnecessary clutter. Of course you could hide the column or put the values well off of the visible screen (e.g. column ZZ or something like that), but you’d have to unhide the column or scroll over to the far right of the spreadsheet every time you wanted to update the list. That is obviously not ideal.

Indirect Function: using named range as the source.

You can simplify things by applying a few simple techniques. While the “Source” box on the data validation window doesn’t allow a range outside of the current worksheet, it does allow named ranges, which can refer to these external ranges. To do so, however, requires the use of the Indirect Function, which will treat anything you feed it as if you’d typed it directly. While typing “animals” into the “Source” box will create a drop down list of one value- “animals”, for example, using Indirect(“animals”) will create a drop down box with the values of the named range called “animals.”

Specifically, apply the following steps to use this method:

  1. On a separate worksheet (which you can later hide if you wish), enter the values you wish to allow
  2. Name the range by using one of the following methods:
    1. Under Formulas Ribbon, choose “Name Manager”; then create “new”
    2. Select the range containing the values; in the address bar, type the desired name; in our example, we use “animals”
  3. On the “Data Tools” Ribbon, select Data Validation/Data Validation; the “Data Validation” window will appear
  4. On the “Validation Criteria” tab, select “List” under for the “Allow” option
  5. The “Source” box will now be active; in type:


  6. Hit the “Ok” button

It’s that simple. In part three of this series, I’ll show you another cool trick. What if you wanted to restrict allowable values in a cell based on the value in another field? For example, let’s say you have a field for “State.” Depending upon the value in that cell, you then want to allow a finite list of cities to be selected. Again, with the use of named ranges and the Indirect function, this is a lot easier than you might think.

Data Validation: Part 1 of 3

Get familiar with the Data Validation Ribbon; it can save you from headaches due to errors in data entry.

If you work with data often, and especially if you collaborate or share your information and work with others, you know this to be fact: like my kids when they tell me they are going to sleep when I know they’re not; if left unsupervised, your colleagues will undoubtedly screw up your spreadsheet. Of course that is partly poking fun, but you get the point and can certainly relate. One of the biggest problems I’ve confronted over the years working with data has to be maintaining accurate and easy data entry.

Fortunately, the two–accuracy and simplicity– go hand in hand. Making the process simple by reducing the potential for human error is paramount. This cannot be understated and serves two purposes. As mentioned in one of my favorite books, The E-Myth Revisited by Michael E. Gerber, one of the biggest mistakes people in business make is making the success of their company, department, or group too heavily dependent on smart people and excellent workers. While I certainly understand the importance of a good team, something that revealed itself time and time again while playing sports my entire life, I also know that having a great structure and process that allows players or employees of different types and skills to succeed, is as important and effective. Making the structure of the “program” simple and effective is step one. Once that is accomplished, communicating that structure clearly and explicitly so that those charged with carrying out the plan will be successful is step two and a natural extension of step one.

The Data Validation Window allows you to customize data entry, user communication, and error handling.

In the working world, this theory manifests itself in many ways. One small way is in the countless reports, processes, and other forms of communications we all share between and amongst each other. Within that context, Data Validation in Microsoft Excel can be an extremely helpful tool. Instead of allowing free and manual entry, I am of the mindset that whenever possible you should restrict the possibilities people have and, therefore, probability of error. Not only does this ensure accurate information is collected and shared. But it also allows employees to perform their jobs well, thus enjoying them more, and frees them up to perform more important duties and grow professionally in business-critical areas.

It’s really pretty simple. To make sure data is entered correctly, apply data validation to a cell, by doing the following:

  1. Select the cell on which you’d like to apply validation
  2. On the “Data Tools” Ribbon, select Data Validation/Data Validation; the “Data Validation” window will appear
  3. There are three tabs on the “Data Validation” pop-up
    1. Settings: Under ‘Validation Criteria’, select “List” for the “Allow:” option
      1. The “Source:” box will then appear. You can either type in the options, separated by a comma; or enter a range of cells that contains the list of values you allow.
    2. Input Message: Allows you to enter text that appears when the cell with validation is selected
    3. Error Message: Allows you to customize the message that appears if invalid data is entered
  4. Using a range on your spreadsheet to limit values is great, but hiding them from the user is even better. I'll show you how in Part II.

    Select OK

Very simple, right? Well, what if you want to use a range for the list, but don’t want it to appear on the same sheet, cluttering up things. In part two of this series, I’ll show you a trick for that.

Getting Unique Values Only

Often times when you are working with Excel, you want to reduce a list of items so that only the unique instances are listed. In other words, if, within a column or list an item happens to be duplicated, you want that item to appear only once. Maybe you have a mailing list, for example, and fear that some customers may be entered more than once. Or perhaps you’ve been collecting emails and are now ready to send out promotional or “Thank you” emails, but don’t want to spam people with multiple messages.

Luckily, Excel makes it very easy to do this, saving you from yourself. In fact, there are two ways. One is simpler and built in to Excel’s multiple functionalities. The other is a bit more difficult and less intuitive, but is more flexible and dynamic. I explain them both to give you flexibility in dealing with these types of business problems. For my example, I use a simple list of animals where a couple of them (cat, bird) are each repeated.

Note: This tip is for Excel 2007, but is applicable, with a tweak or two, with other versions of Excel.

Method 1:

  1. Select data from which you want to retrieve only the unique values. Underline, or otherwise differentiate, the header so that Excel can recognize it as such.
  2. Under the “Data” Ribbon, select the “Advanced” Option.
  3. Under Action, select “Copy to another location.”
  4. The “List Range” should be populated with the data set selected in Step 1
  5. Choose the same range for the “Criteria Range” (I normally copy and paste) from above (the “List Range”)
  6. Check the box next to “Unique Records”
  7. Hit the “OK” button Read more of this post

(E)Mail Merge

Blanca in Modesto, CA asked a very interesting question: Is there a way to perform a mail merge with email? In other words, Blanca was asking if it was possible for her to customize an email so that each person in her distribution list received the email customized to a level as highly personalized as she wanted?

The answer, as is usually the case—it seems almost impossible to find a business problem that the Microsoft Office Suite can’t handle, even if it involves advanced techniques such as VBA—is YES.

Here are the steps to take: Read more of this post

You fill in the blanks!

Figure 1: Neat data is not always "Analysis-Friendly" data

There are occasions when you will want to fill all empty/blank cells in a column. Often times, data is formatted in such a manner so that it is easier to read. Figure 1, for example, shows a portion of what an Accounting Report might produce for expenses over a given period of time. While it is very clean and easy to read, using this data to perform other analysis will require some work. If, for example, you wanted to use this data to show expenses by month or quarter, that would be difficult.

To make the data more “Analysis-Friendly”, then, the data needs to be converted to a “table” or “database” with records. To do so, we must make sure each row (or record) contains the expense (or similar category that is being grouped by the summary report). In our example file (attached), column B needs to contain the category for each row of data (date and amount).

Of course, we can do this the hard, manual way. We can retype the category for each subsequent row. No way! An easier way would be to copy the category heading and fill or paste in the subsequent rows. Better, but still some work.

There is actually an easier way.  Come on; of course there is! Read more of this post