Excel ChatGPT Prompts: 40 Formulas for Data Analysis

The AI-Powered Excel Revolution

For years, mastering Excel felt like learning a secret language. You’d stare at a complex problem, knowing the answer was a VLOOKUP, an INDEX-MATCH, or a dynamic array formula away, but the path to get there was buried in forums and dense tutorials. The friction was real: you had the data, but not the immediate know-how to unlock its story.

That era is over. The emergence of advanced AI, like ChatGPT, has fundamentally changed the game. It’s not about replacing your skills; it’s about augmenting them. Think of ChatGPT as your always-available Excel co-pilot. You describe the problem in plain English, and it translates your intent into precise, working formulas, VBA macros, and PivotTable strategies in seconds. This guide is born from that exact experience—applying this workflow daily to turn data chaos into clear, actionable insights faster than ever before.

This repository directly tackles the core pain points:

• The Syntax Wall: Forgetting the exact structure of a XLOOKUP with multiple criteria.
• The Macro Mystery: Wanting to automate a repetitive task but fearing the VBA editor.
• The PivotTable Puzzle: Knowing you need to summarize data but struggling with the correct field arrangement.

Here, you won’t find theoretical overviews. You’ll get 40 battle-tested, ready-to-use ChatGPT prompts designed to generate specific solutions. We’ll cover how to craft prompts that return accurate formulas, build automated reporting systems, and execute advanced data analysis that would typically take hours to figure out manually.

Your New Workflow: From Question to Formula

The key shift is moving from searching for an answer to directing an AI to build it. For example, instead of scouring the web for a formula to find the latest sale date for each customer, you’ll learn to prompt:

“Act as an Excel expert. I have a table where column A is Customer ID and column B is Sale Date. Write a single formula to extract the most recent sale date for each unique customer ID.”

This guide provides the precise language to bypass generic answers and get the exact, executable code you need. Let’s transform how you work with data.

Section 1: Mastering the Fundamentals: Prompts for Everyday Formulas (Est. 500 words)

Let’s be honest: you don’t need another article explaining what VLOOKUP does. You need to know exactly what to ask ChatGPT to get a working formula for your specific, messy spreadsheet—right now. This section delivers that. We’re moving past theory into the precise prompt engineering that turns AI from a novelty into your most reliable data assistant.

The key is specificity. A vague prompt gets you a generic, often flawed, answer. A precise prompt, built on an understanding of Excel’s logic, gets you a bulletproof solution. Here’s how to craft those prompts for the three pillars of everyday data wrangling.

VLOOKUP & XLOOKUP Demystified: From Fuzzy Matches to Bulletproof Tables

The classic pain point? Trying to pull client names from a massive order log using an ID. A basic VLOOKUP fails if your data isn’t sorted perfectly for approximate matches or if you get the dreaded #N/A. Your prompt must dictate the exact behavior.

For a basic, exact-match VLOOKUP:

“I have two sheets. Sheet1 has a list of Employee IDs in column A. Sheet2 has Employee IDs in column A and corresponding Email Addresses in column B. Write a VLOOKUP formula for Sheet1, column B to pull the email from Sheet2 by matching the ID exactly. Include IFERROR to return ‘Not Found’ if there’s no match.”

The Golden Nugget: Always specify the table array as a fixed range (e.g., Sheet2!$A:$B) in your prompt. This prevents reference errors when copying the formula. ChatGPT will correctly add the $ signs for you, a small detail that prevents huge headaches.

For modern power, XLOOKUP is your best friend. Its default is an exact match, and it can search in any direction. A smart prompt unlocks its potential:

“Using XLOOKUP, find the ‘Department’ for a ‘Employee Name’ typed into cell C2. The lookup array is a named range ‘Employee_List’ on the ‘Data’ sheet. The return array is the adjacent column to the right. If not found, return ‘Invalid Entry’. Make the search mode exact match.”

This prompt demonstrates expertise by using structured references (named ranges) and explicitly controlling the match mode, yielding a cleaner, more resilient formula than VLOOKUP ever could.

Logical Tests with IF, IFS, and SUMIFS: Automate Decision-Making

