What you will build
Reduce calculation lag in a growing order tracker without losing required records. This lesson uses fictional sample data so you can reproduce each action.
Learning objective
Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.
Prepare the sample data
Paste this example as tab-separated values beginning at A1 on a new worksheet:
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.
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.
Open the sample workbook in Google Drive (access required) ↗
Work through the example
Step 1: In H2, enter =SUMIF(B:B,"North",C:C). Confirm the sample returns 200; this whole-column formula has to consider every row in columns B and C when recalculating.
Step 2: Replace it with =SUMIF(B2:B5,"North",C2:C5). Confirm the result is still 200 while the formula now scans only the four actual data rows.
Step 3: When new records are appended, extend the bounded ranges only as far as needed and avoid duplicating unnecessary volatile calculations.
Formula to try
In the verified practice workbook, H2 is the result cell. After comparing the whole-column formula in Step 1, leave H2 using the bounded formula below and confirm the result is 200.
=SUMIF(B2:B5,"North",C2:C5)Checkpoint
| Order | Region | Amount |
|---|---|---|
| O01 | North | 120 |
| O02 | South | 240 |
| O03 | North | 80 |
| O04 | South | 320 |
The source table should keep the four rows above, and H2 should display 200 with =SUMIF(B2:B5,"North",C2:C5). If the data lands in one cell, undo, select A1, and paste the TSV again.
Common mistake and correction
Correction: select H2, read the complete expression in the formula bar, and compare its referenced ranges directly with the four Order, Region, Amount rows in the source table.
Independent practice
After following the example, complete this variation without editing the source data: Append O05 and update the bounded range; confirm the new North amount is included.
Key takeaways
- ✓Whole-column references such as B:B and C:C can increase the amount of data recalculated in large workbooks.
- ✓=SUMIF(B:B,"North",C:C) and =SUMIF(B2:B5,"North",C2:C5) both return 200 for this four-row sample.
- ✓Bounded ranges keep the formula focused on the rows that actually contain data.
- ✓When new rows are added, extend the bounded range deliberately instead of reverting to full-column references.
- ✓If the workbook is still slow, inspect volatile formulas and duplicated calculations as well.
Check your understanding
Answer four questions about the practice sheet.
Continue learning
Previous lesson: Fix incorrect date formats; Next lesson: —.
Comments
No comments yet. Be the first to join the discussion.