Microsoft

August mailbag: The benefits of Excel helper columns

Stop banging your head on your keyboard trying to do one-formula solutions. Break the problem down into steps using helper columns instead.

healthreport.jpg
Complex problems require complex solutions, right? Not necessarily.

I often hear from users struggling to produce a single-formula solution. Even long-time developers struggle to build complex formulas. My advice is to break down the problem into steps and then build those steps into the sheet in the form of helper columns. Your solution will be stable and easy to maintain and enhance. In addition, eventually, someone always asks for one of those mid-step results. When that happens, you're prepared. Instead of "I can have that for you in a few days," you can respond with "I'll have that on your desk in five minutes." This month, I helped two readers with helper column solutions.

For your convenience, a demo Excel worksheet is provided as a free download.

Birthdates

Angel needs to count birthdates that fall within a specific time period - five days before today, today, and five days after today. Below is the sample sheet I sent Angel.

2013174.JPG

Column A contains birthdates, and that's where we begin. First, we need to evaluate dates in the current year, not the birth year. Column B uses the following formula to return the same birthdate day and month, and the current year:

=DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))

This DATE() function generates a new date using today's year and the birthdate's (column A) month and day.

The span is five days prior and five days after, including the current date - an eleven day span. You could hard-code that number into a formula, but I wouldn't. By referencing an input value, the sheet becomes much more flexible. As soon as you come up with a complex formula that hardcodes that value, someone will ask you to expand or shrink the span. To accommodate the span, I entered the following formulas:

F1: =TODAY()

F2: input value

F3: =F1-F2

F4: =F1+F2

F5: =COUNTIF(C2:C8,FALSE)

The first two are self-explanatory. The next two return a date five days in the past and five days into the future, respectively. The COUNTIF() isn't working just yet, but don't worry. Enter the following formula in C2 and copy it to C3:C8:

=OR(B2<$F$3,B2>$F$4)

This formula uses OR() to check two conditions and return TRUE or FALSE, accordingly:

  1. Is the date less than the date in F3?
  2. Is the date greater than the date in F4?

Now the counting formula F5 is counting as expected. A COUNTIF() function counts the number of FALSE values in column C. (You could express the OR() differently and count TRUE values.)

There's still a limitation. You can't easily add records to the data range. If you're working with a stable dataset, that doesn't matter. To add new dates in 2007 or later, convert the data range into a Table object as follows:

  1. Click inside the data range.
  2. Click the Insert tab.
  3. Select Table.
  4. In the resulting dialog, click OK. Be sure to adjust the header question to accommodate your data range.

2013175.JPG

When you're ready to enter a new record, enter the birthdate into the first empty cell in column A and press Enter. Excel will copy the formulas in the data range and automatically update the counting formula in F5. Excel's Table object doesn't play well with all the other features (Subtotal comes to mind), so keep that in mind.

2013176.JPG

Parsing

Tim has a complex parsing problem. His data is in one cell and he needs to parse the individual components into separate cells. To complicate the problem, the middle component may or may not contain additional spaces. Yikes!

Again, I relied on helper columns, as shown below. The data in column A contains previous Derby winners with their positions and winning years. The position and year are easier to parse than the horse's name because of additional spaces that sometimes occur in the name.

2013177.JPG

The first thing I did was determine the length of each string using the following formula in column B:

=LEN(A2)

We'll refer to this value a couple of times. Next, I used the following formulas in columns C and D to locate the position of the first and last space characters, respectively:

=FIND(" ",A2)

=FIND("*",SUBSTITUTE(A2," ","*",B2-LEN(SUBSTITUTE(A2," ",""))))

The first one is self-explanatory. The second returns the position of the last space by substituting the * character for all the other space characters - just for counting purposes. It doesn't actually change the data. (This long-standing formula is used in many parsing techniques.)

With these three values in place, you're ready to start parsing using the following three formulas:

Position: =TRIM(LEFT(A2,C2))

Name: =TRIM(MID(A2,C2+1,D2-C2))

Year: =TRIM(RIGHT(A2,B2-D2))

The TRIM() function removes any trailing space characters. To decipher the formulas, let's evaluate the first string using each formula, starting with Position:

LEFT(Original String,First Space)

LEFT("1. Willing Colors 1998", 3)

1.

This formula parses the first three characters from the original string. The TRIM() function removes the space character. If you don't want to carry over the period, adjust the formula accordingly.

Now, let's parse the Name:

MID("1. Winning Colors 1998", 3 + 1, 18-3)

MID("1. Winning Colors 1998", 4, 15)

