Added to Excel in the 2007 release, tables are one of the most significant enhancements to the application in many years. Yet, working with column headers in Excel tables can sometimes prove to be problematic. In this tip, you will learn how to handle calculations that refer to data stored as table column headers.
Tables treat all column headers, including dates and numbers, as text strings. Though this normally does not create too many issues for those building formulas in tables, if you intend the column header to behave like a number or like a date in a formula, you may need to make adjustments in your formulas.
Consider, for instance, the table and formula pictured in Figure 1. The formula compares the year of the acquisition date to the column header to return a value of TRUE or FALSE. However, as constructed, the formula fails to calculate correctly because it is comparing a number from the Acquisition Date column to text from the column header. Of course, Excel forces this result because Excel always treats table headers as text.
Figure 1 - Formula Referring to Text Column Header
A slight alteration of the formula is all that is necessary to cause it to calculate correctly. As shown in Figure 2, merely adding a VALUE function to the section of the formula that refers to the column header causes the formula to calculate correctly. Excel’s VALUE function converts text strings that represent numbers into the number they represent.
Figure 2 - Formula Correctly Calculating Based on Text Column Header
Knowing that Excel treats all table column headers as text strings is the first step to resolving any issues you may experience when referring to column headers in formulas. Further, knowing that you can use Excel’s VALUE function to convert the text string to a numeric value allows you to easily build formulas that reference table column headers.
For a video demonstration of this tip, please visit www.tinyurl.com/k2tips110.