How to Use ChatGPT for Excel Formulas and Analysis

TL;DR: What You Will Learn

• Generate Excel formulas from a plain English description of what you want
• Debug errors like #N/A, #REF! and #VALUE! by pasting the formula and asking why
• Clean messy data from exports: split columns, fix dates, remove duplicates
• Build and explain charts so your reports look clear, not cluttered
• Reuse a ready-made prompt cheat sheet so you never start from a blank box

Why Pair ChatGPT With Excel

Picture the manager of a small F&B outlet in Tampines closing up on a busy Friday night. She has three payment reports (cash, PayNow, and a delivery app payout) to reconcile against the POS total, and no data analyst on payroll to build the formulas for her.

This is exactly where ChatGPT for Excel earns its keep. It turns a plain English request like “help me match my POS total to three payment sources and flag any gap” into a working formula she can paste straight into a cell.

The value is threefold: it translates vague intent into precise syntax, it saves hours on repetitive lookups and cleanups, and it lowers the barrier for staff who were never trained as analysts. You describe the outcome you want, and ChatGPT drafts the logic to get there.

What ChatGPT Can and Cannot Do With Spreadsheets?

Be clear on the boundaries. The free web version of ChatGPT does not sit inside your workbook, so it cannot see your live data unless you paste it in or upload a file.

What it does well is write and explain formulas: nested IF statements, XLOOKUP, SUMIFS, pivot table setups, and even Power Query steps. It will also debug a formula that returns #REF! or #VALUE! If you show it the error and describe your columns.

What it cannot do is guarantee the answer is right for your specific dataset. It can misread your intent, assume a column layout you did not describe, or produce a formula that runs but returns the wrong number.

So the rule holds firm: ChatGPT drafts the logic, but you validate every result in Excel. Our F&B manager should still eyeball a few rows manually before trusting the reconciliation.

Should You Use Free ChatGPT or Copilot in Microsoft 365?

If your business runs on Microsoft 365, Copilot in Excel is the paid option that works inside your spreadsheet and reads your actual data. It can suggest formulas and generate charts without copy-pasting.

The free ChatGPT web version costs nothing and is often enough for one-off formula help, but you do the pasting yourself. For a lean SME watching cash flow, starting with the free tool and upgrading only if you use it daily is a sensible path.

Where It Fits Alongside SkillsFuture Upskilling

ChatGPT speeds you up, but it does not replace understanding what a good analysis looks like. Knowing when a SUMIFS is the right choice, or why your reconciliation should reconcile at all, is a skill worth building.

That is where SkillsFuture-funded courses come in, giving you the analytical foundation to prompt smarter and spot when the AI is wrong. For a broader view of using the tool responsibly at work, our ChatGPT user guide for the Singapore workforce is a good starting point.

Writing Excel Formulas From Plain English Prompts

The whole promise of ChatGPT for Excel is this: you describe what you want in ordinary words, and you get a working formula back. No memorising syntax, no guessing where the commas go.

But there is a catch. The quality of the formula depends almost entirely on how you describe your data. Vague prompt, vague formula.

How do you write a good Excel formula prompt?

I always tell people to give ChatGPT three things: your column names, a line or two of sample data, and the exact output you want.

Say you run a small F&B outlet and want to total GST-inclusive sales by month. Do not write “sum my sales by month”. Instead, tell it your columns are Order Date in A and Total Amount in D, and that you want the total for January 2024 only.

With that, ChatGPT will hand you a clean SUMIFS with the right date boundaries built in.

This mirrors the same discipline behind good prompt engineering best practices: the more context you give up front, the less back and forth you need.

Here are the three details worth including every time:

• Your actual column headers, so formula references like B2 or the named range match your sheet exactly
• A sample row of data, so it understands your formats
• The result you expect, ideally with a number you can check against

A common use is replacing a messy nested VLOOKUP. Tell ChatGPT you have staff IDs in one sheet and salaries in another, and it will suggest an XLOOKUP that is shorter and does not break when you insert a column.

For tiered logic, describe your rules plainly. “If sales are above 50,000, pay 5 percent; above 30,000, pay 3 percent; otherwise, 1 percent” gives you a clean nested IF you can drop straight in.

