How to Build an Invoice Tracking Spreadsheet in Excel

A well-built invoice tracker saves you from the chaos of scattered PDFs, forgotten payments, and end-of-month scrambles. This tutorial walks you through building one from scratch in Excel.

By the end, you will have a spreadsheet that tracks every invoice, highlights overdue payments automatically, and gives you totals by vendor at a glance.

Setting Up Your Columns

Start with a new worksheet and create these column headers in row 1:

A: Invoice Number
B: Vendor / Client
C: Invoice Date
D: Due Date
E: Amount
F: Amount Paid
G: Status
H: Days Overdue

Format column A as Text (to preserve leading zeros in invoice numbers). Format columns C and D as Date. Format columns E and F as Currency.

The Status Column

Column G uses a formula to calculate status automatically:

=IF(F2>=E2, "Paid", IF(TODAY()>D2, "Overdue", "Pending"))

This checks three conditions in order:

1. If the amount paid equals or exceeds the invoice amount — Paid
2. If today's date is past the due date — Overdue
3. Otherwise — Pending

Days Overdue

Column H shows how many days an invoice is past due:

=IF(G2="Overdue", TODAY()-D2, 0)

This only calculates for overdue invoices. Paid and pending invoices show 0.

Adding Conditional Formatting

Visual cues make scanning your tracker much faster. Select the Status column (G2 downwards), then apply these rules:

For "Paid" cells:

• Condition: Cell value equals "Paid"
• Format: Green fill, dark green text

For "Overdue" cells:

• Condition: Cell value equals "Overdue"
• Format: Red fill, dark red text

For "Pending" cells:

• Condition: Cell value equals "Pending"
• Format: Yellow fill, dark yellow text

Now you can see your payment status at a glance without reading individual cells.

Highlighting Large Overdue Amounts

Add another rule for column E (Amount) where the corresponding status is "Overdue" and the amount exceeds a threshold:

=AND($G2="Overdue", $E2>1000)

Format these with a bold red border. These are the invoices that need your attention first.

Using SUMIFS for Vendor Totals

Below your main data (or on a separate summary sheet), use SUMIFS to calculate totals by vendor:

=SUMIFS(E:E, B:B, "Vendor Name", G:G, "Pending")

This sums all pending invoice amounts for a specific vendor. Create a small summary table:

| Vendor        | Total Pending | Total Overdue | Total Paid  |
|---------------|---------------|---------------|-------------|
| ABC Services  | =SUMIFS(...)  | =SUMIFS(...)  | =SUMIFS(...)|
| XYZ Ltd       | =SUMIFS(...)  | =SUMIFS(...)  | =SUMIFS(...)|

Each cell uses SUMIFS with the appropriate vendor name and status filter.

Grand Totals

Add a totals row using SUMIF (single criterion):

=SUMIF(G:G, "Pending", E:E)
=SUMIF(G:G, "Overdue", E:E)
=SUMIF(G:G, "Paid", F:F)

This gives you a dashboard-style overview of your entire accounts payable position.

Building a Pivot Table Summary

For a more dynamic view, create a pivot table from your data:

1. Select your entire data range (A1 through to the last row of data)
2. Go to Insert → PivotTable
3. Place it on a new worksheet

Configure the pivot table:

• Rows: Vendor / Client
• Columns: Status
• Values: Sum of Amount

This instantly creates a matrix showing how much you owe each vendor, broken down by payment status. You can filter by date range, sort by amount, and drill into individual invoices.

Adding a Timeline Filter

If your data spans multiple months, add a Timeline filter:

1. Click inside the pivot table
2. Go to PivotTable Analyze → Insert Timeline
3. Select the Invoice Date field

Now you can filter your summary by month, quarter, or custom date range using a visual slider.

Data Validation for Consistency

Prevent errors by adding data validation to key columns:

Vendor column (B): Create a dropdown list of your known vendors. Go to Data → Validation → List and reference a named range of vendors on a separate sheet.

Status column (G): Although this is formula-driven, if you ever need manual overrides, add validation allowing only "Paid", "Pending", "Overdue", and "Disputed".

Amount columns (E, F): Set validation to only allow numbers greater than 0. This prevents accidental text entries.

Automating the Hard Part

This tracker works brilliantly once your data is in Excel. The bottleneck is getting it there in the first place. If you receive invoices as PDFs, you are probably typing data manually — vendor names, amounts, dates, line items.

That manual step is where errors creep in and time gets wasted. Consider extracting invoice data automatically and importing it directly into your tracker. The formulas, formatting, and pivot tables all work regardless of how the data arrives.

Maintenance Tips

• Freeze the header row — View → Freeze Panes → Freeze Top Row
• Protect formula columns — lock columns G and H so formulas do not get accidentally overwritten
• Archive completed months — move fully paid months to a separate "Archive" sheet to keep your active tracker fast
• Back up weekly — save a dated copy every Friday

Summary

A good invoice tracker is not complicated — it is disciplined. The right column structure, automated status formulas, conditional formatting for visual scanning, and SUMIFS for totals give you complete control over your accounts payable. Add a pivot table for dynamic reporting, and you have a system that scales from ten invoices to thousands.