Invoice tracker

What you will build

Build a small invoice tracker whose Balance and Status are calculated from Amount, Paid, and Due. The lesson uses fictional data so every action can be reproduced without real customer information.

Learning objective

Use two spilling array formulas to calculate remaining balances and flag overdue invoices, understand row-by-row alignment across equal-sized ranges, and verify the result when Paid changes.

Prepare the sample data

Open the exact source workbook below and make a copy for practice. The Example tab contains Invoice, Customer, Amount, Paid, and Due sample data. The source still contains demonstration columns from an earlier setup; on your copy, clean F:H as described in Step 1 before entering the new formulas.

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.

The Invoice tracker source workbook in Google Sheets with the Example tab and all sample invoice data visible.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: On your copy, keep A:E as the source data. Clear F2:H4, clear the old headers in G1:H1, keep F1 as Balance, and enter Status in G1. The A:G table is then ready for the two new formulas.

The _result workbook after the formula area is cleaned, with Balance in F1, Status in G1, and F2:G4 blank.

Step 2: Select F2 and enter the Balance formula below once. Its results spill through F2:F4 automatically; do not drag the fill handle or copy the formula down.

Balance formula

=ARRAYFORMULA(C2:C4-D2:D4)

How this formula works=ARRAYFORMULA(C2:C4-D2:D4)

Overall meaning of the whole formula
- Subtract column D from column C for rows 2–4 in one array formula, returning one corresponding result per row.
ARRAYFORMULA(array_formula)
- array_formula is required; it accepts an expression that can produce one or more values. Here the expression passed is C2:C4-D2:D4.
- C2:C4 contains Amount values 500, 340, and 220. D2:D4 contains Paid values 500, 100, and 0.
- Both ranges are three rows tall, so Sheets aligns elements from the same row before subtraction.
- ARRAYFORMULA lets the three-item result spill from anchor cell F2 through F4, so no fill or copy step is needed.
Whole-expression evaluation
- Row 2: C2-D2 = 500-500 = 0.
- Row 3: C3-D3 = 340-100 = 240.
- Row 4: C4-D4 = 220-0 = 220.
- The resulting array is {0; 240; 220}.
Why the displayed final result appears
- F2:F4 displays 0, 240, and 220 because each Amount is reduced by the Paid value from the same row.
- During the audit, changing D4 from 0 to 220 changed F4 from 220 to 0; restoring D4 to 0 returned F4 to 220.
- This is a spill formula anchored in F2, so there is no retyping or copy-down step for F3:F4.

Step 3: Select G2 and enter the Status formula below once. Its results spill through G2:G4 automatically; do not drag the fill handle or copy the formula down.

Status formula

=ARRAYFORMULA(IF((F2:F4>0)*ISNUMBER(E2:E4)*(E2:E4<TODAY()),"Overdue",""))

The _result workbook with G2 selected, the formula bar showing the complete ARRAYFORMULA, and only invoice I-03 displaying Overdue.

How this formula works=ARRAYFORMULA(IF((F2:F4>0)*ISNUMBER(E2:E4)*(E2:E4<TODAY()),"Overdue",""))

Overall meaning of the whole formula
- Evaluate rows 2–4; when the remaining balance is greater than 0, the date in column E is valid, and that date is before today, mark the row "Overdue"; otherwise leave it blank.
ARRAYFORMULA(array_formula)
- array_formula is required; here it is the complete IF expression operating on F2:F4 and E2:E4.
- ARRAYFORMULA lets one formula in G2 return three row-aligned invoice statuses and spill through G4.
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression is required; this lesson passes the product of three condition arrays: (F2:F4>0)*ISNUMBER(E2:E4)*(E2:E4<TODAY()).
- value_if_true is required; this lesson passes "Overdue".
- value_if_false is optional; this lesson passes "" so a row stays blank when it does not satisfy every condition.
ISNUMBER(value)
- value is required; this lesson passes E2:E4.
- Valid dates in Google Sheets are stored as serial numbers, so ISNUMBER(E2:E4) tests whether each Due cell is a valid date/number value.
TODAY()
- TODAY takes no parameters; its parentheses stay empty.
- It returns the spreadsheet current date at calculation time. E2:E4<TODAY() is TRUE only for due dates earlier than that date.
Condition arrays and multiplication
- F2:F4>0 tests for a positive remaining balance and evaluates to {FALSE; TRUE; TRUE}.
- ISNUMBER(E2:E4) is TRUE for all three sample rows because all three Due cells are valid date values.
- At audit time, I-02 due 2026-10-05 is not earlier than TODAY, while I-03 due 2026-09-25 is earlier than TODAY.
- All three arrays are three rows tall, so they align by invoice. Multiplication coerces TRUE/FALSE to 1/0; only a row with all three TRUE conditions produces 1.
Whole-expression evaluation
- I-01 has Balance 0, so the first condition is FALSE and Status stays blank.
- I-02 has Balance 240, but Due 2026-10-05 is not earlier than TODAY at audit time, so Status stays blank.
- I-03 has Balance 220, a valid Due date, and Due 2026-09-25 is past, so IF returns Overdue.
Why the displayed final result appears
- G2:G4 displays blank, blank, Overdue.
- During the audit, changing I-03 Paid from 0 to 220 reduced its Balance to 0 and removed Overdue; restoring Paid to 0 returned Balance to 220 and Status to Overdue.
- This is a spill formula anchored in G2, so there is no copy-down step for G3:G4.

