Write Bookkeeping Excel Formulas with ChatGPT
Short answer
Describe your columns and the result you want, and ChatGPT will write the Excel formula, such as a running balance, an XLOOKUP against the GL or an aging bucket. Test every formula on a few sample rows you can check by hand before filling it down.
What the source describes
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.
Result or outcome
According to the source, ChatGPT may choose a method that does not match your intent on the first try, and you can ask it to use a specific method, calculation, grouping or column when you need a particular approach.
How to run it
- Copy 20 to 50 representative rows from each tab into a sample workbook, with descriptive headers in the first row, one record per row and no blank rows or columns.
- Replace client names and account numbers with codes, then upload the sample to ChatGPT.
- Paste the prompt on this page with your tab names, columns and cell references filled in, and ask for Excel formulas rather than a Python result.
- Paste each formula into the first data row of your real workbook, fill it down, and compare five rows against your own hand calculation.
- If a formula gives the wrong answer, ask ChatGPT to use a specific method, such as XLOOKUP instead of nested IFs, and test again.
Try it yourself
Copyable prompt
I have uploaded a sample of [client]'s bookkeeping workbook as [file name].xlsx. Tab [Bank] has columns A [Date], B [Description], C [Amount], with deposits positive and payments negative. Tab [Map] has columns A [Vendor keyword] and B [GL account]. Tab [AR] has columns A [Customer], B [Invoice date], C [Due date], D [Open amount]. Write Excel formulas, not Python, for: 1) a running balance in Bank column D that starts from an opening balance in cell [F1]; 2) a GL account in Bank column E that looks up the first Map keyword found in the description and returns UNMAPPED when none matches; 3) an aging bucket in AR column E (Current, 1-30, 31-60, 61-90, Over 90) based on an as-of date in cell [H1]. For each formula, give the exact text to paste into the first data row, say what it does in one sentence, and test it on the uploaded rows. Show me five rows where you checked the result by hand, and tell me which rows would break the formula.
Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.
What to review
OpenAI says to review generated code, outputs and assumptions before relying on the result. Check that each formula's range covers every row, that the running balance ends at the statement balance, that UNMAPPED descriptions are coded by a person, and that aging buckets use the as-of date rather than today's date.
Related free download: 50 AI Prompts for Bookkeepers (Prompt pack (PDF))
Source: OpenAI Help Center (Data analysis with ChatGPT); also OpenAI Help Center (Data analysis with ChatGPT, archived copy, Oct 12, 2026)
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
Benchmark a Client P&L Against Industry Norms by NAICS
This TechNews.Tax newsletter post describes how a tax firm reviews Profit and Loss statements from clients who do their own books, using an AI prompt that compares the P&L to typical expense categories and percentages for the client's NAICS code. The prompt asks for a NAICS summary, a category and percentage comparison, a judgment on whether expenses are in line (within a 10% variance), and a risk explanation for outliers. The author uses the OpenAI API but notes the same process works in ChatGPT, Gemini, Copilot or similar tools.
Works in ChatGPT, Claude, Grok, Gemini
TechNews.Tax (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 - Reporting and Analysis
Find Trends and Outliers in Ledger Exports with Copilot in Excel
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.
Microsoft Copilot, Microsoft Excel
Microsoft Support - Reporting and Analysis
Draft a Client Financial Health Review from an Income Statement
CPA Practice Advisor walks through pasting an anonymized landscaping company's income statement into ChatGPT and Gemini Advanced and asking how financially healthy the business is and what to recommend. Both tools identified the net loss, flagged salaries as a large share of expenses, and suggested cost and revenue actions. The author stresses anonymizing data, getting client permission, having an expert check every answer, and reviewing terms of service.
Works in ChatGPT, Claude, Grok, Gemini
CPA Practice Advisor
If you want somewhere to check the numbers from this report or analysis, the Small Business Bookkeeping Spreadsheet Bundle is on our sister store.