Most valuable professional
  • Excel Book Excerpt

Excel Dealing with Table Formulas

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Dealing with Table Formulas

Problem: Once I define something as a table, the formulas are strange.

Strategy: You are seeing the new structured referencing in a table. Here is how it works.

Suppose you want to add a Profit % column to a table. Follow these steps:

1. Enter a heading of GP% in cell H1.

2. Format cell H2 as a percentage. Do this before you enter the formula.

3. In cell H2, type an equals sign. Click the Profit in G2. Type a divide sign. Click the Revenue in F2. You will already notice something different: Excel is building a formula of =[@Profit]/[@Revenue].

13Fig04.jpg 

Figure 507 The table formula syntax is like the natural language syntax..

4. Press the Enter key to complete the formula. Excel automatically copies the formula down to all the rows in your dataset!

The automatic copying of the formula is a great feature. However, there will be a few times when you do not want this to happen. If so, find the AutoCorrect drop-down and open it. You will have choices to turn of the calculated column or to turn off the feature permanently.

13Fig05.jpg 

Figure 508 Override automatic formula copying.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: