Software

Use Excel's table nomenclature to quickly create dynamic formulas

In Excel, defining a data range as a table opens the door to a number of efficient behaviors. Susan Harkins explains how to use nomenclature to create and use dynamic formulas.

If you're an Excel 2007 or 2010 user, you've probably discovered the new tables feature. Tables are an extension of Excel 2003's list feature. Defining a data range as a table opens the door to a number of efficient behaviors. We discussed some of these features in Put Excel 2007's new Table feature to work for you! and Use Excel tables for quick formula auto-fill. Today, I'll show you how to use one of my favorite table features, using table nomenclature to create and use dynamic formulas.

First, you need a table. To create a table, select any cell in a data range. Then, click the Insert tab and click Insert in the Tables group. Or, press [Ctrl]+T. In most cases, you won't need to change any of the default settings in the Create Tables dialog box. Using the example sheet below, the feature specifies the correct range and the table has headers, so click OK.

Next, name the table by clicking the contextual Design tab and entering a name into the Table Name control in the Properties group.

With the setup out of the way, let's look at how table nomenclature aids in writing formulas. We'll write a dynamic COUNTIF() function that counts the number of sales per person. (The formula will work the same as it would if you weren't referencing a table.) You can put the formula most anywhere you like; I inserted a few new rows to add the formula above the table. In cell B2, enter

=COUNTIF(

At this point, you'd normally reference a column. Instead, after entering the opening parenthesis, enter the name of the table, followed by an opening bracket, as follows:

=COUNTIF(Sales[

When you enter the opening bracket, Excel will display a list of all the columns in the defined table. To complete the range argument, double-click the appropriate column. In this case, that's Personnel.

Type the closing bracket, enter a comma to separate the range and criteria arguments and then specify the criteria argument by pointing to cell B1. Finish it off by entering a closing parenthesis and pressing Enter.

Change the name in cell B1 to update the formula in B2. If creating the formula this way doesn't seem any easier, it's probably because we've created one formula referencing one column. Once you define the table, you can quickly reference any column in it this way. If you have lots of references, you probably will find it more efficient than traditional referencing.

That's not all though - let's add a new record and see what happens. In cell A13, enter Bill. Excel automatically extends the table to include the new row and Excel updates the COUNTIF()—you don't even have to complete the record! That's table nomenclature at work!

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.

Editor's Picks