The Comprehensive Guide to ChatGPT and Excel

ChatGPT and Excel are better together! Learn how to leverage the powerful AI tool to create better Excel formulas and boost productivity by integrating ChatGPT directly into Excel with cutting edge plugins. Microsoft will soon be integrating ChatGPT into Excel, and we’ll explain why you should be on the lookout for Microsoft Copilot 365.

We’re super excited to see all of the great ways that ChatGPT and Excel.

Let’s dive in!

How to Use ChatGPT for Excel Formulas

With over 450 formulas available in Microsoft Excel, even advanced Excel users can struggle to find the right Excel Formula.

Luckily, ChatGPT can write Excel Formulas for you, and explain the how and why they are working.

Example #1. Asking ChatGPT to generate a formula to calculate the days between a date and the end of the current month.

ChatGPT generating Excel formulas based on a question of how to calculate the difference between two dates

ChatGPT provides a concise answer that you can use the EOMONTH and TODAY functions.

By providing a specific cell reference of column and row, ChatGPT will include the exact formula into its response making it easier for the user to copy and paste the result into their own Excel spreadsheet.

Example #2. Asking ChatGPT to write an Excel formula to count all of the cells in column B that start with letter G

ChatGPT writing an excel formula as an example use case of a wildcard countif

ChatGPT correctly provides an explanation of using a wildcard after the letter G to return the count of columns that start with the letter.

Tips For Working with ChatGPT and Excel Formulas

These are some of our favorite tips and tricks when using ChatGPT to help you write Excel Formulas. We hope they save you as much time as they save us!

  • Start a fresh ChatGPT session with the phrase “In this session, provide brief answers” Because ChatGPT can provide needlessly long responses when you really only want the formula, give it this instruction first. We used it in the responses above and it makes ChatGPT much faster.
  • For even more concise answers ask ChatGPT to provide Excel Formulas Only.
  • Ask ChatGPT for alternative Formulas. If ChatGPT provides you with an Excel formula that looks complicated, prompt it to provide you with an easier formula. There are many ways to use formulas in Excel and ChatGPT will recommend alternatives.
  • Tell ChatGPT if the Excel Formula doesn’t work. Sometimes the AI chat bot misses the mark. Tell it that it doesn’t work or copy and paste an error message into it and ChatGPT will provide an alternative formula.

Use ChatGPT to Code VBA and Create Macros

ChatGPT can write VBA code, making it one of the best tools available to help you automate Excel processes.

VBA stands for Visual Basic for Applications. It’s the programming language Excel uses to create macros.

By describing the process you want to automate in Excel, ChatGPT will create a script on your behalf that you can copy and paste into the VBA editor. (You can launch the editor by pressing Alt + F11 on Windows)

Example: Prompting ChatGPT to write a macro that will automatically refresh all pivot tables in an Excel workbook upon opening.

ChatGPT generating VBA for an Excel Macro to refresh all pivot tables in an Excel workbook

ChatGPT automatically generates the script, and if you click the “Copy code” button at the top right of the response it will copy to the clipboard in a simple format that is even more easily copied.

Tips For Working with ChatGPT for VBA and Macros

  • Provide as many details as possible. – The macros that ChatGPT creates for you will only be as good as the description. Define the desired macro name, the table range, the specific worksheet or if you want it to apply the macro to all worksheets in a workbook etc. Knowing what you want and describing it may be one of the most difficult parts of working with ChatGPT and Macros.
  • Embrace Trial and Error. – Don’t be afraid to copy and paste bad code! The latest version of ChatGPT-4 is a gigantic improvement over previous versions, but it still makes mistakes. After you run the ChatGPT generated code in Excel, copy and paste any error messages that you receive back into ChatGPT and it will re-write the code.
  • Use ChatGPT to Fix and Explain Existing Macros – We love using Excel macros but it can be frustrating when the person who built it leaves the company and takes the knowledge with them. You can copy and paste old Macros into ChatGPT can fix broken VBA. The AI VBA tool can also help you break the code down into smaller more easily explained sections.
  • Break Macros Into Smaller Pieces – It’s easier to describe smaller parts of a task for ChatGPT to create VBA for than it is to accurately describe a long multi-step process in detail. By splitting the job into multiple sections it will make it easier for you to describe and ChatGPT to write.
  • Ask ChatGPT to Add Comments to Explain the VBA Code – ChatGPT is capable of writing VBA with notes embedded into the code. Ask ChatGPT to add comments explaining each section and future VBA programmers will thank you if they have to update it in the future.