This is where you move from retrieving data to making your spreadsheet think. The goal is to categorize data, flag outliers, or perform calculations based on multiple conditions.

To create a tiered categorization system (e.g., for sales performance):

“In column D, create a formula that categorizes the ‘Sales Value’ in column C. Use IFS: If value is over 10000, return ‘Gold’. If over 5000, return ‘Silver’. If over 1000, return ‘Bronze’. For anything else, return ‘Review’. Handle any potential errors gracefully.”

For complex, multi-criteria summaries, SUMIFS is indispensable. A common task: summing sales only for a specific region and product in a given quarter.

“Generate a SUMIFS formula to calculate total ‘Revenue’ (column E) where ‘Region’ (column B) is ‘North’, ‘Product’ (column C) is ‘Widget Pro’, and ‘Qtr’ (column D) is ‘Q1’. The data range is A2:E1000.”

The expert insight here? Order your criteria logically. Place the most restrictive condition first (like a specific product ID) to improve calculation efficiency in large datasets—a nuance ChatGPT will incorporate if your prompt is well-structured.

Text & Date Wizardry: Taming Unstructured Data

Data rarely arrives clean. You get full names in one column, inconsistent date formats, or product codes buried in strings. Your prompts must instruct ChatGPT on the exact extraction or transformation needed.

To split ‘Full Name’ (e.g., ‘Doe, Jane’) into ‘Last Name’ and ‘First Name’ columns:

“In column B, extract the last name from ‘Full Name’ in column A, assuming the format is ‘Last, First’. Use a combination of LEFT and FIND functions to get all text before the comma. In column C, extract the first name using TRIM, MID, and FIND to get text after the comma.”

For standardizing a column of mixed date formats (e.g., ‘03/14/2025’, ‘14-Mar-25’):

“Column A has dates in various formats. Write a formula that uses the DATEVALUE function within IFERROR to attempt to convert each cell to a proper Excel serial number. Provide a second, fallback method using TEXT-to-Columns logic with MID and FIND if DATEVALUE fails, assuming the day is always the first two digits.”

This two-layered approach shows deep, practical expertise. It acknowledges the reality of dirty data and provides a robust solution, not just a textbook answer that breaks on the first error.

The Throughline: Mastering these fundamentals isn’t about memorizing syntax. It’s about learning to diagnose your data problem in plain English and translate it into a structured request for the AI. By starting with these precise prompts for lookup, logic, and text/date functions, you build a reliable toolkit that solves 80% of daily data challenges, forming the essential foundation for the advanced automation we’ll cover next.

Section 2: Data Summarization Power: PivotTable & Analysis Prompts (Est. 550 words)

You’ve mastered the formulas. Now, let’s architect the insights. A PivotTable is more than a feature—it’s a conversation with your data. But setting it up correctly from the start is the difference between a clear answer and a confusing mess. The key is instructing ChatGPT with the precision of a data architect, not a casual user.

Think of your source data as the blueprint. A single blank row or a column with mixed formats (dates stored as text, for instance) will derail the entire process. Your first prompt must enforce data integrity.

ChatGPT Prompt for Clean Structure: “I have an Excel dataset in the range A1:G500. It contains columns for Sale Date, Region, Salesperson, Product, Units Sold, Unit Price, and Total Sale. Generate a step-by-step checklist to prepare this data for a PivotTable, including how to convert text to proper dates, remove blank rows, and ensure Unit Price is in a consistent number format.”

Building the Perfect PivotTable Foundation

With clean data, you can now dictate the narrative. A common rookie mistake is dragging every field into the PivotTable, creating an unreadable report. Instead, you must tell ChatGPT exactly what question you’re answering. Are you analyzing sales trends over time, comparing performance by region, or breaking down product mix? The prompt frames the analysis.

ChatGPT Prompt for Strategic Layout: “Using the cleaned dataset described, I need to analyze quarterly sales performance by region and product. Write the exact steps to create a PivotTable where: Rows = Region and then Product, Columns = Sale Date grouped by Quarters, Values = Total Sale formatted as Currency. Also, add a filter for Salesperson.”

