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:
- Enter a heading of GP% in cell H1.
- Format cell H2 as a percentage. Do this before you enter the formula.
- 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].
- The table formula syntax is like the natural language syntax.
- 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.
- Override automatic formula copying.