Graphed LogoGraphed
Login

How to Create an Invoice Tracker in Excel

Cody Schneider•

Chasing unpaid invoices is one of the most stressful parts of running your own business, and disorganized tracking only makes it worse. A simple, well-structured invoice tracker can bring calm to the chaos, helping you manage your cash flow and get paid on time. This guide will walk you through creating a powerful invoice tracker in Excel, from a basic setup to automated formulas that do the heavy lifting for you.

Why an Excel Invoice Tracker is a Game-Changer

Before jumping into the setup, it's worth knowing why this is such a good use of your time. Manually sending invoices and then forgetting about them is a recipe for cash flow problems. A good tracker moves you from being reactive ("Oh no, this invoice is 60 days late!") to proactive. It gives you a single place to see:

  • What you're owed at a glance (Total Outstanding)

  • Which invoices are coming due soon

  • Which clients are consistently late payers

  • Your total revenue over any period for tax purposes

In short, it’s a simple system that pays for itself many times over by making sure you collect the money you've earned.

Step 1: Setting Up Your Spreadsheet and Headers

The foundation of any good tracker is a logical structure. A confusing layout will just lead to more headaches. Open a new Excel sheet (or Google Sheet) and create a set of clear headers for your columns. Here are the essentials that every invoice tracker should have:

  • Invoice Number: A unique identifier for each invoice (e.g., CLIENT-001, 2024-001). This is crucial for referencing specific payments with clients.

  • Client Name: Who the invoice is for. Clean and consistent naming here is important for later reports.

  • Project/Service Description: A brief description of the work you did. This adds helpful context when you’re looking back months later.

  • Invoice Date: The day you sent the invoice to the client.

  • Due Date: The date the payment is due.

  • Amount: The total amount of the invoice, before taxes if you track those separately.

  • Date Paid: The day you actually received the payment. This column will be empty for outstanding invoices.

  • Late / Overdue (Days): An automated column that calculates how many days an invoice is overdue. Don't worry, we'll build a formula for this.

  • Status: The current state of the invoice. This provides a quick visual cue (e.g., Sent, Paid, Overdue).

Enter these headers into the first row of your spreadsheet (A1, B1, C1, and so on). Your sheet should look something like this:

Turn Your Data into an Excel Table

This is a small step that makes a huge difference. Click anywhere within your headers and press Ctrl+T (or Cmd+T on Mac). A "Create Table" dialog box will appear - make sure the "My table has headers" box is checked and click OK.

Instantly, your data range will get formatted with alternating row colors and filter buttons on each header. But the real benefits are behind the scenes:

  • Auto-expansion: When you add a new invoice in the row right below the table, the table automatically expands to include it.

  • Formula fill-down: Any formulas you create will automatically be copied down to new rows, so you only have to write them once.

  • Easy sorting and filtering: Use the dropdown arrows on the headers to quickly sort by due date or filter to see only invoices for a specific client.

Step 2: Automating Your Tracker with Formulas

Now it’s time to make your spreadsheet smart. Adding a few simple formulas will save you loads of manual calculation and give you the at-a-glance information you need. We'll use the column setup from Step 1.

Calculating Due Dates Automatically

Many projects have standard payment terms, like "Net 30" (due 30 days after the invoice date). Instead of manually calculating this, let Excel do it for you. Assume your 'Invoice Date' is in column D.

In the first data row of your 'Due Date' column (E2), type this formula:

=D2+30

This formula simply takes the date in cell D2 and adds 30 days to it. Press Enter. If you've formatted your data as an Excel Table, this formula should automatically fill down for every row. If not, just click the small square in the bottom-right corner of the cell and drag it down.

Pro-Tip: Change 30 to 15, 60, or whatever your standard payment term is.

Creating an Automatic "Status" Column

This is the most powerful formula in our tracker. We want the 'Status' column (I2) to automatically update based on the dates. Here’s the logic we want to build:

  1. If the 'Date Paid' column (G2) has a date in it, the status should be "Paid."

  2. If not, then check if the 'Due Date' (E2) is in the past. If it is, the status should be "Overdue."

  3. If neither of the above is true, the status should be "Sent."

