Dealing with Table Formulas

May 31, 2022 - by Bill Jelen

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

Formulas in a table are not =G2/F2, but =[@Profit]/[@Revenue] — Figure 499. 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 dropdown and open it. You will have choices to turn of the calculated column or to turn off the feature permanently.

After entering the formula in row 2, the formula is automatically copied down to all rows in the table. — Figure 500. Override automatic formula copying.

This article is an excerpt from Power Excel With MrExcel

Title photo by Artturi Jalli on Unsplash