Skip to content
Reading settings
ExcelIntermediate~30 min practiceLesson 28 of 34

Compare formula designs without losing records

Check source coverage and use a fair calculation comparison.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Compare formulas with the same results and test that a faster design includes added rows.

Example

A regional formula that must keep new rows

Enter Region and Amount in A1:B5: West 120, East 80, West 100 and North 50. West totals 220 and the source totals 350. Start with correctness on these four rows; repeat the block on a copy if you need more data for timing.

How it works

A faster formula must answer the same question. A range that leaves out the next record or a lookup that changes match behavior is a different calculation.

TODAY, NOW, OFFSET and INDIRECT are volatile and can add recalculation work. Use them where needed; share repeated date inputs when appropriate, and keep a live date distinct from a fixed reporting cutoff.

Steps

  1. Create a table named PerfData and enter =SUMIFS(PerfData[Amount],PerfData[Region],"West") outside it. Check the starting result of 220.

  2. If you need a larger timing sample, repeat the four-record block on a practice copy and record the block count. Ten blocks contain 40 rows, West 2200 and full total 3500.

  3. Compare a table or limited-range formula with a much larger source range. Check that both formulas include the same populated records before timing them.

  4. Look for repeated TODAY or NOW calls on a copy. Where the model needs one live date or time, place it in a labeled shared cell and reference that cell instead. Keep its meaning clear.

  5. On desktop, note calculation mode and use the same recalculation command for each version. Repeat under similar conditions and record the raw timings and environment.

  6. Add West 30 to the original four-row table. Check West 250 and full total 380. If either formula still returns the old result, repair its source range before comparing speed.

Try it yourself

  1. Create two formula versions with identical results and write a comparison note covering source range, row count, calculation mode and measured timing procedure. It is acceptable for the small example to show no meaningful difference.
  2. Explain whether a shared review date should be live or a fixed reporting cutoff in a particular fictional report. Choose deliberately and label it so a performance change does not alter the intended meaning.
Show expected result
  • The original table returns West 220 and full total 350. Ten repeated blocks return West 2200 and full total 3500, providing simple independent correctness checks.
  • Adding West 30 to the original table produces West 250 and total 380. No fixed runtime improvement is claimed; only actually measured, comparable results belong in the performance note.

If something goes wrong

The faster formula ignores the new row.
Check its range endpoint or table source. Include the added record and rerun the result comparison before keeping the change.
Manual calculation seems much faster.
It may just defer the work. Recalculate both versions fully, record the mode and compare the same workload.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What should West return after appending West 30 to the original list?
2. Which is an acceptable performance conclusion?
3. Which function is commonly volatile?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Compare these formulas for source coverage and repeated volatile calls. Propose a fair timing method that uses the same data and checks the results first.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…