We hope you have a chance to generate some Macros using ChatGPT, it’s a great way to automate Excel automation!

Integrating ChatGPT directly into Excel and having it answer questions and complete tasks for you is the next level in Excel Automation.

Microsoft is hard at work creating the future of Microsoft Excel with direct AI Integration. Microsoft calls their version of OpenAI’s ChatGPT in Excel, Microsoft Copilot.

Analyzing Excel Data with ChatGPT

ChatGPT can help Excel users analyze their data in a number of ways, but there are some limitations.

Let’s take a look at some use cases and dive into the limitations.

Use Case #1 Using ChatGPT for Text Analysis and Category Groupings.

We copied 200 Google Search terms related to the website that you are reading right now, and asked ChatGPT to categorize them into broad categories.

The search terms range from non-sensical sentences, to brief queries that you would put into a search engine for a broad range of related topics. Putting the data into ChatGPT makes data categorization much easier than trying to determine related keywords in an Excel Pivot Table

Using ChatGPT for Data Analysis with Excel.  ChatGPT is analyzing large amounts of text to group it similar to a word cloud or normalization

We have also used ChatGPT to help us categorize expense reports, credit card transactions, and receipts that have been submitted for expense reimbursement. It’s not 100% accurate but it eliminates 80% of the work and lets human users adjust from a nice starting point.

Use Case #2 Using ChatGPT for Numeric Analysis of Sales

In this use case, we copied and pasted the following Excel spreadsheet into ChatGPT and asked, Which Menu Item has the highest sales?

Example of an Excel Table being loaded into ChatGPT

The answer we were looking for is $1,050 from the $500 Burger sales on 5/1/2023 and $550 on 5/2/2023

ChatGPT responded with $550 which would have been correct if we were looking for the single max value in the Menu Item Column.

Screenshot showing ChatGPT limitation of performing calculations for data analysis

After asking a follow-up question to aggregate all of the sales data ChatGPT created a table of the sales totals by category and sorted it descending.

A follow-up prompt in ChatGPT that shows the correct outcome of a calculation of an Excel data table

This use case highlights a couple of really interesting things when using ChatGPT as a data analysis tool.

  • Be Very Specific with Prompts
  • Check Your Work
  • Even a Small Dataset is Slow to Process

Based on these findings, we would absolutely use ChatGPT as a tool for text analysis. It’s faster and easier than running data through a word cloud or sentiment analysis model and can provide high level insights.

You can use it to normalize lists of words where names and addresses could be similar but not quite the same or extract substrings from text.

However, if you have to double check numeric calculations every time you run them through ChatGPT for analysis, we have to wonder if it is that much of a time saver?

Note: The first time we ran this test, ChatGPT errored out after taking a couple of minutes to attempt the initial calculation. We are using ChatGPT Plus which provides users with priority access and occurs very rarely.

Uploading Excel Files to ChatGPT Advanced Data Analysis

OpenAI recently re-branded ChatGPT Code Interpreter to Advanced Danta Analysis. It’s available with a paid ChatGPT Plus subscription and allows users to upload Excel, or CSV files for analysis and is also capable of generating output files that you can download after transformations have been performed.

ChatGPT Advanced Data Analysis available with ChatGPT Plus can be used to Analyze Excel Data.

ChatGPT Advanced Data Analysis uses the Python Programming Language to make adjustments to the formatting of Excel files, and to analyze the data inside of the excel sheet. While not all python libraries are available at this time, it supports two of the biggest excel analytics packages, Numpy and Pandas.

To upload a file to ChatGPT Advanced Data Analysis, click on the + button in the Send a Message text box. A window appears to upload an Excel file into ChatGPT.

Screenshot of ChatGPT ability to upload an Excel File.

Once the file is uploaded, you can prompt ChatGPT to perform a number of operations and ask questions about your data.

While performing calculations related to an uploaded Excel file, a box will appear that says that it’s calculating. Upon completion the status will update to Finished working. You can click on the Show work button to expand it and see details of which Python commands are being executed to perform the data analysis.

Example of a .csv spreadsheet uploaded to ChatGPT and ChatGPT Advanced Data Analysis answering questions about it.

ChatGPT has improved to the point where it will begin to ask follow-up questions when a prompt is vague. The spreadsheet we uploaded into the example had sales quantity and sales dollar data. We were prompted to clarify our request. Upon clarification, ChatGPT moves forward with performing analysis on the spreadsheet data.

If you have heard of ChatGPT Code Interpreter, ChatGPT Advanced Data Analysis is a re-named version of the already impressive software.

Tip: After completing the analysis, you can ask ChatGPT to export a file to .xlsx, .csv and a number of other file formats. It will generate a file and allow you to download the completed dataset.

There are some additional interesting use cases for advanced analysis such as converting PDF files into Excel spreadsheets or word documents that can be further calculated and manipulated.

Alternatives to Importing Excel Files into ChatGPT

What ChatGPT Advanced Data analysis requires a paid ChatGPT plus subscription, there are a number of ways that you can bring Excel or CSV files into ChatGPT for free.

  • Copy and Paste Excel Spreadsheets into ChatGPT – This is not the most elegant solution, but it’s fast and easy. The biggest challenge is that ChatGPT will lose some of the data structure that comes from having your data separated into rows and columns.
  • Wait for a ChatGPT Plugin – More and more ChatGPT plugins appear every day. We wouldn’t be surprised if something rises to fill this gap. There are already a number of plugins that will convert PDF to text and feed it into ChatGPT. Likely an Excel uploader isn’t far behind.
  • Use the ChatGPT File Loader Chrome Extension We have not used this tool, but it looks interesting enough to mention. ChatGPT File Uploader Extended is a Google Chrome extension that lets you load files into ChatGPT.

Note: Be careful when utilizing third party tools because you don’t know where the data is being sent prior to it going into ChatGPT for analysis.

Be Aware! – ChatGPT is Bad at Math

One of the biggest limitations of using ChatGPT as a tool to analyze data is that it’s bad at math.

It’s a well-documented problem and we have ran into it before when asking ChatGPT to generate datasets for us. OpenAI is aware of the issue and it’s on their list of things to improve in the AI chatbot.

The basic reason why: ChatGPT was designed as an AI and Machine Learning powered large language model. It’s designers fed the AI absolutely massive amounts of text and written information. It uses pattern recognition and reinforcement learning to re-creating sentences and paragraphs. While computers are great at math, ChatGPT was never trained to be.

Sam Altman the founder of OpenAI has openly discussed ChatGPT’s limitations in a fascinating interview with Lex Fridman where they discuss ChatGPT’s math challenges along with some of the other nuances of developing ChatGPT and how he expects it to evolve over time.

ChatGPT is hit or miss on whether it provides accurate calculations. Luckily there are new features coming available that allow you to export data to a .csv from ChatGPT.

How to Export Data from ChatGPT to Excel

Data can be exported from ChatGPT to an Excel file using ChatGPT Advanced Data Analysis functions if you have a subscription to ChatGPT Plus. After ChatGPT answers a question, you can ask it to export a file to a .xlsx or a .csv file and it will generate a version that can be downloaded.

Exporting results from ChatGPT to an Excel .xlsx file

An added benefit of the way that OpenAI implemented the ability to export to Excel files is that ChatGPT can generate larger datasets than what it has the capability to show you on screen.

In instances of large data sets, ChatGPT will present you with a preview of the first ten rows of a data set, and then ask you if you want to generate a full file. The amount of compute power that ChatGPT allocates to the request appears to be improving over time. We have been able to prompt ChatGPT to generate Excel files of 100,000 rows.

There is no specific file size limit on what ChatGPT can generate, but it will only allocate up to 60 seconds to the creation of a dataset, meaning that the limit is on compute time more than it is on the ability to handle big data sets.

ChatGPT Excel Plugins

There are a number of third party Excel plugins that have been published to directly integrate ChatGPT with Microsoft Excel. These solutions are a great way to add ChatGPT to Excel.

These add-ons boost productivity by letting users generate formulas and complete simple tasks.

These are some of the most promising ones from around the internet.

  • Numerous.ai – Numerous.ai rose to popularity after going viral on Reddit. The plugin is avialable for Excel and Google Sheets. It lets users extract data, categorize data, and generate excel formulas from inside your spreadsheet using ChatGPT.
  • Excel Labs from Microsoft Garage – This is pet project of the Excel team at Microsoft. They created a custom function called LABS.GENERATIVEAI that lets Excel users send data to ChatGPT and receive a response using a regular Excel formula.
  • ChatGPT for Excel by Apps Do Wonders – Is another Excel add-on that lets you send information from Excel to ChatGPT.

You can download plugins directly from the solution providers website, or you can find Microsoft Excel Add-ins.

The Microsoft Office Add-ins store is available directly from Excel. Go to Insert > Get Add-Ins and you can search for many of the other AI powered Excel add-ins currently available at a range of difference price points.

Example of how to add ChatGPT Plugins into Excel for direct integration.  Ask questions directly from Excel to ChatGPT

Note: Because of the third-party nature of these Excel plugins, we urge caution when using them to process sensitive business data due to potential cyber security concerns.

Keep an eye on this space as the best plugins ChatGPT for Excel plugins will likely improve rapidly over the coming months!

Direct ChatGPT Integration is Coming Excel to from Microsoft

In May of 2023, Microsoft announced Copilot 365. Copilot is the brand name of integrating a ChatGPT style natural language interface across the entire Microsoft Office Suite of applications. Direct integration of ChatGPT is coming soon to Excel.

They have since announced the subscription price and have updated the back-end of Excel to support the integration.

The video below was published by Microsoft, highlighting their vision of the many features that will eventually make their way into Excel. Some of the areas that we are most excited about is the ability to ask questions about our data, and create data visualizations on command.

If Microsoft is able to bring their vision of ChatGPT and Excel integration to life it could revolutionize office productivity for the hundreds of millions of active Excel users.

What Will Microsoft Copilot Cost?

Microsoft announced pricing of $30 USD per user per month for commercial customers. Pricing has not been announced at this time for non-commercial customers or retail SKUs of Microsoft Copilot features. The exact details of which SKUs the pricing applies to is as follows:

Microsoft 365 Copilot will be available for commercial customers for USD30 per user per month for Microsoft 365 E3, E5, Business Standard, and Business Premium customers when broadly available.

Introducing Bing Chat Enterprise, Microsoft 365 Copilot pricing, and Microsoft Sales Copilot | Microsoft 365 Blog

The way that Microsoft has made the announcement makes us wonder if commercial customers will be prioritized in releasing ChatGPT integration into the office applications. There is a known limitation of cloud compute power available to support the demand for natural language processing. Microsoft may not have the resources available to launch a wide range of AI features across all Microsoft Office users globally.

Microsoft Excel Adds Python Support

A recent change to Microsoft Excel was announced that many people may have missed. Microsoft is enabling Excel to run Python scripts. Python is the world’s most widely used programming language, and is also the programming language that ChatGPT uses for Advanced Data Analysis to analyze and make changes to uploaded Excel files.

Microsoft may be planning to allow Copilot to make changes to Excel files using Python. It’s a highly efficient programming language, and with its wide user base there is a lot of data that Microsoft can use to train AI models and enable users to perform more operations than they otherwise would be able to.

Python in Excel – Python to Excel | Microsoft 365

Microsoft Data Wrangler is Excel Power Query for Python

Coming out of the Machine Learning division of Microsoft is a product called Microsoft Data Wrangler. The interface looks very similar to Excel Power Query but generates Python code instead of M behind the scenes. It’s likely that Microsoft may be leaning into Python as a back-end to perform more tasks using AI.

The proliferation of Python across the Microsoft ecosystem would enable them to have one set of AI tools that are highly trained on performing tasks in a single programming language. It would be easier to scale and deploy across products while using a single pre-trained AI model.

If Microsoft does end up integrating ChatGPT style features with Power Query, it would be a very powerful data analysis and data transformation platform.

Continued Model Improvement and Model Fusion

The rate at which AI has progressed in a short period of time has been astonishing.

For reference, check out this video from 2017 that summarizes state of the art AI models at the time. They look ancient compared to the Large Language Models that we use today like ChatGPT or Google Bard.

AI models will continue to improve, and they will improve at an ever increasing rate. There may be a terminal level where they can only advance up to a certain point until margin gains are made but only time will tell.

ChatGPT’s Current Math Limitations

Being unable to reliably perform math using ChatGPT is a big drawback of using it as an analytics tool and limits some of the applications with ChatGPT and Excel.

We expect that OpenAI will either find a way to improve their AI model, or simply adjust it so that it knows when to invoke a different model or technique to complete computations efficiently and accurately.

This is already becoming a feature in early stages as presented with ChatGPt plugins and Code Interpreter. Ideally the end user will have no idea which model is being used and they can ask a question to receive an accurate answer.

ChatGPT Quick Reference Websites and Services

A number of websites and services have recently launched to help users create Excel formulas faster and more efficiently. They do not necessarily integrate directly into Excel as a plugin or add-on, but they’re designed to give users a low-cost option to ask ChatGPT or other AI models questions without having to pay $20 USD per month to access ChatGPT Plus.

These two solutions are relatively inexpensive and are setup to provide users with the ability to generate formulas and VBA in a quick concise manner. If you are on a strict budget, you can use the free version of ChatGPT from OpenAI or Google Bard, Google’s AI assistant as an alternative.

Our recommendation: Instead of specialized AI applications, we would pay $20 per month to access ChatGPT Plus. It’s a premium tier from the creators of ChatGPT, OpenAI.

We like it because it gives you priority access to OpenAI’s most advanced AI models. ChatGPT Plus also gives users access to ChatGPT plugins which lets ChatGPT interact with 3rd party software, provides up to date information by querying Microsoft Bing and will soon let people generate even more advanced automations utilizing Code Interpreter.

How to Automate Processes with Zapier, Excel, and ChatGPT

One of the more interesting use cases we found with ChatGPT was sending a list of questions from an Excel spreadsheet to ChatGPT. This can be a time consuming task of copying and pasting questions and responses.

Luckily Zapier has been adding tons of AI powered features. Zapier is a low-code process automation software that’s very similar to Microsoft Power Automate Flows. It has hundreds of integrations and automation options available which range from Quickbooks Online to Gmail and DocuSign.

Think of it as a way to drag and drop steps like:

Step 1.) Connect to ChatGPT Step 2.) Send it a question Step 3.) Save Response to Excel next to the Question

If you need to load large amounts of spreadsheet data into ChatGPT for responses, it may be beneficial to utilize a low-code automation platform. You don’t need to know how to setup an API connection other than adding a credit card to OpenAI and copying and pasting an access token.

Learn how to integrate ChatGPT and Excel using Zapier

Use Case Example: You can setup Zapier to take an e-mail received from your boss, send it to ChatGPT to write a response, and then automate sending an e-mail back to your boss while you’re away fishing.

Enterprise Grade ChatGPT and Private Sandboxes

Microsoft knows that Fortune 100 companies will not use ChatGPT and their Azure OpenAI Cognitive services unless these companies can keep their data separate from the public and control access at a very granular level even within their own organization.

At Build 2023 Microsoft Announced Microsoft Fabric

Fabric is the name given to Microsoft’s suite of data integration tools that will have Copilot capabilities built into them. We expect that this is laying the groundwork for companies to consolidate more data into one place, connect it and give users the ability to ask questions about their data.

This will be revolutionary if users can not only ask about their spreadsheet data, but access files saved on SharePoint, OneDrive or in various cloud data warehouses on premises locations.

Screenshot of Microsoft Fabric. Microsoft solution for data integration that will become the basis for data analysis and allow users to ask questions about excel data or any other data source

AI Will Evolve from Answering Questions to Completing Tasks

With the recent release of ChatGPT Advanced Avanced Data Analysis, please can upload Excel, CSV, or even PDF files and ask questions about their data. An added benefit is that you can ask ChatGPT to perform additional operations, such as remove blank rows, create a summary table etc. Some features work better than others but will improve over time.

In its current form, it can be quicker to perform actions on spreadsheets yourself than it is to upload it to ChatGPT and hope that the data transformations are correct. Using AI will become easier and more efficient when it’s embedded directly into Microsoft Office with their upcoming Copilot features.

The more users that take advantage of the service will allow Microsoft to collect more data to further train the model and improve it with time.

As an interim step, we see tools like Excel and Power BI suggesting possible charts and graphs based on text input and allowing the user to select the one that they like.

This solution has two benefits.

  1. Users often don’t know what they want until they see it.
  2. Providing options lets Microsoft train the AI model and provides telemetry data to know when an AI suggestion was accurate which will improve the model for all future users.

Conclusion

Microsoft continues to bring new features to Microsoft Copilot, their brand name given to direct integration of ChatGPT style features into Microsoft Office. They’ve been rapidly improving the back-end of Microsoft Excel, likely with the idea of enabling more AI features and functions.

If Microsoft Copilot can fulfill its promise then data analysis and Excel spreadsheets will be revolutionized.

Users will not only be able to complete analysis faster, but it may mean that analysts will be able to perform tasks that IT historically had to be involved in such as data integration, connecting systems together, or allowing novice Excel users to write complex SQL queries.

ChatGPT will truly be a force multiplier and will be fun to see how it progresses!

Scroll to Top