Bookkeeper reviewing a printed invoice spreadsheet at her desk beside a laptop showing an out-of-focus screen.

Images: AI-generated for CodeNicely

AI Tools Finance & Accounting November 19, 2026 • 11 min read

Draft a Accounts Payable Aging Report with Copilot and Excel

For: An office manager or bookkeeper at a 15–40 person services business who owns AP but has no dedicated accounting software beyond Excel and spends two hours every month manually sorting and bucketing overdue invoices

In this guide
  1. Who this is for, and what you'll get
  2. What you need
  3. Step-by-step
  4. A worked example
  5. Limits and gotchas
  6. When not to use AI for this, and honest alternatives
  7. Frequently Asked Questions

By the end of this tutorial you'll have a tidy accounts payable aging report — with 0–30, 31–60, 61–90, and 90+ day buckets, a per-vendor summary, and a short priority payment list — generated from your open invoice spreadsheet using Copilot in Excel. Budget around 30–45 minutes the first time, and closer to 10 minutes once you've reused the prompts.

Last verified: November 2026, against the tools' own documentation. AI tools change often — if a step no longer matches, check the sources at the end.

Who this is for, and what you'll get

You're the office manager or bookkeeper at a 15–40 person services business. You handle AP in Excel, your invoice list is messy (dates in mixed formats, vendor names inconsistent), and leadership wants a cash-flow view before the next meeting. You don't want to learn DATEDIF — you want the report.

What you'll have at the end:

What you need

Copilot in Excel is a paid feature. It is not included in free Microsoft accounts or Microsoft 365 Basic. Pick the tier that matches how you work.

Animated bar chart showing AP aging buckets (Current to 90+) building up, then a panel reveals the five steps Copilot handles automatically.
Watch each aging bucket appear as Copilot works through your invoice data — from raw dates to a prioritized payment list in under 45 minutes.
Overhead flat-lay of a checklist, billing statement, and pen on a desk, laptop screen turned away.
Before you open Excel, confirm you have the right Microsoft 365 plan — Copilot in Excel is a paid add-on, not included in free accounts.
ToolPlan neededPrice (as published)
Excel (desktop or web)Part of a Microsoft 365 subscriptionIncluded with the plans below
Copilot in Excel — individualMicrosoft 365 Personal, Family, or Premium$9.99/mo (Personal), $12.99/mo (Family), $19.99/mo (Premium)
Copilot in Excel — businessMicrosoft 365 Copilot Business add-on$21/user/month ($18 promo through 12/31/2026)
Copilot in Excel — enterpriseMicrosoft 365 Copilot enterprise add-on$30/user/month, billed annually
OneDrive or SharePointFor AutoSave (required for Copilot to write back)Included with the plans above

You'll also need your current open AP data in a spreadsheet. Copilot in Excel only works on data that's already in the workbook — it has no connector to QuickBooks, Xero, SAP, or any ERP. You export the data yourself first, then Copilot takes it from there.

Step-by-step

  1. Export your open invoices to Excel. From whatever you track AP in today (another spreadsheet, a PDF statement, or your bank portal), get the open invoices into a single sheet. Aim for one row per invoice with columns like Vendor, Invoice Number, Invoice Date, Due Date, Amount, and optional Notes. Don't worry yet about inconsistent date formats — Copilot can handle a fair amount of mess, but the cleaner the input, the better the output.

  2. Save the file to OneDrive or SharePoint and turn on AutoSave. Copilot in Excel can only write changes back to the workbook if the file lives in OneDrive or Microsoft 365 SharePoint and AutoSave is on. Save as .xlsx, .xlsb, or .xlsm — legacy .xls and Strict Open XML aren't supported.

  3. Format your data as an Excel Table. Click anywhere in your data and press Ctrl+T (or go to Insert → Table). Confirm the range and tick "My table has headers." Copilot in Excel will refuse to work on unformatted ranges — you'll see an "I can only work with Excel Tables" message if you skip this. Give the table a clear name like APOpen from the Table Design tab so your prompts can refer to it.

  4. Open Copilot in Excel. Click the Copilot icon in the lower-right corner of Excel, or the Copilot button on the Home tab. You'll see three modes: edit (default, makes changes directly), plan (shows you a plan to approve before acting), and chat (answers questions without editing). For this job, start in plan mode so you can review before anything is written to your sheet.

  5. Ask Copilot to normalize your dates. If your Due Date column has a mix of formats (e.g., 10/3/25, 2025-10-03, 3-Oct-25), get them into a single real-date format first. Paste something like:

    In the APOpen table, standardize the Due Date column so every value is a real Excel date in MM/DD/YYYY format. Flag any rows where the Due Date can't be parsed in a new column called "Date Issue".

    Review the plan Copilot shows you, then approve it. Scroll through the sheet and spot-check a few rows against your original data.

  6. Ask Copilot to add Days Overdue and Aging Bucket columns. This is the step most tutorials make you do by hand with DATEDIF. You don't need to. Use:

    In the APOpen table, add a column called "Days Overdue" that equals TODAY() minus Due Date (zero if not yet due). Then add a column called "Aging Bucket" with the values "Current", "0–30", "31–60", "61–90", or "90+" based on Days Overdue. Explain the formulas you used.

    Copilot will write the formulas into the table and describe them in the chat panel. Read the explanation — if the bucket boundaries aren't what you want (for example, you consider day 30 as "0–30" not "31–60"), ask it to adjust.

  7. Ask Copilot to build the vendor summary PivotTable. Prompt:

    Create a PivotTable on a new sheet called "Vendor Aging" summarizing the APOpen table. Rows: Vendor. Columns: Aging Bucket (ordered Current, 0–30, 31–60, 61–90, 90+). Values: sum of Amount. Add a grand total row and column.

    When it finishes, check that the bucket column order is correct — PivotTables sometimes alphabetize labels. If it did, ask Copilot to sort the columns in that specific order.

  8. Add conditional formatting so overdue rows stand out. On your main invoice sheet, prompt:

    On the APOpen table, apply conditional formatting to the Aging Bucket column: Current = green, 0–30 = yellow, 31–60 = orange, 61–90 = red, 90+ = dark red with white text.
  9. Generate a priority payment list. Switch Copilot to chat mode so it doesn't edit the sheet, and ask:

    From the APOpen table, list the top 10 invoices to prioritize paying this week. Prioritize by highest Days Overdue, then largest Amount. Show Vendor, Invoice Number, Due Date, Days Overdue, and Amount. Format as a table I can paste into an email.

    Copy the response into your draft email or paste it into a new sheet called "Priority Payments".

  10. Verify the totals manually. Before you share anything, do two quick checks: (a) sum the Amount column on the main table and confirm it matches the grand total on the Vendor Aging PivotTable, and (b) spot-check three or four aging buckets against the actual due dates. Microsoft's own documentation says Copilot outputs "can be inaccurate." Trust but verify.

