Excel formulas for work: build an invoice tracker you can trust

Knowing a function's name is different from knowing when to use it. A useful workplace spreadsheet answers a specific question and lets another person check the answer. This guide uses a fictional invoice tracker to practise four common tasks: adding amounts, identifying unpaid invoices, counting records and finding a matching value.

Use invented records while learning. Do not upload customer, pupil, employee or financial details to a training service without appropriate permission and safeguards. For one-to-one Excel training, a small anonymised example is usually enough to explain the task.

Start with the question and the data

Put this table in A1:D4. Enter the amounts as numbers, not sentences. Use a consistent status spelling and a separate invoice identifier for each record.

The business questions are straightforward: what is the total invoiced, how much remains unpaid, and which customer belongs to a particular invoice? Decide those questions before choosing the formula. A colourful dashboard is not useful if it is answering the wrong question.

SUM: add the intended records

Use =SUM(C2:C4) to get 350. The range should contain the individual invoice amounts and nothing else. If you later add a subtotal below the records, do not include both the subtotal and its underlying rows in another total.

Test the formula by increasing C2 from 120 to 130. The total should rise by 10. Restore the value afterwards. This small change is a quick way to check whether the formula is reading the cells you intended.

IF: make the rule visible

Label E1 “Unpaid amount”. In E2 enter =IF(D2="Unpaid",C2,0) and copy it down to E4. The results should be 0, 80 and 150. Summing E2:E4 gives 230.

Here zero has a specific meaning: this record contributes nothing to the unpaid total. A blank status is different from a confirmed payment. Before using the tracker in real work, agree how missing or unrecognised statuses should be flagged instead of silently treating them as paid. For example, use a controlled dropdown and check that no statuses are missing.

COUNTIF: count records, not pounds

Use =COUNTIF(D2:D4,"Unpaid") to count two unpaid invoices. This is a count of records, not the total amount owed. Put a clear label beside it so nobody mistakes “2” for a financial result.

If the result is unexpected, inspect spelling, extra spaces and whether the selected range includes all records. Two identical invoice numbers might represent duplicate data rather than two separate debts. Formula correctness cannot fix an unclear definition of what each row represents.

XLOOKUP: find a matching invoice

Type INV002 into G2. In H2 enter =XLOOKUP(G2,A2:A4,B2:B4,"Not found"). The result should be Willow. Try INV999: the result should be “Not found”, not the customer from a nearby row.

The lookup and return ranges must describe the same records in the same order. Invoice identifiers should be unique because a normal lookup returns one match, not a total across duplicates. Microsoft notes that XLOOKUP is unavailable in Excel 2016 and 2019. If that is your version, learn an exact-match VLOOKUP or INDEX/MATCH approach rather than copying a formula your software cannot use.

A five-minute handover check

• Can another person tell what each row represents?
• Are inputs separate from calculated results?
• Does adding one record update every relevant total?
• Are missing statuses and duplicate identifiers visible?
• Can you explain why the total and unpaid amount differ?

Once these checks are comfortable, move to the advanced Excel skills checklist. Progress means producing a repeatable, checkable result, not collecting the longest formula you can write.