Refer to Excel Table in Formula
While I strongly recommend creating “Named Ranges” that you can refer to in an Excel Formula, there is one drawback. By default, “named ranges” refer to “Absolute” Cells – e.g. $A$1:$A$15
However, what happens when your data set expands? For example, when you add records beyond row 15 in this case. Now, your formulas do not refer to the additional data in each field. You need to find a way to automatically expand the range of cells that your formula refers to.
Tables in Excel 2007 and Excel 2010
Fortunately, if you are using either Excel 2007 or Excel 2010, you can “format” your data set as an EXCEL TABLE and take advantage of many great options:
- When you append records (rows) , the definition of your Table automatically expands.
- You can “name” your table to reflect its contents. For example, in this video I name my Table – DRTable – and I refer to it in my formulas.
Writing Formulas that Refer to Excel Table Field
In this video tutorial I use the SUMIF Function. Remember that I have “named”my table DRTable. Here is how I begin my formula. =SUMIF(DRTable[Customer]. Notice the use of brackets ([ ]) around the [Customer] Field in the formula. This is the gotcha step in this lesson.
Watch This Video in High Definition on YouTube
I created this video tutorial in High Definition Mode. Click on this link to view it on my YouTube Channel – DannyRocksExcels
Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” – Visit my Online Bookstore for more information.