The Golden Nugget: Always group dates within the PivotTable Field Settings, not in your source data. This keeps your raw data pristine and allows you to easily regroup by months, years, or fiscal periods later. Simply instruct ChatGPT: “Include steps to group the Sale Date field by Quarters after adding it to the Columns area.”

Beyond Totals: Crafting Custom Metrics with Calculated Fields

What’s your average sale value per transaction? What’s the profit margin if you had a Cost column? PivotTable totals can’t answer these directly. You need Calculated Fields—virtual columns that live only in your PivotTable. This is where you move from reporting what is to analyzing what matters.

ChatGPT Prompt for a Calculated Field: “In my PivotTable from the previous prompt, add a Calculated Field named Avg. Transaction Value. The formula should be = 'Total Sale' / 'Units Sold'. Provide the exact steps: from accessing the ‘Fields, Items, & Sets’ menu to naming the field and writing the formula. Then, instruct how to add this new field to the PivotTable’s Values area and format it as Currency.”

This transforms your static table into a dynamic analysis tool. You can create fields for percentages of total, differences from average, or custom KPIs specific to your business logic.

Creating Interactive Dashboards with Slicers & Timelines

A report that requires manual filtering for every question is a dead report. Slicers and Timelines bring your PivotTable to life, creating an interactive dashboard that anyone can use. The magic here is in connecting multiple PivotTables to a single set of slicers for a unified control panel.

ChatGPT Prompt for an Interactive Dashboard: “I have three PivotTables on one worksheet from the same data source: one for Sales by Region, one for Units Sold by Product, and one for Monthly Trends. Generate the instructions to: 1. Insert a Slicer for Region and a Timeline for Sale Date. 2. Connect both controls to all three PivotTables. 3. Format the slicer to show 4 columns and use the Timeline to filter for the last full calendar year by default.”

The professional touch? Always use a Timeline for date filtering instead of a standard Slicer when possible. It provides a more intuitive, visual method for users to select ranges and is less prone to error. This small choice signals a deep understanding of user experience in data reporting.

By deploying these prompts, you’re not just building tables; you’re constructing an insight engine. You shift from asking “What happened?” to enabling users to ask “What happened for this product, in this region, during this period?”—and getting the answer in two clicks. This is how you turn raw data into a strategic asset.

Section 3: Automation Unleashed: Prompts for Macros & VBA Scripts (Est. 600 words)

You’ve mastered formulas and tamed PivotTables. Now, let’s address the true time-sink: the repetitive, manual tasks that eat up your week. This is where moving from recording simple macros to instructing ChatGPT to write custom VBA scripts becomes a superpower. The key shift? You’re no longer just automating a single action; you’re engineering a resilient, reusable process.

The most common mistake here is being too vague. Telling ChatGPT to “make a macro for cleaning data” will yield generic, often flawed code. Your success hinges on providing clear, procedural context—just like you’d brief a junior developer. Frame the task, define the parameters, and specify the desired outcome.

From Tedious Task to One-Click Macro

Start by identifying any process you perform more than twice a week. The best prompts dissect that process into clear, sequential steps for the AI.

• For Automated Formatting & Reporting: Instead of manually adjusting every new report, use a prompt that defines the structure.

  Your Prompt: “Write a VBA macro for Excel that does the following: 1) Sets the font of cells A1:G50 to Calibri 11, 2) Applies bold formatting to the first row, 3) Adds alternating light gray fill to every other row for readability, 4) Auto-fits all columns, and 5) Sets print area to the used range. Include error handling to stop if the sheet is empty.”

  The Golden Nugget: Always include the target range (e.g., A1:G50 or UsedRange). This prevents the macro from accidentally running on your entire 1-million-row dataset and freezing Excel. It’s a small detail that demonstrates practical, hard-won experience.

• For Data Import & Consolidation: Tired of copying, pasting, and cleaning files from a folder?

  Your Prompt: “Generate VBA code that opens all Excel files in a specified folder ‘C:\Reports\Daily’, copies data from ‘Sheet1’ range A2:K100 from each file, and pastes it as values into a master sheet in a new workbook, stacking the data. Add a timestamp column to record when each import occurred.”

  This prompt moves you from a manual chore to a scheduled, reliable data pipeline.

