30 Best ChatGPT Prompts for Excel for Accountants

These are 30 ChatGPT prompts for Excel built around the sheets an Indian accountant or CA actually builds — formulas, data cleaning, pivot analysis, GST/TDS working papers, and macros. Each one assumes you replace the bracketed placeholders with your own column letters and structure, and that you use dummy data rather than real client figures when the question is about formula logic. For the wider case on when to trust AI-written formulas and when to stop, read how to use AI with Excel for accounting and tax work first.

How to use this list: copy a prompt, describe your actual sheet layout (column letters, headers, roughly how many rows), then test the returned formula on a row where you already know the right answer before using it across the sheet.

Formulas and functions (1–8)

1. "Write an Excel SUMIFS formula that totals column D where column B equals [client name] and column C's date falls in [month/year]. My data starts at row 2."
2. "Convert this VLOOKUP formula to XLOOKUP so it still works if I insert a column: [paste formula]."
3. "My IF formula in [cell] is returning #VALUE!. Here is the formula: [paste]. What is likely wrong and how do I fix it?"
4. "Write a formula that flags any row in column [X] where the GSTIN is not exactly 15 characters or contains a space."
5. "Give me a formula to calculate the number of days between an invoice date in column [A] and today, and bucket the result into 0–30 / 31–60 / 61–90 / 90+ using a nested IF or IFS."
6. "Write an INDEX/MATCH formula that returns the vendor name from [sheet name] where the invoice number in column [A] matches, instead of a plain VLOOKUP."
7. "Explain what this formula does in plain English, step by step: [paste inherited formula from an old workbook]."
8. "Write a formula that rounds a GST amount to the nearest rupee only where the value in column [X] is above [threshold], and leaves smaller values unrounded."

Data cleaning and formatting (9–14)

9. "I have a bank statement export where dates are sometimes text and sometimes real dates. Give me a step-by-step Excel process (formulas or Text-to-Columns) to standardise the whole date column."
10. "Write a formula using TRIM and CLEAN to remove extra spaces and hidden characters from a column of vendor names pasted from a PDF."
11. "My GST portal Excel export has merged header cells across two rows. Describe how to un-merge and flatten this into a single clean header row I can filter on."
12. "Give me a formula to flag duplicate invoice numbers in column [A] so I can review possible double-entry before posting."
13. "Write a formula to split a single 'Narration' column from a bank statement into separate columns for transaction type, reference number, and payee, assuming the format is [describe pattern, e.g. 'UPI/123456/PAYEE NAME']."
14. "Give me conditional formatting rules to highlight negative values in a column that should never be negative, and highlight blank cells in a column that must always be filled."

Pivot tables and analysis (15–20)

15. "I have a ledger export with columns for date, ledger head, and amount. Suggest the cleanest PivotTable layout to show monthly expense by ledger head."
16. "How do I build a PivotTable that shows GST output tax by month and by GSTIN from a sales register with columns [list columns]?"
17. "Write a formula-based (non-pivot) summary that totals sales by customer for a sheet with thousands of rows, so I can paste the result as values into a report."
18. "Suggest a chart type and layout to show cash inflow versus outflow by month for a client presentation, given columns [describe]."
19. "Give me a PivotTable calculated field formula to show each expense head as a percentage of total expenses for the period."
20. "Write a formula to identify the top 10 vendors by total payment value from a purchase register with columns [list columns]."

GST and TDS working sheets (21–26)

21. "Write a formula that flags a purchase entry as blocked input tax credit under Section 17(5) if the expense head in column [X] matches any value in a lookup list I maintain in another sheet."
22. "Design a running-total formula that accumulates payments to each vendor across the financial year and flags when the total crosses a threshold I specify, for tracking TDS applicability."
23. "Suggest column headers and a match-key structure for reconciling a GSTR-2B export against my purchase register, assuming both have invoice number, GSTIN, and value columns with slightly different formats."
24. "Write a formula to age outstanding debtor balances into 0–30 / 31–60 / 61–90 / 90+ buckets from an invoice date column, for a debtor register with [X] rows."
25. "Give me a formula that calculates TDS to be deducted at [rate]% on payments above [threshold] in column [X], leaving amounts below the threshold at zero."
26. "Write a formula that compares two columns — GST liability as per books and GST liability as per GSTR-3B filed — and flags any row where the difference exceeds ₹1."

For the reconciliation workflow these sheets feed into, see automating GST reconciliation with AI and automating TDS reconciliation with AI.

Macros and automation (27–30)

27. "Write a VBA macro that splits a single Excel sheet into separate sheets based on the value in column [X] (for example, one sheet per GSTIN), and explain each line before I run it."
28. "Give me a VBA macro that highlights any row in a purchase register where the same invoice number appears more than once."
29. "Write a macro that exports a filtered range (only rows where column [X] equals [value]) to a new workbook, for sending a client their filtered transaction list without the rest of the ledger."
30. "Explain, line by line, what this macro does before I run it on a live client file: [paste macro]."

Prompt hygiene for Excel work

• State your actual column letters and roughly how many rows — vague prompts get vague formulas that need rework.
• Test on a known row first. Before applying any formula sheet-wide, check it against a row where you already know the correct answer.
• Never paste real PAN, GSTIN, account numbers, or full client ledgers into a consumer chat tool — describe the structure or use dummy data instead.
• Read macros before running them, and run them on a copy of the file first.
• Keep rates, thresholds, and section numbers your own responsibility — AI writes the formula logic; you supply and verify the current GST rate or TDS threshold.

When Excel plus ChatGPT isn't enough

These prompts speed up manual spreadsheet work, but once a client generates real volume — hundreds of bank lines or dozens of invoices a month — retyping into Excel even with AI-assisted formulas stops scaling. Accountooze AI reads bank statements and bills directly and syncs categorised entries with Tally; SmartLedger AI reconciles books across Tally, Zoho Books, and Excel and lets you query results in plain English; Zoho Books is a cloud alternative for clients with no Tally legacy. See AI bookkeeping automation for Indian CA firms for when to make that move.

Frequently asked questions

Can I use these prompts with real client numbers?

Prefer dummy data shaped like the real thing. Formula and structure questions almost never need the actual figures — swap real amounts, GSTINs, and names for placeholders before you paste anything into a consumer chat tool.

Do these prompts work in Microsoft 365 Copilot as well as ChatGPT?

Most of them do, with one difference: Copilot inside Excel can see your open workbook, so you can often skip describing the layout and just say "in this sheet." ChatGPT and similar chat tools need you to describe or paste the structure since they cannot see your file.

Will ChatGPT get an Excel formula right every time?

No. It is reliable for common patterns like SUMIFS, XLOOKUP, and pivot layouts, but test every formula on a row where you already know the correct answer before applying it to the whole sheet. Treat the output as a strong first draft, not a verified formula.

Are there prompts here for GST and TDS work specifically?

Yes — prompts 21 to 26 cover ITC eligibility flags, TDS threshold trackers, GSTR-2B match layouts, and debtor ageing, which are the recurring Excel-based compliance sheets in an Indian CA practice.

The takeaway

These 30 prompts cover the Excel work that actually eats an accountant's time — formulas, cleanup, pivots, and the GST/TDS sheets every practice builds every month. Use dummy data for anything sensitive, test every formula against a known answer before trusting it sheet-wide, and read macros before you run them. When a client's volume makes Excel the bottleneck rather than the tool, that is the signal to move to dedicated bookkeeping software instead of building a bigger spreadsheet.

For prompts outside Excel work, see 50 Best ChatGPT Prompts for Chartered Accountants in India.

Primary sources

Treat anything a model says about Indian compliance as a draft. The authoritative material sits here:

• ICAI — professional standards, guidance notes and member announcements
• Income Tax Department — the Act, rules, forms and utilities
• GST Portal — returns, due dates and taxpayer services