What you will build

You will build a four-column budget for Rent, Food, and Travel. Planned and Actual amounts stay as inputs; Variance is calculated as Actual minus Planned so a negative number means spending is under plan and a positive number means spending is over plan.

Learning objectives

Open the practice workbook

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Sample data

CategoryPlannedActualVariance
Rent800800
Food300270
Travel150190

Keep columns A:C as the source inputs. Column D is reserved for the variance formula.

Google Sheets budget source table with Category, Planned, Actual, and Variance columns before the variance formula is filled down.

Hands-on practice

Step 1: Confirm A1:D4 contains the four headers Category, Planned, Actual, Variance and the three rows Rent, Food, and Travel. Leave D2:D4 for calculated results.

Step 2: Select D2 and enter =C2-B2. Press Enter. The Rent variance should be 0 because Actual 800 minus Planned 800 equals 0.

=C2-B2
Cell D2 selected in Google Sheets with =C2-B2 visible in the formula bar and the source budget table visible.

Step 3: Copy/fill D2 down through D4; do not retype separate formulas in D3 and D4. Google Sheets should adjust the relative references to =C3-B3 and =C4-B4.

Step 4: Verify D2:D4 shows 0, -30, and 40. As a formula test, change C3 (Food Actual) from 270 to 300 and confirm D3 recalculates from -30 to 0. Restore C3 to 270 and confirm D3 returns to -30.

Google Sheets budget checkpoint with D2:D4 filled by relative formulas and results 0, -30, and 40 after the Food test value is restored.

Checkpoint

CategoryPlannedActualVariance
Rent8008000
Food300270-30
Travel15019040

D2:D4 must be formula results. After the test, Food Actual is restored to 270 and Food Variance is -30.

Common mistakes and fixes

If every row points to row 2, the references were locked or copied incorrectly. Restore D2 to =C2-B2, then fill D2 down through D4 again so the row references change automatically.

Independent practice

Add Utilities with Planned 120 and Actual 135 below the sample. Fill the existing Variance formula into the new row and predict the result before checking it. Then remove the practice row so the workbook returns to the checkpoint.

Key takeaways

  • ✓Keep Planned and Actual as source inputs and calculate Variance in column D.
  • ✓Enter =C2-B2 once in D2, then fill it down instead of retyping equivalent formulas.
  • ✓Interpret negative variance as under plan, positive variance as over plan, and zero as exactly on plan.
  • ✓Verify formulas by changing an input, observing recalculation, and restoring the original value.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. Which formula belongs in D2?
2. After filling D2 down to D3, what should the formula become?
3. What does Food variance -30 mean in this sample?
4. How should you test the Food formula without leaving the workbook changed?

Next lesson

Continue in recommended learning order with Audit a beginner spreadsheet checklist.