Building Bulletproof Macros: Error Handling & User Input

A macro that works perfectly on your test data but crashes on a real-world exception is worse than no macro at all. Robustness is what separates a handy script from professional-grade automation.

• Implementing Basic Error Handling: Instruct ChatGPT to make your code fail gracefully.

  Your Prompt: “Add error handling to the following macro code [paste your code]. Include a ‘On Error GoTo ErrorHandler’ section that displays a user-friendly message box stating the error number and description, then resumes at the end of the subroutine.”

• Creating Simple Dialog Boxes: Move beyond hard-coded values. Let users provide input.

  Your Prompt: “Write a VBA script that shows an input box asking the user ‘Enter the Fiscal Year (YYYY):’. Store the input as a variable. Then, use that variable to filter a table on Sheet1 in column C (which contains dates) to show only rows from that fiscal year (July 1 to June 30). Validate that the input is a 4-digit number.”

  This transforms a static macro into a flexible tool your whole team can use.

Looping Through Data Like a Pro

The real magic of VBA is applying logic across hundreds or thousands of rows programmatically. The For Each or Do While loop is your engine. Your prompt must clearly define the condition and the action.

• For Conditional Formatting & Flagging: Need to highlight all rows where sales are down more than 15% month-over-month?

  Your Prompt: “Create a macro that loops through rows 2 to the last row in Sheet1. In each row, compare the value in column E (Current Month) to column F (Previous Month). If the value in E is at least 15% less than F, highlight the entire row in light red. Also, write the text ‘Review’ in a new column K.”

• For Data Cleansing & Standardization: Cleaning product codes or addresses?

  Your Prompt: “Write VBA code that iterates through all cells in column B (‘Product Code’). For each cell, remove any leading or trailing spaces, change all text to uppercase, and replace any instance of ’#’ with ’-’. Skip any cell that is empty.”

The Throughline: The leap into VBA isn’t about memorizing syntax; it’s about developing a procedural mindset. You learn to deconstruct any repetitive task into a logical sequence a machine can execute. By using these structured prompts with ChatGPT, you act as the systems architect—defining the rules, boundaries, and user experience—while the AI handles the precise coding. This allows you to automate not just tasks, but entire workflows, reclaiming hours for genuine analysis and strategy. Start with one specific, annoying task this week, describe it precisely, and build your first custom solution.

Section 4: Advanced Analysis & Data Modeling Prompts (Est. 550 words)

You’ve mastered the formulas and automated the basics. Now, it’s time to architect. This section is for when your data lives in multiple files, your lookups need to be bulletproof, and your summary reports require custom business logic. Here, we move beyond worksheet functions into the powerful trio of Power Query, dynamic array formulas, and the Data Model with DAX. These are the tools that transform Excel from a calculator into a genuine business intelligence platform.

The key to prompting ChatGPT for these advanced concepts is specificity. You’re no longer just asking for a formula; you’re describing a data transformation pipeline or a business rule. The more precise your description of the starting point and the desired outcome, the more accurate the generated M code or DAX measure will be.

Power Query (Get & Transform) Automation

Power Query is Excel’s built-in ETL (Extract, Transform, Load) tool. Its strength is in repeatability: build a cleaning process once, and refresh it with new data with one click. The challenge is writing the “M” language code that powers it. Use ChatGPT to bridge that gap.

Your Prompt Strategy: Frame your request as a step-by-step recipe. Describe your source data’s flaws and the clean table you need.

Example Prompt: “I have an Excel table named SalesData with columns: [Date, Product_ID, Region, Sales_Rep, Units, Revenue]. I need a Power Query script to: 1) Change the Date column to a Date data type. 2) Split the Product_ID column (format ‘CAT-12345’) into two new columns: ‘Category’ (the ‘CAT’ part) and ‘SKU’ (the numeric part). 3) Remove any rows where the Region column is blank or contains ‘TEST’. 4) Pivot the data to show Units by Region for each Product_ID. Write the complete M code for this transformation.”

Why This Works: This prompt provides clear object names, specific column issues, and a logical sequence. It tells ChatGPT to generate ready-to-use Advanced Editor code, saving you from manually clicking through the GUI. A golden nugget from experience: Always ask ChatGPT to include the let and in structure in its M code, as incomplete snippets are a common point of failure for beginners.