Handling Singapore-specific formats

Our dates trip up a lot of formulas because we use DD/MM/YYYY, not the US MM/DD/YYYY. Always state this in your prompt.

If your Order Date column reads 03/06/2024, meaning the 3rd of June, tell ChatGPT that outright so it does not read it as March.

The same goes for S$ currency. If your amounts are stored as text like “S$1,250.00”, mention it, and ChatGPT will wrap the formula in a VALUE or SUBSTITUTE to strip the symbol before summing.

How do you convert formulas between Excel and Google Sheets?

Many Singapore teams live in both. If a colleague sends you a Google Sheets formula that will not run in Excel, paste it in and ask ChatGPT to convert it, and vice versa.

Here is a realistic exchange a payroll officer might use.

Prompt: “In Excel, my staff data has Employee Name in A, Basic Pay in B, and Allowance in C, starting in row 2. Give me a formula in column D for gross monthly pay, formatted for Singapore.”

Response: `=B2+C2`, with a note to apply an accounting format showing S$.

Remember to paste your real headers, not my examples, so the references line up on the first try.

Explaining And Debugging Spreadsheets You Inherited

Picture this: you are a new finance hire at a mid-sized firm in Singapore, and on day two you open the quarterly budget tracker. The person who built it has left, there is no documentation, and one cell holds a formula so long it wraps three times in the formula bar.

This is where ChatGPT for Excel stops being a novelty and starts saving your afternoon. Instead of reverse engineering someone else’s logic cell by cell, you paste it in and ask for a translation.

Decoding A Long Nested Formula

Say the tracker’s forecast column contains a monster like `=IF(AND(B2>0,C2″”),VLOOKUP(A2,’FY24 Rates’!$A:$D,4,FALSE)*(1+D2),0)`.

Paste it into ChatGPT and ask: “Explain this Excel formula step by step in plain English, and tell me what each part is checking.” You will get a breakdown that says it only calculates when column B is positive and column C is not blank, then looks up a rate from the FY24 Rates sheet and applies a growth factor from column D.

The key is context. Tell ChatGPT what the columns represent (budget month, department code, actual spend) and what the sheet is meant to do. A vague paste gets a vague answer, but a described one gets you a diagnosis you can actually trust.

How Do You Fix Common Excel Errors Like #N/A and #REF!?

Errors are where inherited workbooks really bite. When you drag that formula down and half the column returns `#REF!` or `#N/A`, you can paste the exact error along with the formula and a line about what changed.

For example: “This cell shows #N/A after I sorted the sheet. Here is the formula and here is a sample of the lookup table. What is the likely cause and how do I fix it?” ChatGPT will usually point to a missing lookup value or a range that no longer matches, and suggest a corrected VLOOKUP or a switch to INDEX MATCH.

A `#REF!` almost always means a deleted row or column broke a reference, while `#DIV/0!` flags a division by an empty or zero cell. Describe what you did just before the error appeared, because that timeline is often the biggest clue.

Adding Comments Or Simplifying The Formula

Once you understand the formula, ask ChatGPT to make it maintainable for the next person. Prompt it to “add an inline comment explaining this formula” or “rewrite this to be simpler and easier to read using LET or named ranges.”

For the budget tracker, that might mean splitting one nested giant into two clearer helper columns so your teammates are not stuck decoding it later.

If you want more of these workflows, this rundown of AI hacks that save hours of data analysis covers plenty of adjacent tricks. Always paste enough surrounding context, because an accurate diagnosis depends on ChatGPT seeing what the formula is actually working with.

Cleaning Messy Data With Guided Prompts

Here is the uncomfortable truth about spreadsheets: the data you export rarely arrives clean. Names come through in ALL CAPS, phone numbers hide trailing spaces, and dates show up as a chaotic mix of DD/MM/YYYY and text strings that Excel refuses to sort.

This is where ChatGPT for Excel earns its keep. Instead of hunting for the right function, you describe the mess, and it hands you the exact formula or a Power Query sequence to fix it.

Picture a Singapore seller exporting a customer list from their Shopee or Lazada dashboard before sending a festive mailer. The export is usable, but it is not tidy, and a sloppy list means a sloppy campaign.