Winning Colors

The formula begins parsing at character 4 and parses the next 15 characters.

The Year formula evaluates as follows:

RIGHT("1. Winning Colors 1998", 22 - 18)

RIGHT("1. Winning Colors 1998", 4)

1998

This formula parses the last four characters from the original string.

I'd like to mention that the Text To Column feature could quickly parse the first component using the period character (.) as the delimiter. Unfortunately, the wizard can't handle the other two components because of the extra space characters in the name.

Helper columns In both cases, neither reader needed to see the helper columns, but eliminating them means combining all of those formulas into one - good luck with that! I can produce both sheets faster than I can build just one of those one-formula solutions. The results are easy to maintain, troubleshoot, and enhance. Perhaps more importantly, both readers will be ready for the "This is great, but can you also" requests.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

17 comments
sparent
sparent

One tip, I find invaluable when debugging formulas, is to highlight part of the formula, then hit F9. As long as the selected text is well formed, you will get the calculated value. Just remember to escape out of the cell editing, to not update your formula. This can be quicker than using Evaluate Formula.

DonG43
DonG43

Thanks for another excellent article, Susan.  Even if you are going to create a very complex equation, I find it easier to build it up with helper columns. That way you can see the intermediate results.  Then you can combine all the formulas/functions into one large one. 

rgallier
rgallier

The use of named formulas will also eliminate the complex multi-step formula. Create a separate named formula for each step (instead of putting this formula in a column); just refer to the names instead of the columns in subsequent formulas.

lester69
lester69

Great article and very helpful (and there ARE functions present). Thanks!

Graynett
Graynett

It's nice to know that I'm not a simpleton cause I find it hard to figure out nested formulae that often returns what I wanted (NOT) helper columns is the way to eliminate errors

thank you from the also ran

Thack
Thack

I absolutely agree!!  IN PARTICULAR, maintenance is a complete nightmare when you use highly complex, deeply nested formulae, and debugging is like tinkering with an unexploded bomb.

Simple, step-by-step calculations are the way to go, every time.  Simply hide the intermediate columns to keep it looking clean and simple.

aksalaymeh
aksalaymeh

Cool as usual, especial adding helper column principal 

Michael99
Michael99

Isn't the example under "Parsing", the wrong example and is the same example as in "Birthdate?"

ahadenfeldt
ahadenfeldt

Minor issue with this statement: "The second returns the position of the last space by substituting the * character for all the other space characters". It does return the position of the last space, but it appears the tactic is to a) count the number of spaces in A2 b) SUBSTITUTE the last space (and only that space) with "*", then c) FIND() its position.

Susan, thanks for doing these. Admittedly there were no surprises for me in this one, yet I know first hand how little the average Excel user knows and your columns are good references. Also, while I've been known do complex things in personal sheets, as a programmer I've learned that simpler is almost always better--especially when it's a sheet others I'm building for others to maintain.

midlantic
midlantic

Thanks! My head thanks you and so does my keyboard!

TanteWaileka
TanteWaileka

Is this column for absolute beginners? Seems like it must be. What a waste of time. Learn functions.

luc.bogaert
luc.bogaert

I love reading your articles about excel. I think that in the current article there's a copy/paste error in the 2nd part about parsing, where the birthdays excel sheet is pasted again.

ssharkins
ssharkins

@ahadenfeldt I think most users aren't programmers -- they're just ordinary folks trying to get their jobs done. I want to help them make things as easy as possible. There's nothing wrong with complex formulas and solutions, but most users will spend a lot of time and frustration pounding out that one-formula solution. Unfortunately, it might return a result, but it might not be correct -- and they might never know it! In addition, once you've got the pieces working together, you can use them to create that one formula if necessary. For me, helper columns are a great tool. 

jonnotjohn
jonnotjohn

@TanteWaileka Clearly this article wasn't written for you.  There's no need belittle the author, nor the folks who may benefit from the information. 

rkleinhenz
rkleinhenz

@TanteWaileka I wish there were not just a 'like' button but also a 'dislike'.  Posts like this go a long way to help Joe Blow solve common everyday problems with ease.  Note that the post had nothing to do with formulas per se but an approach.  I have used this technique many times so while I didn't get anything out of it personally I am always grateful when someone more expert takes the time to abstract techniques - whether it's new or an old hat for me personally

ahadenfeldt
ahadenfeldt

@ssharkinsI could've phrased my original comment better but we are in complete agreement here. I have recommended helper columns to others, and use them myself for simplifying formulas & debugging. As you say, they're a great tool.

Editor's Picks