Find Trends and Outliers in Ledger Exports with Copilot in Excel
What the source describes
Microsoft Support explains that Copilot in Excel lets users ask questions in their own words to surface summaries, trends and outliers, returning results as charts, PivotTables or summaries. It can also add formula columns and rows (for example profit margin or percent of total), suggest single-cell formulas, build lookups such as XLOOKUP, and explain existing formulas. Microsoft advises being specific about which columns to analyze and to review and verify anything Copilot creates.
Result or outcome
According to the source, Copilot returns insights such as charts, PivotTables, summaries, trends or outliers from a user's data, and more specific questions that name columns produce more accurate results.
How to run it
- Export the general ledger detail for the period to Excel with clear column headers, then open Copilot from its icon in Excel.
- Run the prompt on this page, naming the exact columns to analyze and asking for a PivotTable when you want results in that format.
- Ask Copilot to add formula columns, such as each vendor's percent of total spend, and to explain each formula it creates.
- Ask follow-up questions about outliers or anomalies, then record the items that need investigation in your review notes.
Try it yourself
Copyable prompt
This sheet is a general ledger detail export for [client business name] covering [date range], with columns [Date], [Account], [Vendor], [Memo], [Amount]. 1) Create a PivotTable of total spend by Account by month. 2) Identify any outliers or anomalies in [Amount] by Account, such as values far above the account's usual monthly range, round-number payments, or weekend dates. 3) Add a column showing each vendor's percentage of total spend. 4) List the top [10] vendors by spend and any vendor that appears under more than one account. Summarize findings in five bullet points I can paste into a review note, and explain each formula you add.
Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.
What to review
Microsoft says to review and verify anything Copilot creates. Check that the PivotTable grand total equals the general ledger total for the period, open each added formula to confirm its range covers every row, and pull the source documents for any flagged outlier before calling it an error, since unusual amounts can be legitimate.
Related free download: 50 AI Prompts for Bookkeepers (Prompt pack (PDF))
Source: Microsoft Support
Not professional advice. This is general information, not accounting, tax, legal or financial advice. Check AI output yourself and consult a licensed professional for your situation. See our Terms of Use.
This entry summarizes a public source. Features, plans, and availability change, so confirm details with the vendor. Tool names are trademarks of their respective owners.
Related workflows
- Reporting and Analysis
Write Bookkeeping Excel Formulas with ChatGPT
OpenAI's help article on data analysis says ChatGPT can analyze uploaded spreadsheets, including .xls, .xlsx and .csv files, answer questions about the data and create tables or charts. It recommends descriptive column headers in the first row, one row per record, and no empty rows or columns splitting the data. For some tasks ChatGPT writes and runs Python code, and OpenAI says to review the generated code, outputs and assumptions before relying on the result. The article also notes that the number and complexity of formulas in a spreadsheet can affect processing.
ChatGPT, Microsoft Excel
OpenAI Help Center (Data analysis with ChatGPT) - Reporting and Analysis
Build a Rolling 13-Week Cash Flow Forecast with AI and Excel
In this Mostly Metrics tutorial, the author records building a 13-week rolling cash forecast for a fictional software company from eight messy data tabs, including an invoice export with inconsistent terms, collections history, AP, a payroll calendar and one-off items. Claude first works out each customer's actual days-to-pay from collections history rather than nominal terms, then builds a forecast with beginning cash, collections, disbursements, net change and ending cash chained week to week. The model includes a covenant line with an automatic OK/BREACH flag.
Works in ChatGPT, Claude, Grok, Gemini
Microsoft Excel
Mostly Metrics (newsletter) - Reporting and Analysis
Build Horizontal, Trend and Ratio Analysis in Excel with AI
A Journal of Accountancy technology Q&A shows how ChatGPT-4o can create Excel spreadsheets from scratch and analyze uploaded files. In the financial analysis example, the author pasted raw financial data and ChatGPT returned a workbook with tabs for Raw Financials, Horizontal Analysis, Trend Analysis and Ratio Analysis. The author advises being specific in the prompt if you want working formulas rather than values.
Works in ChatGPT, Claude, Grok, Gemini
Microsoft Excel
Journal of Accountancy - Reconciliations
Reconcile two Excel tables with the Microsoft Financial Reconciliation agent
Microsoft Learn explains that the Financial Reconciliation agent in the Finance Agent add-in for Excel reconciles two data sets formatted as tables on one or two worksheets. The agent suggests the tables and uses AI to propose reconciliation vectors (mapping and monetary keys) that you can keep, edit or remove before selecting Reconcile data. The resulting report groups transactions as matched, potentially matched or unmatched and includes a generative AI summary, with a Troubleshoot transactions feature for the unmatched items.
Microsoft Excel, Microsoft Finance Agent +1
Microsoft Learn (product documentation)
If you want somewhere to check the numbers from this report or analysis, the Small Business Bookkeeping Spreadsheet Bundle is on our sister store.