AI WORLD HQHow to Build a Clean Invoice and Payment Tracker in Excel with Copilot
Set up a simple spreadsheet that tracks who owes you, when it's due, and gently highlights overdue payments.
In short: You can use Copilot in Excel to build a simple invoice tracker by describing the columns you want in plain English and letting Copilot add the formulas — but Copilot requires a paid Microsoft 365 plan, and it can't send payment reminder emails for you.
If you send invoices to clients, you know the awkward feeling of chasing payments. A clean tracker turns that into a 30-second glance. With Copilot in Excel, you describe what you want in everyday words and watch a working tracker appear — no manual formula-writing, no fiddly formatting. This guide is for small-business owners, freelancers, and anyone who wants a simple "who owes me, and how late" view without buying accounting software.
💡 Tip: tap a step’s number when you finish it — a green tick appears and your browser remembers how far you got.
- A paid Microsoft 365 subscription that includes Copilot — either Copilot Pro for personal use, or Copilot for Microsoft 365 for business. Check Microsoft's current pricing page to see which plans include Copilot.
- Excel on Windows, Mac, or the web. Some Copilot features appear slightly differently between desktop and web.
- About 15–20 minutes for the first build; faster once you have a template.
- A handful of real invoices to type in (even three to five rows is enough to test the formulas).
- Honestly, what this guide cannot do for you: send the actual reminder emails. You will still need Outlook, Gmail, or another email tool to do the chasing.
Open a new workbook and open the Copilot panel
Open Excel and start a blank workbook (the option usually labeled "Blank workbook" on the start screen). Now look at the ribbon — that's the long strip of buttons across the top of Excel, with tabs like Home, Insert, and Page Layout. Find the Copilot button near the top-right of the Home tab, usually marked with a small sparkle or four-pointed-star icon ✨. Click it once. A chat panel slides open on the right side of the screen with a short welcome message and a few suggested prompts. If you don't see a Copilot button anywhere on the ribbon, your plan almost certainly doesn't include it — that's the most common reason this step fails. As a fallback, look under the Home tab for any item labeled "Copilot", or check whether a Copilot tab appears as its own ribbon section.
You'll know it worked when a chat panel appears on the right side of the screen and Copilot replies to your first message.
Ask Copilot to design the table
Click into cell A1 of the new workbook (the very first cell in the top-left corner) so Copilot knows where to start. In the Copilot panel, type a request describing the columns you want. Copilot usually proposes a sample table in the panel and asks if you want to insert it. Click Insert and the column headers appear across row 1, with example data in row 2. If your version of Copilot asks you to add at least one row of data first, just type a single fake invoice in row 2 and ask again.
You'll know it worked when seven column headers appear in row 1 and small filter dropdown arrows show up on each header (you can use these arrows later to sort or filter).
Fill in your real invoices
Click into the cells under each header and type your actual invoice details — one invoice per row. The table grows automatically as you type, and the filter arrows move down with you. For dates, type them in a form Excel recognizes, like 15 March 2026 or 3/15/2026, and Excel will store them as real dates. For amounts, type just the number (no dollar sign) and Excel handles the rest. If the filter dropdowns didn't appear after Step 2, click anywhere inside your data and press Ctrl + T (Windows) or ⌘ + T (Mac) to convert the range into a proper table.
You'll know it worked when you can click the filter arrow on the Client column and see the names you entered.
Fill in the Status column yourself (and let Copilot handle the formula)
The Status column is one you'll type into by hand for each row — things like "Paid", "Pending", or "Overdue" — because only you know the real status. To handle "Overdue" automatically, click into the Days Overdue column on row 2 and ask Copilot to write a formula. Copilot usually shows the formula in the panel first; click Insert to drop it into the cell, and Excel will auto-fill it down the rest of the column. So the simple rule: Status = you type the words; Days Overdue = Copilot writes the formula. If the formula looks off, ask Copilot to rewrite it; never trust a formula you can't explain in plain English.
You'll know it worked when numbers like 5, 12, or 0 appear in the Days Overdue column, with overdue rows showing positive numbers.
Ask Copilot to color-code the overdue rows
This is the "gentle nudge" part. Ask Copilot to apply conditional formatting — that's a rule that automatically colors cells based on their value, so the colors update themselves when your data changes. Copilot typically shows a preview of which cells will be highlighted before applying. Confirm and the formatting sticks. If the prompt doesn't work, do it manually: select the Days Overdue column, go to Home → Conditional Formatting → Highlight Cell Rules → Greater Than, and enter 7. For a yellow warning band, repeat with Between, then enter 1 and 7.
You'll know it worked when overdue rows visibly stand out — red for the late ones, yellow for the almost-late ones — and the colors stay correct even after you add new invoices.
Save, sort, and (manually) send reminders
Press Ctrl + S (Windows) or ⌘ + S (Mac) and pick a OneDrive or local location to save your file. Your tracker is now ready to use. Here's the honest limit again: Copilot in Excel cannot send emails automatically. For the actual nudge, you'll open Outlook or Gmail, filter your tracker by the red rows, and send a polite follow-up yourself. If you want automatic reminders, you'd need a separate tool like Power Automate, a dedicated invoicing app, or a workflow service — that's beyond what Excel's Copilot does today. To filter, click the dropdown arrow on the Status column, pick "Overdue", and your chase list appears in one click.
You'll know it worked when you can re-open the file tomorrow, sort by Days Overdue, and instantly see who to chase first.
- Trusting a formula you didn't read. Copilot writes decent formulas, but they're sometimes wrong — wrong column reference, wrong date logic, missing parentheses. Always click the cell and read the formula in the formula bar (the long white box above the spreadsheet that shows whatever a cell contains) before trusting the result. If it looks suspicious, ask Copilot to explain it in plain English, or rewrite the prompt.
- Skipping the plan check. If Copilot never appears, it's almost always because the account doesn't have Copilot enabled. Confirm your Microsoft 365 plan includes Copilot before troubleshooting anything else — that one check saves an hour.
- Typing dates as text. If you type "31 March" without a year, Excel may store it as text and your formulas will break silently. Always include the year, and confirm the column header shows the word "Date" in the column's format settings.
- Letting Copilot fill in Status for you. Status is a human judgment — "Paid", "Pending", "Overdue" — and Copilot will guess wrong. Type the Status yourself for each row.
Open a blank Excel workbook, click the Copilot button, and paste this exact prompt: "Create a table with columns: Client, Invoice Number, Invoice Date, Due Date, Amount, Status, Days Overdue." See what Copilot proposes before you change anything — that's the fastest way to feel how it works.
❓ Quick questions
How long does this take?
About 6 minutes — the guide has 6 steps, and you can tick each one off as you go.
Which tool do I need?
This guide uses Microsoft Copilot — but the approach works very similarly in other AI assistants.
Do I need to prepare anything?
- A paid Microsoft 365 subscription that includes Copilot — either Copilot Pro for personal use, or Copilot for Microsoft 365 for business. Check Microsoft's current pricing page to see which plans include Copilot.
- Excel on Windows, Mac, or the web. Some Copilot features appear slightly differently between desktop and web.
- About 15–20 minutes for the first build; faster once you have a template.
- A handful of real invoices to type in (even three to five rows is enough to test the formulas).
- Honestly, what this guide cannot do for you: send the actual reminder emails. You will still need Outlook, Gmail, or another email tool to do the chasing.
What mistakes should I avoid?
- Trusting a formula you didn't read. Copilot writes decent formulas, but they're sometimes wrong — wrong column reference, wrong date logic, missing parentheses. Always click the cell and read the formula in the formula bar (the long white box above the spreadsheet that shows whatever a cell contains) before trusting the result. If it looks suspicious, ask Copilot to explain it in plain English, or rewrite the prompt.
- Skipping the plan check. If Copilot never appears, it's almost always because the account doesn't have Copilot enabled. Confirm your Microsoft 365 plan includes Copilot before troubleshooting anything else — that one check saves an hour.
- Typing dates as text. If you type "31 March" without a year, Excel may store it as text and your formulas will break silently. Always include the year, and confirm the column header shows the word "Date" in the column's format settings.
- Letting Copilot fill in Status for you. Status is a human judgment — "Paid", "Pending", "Overdue" — and Copilot will guess wrong. Type the Status yourself for each row.
Keep reading
✦ Original step-by-step guide by AI World HQ's AI editorial team. Written in plain language, reviewed for accuracy.
← Back to all stories