Checkpoint

Invoice	Customer	Amount	Paid	Due	Balance	Status
I-01	Acme Demo	500	500	2026-09-20	0
I-02	North Demo	340	100	2026-10-05	240
I-03	River Demo	220	0	2026-09-25	220	Overdue

Overdue depends on TODAY at calculation time. In this audit, I-03 is the only invoice that simultaneously has Balance > 0, a valid Due date, and Due earlier than TODAY.

Common mistakes and fixes

If Status is unexpected, confirm Paid is numeric, Due is a real date value, and all condition ranges have the same number of rows. TODAY also means the result can change as the calculation date changes.

Independent practice

Temporarily change D4 from 0 to 220. Confirm F4 changes from 220 to 0 and G4 changes from Overdue to blank; then restore D4 to 0 and verify both results return to their original state.

Key takeaways

✓Balance is calculated row by row as Amount minus Paid.
✓One ARRAYFORMULA in F2 produces all Balance values: 0, 240, and 220.
✓Status is Overdue only when Balance is positive, Due is a valid date, and Due is earlier than TODAY.
✓One ARRAYFORMULA in G2 produces all three Status values, so no drag or copy-down step is needed.
✓Changing Paid automatically updates both Balance and Status; with the restored sample data, only I-03 is Overdue.

Continue learning

Previous lesson: Simple CRM spreadsheet. Next lesson: Project timeline.

What you will build

Build a small invoice tracker whose Balance and Status are calculated from Amount, Paid, and Due. The lesson uses fictional data so every action can be reproduced without real customer information.

Learning objective

Use two spilling array formulas to calculate remaining balances and flag overdue invoices, understand row-by-row alignment across equal-sized ranges, and verify the result when Paid changes.

Prepare the sample data

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.

Open the exact source workbook in Google Sheets ↗

Work through the example

Step 1: On your copy, keep A:E as the source data. Clear F2:H4, clear the old headers in G1:H1, keep F1 as Balance, and enter Status in G1. The A:G table is then ready for the two new formulas.

Step 2: Select F2 and enter the Balance formula below once. Its results spill through F2:F4 automatically; do not drag the fill handle or copy the formula down.

Balance formula

=ARRAYFORMULA(C2:C4-D2:D4)

How this formula works=ARRAYFORMULA(C2:C4-D2:D4)

Overall meaning of the whole formula
- Subtract column D from column C for rows 2–4 in one array formula, returning one corresponding result per row.
ARRAYFORMULA(array_formula)
- array_formula is required; it accepts an expression that can produce one or more values. Here the expression passed is C2:C4-D2:D4.
- C2:C4 contains Amount values 500, 340, and 220. D2:D4 contains Paid values 500, 100, and 0.
- Both ranges are three rows tall, so Sheets aligns elements from the same row before subtraction.
- ARRAYFORMULA lets the three-item result spill from anchor cell F2 through F4, so no fill or copy step is needed.
Whole-expression evaluation
- Row 2: C2-D2 = 500-500 = 0.
- Row 3: C3-D3 = 340-100 = 240.
- Row 4: C4-D4 = 220-0 = 220.
- The resulting array is {0; 240; 220}.
Why the displayed final result appears
- F2:F4 displays 0, 240, and 220 because each Amount is reduced by the Paid value from the same row.
- During the audit, changing D4 from 0 to 220 changed F4 from 220 to 0; restoring D4 to 0 returned F4 to 220.
- This is a spill formula anchored in F2, so there is no retyping or copy-down step for F3:F4.