A worked example

Say you're Priya, the office manager at a 28-person marketing agency. You have a spreadsheet called AP_October.xlsx with 142 rows of open invoices: freelancers, SaaS subscriptions, your landlord, printer leasing, two ad platforms. Due dates come from three sources, so you've got a mess of 10/15/2025, 15-Oct-25, and 2025-10-15 in the same column.

Abstract AI visualization of invoice data being sorted into four glowing aging-bucket columns on a dark background.
Copilot groups every open invoice into an aging bucket automatically — what once took two hours of manual sorting happens in a single prompt.

You save it to OneDrive, press Ctrl+T to make the table, name it APOpen, and run the prompts above. Copilot normalizes 139 of the 142 dates and flags three freelancer invoices where the date was typed as "Net 30" — you fix those by hand. It then adds Days Overdue and Aging Bucket, and builds the PivotTable.

Your output looks something like:

Your priority list surfaces the 90+ disputed invoice first (worth escalating before it becomes a relationship issue) and the two ad platforms (worth paying this week to avoid an account pause). Total time: about 25 minutes, most of it spent on the manual verification pass.

Limits and gotchas

When not to use AI for this, and honest alternatives

Skip Copilot and use a different approach if any of these apply:

Frequently Asked Questions

Can Copilot pull my open invoices straight from QuickBooks or Xero?

No. Copilot in Excel only works on data that is already in the workbook, and it has no native connector to accounting systems. You export the open invoice list from your accounting tool (or compile it manually) into Excel first, then let Copilot do the categorizing and summarizing.

I have a free Microsoft account. Can I still do this?

Not with Copilot in Excel — it isn't available on free Microsoft accounts or Microsoft 365 Basic. The lowest consumer tier that includes it is Microsoft 365 Personal at $9.99/month. For business use you'll need the Microsoft 365 Copilot add-on on top of your existing Microsoft 365 plan.

Why does Copilot keep saying it can only work with Excel Tables?

Your data is still a plain range. Click anywhere in it, press Ctrl+T, confirm headers, and give the table a name from the Table Design tab. Copilot will then be able to read and modify it.

How much should I trust the numbers Copilot produces?

Treat them as a draft. Microsoft's own documentation says Copilot's content "can be inaccurate or inappropriate." Always reconcile the aging totals against a plain SUM of your Amount column, and spot-check a handful of rows before sharing the report with leadership or your accountant.

Can I run this from my phone before a meeting?

Partially. On iPhone and Android, Copilot in Excel only offers chat mode — you can ask questions about the data but you can't make edits. Build the report on desktop or Excel for the Web, then use your phone to query it on the way to the meeting.


If this monthly process grows into something more — say, pulling live AP data from multiple systems, routing approvals, and emailing vendors automatically — that's the point where an off-the-shelf tool stops being enough and a small custom build (which CodeNicely does) starts to pay back quickly.

Sources & further reading

Get one practical guide a week

Costs, AI tools, partner selection — written for people who make the decision. No spam, unsubscribe anytime.

Thanks — you're on the list. One practical guide a week, nothing else.

Building something in Finance & Accounting?

CodeNicely partners with founders and tech teams to ship AI-native products that move metrics. Tell us about the problem you're solving.

Talk to our team Book a 30-min call