Data Validation: Part 3 of 3

Make your spreadsheets stand out with Data Validation and Dependent Drop-Down Lists

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, as I mentioned, 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.

A better way to accomplish this was discussed in part two of this Data Validation Series, where I showed how using a named range allows for a cleaner worksheet. I showed how listing the values you want to allow for a specific cell (field) on another sheet, then naming this range so that you can refer to it in the Data Validation window is cleaner and more efficient than listing them in the Data Validation window itself or separately in a list, but on the same sheet. Read more of this post

Data Validation: Part 2a of 3

Convert your named Range into a Table for more flexibility

In part 2 of this Data Validation Series, I showed how using a named range when adding data validation to your fields allows for a cleaner worksheet. Listing the values you want to allow for a specific cell (field) on another sheet, then naming this range so that you can refer to it in the Data Validation window, I showed you, is more efficient and cleaner than listing them in the Data Validation window itself or separately in a list, but on the same sheet.

But we can enhance this method even further. Using the named range, you might have discovered, makes adding an item to the list difficult. You either have to change the referenced range area each time you add an item or you have to make the original range large enough to include further inclusions. Unfortunately, creating a range with blank rows leaves spaces in your data validation list.  That’s obviously not ideal or neat.

A great workaround for this is to convert the range of allowable values into a table as follows: Read more of this post

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:

    =INDIRECT(“animals”)

  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.

9/12/2011: Practice Forgiveness for Better Health

As I wrapped Halloween goodie bags for the elderly at a National Charity League meeting on 9/11 with my 13-year-old daughter, she asked if we’d have time to attend mass that evening. We are hardly model churchgoers and—as I’ve mentioned (See Recipe for Change- Less Everyday) —I consider myself at best a “Recovering Catholic” open to the tenets of several faiths. Our children are the product of this open-minded spirituality regardless of their more formal exposure to Catholic teachings their first nine years. For me it is a gift to see them reach for their own spiritual lifeline when it comes to navigating their way through difficult times.

Over the past week we have discussed the 9/11 of ten years ago, when my son and daughter were just two and three; and, as I answered questions and cried tears as I recounted the day—the moment—that we all personally own, I realized that they too need to personally mourn this piece of history that defines their generation. Read more of this post

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

Keep Choppin’ and Keep Steppin’

Chop big or chop small. Chop quickly or chop slowly. But whatever you do, Keep Choppin’!

Not long ago I was going through one of my crazy circuit training routines when one of the employees at the gym looked at me with a peculiar, perplexed, almost confused face. She’s seen me there dozens of times—always friendly and conversant—so I was wondering why she was looking at me in such a manner. Her confused look was actually, well, confusing me a bit. Later she walked by and said “Why do you work so hard? Is it like your thing or something?” I didn’t really know how to respond. As odd as it sounds, I initially felt the way I did when I was in high school, the same way a lot of kids, struggling to find an identity, to fit in yet stand out, feel. I thought back to a time when doing the best you could in school meant you were being a “teacher’s pet”, “bookworm”, or worse yet, a “know-it-all”. I remember when my teammates called me “Coach’s Favorite” just because I did my best to be first in every sprint, first in the weight room, and last to leave the field after practice. Even as you get older, these labels don’t escape you. Young men and women who go to college are often accused by their less ambitious friends and classmates of “thinking they’re better” than them. Even wearing a suit to work, as I experienced firsthand, can create a stir and be frowned upon when those around you are more comfortable in Kakis and Polo Shirts.

So, I was actually unprepared to respond to Jo, the employee who had caught me off guard with her comment. I forgot what my response was. I probably said something I thought was funny like “I have to keep at it because the pool boy my wife hired is too handsome.” Or I may have brushed it off with “Nobody’s going to do it for me.” Whatever I said, I realized later the real reason. And unlike my normal witty and charismatic responses, it’s not very funny, clever, or necessarily inspiring or informative. It is however, law. It is a universal law that, while boring and simple, is one that few accept, let alone embrace.

I’ve gotten to where I am, you see, not by large jumps or progressions. Whether as a student, a finance professional, a wanna-be programmer, a father and husband, or a fitness junkie (and sometimes coach), it’s always taken (actually taking) some time to grow and learn. And I’ve always understood and accepted that. It was never—it is never—one big step. It is dozens, hundreds, even thousands of small ones. When you’re chopping a tree down, for example, you never know which chop ultimately brings it down. It’s a combination of all of them. When you study for a test, you can never pinpoint which hour or minute ultimately produces the outstanding score you receive. And it’s never one, two, or even three things that make someone fall in love with you. It’s the entire you. The whole is always more than the sum of the parts. Read more of this post