ChatGPT for Excel Formulas: Worked Examples on GST, Salary and Sales Sheets
Short answer: ChatGPT can write Excel formulas from a plain-English description if you tell it your column letters, header row, Excel version and an example of the result you expect. It is fast at SUMIFS, XLOOKUP, IF logic and text cleanup, but it regularly gets references and data types wrong, so test every formula on rows you can check by hand.
Generic tutorials use made-up 'Product A, Product B' tables. Here we work through three sheets that Indian offices actually maintain: a GST invoice register, a monthly salary sheet and a sales report by pincode. For each, you get the prompt, the formula and the mistakes we see AI make on that kind of data.
The examples work in Microsoft 365 Excel and mostly in Google Sheets. Where a function needs a newer Excel version, it is flagged.
How do you ask ChatGPT for an Excel formula?
Describe the sheet as if the AI cannot see it, because it cannot unless you upload it. Five facts make the difference between a formula that works and one that silently returns the wrong number.
- Column letters and headers: 'A = Invoice No, B = Date, C = Customer GSTIN, E = Taxable Value, F = GST Rate.'
- Where data starts: 'Headers in row 1, data from row 2 to row 500.'
- How values are stored: 'Rate is stored as a percentage like 18%, not as 18.' 'Dates are real dates, not text.'
- Your Excel version: 'Excel 2019' or 'Microsoft 365'. XLOOKUP, FILTER and UNIQUE do not exist in Excel 2016 or 2019.
- One worked example: 'For row 2, taxable ₹10,000 at 18% to a Delhi customer, CGST should be ₹900.'
That last line is the most useful. It gives you a test case: if the formula does not return ₹900 on that row, it is wrong.
Worked sheet 1: a GST invoice register
Say you keep accounts for a Delhi-registered trader. Each sale needs CGST and SGST if the customer is in Delhi, and IGST if the customer is in another state. The first two digits of a GSTIN are the state code, and Delhi's is 07.
Prompt: 'Excel 365. Headers in row 1. C = Customer GSTIN (text), E = Taxable Value, F = GST Rate as a percentage. Our GSTIN is Delhi (state code 07). Give me formulas for G = CGST, H = SGST, I = IGST, rounded to 2 decimals. Intra-state sale: CGST and SGST are each half the rate. Inter-state: IGST at full rate.'
| Column | Formula for row 2 | What to check |
|---|---|---|
| G (CGST) | =IF(LEFT(C2,2)="07",ROUND(E2*F2/2,2),0) | Is "07" in quotes? LEFT returns text, so comparing with the number 7 always fails. |
| H (SGST) | =IF(LEFT(C2,2)="07",ROUND(E2*F2/2,2),0) | Same as CGST; totals of G and H should match. |
| I (IGST) | =IF(LEFT(C2,2)<>"07",ROUND(E2*F2,2),0) | Blank GSTIN (B2C sale) wrongly lands here. Decide how you treat B2C rows. |
| J (GSTIN check) | =IF(LEN(TRIM(C2))=15,"OK","Check") | A GSTIN has 15 characters; this catches typos and stray spaces. |
Errors we commonly catch on this sheet: AI writes LEFT(C2,2)=7 without quotes; it assumes the rate is stored as 18 and multiplies by 18 instead of 18%; and it forgets that blank GSTINs for walk-in customers will be treated as inter-state. The tax treatment itself (place of supply, B2C rules) is a GST question, not an Excel one, so confirm it with your CA. For more on how accounting firms use these tools, see our guide on AI for chartered accountants.
Worked sheet 2: a monthly salary sheet
Payroll sheets are where a wrong formula costs real money. Say a 20-person office in Tilak Nagar keeps B = Basic, C = HRA, D = Other allowances, and wants PF and a net figure.
Prompt: 'Excel 365. B = Basic, C = HRA, D = Special allowance. Employee PF is 12% of basic, but calculated on a maximum of ₹15,000 basic. Round to the nearest rupee. Give formulas for E = Gross, F = Employee PF, G = Net before tax.'
- E (Gross): =B2+C2+D2
- F (Employee PF): =ROUND(MIN(B2,15000)*12%,0)
- G (Net before tax): =E2-F2
The AI only knows the ₹15,000 ceiling because the prompt said so. Ask it cold and it may use a different wage definition or skip the cap entirely. PF wage definitions, ESI thresholds and professional tax differ by situation and state and have changed over the years, including with the labour codes, so treat every statutory number in a payroll sheet as something to verify against the EPFO and ESIC sites or with your CA, not something to take from a chatbot.
A practitioner tip: add a 'control total' row that sums each column, and compare it with last month's payroll register. A sudden jump usually means a broken reference, not a pay rise.
Worked sheet 3: sales by pincode
Say a Jaipur D2C brand exports orders from its store: A = Order ID, B = Order Date, C = Pincode, D = Order Value. You want total sales and order count per pincode for October.
Prompt: 'Excel 365. Orders in A2:D3000. On a new sheet, list each unique pincode in column A, total October 2026 order value in B, and order count in C. Dates in column B are real dates.'
- A2 (unique pincodes): =UNIQUE(Orders!C2:C3000)
- B2 (October value): =SUMIFS(Orders!D:D,Orders!C:C,A2,Orders!B:B,">="&DATE(2026,10,1),Orders!B:B,"<"&DATE(2026,11,1))
- C2 (October orders): =COUNTIFS(Orders!C:C,A2,Orders!B:B,">="&DATE(2026,10,1),Orders!B:B,"<"&DATE(2026,11,1))
The classic failure here is data type. Store exports often bring order values in as text, and SUMIFS silently skips text in the sum range, so every total shows zero. Select the column and convert it to numbers (Data > Text to Columns > Finish works), or ask the AI for a version using VALUE(). Pincodes stored as text matter less for SUMIFS and COUNTIFS, which usually still match them, but they will break a VLOOKUP or XLOOKUP against a pincode you typed as a number. The second failure is dates exported as text like '05/10/2026': the date criteria skip them, and on a system set to US date format they can be read as 10 May. Check one date with =ISNUMBER(B2) and =MONTH(B2) before trusting any monthly total.
For a quick summary, a PivotTable is often simpler than formulas, and you can ask ChatGPT to walk you through building one for this exact layout.
What about Copilot in Excel and uploading the file to ChatGPT?
Both can analyse a sheet directly instead of only writing formulas. ChatGPT can take an uploaded Excel or CSV file and run calculations on it, with tighter limits on the free plan; Copilot in Excel works inside the workbook on Microsoft 365 plans that include it. As of November 2026, which plans include these features and their limits keep changing, so check the current pricing pages before you buy.
Two cautions. First, uploading a file means the data leaves your machine, so do not upload client ledgers or salary data with names unless your firm's policy and the tool's data settings allow it. Second, analysis tools still make the same type mistakes described above. Ask the tool to show the formula or method it used, and recheck one number by hand.
A five-minute check before you trust any AI formula
A beginner mistake we see often is fixing a wrong formula by asking the AI to 'try again' without saying what was wrong. Paste the formula back with the wrong result and the expected one ('row 14 shows ₹0, should be ₹1,620'). The model can then find the actual bug instead of guessing a new formula.
- Test it on 3 rows you can calculate in your head, including one edge case (a blank cell, a zero, a B2C sale).
- Press F2 on the cell and check that ranges cover all your rows and that $ signs are where you need them before copying down.
- Compare the column total with a total from another source, such as the GST portal summary or last month's register.
- Ask ChatGPT: 'Explain this formula part by part and tell me what input would break it.' It is good at spotting its own edge cases when asked directly.
- Keep a note of the prompt next to the sheet so a colleague can rebuild the logic later.
These checks matter far more than which tool you use. They also apply to the other office work in our practical guide to using AI at work, and to the stock and quote sheets covered in AI for small business owners. Excel and data prompting is one module in our no-code AI course for professionals.
Frequently asked questions
Can ChatGPT write complex Excel formulas?
Yes, including nested IFs, SUMIFS, XLOOKUP, INDEX-MATCH and dynamic arrays. The more precisely you describe columns, data types and your Excel version, the more often the first answer works. Always test before copying down.
Is Copilot in Excel better than ChatGPT for formulas?
Copilot works inside your workbook, so it can see your headers and data, which saves describing the sheet. ChatGPT is more flexible for explaining and debugging. Availability and plans change, so check what your Microsoft 365 plan includes as of November 2026.
Why does my AI-generated SUMIFS formula return zero?
Usually because the values being added are stored as text, which SUMIFS skips, or because dates are stored as text and fail the date criteria. Stray spaces in the criteria column are another cause. Convert the columns to real numbers and dates, use TRIM on text, and the formula typically works.
Can accountants use ChatGPT for GST calculations in Excel?
For building formulas, yes. The tax logic itself, such as place of supply or rates for a specific item, must come from the law and your CA's judgement, not the chatbot. Keep client data redacted when asking for help.
Does ChatGPT work for Google Sheets formulas too?
Yes. Tell it you use Google Sheets, since some functions differ slightly, for example QUERY and ARRAYFORMULA exist only in Sheets.
Keep reading in AI at work
- How to Use AI at Work: Practical Examples for Every Role in an Indian Office Start here
- Company AI Usage Policy: A One-Page Starter Template for Indian Firms
- ChatGPT for Performance Reviews: From Your Notes to a Fair, Specific Appraisal
- AI for Teachers: Build Lesson Plans and Question Papers Faster, Without Losing Quality
- ChatGPT for HR: Drafting JDs, Policies and Employee Letters Without the Usual Mistakes
Courses: Applied AI Professional · Corporate AI training · All guides in AI at work