Fixing text and formatting inconsistencies

Paste a sample row and ask something like: “My customer names are in mixed casing with extra spaces; how do I standardise them in Excel?” ChatGPT will typically suggest `TRIM` to strip stray spaces and `PROPER` to convert “TAN AH KOW” into “Tan Ah Kow”.

For those inconsistent dates, it will point you to `TEXT` or Power Query’s data type detection to force everything into one clean format. The trick is telling it exactly what your messy input looks like and what you want the output to be.

If your export is large enough that formulas feel clunky, ask for the Power Query steps instead. It will walk you through Transform Data, changing column types, and applying trim and clean transformations in a repeatable query you can refresh next month.

Splitting and combining columns

Marketplace exports love to dump a full name into a single column, but a personalised mailer needs a first name.

Ask ChatGPT to split “Full Name” into “First Name” and “Last Name”. It will usually offer `TEXTBEFORE` and `TEXTAFTER`, the older `LEFT`, `RIGHT`, and `FIND` combinations, or the Text to Columns feature depending on your Excel version. Combining works the same way in reverse using `TEXTJOIN` or a simple `&` join.

Removing duplicates and blanks

Repeat buyers often appear multiple times, and blank rows sneak in from the export. Ask for a de-duplication approach, and it will suggest the Remove Duplicates button, `UNIQUE`, or a Power Query step, plus filters to drop empty rows before you count anything.

Now, a serious reminder. Customer names, emails, and phone numbers are personal data under Singapore’s Personal Data Protection Act (PDPA), enforced by the Personal Data Protection Commission (PDPC), and pasting them into a public AI tool is a real risk.

Before you prompt, do the following:

• Replace real values with dummy sample rows that mirror the structure
• Anonymise identifiers, for example “Customer A” instead of a real name
• Ask for the method, then apply it to your actual file offline

Clean data is the foundation of every reliable insight, which is exactly why it matters so much in what is data analytics and why it matters.

Building Charts And Summaries From Your Data

Once your formulas are working, the next hurdle is turning rows of numbers into something a manager can actually read at a glance. This is where ChatGPT for Excel shifts from formula helper to visualisation coach.

Imagine you are a retail manager tracking quarterly footfall across your Orchard and Jurong outlets. You have four quarters of numbers per store, but no idea how to present the story to your regional director. ChatGPT can help you decide the chart first, then walk you through building it.

Which Excel Chart Type Should You Use?

The mistake I see most often is picking a chart because it looks nice, not because it answers the question. ChatGPT is good at reversing that logic if you tell it what you want the reader to notice.

Ask it plainly: “I want to show how footfall at Orchard versus Jurong changed each quarter. Which chart type is best and why?” It will usually recommend a clustered column chart for comparing two outlets across time, and explain that a line chart works better if you care mainly about the trend direction rather than the exact quarterly gap.

If you want to understand the bigger picture of framing questions before you chart them, this primer on what data analytics is and why it matters is worth a read.

Generating PivotTable steps via prompts

Charts are only as good as the summary behind them, and PivotTables are how you get there fast. Instead of guessing which fields to drag, ask ChatGPT for the exact steps.

Try: “Give me step-by-step Excel instructions to build a PivotTable that shows total footfall by outlet and by quarter.” It will tell you to select your range, insert the PivotTable, drop Outlet into Rows, Quarter into Columns, and Footfall into Values, then it will explain how to insert a PivotChart from that summary in a couple of clicks.

Uploading a file versus doing it manually

ChatGPT can upload your workbook and generate charts and summaries directly, which saves the copy-paste dance. File upload and data analysis are available to free users too, though with tighter usage limits than the paid tiers, which give you higher caps and more consistent access.

Here is a summary prompt you can paste with a pasted sales table if you are on the free tier: “Summarise the key trends in this quarterly sales table, note the top and bottom performing outlet, and flag any quarter that dropped sharply.”

A few things to keep in mind before you upload anything:

• File upload and data analysis are available to free users, but with lower usage limits, so for heavy or repeated work the paid tiers are more reliable; otherwise pasting tables as text still works fine.
• Under PDPA, strip out anything that identifies customers or staff before uploading real files.
• Always sense-check the chart it describes against your own eyes, because it can misread merged cells.

