26. Excel Tricks Every Analyst Should Know

26.4 Formula Tricks

By Ravindra Bagale · Last updated: 6 October 2026

Example first. A commission column breaks when copied, an IF gives a surprise, one Amount cell is secretly typed, and a table needs row and column totals. Four formula tricks.

Trick 13. Lock a rate cell with F4 is shown in the box below; tricks 14–16 follow it.

First look at the data (before → after):

Expected result — After pressing F4: =B2*$E$1 (E1 = 5%)

C2: =B2*$E$1

Trick 14. Watch a formula work, step by step

Before:

After:

Steps:

1. Select C2.
2. Click Formulas › Formula Auditing › Evaluate Formula.
3. Click Evaluate repeatedly; the underlined part is calculated each time.
4. Use it whenever an IF or lookup gives a surprise.

Trick 15. Find a typed number hiding among formulas

Before:

After:

Steps:

1. Press Ctrl+Grave accent (the key left of 1).
2. Look down the column: one cell shows a number instead of a formula.
3. Replace it with the formula and press Ctrl+Grave accent again to return.

Trick 16. Add row and column totals in one AutoSum

Before:

After:

Steps:

1. Select B2:D5 (the numbers plus the empty total row and column).
2. Press Alt+= (or Home › Editing › AutoSum).
3. Excel fills every total, including the grand total in D5.

Practice task (try it before you open the answer):

1. Evaluate =IF(B2>200,B2*5%,0) for B2 = 270.
2. Find the typed number in an Amount column.
3. Add row and column totals to a 3×2 block in one step.