Best ChatGPT Prompts for Excel and Data Analysis

The most useful ChatGPT prompts for Excel target specific tasks: writing formulas, cleaning messy data, structuring pivot tables, and generating VBA macros. Vague requests return generic answers — prompts that describe your exact column layout, data format, and desired output return something you can paste straight into your spreadsheet and use immediately.

Why Specificity Is Everything With Excel Prompts

Most people use Excel at around 20% of its actual capability. VLOOKUP, SUM, and maybe a pivot table — that’s the ceiling for a lot of professionals who spend hours in spreadsheets every week. ChatGPT can push you well beyond that, but only if you give it enough context.

The difference between a mediocre result and a great one comes down to column names and data format. “Write me an Excel formula” returns rubbish. “I have sales data in columns A to E, with dates in column A and revenue in column D. Write an XLOOKUP formula to return the revenue for a specific date I enter in cell H2” returns something immediately useful. Always include your column headers, the sheet name if relevant, and the exact outcome you’re after.

Prompts for Writing Excel Formulas

These cover the tasks that eat up the most time for most professionals:

For lookups:

“My spreadsheet has customer names in column A and account IDs in column B. I need a formula in column C that looks up the account ID from a second sheet called ‘Accounts’, matching on customer name. Write the XLOOKUP formula.”

For conditional logic:

“I have a column of sales figures in column D. Write a formula in column E that labels each row as ‘High’ if the value is above $50,000, ‘Medium’ if between $20,000 and $50,000, and ‘Low’ if below $20,000. Use IFS.”

For date calculations:

“Column A has invoice dates and column B has payment dates, both in DD/MM/YYYY format. Write a formula that calculates the number of business days between them, excluding Australian weekends.”

XLOOKUP, IFS, and NETWORKDAYS are all native to Excel 365 and Excel 2021+, which most Australian business users run as of mid-2026. If you’re on an older version, tell ChatGPT — it’ll suggest INDEX MATCH or nested IF alternatives without complaint.

Prompts for Data Cleaning and Transformation

Dirty data is the single biggest time sink in any analysis workflow. These prompts fix the most common problems fast:

Standardising text:

“I have a column of company names with inconsistent capitalisation and trailing spaces. Give me a formula in the next column to standardise them to proper case with no extra spaces.”

Splitting full names:

“Column A has full names in ‘Firstname Lastname’ format. Write formulas to split them into separate first name (column B) and last name (column C) columns using only Excel functions — no Power Query.”

Fixing numbers stored as text:

“I imported data from a CSV and the numbers in column D are stored as text with leading apostrophes. Explain how to convert the entire column to actual numbers in Excel 365 without losing data.”

For best results, paste three or four rows of sample data directly into your message. ChatGPT will infer the exact format and tailor the solution accordingly — no back-and-forth guessing.

Prompts for Pivot Tables and Summary Stats

Pivot tables are powerful but the setup is non-obvious if you haven’t used them much. Use this prompt structure:

“I have a dataset with columns: Date, Region, Salesperson, Product, and Revenue. I want a pivot table showing total revenue by Region and Product, filtered to the last financial quarter. Walk me through the exact configuration steps in Excel 365.”

For formula-based summaries without a pivot table:

“Using SUMIFS, write a formula that totals revenue in column E only where column B equals ‘Victoria’ and column C equals ‘Q2 2026’.”

Prompts for VBA Macros and Automation

Repetitive tasks — reformatting reports, copying data across sheets, sending consistent emails — are where macros earn back hours every week. You don’t need to know how to code:

“Write a VBA macro that loops through every worksheet in this workbook, copies data from columns A and B, and pastes it into a new summary sheet called ‘Master’. Add the source sheet name as a third column.”

“Write a VBA macro that formats every cell in column C as Australian dollar currency (AUD, two decimal places) and applies conditional formatting to highlight values over $100,000 in green.”

Paste the output into the VBA editor (Alt + F11 on Windows), run it on a copy of your file first, and adjust as needed. ChatGPT is good at debugging VBA — if it throws an error, paste the error message straight back in and it’ll fix it.

Frequently Asked Questions

Can ChatGPT write Excel formulas accurately?

Yes — for standard formulas, ChatGPT is highly reliable. XLOOKUP, IFS, SUMIFS, and TEXT functions come out correctly most of the time. Accuracy improves significantly when you describe your column layout precisely. Complex nested formulas occasionally need minor edits, so always test in a cell before relying on them in production.

Does ChatGPT work with Google Sheets too?

Most Excel formulas translate directly to Google Sheets, with minor syntax differences. XLOOKUP, IFS, and ARRAYFORMULA all work in Sheets. VBA macros don’t translate — Google Sheets uses Apps Script (JavaScript-based). Specify your platform in the prompt and ChatGPT will write the right code for it.

What Excel version do these prompts assume?

The prompts above use functions available in Excel 365 and Excel 2019 and later. If you’re on Excel 2016 or earlier, XLOOKUP won’t be available. Tell ChatGPT your version and it will substitute compatible alternatives like VLOOKUP, IFERROR, or nested INDEX MATCH formulas.