Tips for Excel, Word, PowerPoint and Other Applications
Helper Columns
Why It Matters To You
Helper columns are simply an easy way to simplify your analysis. Creating the right helper column (or row) or two ... or three, can reduce a complex piece of analysis into something fairly simple. There is also the added benefit of being able to see the results of each piece of the analysis in it's own column for validation.
How To ...
Add a column anywhere. If it's a data set that I'll only be getting once, then I'm less picky about where I inset the column, but next to the target column is nice. If it's a data set that I'll be receiving multiple times (e.g., the monthly HR feed), then I'll put all of my helper columns to the far right of the data set. This lets me take in a new data set, copy and paste, and avoid overwriting any of my formulas.
I created an example file, helper_columns.xls that I'll use to illustrate how I've used helper columns.
Example 1
This is fairly common. You have a bunch of data with dates and you'd like to run a summary by month or year. You could do this without helper columns, but it's messy. In this example, I've used two helper columns, one to extract the month code and another to transform the month code into the name of the month, using a VLOOKUP table. I probably could have combined columns 1 and 2, but I split them apart to make the example a little more clear. If I wanted to summarize the annual values, I could have added a third helper column to extract the year with a YEAR function. After you have all of this information parsed out, a simple Pivot Table gets you the summarized figures you need.
Example 2
In Example 2, I'm going to use a helper column to assign groupings based on age. I want to take a list of employees and their ages and assign each to their generation group. Again, I create a mapping table and then use a VLOOKUP to determine which group each person belongs to.
Example 3
Example 3 is a little different. This one is dynamic. I want to calculate days overdue based on today's date and the Payment Due Date. Based on the number of days overdue, I'll assign it to a payment status group. Just like the other examples, I've created a lookup table with the criteria and groupings, but the formula in my helper column (Status) is a little more complex. It generates a lookup value by calculating the spread between TODAY() and the Payment Due Date and then assigns a Status.
Example 4
The last example doens't use a lookup table. Instead, I'm using some simple math to determine if the player's rank is in the top half (winners' bracket) or lower half (losers' bracket) and then using a simple IF to group them one way or the other.
That's all.
Notes
| Last updated | 9/19/08 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips |
LEFT, RIGHT, & MID PROPER, LOWER, UPPER, and SUBSTITUTE CONCATENATE |