Automating Repetitive Tasks With Macros And Scripts

Once you are comfortable asking for single formulas, you can push ChatGPT for Excel much further and get it to write full macros that do a whole job for you in one click.

A formula fixes one cell or column. A macro, written in VBA or as an Office Script, can reformat an entire sheet, generate a report, or loop through rows to send emails, all automatically.

A Monday morning scenario

Picture an admin executive at a mid-sized firm in Singapore who receives a raw HR attendance export every Monday. It comes as an ugly dump: unformatted dates, staff IDs in the wrong order, no totals, and no clean layout for the manager.

Doing this by hand takes twenty minutes of copying, sorting, and formatting before the report is even readable.

Instead, describe the task to ChatGPT in plain words. Tell it exactly what the raw file looks like and what you want the finished report to look like.

Your prompt might be: “Write an Excel VBA macro that takes columns A to E of raw attendance data, formats the date column as DD/MM/YYYY, sorts by staff ID, adds a total absences column, and applies bold headers with a blue fill.”

Running the code safely

ChatGPT will return code you can paste into the VBA editor (press Alt and F11 in Excel, insert a module, paste, then run). But how you run it matters just as much as the code itself.

Never run a macro on your only copy of important data. Save a duplicate first and test on that, because a badly written loop can overwrite or delete rows with no undo.

Keep these habits every time you use AI-generated automation:

• Test on a copy of the file, never the live original, until you trust the result.
• Understand that enabling macros lowers your security, so only enable them for files you created or fully trust.
• Read the code and ask ChatGPT to explain any line you do not understand before running it.
• Watch out for macros in files sent by others, as they are a common malware vector.

If you cannot explain roughly what a line of code does, ask ChatGPT to comment each section in plain English. This turns the macro into a learning tool rather than a black box.

This same mindset, describing a repetitive job clearly and letting a tool handle the grunt work, is exactly what powers modern automation platforms. If you want to go beyond Excel, you can learn to build an AI workflow automation step by step and connect ChatGPT to your wider stack.

Avoiding Common Mistakes And Data Risks

When I first started using ChatGPT for Excel, I trusted its output too readily. A SUMIFS it wrote pointed to column C when my amounts sat in column D, and I lost the best part of an hour reconciling a monthly sales total that quietly refused to add up until I checked the reference by hand.

The tool is confident, always. That confidence is exactly why you need a verification habit before you paste anything into a live workbook.

Spotting And Correcting Wrong Formulas

The three failure modes I see most often are hallucinated functions, wrong cell references, and logic that sounds right but isn’t.

Hallucinated functions are the easiest to catch: ChatGPT sometimes suggests a function that does not exist in your version of Excel, and the cell simply throws a #NAME? error.

Wrong references are sneakier. It might build a SUMIFS that points at column C when your amounts sit in column D, and the formula runs perfectly while returning nonsense.

Confidently wrong logic is the dangerous one. A nested IF that looks tidy can still misclassify your edge cases, so a commission tier gets applied to the wrong band and nobody notices until reconciliation.

My rule is simple: never accept a formula on faith. Test it on three or four sample rows where you already know the answer, then cross-check any total against a manual sum or a pivot table.

Protecting Confidential And Personal Data Under PDPA

Here is the risk that keeps compliance officers awake. Imagine a finance team at a Singapore SME pasting a full client ledger, complete with names, NRIC fragments, and revenue figures, straight into a public chatbot to speed up a reconciliation.

That is a genuine PDPA concern. Under Singapore’s Personal Data Protection Act, personal data should not be disclosed to a third party without proper safeguards, and a public AI service can retain or process what you submit in ways you do not control.

The fix is to anonymise before you ask. Replace real names with Client A and Client B, strip out identifiers, and describe your problem using dummy figures. You want the formula, not to hand over your customers.

Getting this right is partly about how you frame the request, which is where understanding context engineering vs. prompt engineering helps you supply enough structure without leaking sensitive data.

Knowing When To Switch Tools Or Ask A Human

