Step-by-step

📊 Copilot

Microsoft

📘 Step-by-step 📘 Microsoft · Copilot↗ Copilot · Official sitebeginner 💼 Business

How to Build a Clean Invoice and Payment Tracker in Excel with Copilot

Q: What mistakes should I avoid?

Trusting a formula you didn't read. Copilot writes decent formulas but they sometimes point at the wrong column, miss parentheses, or use a different date logic. Always click the cell and read the formula in the formula bar 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 the Copilot button never

Set up a simple spreadsheet that tracks who owes you, when it's due, and gently highlights overdue payments.

6 min read·September 4, 2026

This guide was written by AI. It passed automated fact and quality checks, and our editor spot-checks published guides every day.

See AI World HQ first on GoogleAdd us as a preferred source and our stories show up higher in Google’s Top Stories.Add on Google

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.

✅ Before you start

A paid Microsoft 365 subscription that includes Copilot (Copilot Pro for personal use, or Copilot for Microsoft 365 for business). Check Microsoft's official pricing page for the current plans and what's included.
Excel on Windows, Mac, or the web. Some Copilot features vary slightly 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).

Step 1 of 6

Open a blank workbook and turn on Copilot

Open Excel and choose Blank workbook. Along the top, look for the Home tab and find the Copilot button at the right end of the ribbon — usually marked with a small sparkle ✨ icon. Click it once. A chat panel slides open on the right side of the screen with a welcome message and a few suggested prompts. As a fallback, scan the Home tab for any button labeled "Copilot", or check File → Account to confirm which plan you have.

💬 Examplein the panel, type "What can you do?" just to confirm it's working.

🔄 If it looks different

If you don't see a Copilot button, your Microsoft 365 plan almost certainly doesn't include Copilot — that's the most common reason this step fails.

✅ You'll know it worked when

the chat panel appears on the right and Copilot replies to your first message.

Next step ↓

✏️ Sketch — your screen may look a little different

Step 2 of 6

Ask Copilot to design the table

Click into cell A1 so Copilot knows where to put the table. In the chat panel, type a request describing the columns you want. Copilot usually proposes a sample table and asks if you want to insert it — confirm and the column headers appear across row 1, with one row of example data in row 2. If your version refuses because the sheet is empty, just type one fake invoice in row 2 first and try the prompt again.

💬 Try typing this"Create a table with columns: Client, Invoice Number, Invoice Date, Due Date, Amount, Status, Days Overdue."➤

✅ You'll know it worked when

seven column headers appear in row 1, formatted as a table with small filter dropdown arrows on each header.

Next step ↓

✏️ Sketch — your screen may look a little different

Step 3 of 6

Type in your real invoices (Status is a manual label)

Click into the first empty cell under each header and type your real invoice details — one invoice per row. Excel stores dates correctly when you include the year (like 15 March 2026 or 3/15/2026), and amounts as numbers when you type just the digits. For the Status column, type a short label — "Paid", "Pending", or "Overdue". You update these yourself as payments arrive; Status is a manual note, not a formula Copilot will calculate. If the filter dropdowns didn't appear after Step 2, click anywhere in your data and press Ctrl + T 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 just typed.

Next step ↓

✏️ Sketch — your screen may look a little different

Step 4 of 6

Ask Copilot for the Days Overdue formula

Click into the cell in row 2 of the Days Overdue column so Copilot knows where to start. In the chat panel, type a request asking for a formula that calculates how late each invoice is. Copilot usually shows the formula first — read it and check it mentions "TODAY" and the Due Date column. If it looks right, click Insert and the formula appears, then auto-fills down the column. If it looks wrong — wrong column, missing parentheses, weird logic — ask Copilot to rewrite it. Never trust a formula you can't explain in plain English.

💬 Try typing this"In the Days Overdue column, write a formula that subtracts the Due Date from TODAY(). Show 0 if the date isn't past yet."➤

✅ You'll know it worked when

numbers like 5, 12, or 0 appear in the column, with overdue rows showing positive numbers.

Next step ↓

✏️ Sketch — your screen may look a little different

Step 5 of 6

Ask Copilot to color-code the overdue rows

Click anywhere in your data first, so the formatting applies to the whole table. In the chat panel, ask Copilot to apply conditional formatting (rules that color cells automatically based on their value) using the Days Overdue column. Copilot usually 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, enter 7 for red, then repeat with Between 1 and 7 for yellow.

💬 Try typing this"Highlight rows red where Days Overdue is more than 7, and yellow where it's between 1 and 7."➤

✅ You'll know it worked when

overdue rows visibly stand out — red for the very late ones, yellow for the almost-late ones.

Next step ↓

✏️ Sketch — your screen may look a little different

Step 6 of 6

Save, sort, and send reminders yourself

Press Ctrl + S (Windows) or ⌘ + S (Mac) and pick a OneDrive or local location to save your tracker. It's ready to use. Here's the honest limit: Copilot in Excel cannot send emails automatically. For the actual nudge, you'll still open Outlook or Gmail, filter your tracker by the red rows, and send a polite follow-up yourself. If you want automatic email reminders later, you'd need a separate tool — Power Automate, a dedicated invoicing app, or a workflow service — which is beyond what Excel's Copilot does today.

💬 Examplefilter the table by Status "Overdue" to see your chase list 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.

✏️ Sketch — your screen may look a little different

⚠️ Common mistakes

Trusting a formula you didn't read. Copilot writes decent formulas but they sometimes point at the wrong column, miss parentheses, or use a different date logic. Always click the cell and read the formula in the formula bar 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 the Copilot button 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 of frustration.
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" formatting.

🚀 Try it now

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.

🎯 Next guideStart Using Microsoft Copilot in Word, Excel, and OutlookDiscover how to draft documents, analyze data, and manage your inbox more efficiently with Microsoft's built-in AI assistant.

❓ 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 (Copilot Pro for personal use, or Copilot for Microsoft 365 for business). Check Microsoft's official pricing page for the current plans and what's included.
Excel on Windows, Mac, or the web. Some Copilot features vary slightly 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).

What mistakes should I avoid?

Trusting a formula you didn't read. Copilot writes decent formulas but they sometimes point at the wrong column, miss parentheses, or use a different date logic. Always click the cell and read the formula in the formula bar 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 the Copilot button 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 of frustration.
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" formatting.

Keep reading

📘 Use Copilot to Write Spreadsheet Formulas Without the Tech Jargon A plain-English walkthrough for getting Copilot to draft Excel and Google Sheets formulas you can actually paste and use 📘 How to Spot Hidden Errors in an Excel Budget with Copilot Use Microsoft's Copilot to scan your Excel budget for broken formulas and inconsistent numbers before they quietly drain your wallet 📘 Start Using Microsoft Copilot in Word, Excel, and Outlook Discover how to draft documents, analyze data, and manage your inbox more efficiently with Microsoft's built-in AI assistant. 📘 How to Clean Up a Messy Old Word Document with Copilot in Ten Minutes Turn a chaotic file with mixed fonts and broken formatting into a clean, modern document ready to share. 📘 How to Triage and Clean Up an Overwhelming Inbox with Copilot Use Copilot inside Outlook to summarize, prioritize and draft replies so a 500-message inbox shrinks to the five emails that matter today. 📘 Getting started with Microsoft Copilot in everyday apps Learn how to find and use Microsoft Copilot inside Windows, Edge and Office to automate simple tasks in minutes

Was this helpful?

✦ Generated by AI in AI World HQ's automated newsroom, written in plain language. Checked by automated fact and quality gates, and our editor spot-checks published guides every day. Spot a mistake? Use the buttons above.

← Back to all stories