Home > Sample chapters > Microsoft Office > Excel

Using Formulas and Functions

Creating a Formula That References Values in an Excel Table

In previous versions of Excel, it was a challenge to create named ranges that included an entire column in a data list. Suppose that you created a named range that encompassed the existing cells in a data column, such as the cell range A3:A44; if you added data to cell A45, you would need to change the cells in the named range’s definition. Yes, there is a complicated way to create a dynamic named range in Excel 2003 and earlier versions, but you don’t have to worry about it in Excel 2010. All you need to do is create an Excel table (as shown in Creating an Excel Table on page 41) and select the headers of the columns that contain the data you want to summarize in your formulas.

Create a Formula with an Excel Table Reference

  1. Click the cell in which you want to create the formula.

  2. Type =, followed by the function to include in the formula and a left parenthesis; for example, =SUM(would be a valid way to start.

  3. Type the name of the Excel table.

  4. Type a left square bracket.

  5. Click the name of the table column.

  6. Type a right square bracket, a right parenthesis, and press Enter.

    httpatomoreillycomsourcemspimages632183.png