Step 3: Select G2 and enter the Status formula below once. Its results spill through G2:G4 automatically; do not drag the fill handle or copy the formula down.

Status formula

=ARRAYFORMULA(IF((F2:F4>0)*ISNUMBER(E2:E4)*(E2:E4<TODAY()),"Overdue",""))

How this formula works=ARRAYFORMULA(IF((F2:F4>0)*ISNUMBER(E2:E4)*(E2:E4<TODAY()),"Overdue",""))

Overall meaning of the whole formula
- Evaluate rows 2–4; when the remaining balance is greater than 0, the date in column E is valid, and that date is before today, mark the row "Overdue"; otherwise leave it blank.
ARRAYFORMULA(array_formula)
- array_formula is required; here it is the complete IF expression operating on F2:F4 and E2:E4.
- ARRAYFORMULA lets one formula in G2 return three row-aligned invoice statuses and spill through G4.
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression is required; this lesson passes the product of three condition arrays: (F2:F4>0)*ISNUMBER(E2:E4)*(E2:E4<TODAY()).
- value_if_true is required; this lesson passes "Overdue".
- value_if_false is optional; this lesson passes "" so a row stays blank when it does not satisfy every condition.
ISNUMBER(value)
- value is required; this lesson passes E2:E4.
- Valid dates in Google Sheets are stored as serial numbers, so ISNUMBER(E2:E4) tests whether each Due cell is a valid date/number value.
TODAY()
- TODAY takes no parameters; its parentheses stay empty.
- It returns the spreadsheet current date at calculation time. E2:E4<TODAY() is TRUE only for due dates earlier than that date.
Condition arrays and multiplication
- F2:F4>0 tests for a positive remaining balance and evaluates to {FALSE; TRUE; TRUE}.
- ISNUMBER(E2:E4) is TRUE for all three sample rows because all three Due cells are valid date values.
- At audit time, I-02 due 2026-10-05 is not earlier than TODAY, while I-03 due 2026-09-25 is earlier than TODAY.
- All three arrays are three rows tall, so they align by invoice. Multiplication coerces TRUE/FALSE to 1/0; only a row with all three TRUE conditions produces 1.
Whole-expression evaluation
- I-01 has Balance 0, so the first condition is FALSE and Status stays blank.
- I-02 has Balance 240, but Due 2026-10-05 is not earlier than TODAY at audit time, so Status stays blank.
- I-03 has Balance 220, a valid Due date, and Due 2026-09-25 is past, so IF returns Overdue.
Why the displayed final result appears
- G2:G4 displays blank, blank, Overdue.
- During the audit, changing I-03 Paid from 0 to 220 reduced its Balance to 0 and removed Overdue; restoring Paid to 0 returned Balance to 220 and Status to Overdue.
- This is a spill formula anchored in G2, so there is no copy-down step for G3:G4.

Checkpoint

Invoice	Customer	Amount	Paid	Due	Balance	Status
I-01	Acme Demo	500	500	2026-09-20	0
I-02	North Demo	340	100	2026-10-05	240
I-03	River Demo	220	0	2026-09-25	220	Overdue

Overdue depends on TODAY at calculation time. In this audit, I-03 is the only invoice that simultaneously has Balance > 0, a valid Due date, and Due earlier than TODAY.

Common mistakes and fixes

Independent practice

Temporarily change D4 from 0 to 220. Confirm F4 changes from 220 to 0 and G4 changes from Overdue to blank; then restore D4 to 0 and verify both results return to their original state.

Key takeaways

✓Balance is calculated row by row as Amount minus Paid.
✓One ARRAYFORMULA in F2 produces all Balance values: 0, 240, and 220.
✓Status is Overdue only when Balance is positive, Due is a valid date, and Due is earlier than TODAY.
✓One ARRAYFORMULA in G2 produces all three Status values, so no drag or copy-down step is needed.
✓Changing Paid automatically updates both Balance and Status; with the restored sample data, only I-03 is Overdue.

Continue learning

Previous lesson: Simple CRM spreadsheet. Next lesson: Project timeline.

Invoice tracker

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Balance formula

Status formula

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Invoice tracker

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Balance formula

Status formula

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments