10 Examples of How You Can Use ChatGPT for Excel

Tired of wrestling with Excel formulas or spending hours cleaning up data? We’ve all been there and wished there was a better or perhaps easier way to get things done.

Well, what if you could just say what you need and have AI handle the rest? The good news is that you can!

In this guide, we’ll explore how ChatGPT fits into Excel, share some of the tasks it can handle and how you can use it effectively - including the best method for keeping your data secure. So whether you're a casual user or work with spreadsheets daily, it can save time and simplify your workflow.

Let’s dive in.

Sidenote: Want to learn how to put all this into action? Check out my brand new ChatGPT for Excel course!

With that out of the way, let’s get into this guide.

What is ChatGPT for Excel?

In simple terms, it's learning how to integrate and use ChatGPT with Excel so that you can have your own AI Excel expert available 24/7.

However, it's important to understand that ChatGPT for Excel isn’t a replacement for Excel skills. These tools work so much better when you can use them to help streamline your current skillset - not to bypass it.

For example

Instead of spending time searching for the right formula, manually cleaning up data, or figuring out why an error is breaking your calculations on your own, you can describe what you need, and ChatGPT will respond with solutions.

What like?

I’ll share 10 examples in just a second, but some of the biggest ways it helps include generating formulas, automating repetitive tasks, analyzing data, and assisting with reporting. No joke but this single tool can fundamentally change how you use Excel by reducing the time spent on problem-solving and allowing you to focus on more important work!

Before we get into the different things you can do and examples, let’s break down the different methods of actually using these two tools together.

How to use ChatGPT with Excel

There are 4 options to set this up. Each is fairly easy to use, but with different pros and cons.

Option #1. Plain-text prompting

The most basic method is to simply open up ChatGPT on your browser, and then ask for help in a chat window. Then take its answer and apply the solution manually in Excel.

We can take this a step further though…

Option #2. Working directly with Excel files inside ChatGPT

Rather than just ask a question, you could also upload an Excel file directly to ChatGPT, which can then analyze it, create visualizations based on it, and even generate modified versions of the file!

Pretty handy right?

There is an even better option though.

Option #3. Use built-in Excel add-ins

You can also download and install third-party add-ins that integrate ChatGPT, directly into Excel.

This way you can ask questions, generate formulas, and automate tasks - all without having to switch back and forth between tools.

However, there is one more option that's a little more detailed but worth mentioning.

Option #4. Use OpenAI’s API

For businesses or anyone handling confidential data, you can also connect the ChatGPT API.

Why do this?

The API level service provides more control and security, allowing ChatGPT to automate Excel but without exposing sensitive information. (Since ChatGPT isn’t designed to process private data, the API method is the safest option if security is a concern).

TL;DR

Whichever you choose, each method is easy to set up and use. I recommend starting with a basic prompt and then trying out each method.

Now that we know how to use these two together, let’s look at exactly what ChatGPT can do in Excel and how it can make your work easier.

What can you do with ChatGPT in Excel?

This isn’t an exhaustive list. It's merely some of the most exciting features you might not have thought of using, that can instantly improve your workflow.

Example #1. Converting messy text into structured data

One of the biggest frustrations with Excel is dealing with raw, unstructured data. Whether you're pasting in data from an email, a PDF, or a report, it often comes in a disorganized format that doesn’t fit neatly into rows and columns.

However, instead of manually cleaning and reformatting the text, you can ask ChatGPT to structure it into a table that makes sense in Excel.

For example

Imagine you have a list of customer orders pasted from an email like this:

• John Smith - 2 iPhones, $1,900
• Jane Doe - 1 MacBook, $2,400
• Carlos Mendez - 3 AirPods, $450

This isn't useful in its current state, but you could ask ChatGPT:

"Can you reformat this data into a table with columns for Name, Product, Quantity, and Price?"

ChatGPT would return something like:

And so instead of spending time manually extracting information, you can copy and paste this structured data into Excel in seconds.

Example #2. Debugging formulas and fixing Excel errors

Few things are more frustrating than getting an Excel error like #VALUE!, #N/A, or #REF! and having no idea what went wrong. However, ChatGPT can analyze your formula, explain what the error means, and suggest ways to fix it.

For example

Let’s say you have this formula in Excel:

=VLOOKUP(A2, Sheet2!A:D, 5, FALSE)

But instead of returning a result, you get #REF!.

Rather than trying to figure this out yourself, you could get a head start on the issue by asking ChatGPT:

"Why is this VLOOKUP formula returning #REF!?"

ChatGPT would explain that the issue is in the third argument in this example. (Perhaps column 5 doesn’t exist in Sheet2!A:D, since the table only has four columns A to D).

It would then suggest a corrected formula, like:

=VLOOKUP(A2, Sheet2!A:D, 4, FALSE)

Example #3. Writing advanced formulas and combining functions

We all know that Excel becomes much more powerful when you start combining multiple functions, but writing complex formulas can be tricky.

The good news is that ChatGPT can help you structure advanced formulas step-by-step and even suggest alternative approaches that might be more efficient.

For example

Suppose you need a formula to find the most recent purchase date for each customer in a sales dataset. Instead of searching online for hours, you could ask:

"How do I write a formula that finds the most recent purchase date for a specific customer?"

ChatGPT could then suggest using MAXIFS:

=MAXIFS(B:B, A:A, "CustomerName")

You could even take it a step further.

For example

If you needed something a bit more advanced like pulling the customer name associated with the most recent purchase amount, then it could recommend the vaunted tandem of INDEX and MATCH, alongside MAXIFS

=INDEX(C:C, MATCH(MAXIFS(B:B, A:A, "CustomerName"), B:B, 0))

This is especially useful when working with financial models, forecasting, or large datasets where efficiency matters, and is the kind of tricky formula combo even seasoned Excel users might not think of!

Example #4. Generating realistic test data

If you're building a report, testing formulas, or creating an Excel template and need to test it all works, ChatGPT can generate sample data for you.

For example

"Generate a table of 100 fake sales transactions with columns for date, customer name, product, quantity, and total price."

ChatGPT will then create structured data that you can copy into Excel for testing purposes.

Example #5. Applying conditional formatting rules

Conditional formatting helps highlight key trends in your data. ChatGPT can suggest custom formulas for formatting cells dynamically.

For example

"How do I highlight all sales over $10,000 in red and under $1,000 in green?"

ChatGPT might suggest formulas like:

• '=AND(A1>10000)' For red
• '=AND(A1<1000)' For green

It would also guide you on how to apply them using Excel’s Conditional Formatting menu.

Example #6. Creating and analyzing pivot tables

Pivot tables are one of the most powerful features in Excel, but they can be intimidating if you’re not familiar with them. But ChatGPT can walk you through creating one step by step, suggest how to organize your data, and even troubleshoot common issues.

For example

If you have a dataset with sales transactions and want to analyze total sales by region, you could ask:

"How do I create a pivot table that shows total sales by region and product category?"

ChatGPT would then provide step-by-step instructions similar to the following:

1. Select your dataset
2. Click Insert > Pivot Table and choose where to place it
3. Drag Region into the Rows section
4. Drag Product Category into the Columns section
5. Drag Sales Amount into the Values section and set it to Sum

It can also help troubleshoot issues, like why your pivot table isn’t updating or why values are being counted instead of summed.

Example #7. Generating Power Query and Power BI formulas

For users who work with large datasets, Power Query and Power BI are essential tools for data transformation and visualization. ChatGPT can help by writing M code for Power Query and DAX formulas for Power BI, making these tools easier to use even if you’re not an expert.

This is especially useful when working with financial models, forecasting, or large datasets where efficiency matters.

For example

Let’s say you need to calculate year-over-year growth in Power BI. Instead of manually searching for the correct DAX formula, you could ask:

"What DAX formula should I use to calculate year-over-year growth in Power BI?"

To which ChatGPT might suggest something like:

YoY Growth = ([Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])))/ CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

This is a huge time-saver for users who need to create reports but aren’t familiar with Power BI’s syntax.

Example #8. Extracting specific data from large datasets

