subject: Excel Formulas - A not so Known but Powerful Workaround [print this page] This workaround is a natural step but it is usually underused.
I used my first helper column in Excel intuitively. I was working with a big table and the "Name" column contained leading spaces; a friend of mine taught me about the TRIM function. I inserted a column to the right of the messy column and applied that formula and then copy and paste values; finally I deleted the auxiliary column.
Later I discovered by accident also that a helper column allows you to do a lot of things. It is like the cheapest escape to a complex Excel problem.
Here's a list with more benefits of using a helper column...
Makes a formula more readable. You write partial formulas instead of a single formula paragraph that is later difficult to read and maintain
Turns a VLOOKUP function into a multiple criteria formula. As you know this function only searches one column of the backend list. If you insert a column to the left of your lookup table and then use logical functions (IF, AND, OR) to evaluate two, three or N columns, you can later reference your VLOOKUP lookup value to this combo column
Turns a SUMIF/COUNTIF function into a multiple criteria formula. You can apply the same approach explained above to craft multiple criteria into a single column
Avoids the use of array formulas when you are not proficient with them yet. You can apply the same approach explained above to craft multiple criteria into a single column
Adds more criteria layers to your analysis. For example you can add a check column for review purposes: completed, pending, etc
The secret is very simple; you add a column and use it at your convenience.
The problem with this approach is that your sheets look messy.
Excel Formulas - A not so Known but Powerful Workaround
