Excel: Dealing with Table Formulas

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

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].

  1. The table formula syntax is like the natural language syntax.
    1. 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 dropdown and open it. You will have choices to turn of the calculated column or to turn off the feature permanently.

  2. Override automatic formula copying.