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.

5 comments
Robiisan
Robiisan

But I don't even copy the comments. I also delete the links at the tops of the articles and re-format the titles and such for readability in Word. Minor P.S. to Susan - Two n's in personnel. :-)

wwgorman
wwgorman

What happened that the items are not directly available as a PDF download. Printing the item and archiving it was much easier. As it is now I copy the material into Word and then edit out the comments and any ads and then convert to a PDF. All steps that were previously not required.

LonMcClure
LonMcClure

Evernote works well for archiving these articles

fatchoi
fatchoi

I installed Cute PDF Writer as a Printer. I set it as my default printer in Firefox. When I find and Helpful Hint or other article that i want to keep. I click on "more +" in the social sharing box. Then click on "Print'. Cute PDF Writer then generates a PDF file which I store in a temporary directory until I read the article, then either delete or file in appropriate folder.

wwgorman
wwgorman

I tried it and it works. I also found a free program called Solid PDF Creator that does the same thing.