Mastering INDEX-MATCH & Dynamic Arrays

While VLOOKUP is a great starter, INDEX-MATCH is its more flexible, powerful cousin. And in modern Excel (Office 365), dynamic array functions like XLOOKUP, FILTER, SORT, and UNIQUE are game-changers. They spill results automatically, creating fluid, interactive reports.

Your Prompt Strategy: Specify the need for a two-way lookup (searching by row and column) or a need to return multiple matching records.

Example Prompt: “In my pricing table, I need a formula to find the price for a specific product in a specific region. My lookup table has products as row headers (A2:A100) and regions as column headers (B1:Z1). The price grid is in B2:Z100. Write an INDEX-MATCH formula where I can input the product name in cell D2 and the region in cell E2, and return the correct price. Then, provide the equivalent XLOOKUP formula using the same logic.”

Example Prompt for Dynamic Arrays: “I have a transaction list in Table1 with columns [InvoiceID, Customer, Amount, Date]. I want to extract a unique, sorted list of customers who had transactions over $500 in the last 30 days. The report date is in cell G1. Use the FILTER, UNIQUE, and SORT functions together in a single formula to accomplish this.”

The Expert Insight: XLOOKUP is often superior, but INDEX-MATCH is still essential for backward compatibility with older workbooks. Teach ChatGPT which version of Excel you’re using. For dynamic arrays, remember: if your formula returns a #SPILL! error, it usually means there’s data blocking the cells below your formula. Clearing those cells fixes it.

Introduction to Data Model & DAX

When you have multiple related tables (e.g., Sales, Products, Customers), PivotTables alone get messy. The Data Model creates relationships between tables, and DAX (Data Analysis Expressions) lets you write custom measures that work across these relationships.

Your Prompt Strategy: Focus on the business logic of the calculation you need, explicitly mentioning table and column names.

Example Prompt: “I have two tables in my Excel Data Model: Sales (with columns [SaleID, CustomerID, ProductID, Quantity, SaleDate]) and Products (with [ProductID, ProductName, Cost, Price]). They are related by ProductID. I need to create two DAX measures for a PivotTable:

1. Total Profit: Sum of ([Quantity] * ([Price] - [Cost])) from the related tables.
2. Year-over-Year Growth %: Calculate the percentage change in Total Profit compared to the same period last year. Assume a Calendar table is also connected to Sales[SaleDate]. Write the DAX code for both measures, with clear comments.”

Building Trust with Accuracy: DAX is context-sensitive. A great prompt clarifies if the calculation should be a measure (for aggregations in a PivotTable) or a calculated column (a new static column in a table). Measures are far more common and efficient for analysis. Emphasize to ChatGPT that you need a measure unless you specify otherwise.

Mastering these prompts shifts your role from spreadsheet operator to data architect. You’re not just solving a single problem; you’re building a scalable, self-documenting system for analysis. Start by automating one complex monthly report with Power Query, and you’ll instantly reclaim hours—that’s the tangible power of applying AI to advanced Excel workflows.

Section 5: The Art of the Prompt: Best Practices & Troubleshooting (Est. 450 words)

You’ve seen the prompts. You’ve generated code. But why does one prompt return a perfect VLOOKUP while another gives you a broken macro? The difference isn’t luck—it’s prompt engineering. Think of ChatGPT as a brilliant but literal intern. Your job is to provide crystal-clear direction. Mastering this turns a curious tool into a reliable co-pilot for your most complex Excel tasks.

Prompt Engineering for Excel: The Three-Part Blueprint

Throwing a vague question at ChatGPT is like asking a colleague, “Can you analyze this data?” You’ll get a generic, often useless response. For Excel, specificity is your superpower. Every effective prompt should have three core components:

