Writer, with four books and about 50 magazine articles about the business use of spreadsheets.
Web developer of ExcelUser.com, which is one of the few web sites dedicated to the business use of Microsoft Excel.
John: How would you define HCs and what are the practical applications Excel Users can give them for summarizing, reporting, analysis, budgeting, and forecasting?
Charley: Helper columns are columns of formulas or values that we add to a data file to help us sort or summarize the data. Here are four common ways that I have used them in the past:
a. When I want to summarize data using multiple complex criteria, I can set up an HC that uses the AND, the OR, or both functions to return a column where TRUE specifies rows with the data I care about and FALSE specifies everything else. I can then sort or summarize the data using common Excel techniques.
b. When I import CSV files, the data often isn’t suitable for analysis. Excel might not recognize the date formats. Numbers might appear as text. One column of data might need to be split into several columns. And so on. I use HCs to correct all of these problems.
c. I use HCs frequently when I’m sorting to create a column that I can sort on easily. Particularly in Excel 2003 and before, this allows us to effectively sort using any number of criteria we want.
d. Most of the time, when I sort data, I’ll create an HC that simply counts from 1 to the last row of data. With each row having a sequence number, I can always sort the data back into its original sequence when I need to.
John: Have HCs saved you the day? Please tell us the details of a specific work you remember
Charley: In the past, with some legacy computer systems, the only way to capture their data was to print their reports to a text file. We could open this file in Excel then use the Text to Columns feature to split the data into multiple columns. That was only the first step, however, because there were often column headers, strange number formats, page headers and footers, subtotals, and other clutter in the text file. (I printed a formatted *report* remember.)
So, to extract the data from this mess, I used one or several HC’s to identify the rows that contained the data I needed, then I would sort those rows into a relatively small number of rows with my data and a large number of rows with the clutter.
John: On August 18, 2009; in the discussion at Ms Excel Users LinkedIn group “Do Helper Columns Really Mess your Spreadsheets? What is the Balance between Pure Formulae and a Practical Approach?” You pointed that HCs represent problems. What are the most important ones to take into account?
Charley: For me, the most time-consuming problem with HCs is that I have to create and maintain them. They represent overhead that is difficult to avoid. Ideally, we could find ways to create clever formulas in our reports and analyses, formulas that could return the data we need without using HCs. Sometimes, however, we *must* transform the data into something we can use. So we use HCs in spite of their overhead.
John: Excel Users seek the expert way, you know using pure formulae. What would you say to them?
Charley: If you can find a practical way to use formulas without HCs, that’s usually the most efficient approach. Usually, this solution will involve array formulas.
John: What recommendation would you give to users that need too much of HCs? (optional)
Charley: Often, HCs are used to correct problems with the source data. So when users use HCs too much, it often means that there’s a problem with the systems that generated the data in the first place. Often, it’s possible to modify those systems to produce output that requires fewer HCs or none at all.
John: Well, in conclusion, are HCs good and bad?
Charley Kyd is the developer of Excel Dashboards. Learn more here