ChatGPT is a helper, not a replacement for your judgement. When you hit complex modelling, large datasets, or audited financial reporting, switch to Excel’s own tools like Power Query, or bring in a trained analyst who can validate the approach.

Treat the AI as a fast drafting partner and yourself as the reviewer who signs off. Human-in-the-loop use, where you understand every formula before it touches real numbers, beats blind trust every single time.

A Copy-Paste Prompt Cheatsheet For Excel

Here is the part you can actually put to work today. I have gathered the prompts I reach for most when I use ChatGPT for Excel, grouped so you can find the right one fast.

Copy these into a notes file or a pinned tab, then swap in your own column names and goals. The placeholders in square brackets are where you fill in your specifics.

Prompts For Writing And Converting Formulas

When you know the outcome but not the syntax, describe the outcome plainly and let ChatGPT translate it.

• Write an Excel formula that [calculates X] using columns [A] and [B], and explain what each part does.
• Convert this nested IF formula into a cleaner version using IFS or SWITCH: [paste formula].
• I have data in column [X]. Write a formula to [look up, sum, count, or flag] based on a match in column [Y].
• Rewrite this formula so it works in Google Sheets instead of Excel: [paste formula].

Here is a Singapore-flavoured one I use for GST work. If you run a small business or handle finance for one, try this: “Write an Excel formula that calculates 9% GST on the net amount in column [C], then a second formula for the gross total including GST, and show me both.”

That single prompt saves you from second-guessing the rate/basics-of-gst/current-gst-rates) and the rounding, which matters when you are preparing figures for your quarterly GST filing.

Prompts For Explaining And Debugging

Half the battle with inherited spreadsheets is understanding what someone else built. ChatGPT is excellent as a patient explainer.

• Explain this Excel formula in plain English, step by step: [paste formula].
• This formula returns a #REF! (or #N/A, #VALUE!) error. Here it is: [paste formula]. What is wrong, and how do I fix it?
• I expected [result] but got [actual result] from this formula: [paste formula]. What could cause that?
• Simplify this formula and tell me if there is a risk it breaks when I copy it down the column: [paste formula].

I once spent twenty minutes hunting a broken VLOOKUP on a staff-salary lookup that turned out to be an exact-match versus approximate-match issue, the fourth argument left as TRUE instead of FALSE. Pasting it in with the “explain step by step” prompt would have caught it in seconds.

Prompts For Cleaning Data And Building Charts

Messy data is where analysts lose the most time, so these prompts pull double duty.

• I have inconsistent text in column [X], such as [examples]. Write a formula or steps to standardise it.
• Suggest Power Query steps to split the full name in column [A] into first and last name columns.
• Recommend the best chart type to show [relationship or trend] from columns [X] and [Y], and tell me how to build it.

Save this cheat sheet and keep adapting it as your work changes. The real leap comes from stronger prompting, so it is worth studying prompt engineering techniques that improve AI results and building the habit through structured, SkillsFuture-supported training rather than guessing alone.

Conclusion

Remember that stuck feeling from the start, staring at a formula error or an inherited sheet nobody could explain? That is exactly the moment ChatGPT for Excel earns its keep.

Across this guide, we have seen how it can write formulas from a plain English description, explain and debug someone else’s messy workbook, clean inconsistent data, build charts, and even sketch out macros to automate the boring bits.

The catch, and I keep coming back to this, is that ChatGPT is a co-pilot, not an autopilot. You still verify every result against your own numbers, and you never paste customer NRICs, salaries, or anything PDPA-sensitive into a public chat window.

Used that way, it becomes a genuinely high-utility everyday skill. For a finance executive, an ops coordinator, or an SME owner in Singapore juggling their own reports, that difference between staring blankly and shipping a clean analysis is worth real hours every week.

So save the cheat sheet from this article, then open one of your own files tonight and practise. A GST reconciliation sheet or a monthly sales tracker is the perfect place to test a prompt or two.

When you are ready to move from ad hoc prompting to structured, confident analysis, that is where formal training pays off. Consider enrolling in the SkillsFuture-supported AI in Data & Business Analytics Course to learn the frameworks behind the formulas, so ChatGPT amplifies real analytical judgement rather than replacing it.