We can achieve this with a nested IF formula. In cell I2, enter the following:

=IF(G2<>"", "Paid", IF(E2<TODAY(), "Overdue", "Sent"))

Let's break that down:

  • =IF(G2<>"", "Paid", ...) — Checks if cell G2 is NOT empty (<>""). If true (there's a paid date), it returns "Paid."

  • ... IF(E2<TODAY(), "Overdue", "Sent")) — If G2 is empty, this second IF function runs. It checks if the due date in E2 is less than today's date (<TODAY()). If true, it returns "Overdue." If false, it returns "Sent."

Calculating "Days Overdue"

In your 'Late / Overdue (Days)' column (H2), you can add a formula to see exactly how late a payment is. This is useful when sending reminder emails.

Enter this formula in cell H2:

=IF(I2="Overdue", TODAY()-E2, 0)

This checks the 'Status' column (I2). If the status is "Overdue", it calculates the number of days between today and the due date (TODAY()-E2). If the invoice isn't overdue, it just shows 0. You may need to format this cell as a "Number" if it shows up as a date.

Step 3: Leveling Up Your Tracker with Formatting and Dashboards

Now that the core logic is in place, we can add some polish to make the tracker even easier to use. Visual cues help you spot important information without having to read every single line.

Use Conditional Formatting to Highlight What Matters

Conditional Formatting automatically changes a cell's appearance based on its value. Let’s make our "Overdue" invoices impossible to miss.

  1. Highlight your entire data set inside the table (excluding the headers).

  2. Go to the Home tab ribbon, click Conditional Formatting → New Rule.

  3. Select "Use a formula to determine which cells to format."

  4. In the formula box, enter: $I2="Overdue" (This checks if the value in column I for the current row is "Overdue". The $ anchors the check to column I.)

  5. Click the "Format..." button. Choose a light red fill color and click OK twice.

Now, any row with an "Overdue" invoice will instantly stand out with a red highlight. You can repeat this process to make "Paid" invoices green ($I2="Paid").

Creating an "At-a-Glance" Summary Dashboard

A small summary section at the top of your sheet can provide a powerful financial overview. Here’s how to set one up in an empty area above your table.

Create a few labels first: Total Billed, Total Collected, and Total Outstanding.

Next to "Total Billed," use a simple SUM formula to add up all invoice amounts. Assuming your invoice amounts are in column F:

=SUM(Table1[Amount])

Next to "Total Collected," use a SUMIF formula to add up only the paid invoices. This formula looks for the word "Paid" in the 'Status' column (column I) and only sums the corresponding amount from the 'Amount' column (F).

=SUMIF(Table1[Status], "Paid", Table1[Amount])

Next to "Total Outstanding," you can subtract Total Collected from Total Billed:

=[Total Billed Cell] - [Total Collected Cell]

Or use another SUMIF to add up invoices that are not paid:

=SUMIF(Table1[Status], "<>Paid", Table1[Amount])

(Note: Table1 is the default name for the first Excel Table you create. Yours might be different. Excel will usually autocomplete the structured table reference name for you.)

This mini-dashboard now gives you a real-time summary of your finances every time you update your tracker.

Final Thoughts

Putting together an invoice tracker in Excel is a small time investment that gives you huge control over your cash flow and significantly reduces financial stress. By following these steps, you’ll have a professional, automated system for tracking what you’re owed, what’s overdue, and how your business is performing over time.

Of course, as your business grows, manually updating spreadsheets across different revenue sources can still become a painful chore. That's actually why we built Graphed. We wanted a way to connect directly to all our sales and marketing sources - like Stripe, Shopify, or HubSpot - and automatically build real-time dashboards using plain English. If you ever find yourself spending more time managing spreadsheets than analyzing your business, we can help you turn all that manual data work into a 30-second conversation.