1. Context & Role: Set the stage. Tell ChatGPT who it should be. "Act as an expert Excel analyst specializing in financial data models." This frames its thinking.
2. Your Data Structure: This is non-negotiable. Provide sample column headers, data types, and a snippet of what your data looks like. For example: "My data is in a table named 'SalesData' with columns: Date (mm/dd/yyyy), Product_ID (text), Region (text), Units_Sold (integer), Revenue (currency)."
3. The Precise Task & Format: State the exact outcome. Do you need a formula, a PivotTable setup, or a VBA script? Specify the cell for the output. "Write a dynamic array formula for cell G2 that sums 'Revenue' for 'Product_ID' in cell F2 and 'Region' in cell F1."

Golden Nugget: Always include an error-checking clause. Add a line like: "Explain the logic of the formula in one sentence and list one common error to watch for." This forces the AI to self-audit and gives you immediate insight into how it works.

Debugging & Interpreting ChatGPT’s Code

Even with a perfect prompt, you must validate the output. Never blindly run VBA code on your master file. Here’s a safe testing protocol:

• Test in Isolation: Create a new workbook with dummy data that mirrors your structure. Paste and run the formula or macro there first.
• Decode Error Messages: When you get a #N/A, #REF!, or a VBA “Run-time error 1004,” don’t just re-prompt. Copy the exact error message and your formula back into ChatGPT with this prompt: "I received this error: [Paste Error]. The formula I'm using is [Paste Formula]. My data is in [Describe Range]. What is the most likely cause and how do I fix it?"
• Ask for a Walkthrough: If a formula is complex, prompt: "Explain this formula step-by-step as if I'm a beginner: [Paste Formula]." Understanding the why is crucial for adaptation and trust.

Ethical Use and Acknowledging Limitations

In 2025, the ethical use of AI in data analysis hinges on informed oversight. ChatGPT is a powerful accelerator, not an oracle.

• You Must Understand the Output: If you can’t explain the basic logic of a generated formula to a teammate, you shouldn’t deploy it in a business-critical model. The tool removes the burden of syntax, not the responsibility of logic.
• Know the Limits: ChatGPT’s knowledge is static; it doesn’t know about the new XLOOKUP function unless its training data includes it (though it now typically does). More critically, never feed it hyper-sensitive data (PII, financials, proprietary models). Assume any information you paste is processed.
• Complex Model Warning: For intricate financial models or statistical forecasting, use AI to draft components, but always have a human expert validate the methodology. The AI can assemble the pieces, but you must assure the structural integrity.

Your ultimate goal is a collaborative workflow: you provide the strategic direction and domain expertise; ChatGPT handles the tedious translation into code. By applying these best practices, you move from simply getting an answer to building a reliable, scalable system for Excel efficiency. Start your next prompt with the three-part blueprint, and watch your success rate soar.

Conclusion: Your Future with AI-Assisted Excel

Mastering the prompts in this guide does more than just save you time on your next spreadsheet. It fundamentally redefines your relationship with data. You’re no longer limited by the formulas you can recall or the macros you know how to write. Instead, you become a director of analysis, using plain English to instruct a powerful AI co-pilot to execute complex logic, build dynamic models, and automate tedious workflows. This combination—your domain expertise and strategic thinking with ChatGPT’s executional speed—is the new frontier of productivity.

The most common mistake I see is trying to learn everything at once. Don’t. The most effective path is to build compound skill.

• Start with a single, immediate pain point. Is it a recurring VLOOKUP that breaks? A PivotTable you rebuild weekly? Pick one relevant prompt, use it, and validate the output. This builds practical confidence.
• Iterate from there. Once you’ve successfully automated a summary report, use that saved hour to learn a Power Query prompt. Skills built this way stick because they solve real problems and create immediate value.
• Focus on the translation, not the syntax. Your growing expertise isn’t in VBA code itself, but in deconstructing a business process into the step-by-step logic an AI can code for you.

Future-Proofing Your Analytical Value

Looking ahead to 2025 and beyond, the most valuable analysts won’t be the fastest spreadsheet operators. They will be those who can ask the smartest questions and orchestrate tools—both AI and traditional—to find the answers. By integrating ChatGPT into your Excel work, you’re not just keeping up; you’re building a scalable, adaptable skill set that turns data overwhelm into strategic insight faster than ever before.

Your next step is simple: Open your most stubborn workbook, identify one repetitive task, and command your new AI assistant to fix it. That’s where your future in data begins.