If you're working with thousands of rows of data and need to find specific information, ChatGPT can suggest filtering techniques, formulas, or even Power Query methods to extract exactly what you need.

For example

Suppose you have a dataset with customer names and email addresses in a single column, and you want to extract only the email addresses. Well, instead of manually copying them one by one, you could ask:

"How do I extract only the email addresses from a column that contains both names and emails?"

ChatGPT might suggest using a formula with FILTERXML:

=FILTERXML("<t><s>" & SUBSTITUTE(A1:A100, " ", "</s><s>") & "</s></t>", "//s[contains(., '@')]")

Or, it might even recommend a Power Query method for better scalability.

Example #9. Directly manipulating Excel files with ChatGPT

One of ChatGPT’s most powerful (and lesser-known) abilities that we mentioned earlier, is directly working with Excel files you upload, allowing you to make precise changes without manually editing your spreadsheets.

So whether you’re cleaning messy datasets, automating tedious formatting tasks, or adding calculated columns, ChatGPT streamlines the process and saves valuable time.

For example:

Imagine you have an Excel file with hundreds of sales transactions, but the dates are in an inconsistent format, such as a mix of "MM/DD/YYYY," "DD-MM-YYYY," and even textual dates like "January 2, 2024."

Standardizing these dates manually would be tedious and error-prone. Instead, you could simply upload your Excel file to ChatGPT and ask:

“Please standardize the 'Sale Date' column in my Excel file so all dates follow the 'YYYY-MM-DD' format.”

ChatGPT would directly analyze your data, automatically detect and interpret the various date formats, and standardize all entries consistently into your requested format. You’d get a clean, uniform 'Sale Date' column without having to write complex formulas or macros yourself.

Beyond date formatting, you could also request ChatGPT to:

• Quickly remove duplicate records
• Split names into separate columns (First Name, Last Name)
• Add calculated fields, such as a "Profit Margin %" or a custom status indicator based on multiple conditions

And that’s just for starters!

This capability empowers even Excel novices to efficiently manage and clean datasets, eliminating hours of manual editing and significantly reducing human error.

Example #10. Analyzing Excel files and extracting insights

When faced with large datasets, identifying trends or summarizing information can be overwhelming for anyone, even for seasoned Excel users. Thankfully, ChatGPT can quickly analyze your uploaded Excel files and uncover actionable insights, saving you time and effort.

For example:

Imagine you have an Excel file containing monthly sales data across multiple product categories and regions, and you’re preparing for a quarterly business review and need quick, high-level insights to include in your presentation.

Instead of manually creating pivot tables and charts, you could simply upload your Excel file to ChatGPT and ask:

“Can you summarize the key trends in my sales data and highlight the top-performing regions and product categories?”

ChatGPT would promptly analyze the dataset and respond with a clear summary such as:

“Your data shows a 12% increase in total sales this quarter compared to last quarter. Electronics was your highest-performing category, with sales growing by 25%. The North America region contributed most significantly to growth, while sales in Europe remained flat. Additionally, seasonal trends suggest heightened demand for Home Appliances during Q4.”

Even better? ChatGPT can even generate helpful visualizations to complement its analysis as well!

Give this a try yourself!

So as you can see, ChatGPT isn’t just a tool for answering questions, but a way to work smarter in Excel. Whether you need help with formulas, automation, data analysis, or reports, it can save time and simplify complex tasks.

The best way to see its value? Try it out for yourself. Open ChatGPT, describe what you need, and see how it transforms your workflow.

With a little experimentation, you’ll start to see how AI can take the frustration out of spreadsheets and make Excel work for you.

P.S.

And remember, if you want a deeper dive into this, be sure to check out my brand new course on how to use ChatGPT for Excel:

Better still?

If you become a ZTM member, you’ll get access to this course as well as all the other Excel, Power BI, and Data Analysis courses of mine. Heck, you also get access to every other course on the platform so it’s a no brainer not to join!

Not only that, but you’ll also be able to join our private Discord community and ask questions from myself, other students and other working Data professionals, so you’ll never be far from a helping hand!