How To Use ChatGPT with Excel

Artificial intelligence (AI) technology advancements have improved efficiency in different work environments. This has dramatically impacted productivity and economic growth through increased profits and reduced prices of business products and services.

ChatGPT, an NLP (Natural Language Processing) tool from OpenAI, is the most widely used AI in various businesses. It mainly enhances productivity primarily through process automation, and one of the areas in which it’s frequently used is Microsoft Excel through ChatGPT Excel plugins and ChatGPT-based websites. However, before AI, users could use Microsoft VBA and Macros to increase productivity in Excel in ways almost similar to how AI works.

Microsoft VBA and Macros

Launched in 1985, Microsoft Excel has been around for a while and has seen productivity enhancements over the years through Microsoft VBA (Visual Basic for Applications) and Macros. Microsoft VBA is a programming language used in Microsoft Office Products to write commands called Macros. Macros allow users to create custom user-designed functions (UDFs) and automate repetitive tasks from the basic Microsoft functions. For Excel, users can also create custom Excel add-ins using VBA.

The only significant disadvantage of Microsoft VBA and Macros is that commands can be challenging to write and execute, especially for users unfamiliar with programming. Additionally, the chances of errors are high, and they have limited functionality and compatibility issues. Luckily, you can use ChatGPT to write and execute Macros.

Microsoft BingAI and 365 Copilot

Microsoft’s partnership with Open AI began when they invested $1 billion in OpenAI in July 2019, announcing a multi-year exclusive computing partnership that made Microsoft the cloud computing services provider to OpenAI. The partnership also included building new AI technologies and expanding OpenAI’s AI capabilities. Microsoft Azure AI was among the first products of the partnership unveiled in 2021 that gave developers and data scientists access to Open AI’s ChatGPT3. Reports state that Microsoft further invested $2 billion in OpenAI between 2019 and 2023 before extending their partnership, injecting $13 billion in January 2023.

Satya Nadella, Microsoft’s CEO, said that Microsoft formed a partnership with OpenAI around a shared ambition to responsibly advance cutting-edge AI research and democratize AI as a new technology platform. He added that in the next phase of their partnership with OpenAI, developers and organizations across industries will have access to the best AI infrastructure, models, and toolchains with Azure to build and run their applications. Microsoft then unveiled the BingAI chatbot in February 2023 and the 365 Copilot a month later in mid-March.

Microsoft’s BingAI is a chatbot based on OpenAI’s GPT4 built into Microsoft Edge, enabling users to get more precise and useful search results. It’s similar to ChatGPT but doesn’t have a paywall behind it, meaning it’s accessible to all Microsoft Edge users. Developers have also made it possible to integrate Bing AI into other browsers, such as Chrome and other Chromium Browsers.

Excel users can use BingAI to generate ChatGPT Excel formulas, write Excel Macros, and ask Excel-related queries among applications. Additionally, BingAI provides a chat history that you can refer to when working on new projects that require similar answers to previous projects you worked on. In May, Microsoft announced that they’d add an export and share functionalities to BingAI, enabling users to share their chats with others. This will allow sharing of new ideas and solutions with others in a work team or interested parties, thus increasing productivity and improving teamwork. Other features that might be coming to BingAI include summarization and page context-generated answers that might be useful when using Microsoft Excel online.

365 Copilot is Microsoft’s GPT-4-based AI productivity tool integrated into Microsoft Graph and Office 365 applications, including Excel. But before it, there was the Microsoft Office intelligent assistant, an interactive animated character dubbed Clippy, available in Office versions released from 1997 to 2003. Clippy was used to respond to general Microsoft Office questions and perform basic tasks. Microsoft also made other animated assistant characters such as the Dot, Links the Cat, Rocky the Dog, Genius, Mother Nature, and an Office Logo that users could click if they needed help. Microsoft revived the Clippy assistant in 2021 for Microsoft 365. However, the nostalgic happiness was short-lived as they retired it again after a few days.

Most users who used Clippy when it was relatively new in the late 90s and early 2000s remember it annoying. Is 365 Copilot the same? Like BingAI, 365 Copilot is integrated into Office 365 apps as a chatbot that generates and edits sheets, slides, and documents, analyzes data, and answers questions from the user’s prompts. It also explains the workings and sources from which the answers are derived. This makes it easy to use, even for beginners, and it’s not annoying, unlike Clippy. The only significant disadvantage is that it will not be accessible to everyone when fully rolled out since Office 365 apps have a paywall.

Despite being relatively new, Microsoft has equipped 365 Copilot with some of the best Excel productivity features. You can create new spreadsheets with customized rows, columns, and cells, reducing time spent manually or searching for spreadsheet templates. 365 Copilot can also generate Excel formulas from the chat box and apply them to data sets without manipulating the spreadsheet’s values. This allows the user to test different formulas to check for outcomes without editing the spreadsheet.

Time spent analyzing results and data sets to find trends is cut short with 365 Copilot, as it can do so in seconds. Users can also prompt it to generate a follow-up spreadsheet depending on the trends, thus enabling them to see what needs to be changed on the spreadsheet. This is handy for businesses as it gives insights and provides suitable actions and results for each action on a different sample spreadsheet. Microsoft has made it possible to use natural language prompts with 365 Copilot making it easy to use across all industries. To access 365 Copilot in Excel, click the Copilot button on the right-hand side underneath the share button.

Uses of ChatGPT In Excel

When OpenAI launched ChatGPT in November 2022, it was received with mixed reactions and speculations that AI will soon replace humans in the creativity and productivity sectors. This is due to its ability to answer questions precisely, challenge incorrect premises and reject inappropriate prompts. For example, it gives an “I’m sorry, but I don’t have enough information to answer that question” answer if the prompt is unanswerable or inappropriate. Masses were also worried about its ability to generate dialogues and follow-up conversations from natural language prompts, making it human-like. Yet, the worry cries soon died after people discovered what ChatGPT could do.

Using ChatGPT, you can quickly write and troubleshoot Microsoft Excel formulas and ask Excel-related queries, among other uses we will expound on. One way you could do so is by creating an OpenAI account that gives you ChatGPT access. However, with a $20 paywall implemented in February, you can lean towards other ChatGPT-based websites such as Chat.forefront.ai and Hugging Face. Furthermore, developers are making it possible to integrate ChatGPT and Microsoft Excel using ChatGPT Excel Plugins. But before we dive into that, how can you use ChatGPT with Excel?

Writing, Implementing, And Troubleshooting Excel Formulas

Navigating Excel has never been as easy as it seems, especially for newbies, even when writing the most straightforward addition, mean, subtraction, or division formulas. However, remember you were not always a guru before making fun of an Excel newbie.

How to use ChatGPT to write Excel Formulas

Basic Excel formulas are easy to write, with most requiring the user to type an equals and equation sign between the constants (values in a cell) in the cell where they want to output the result. The shorter method is to select the cells with the constants instead of typing them in a technique acknowledged as cell referencing.

Microsoft Excel has formulas alphabetically arranged in the function library on the ribbon. But sometimes, scrolling to find one can be a pain in the neck, especially when working long hours on a spreadsheet. Testing and embracing new productivity tools such as ChatGPT is also good.

You can write or generate Excel formulas using ChatGPT in two ways. The first, and probably the plainest way, is to prompt ChatGPT to generate results from constants in an equation, all clearly included in the prompt. Here’s a prompt input example from a spreadsheet containing funds payable by different corporates, received amounts, disallowed, and outstanding payments. We wanted to calculate the amount owed by corporate EKL and the received amount from corporate ASL.

Sample spreadsheet

ChatGPT prompt based on the spreadsheet’s values

Given that we didn’t include column lettering and row numbers, ChatGPT generated Excel formulas without these, and this is what we got:

Another way you could use ChatGPT to write Excel formulas is by prompting it with a detailed description of what you want the result to be. This way, it translates natural language queries into formulas. From the same Excel spreadsheet used earlier, we asked ChatGPT to write an Excel formula to find the amount owed by corporate EKL.

Here’s the Excel formula generated by ChatGPT:

How To Use ChatGPT To Implement Excel Formulas

You can copy and paste the results into the required cells, which most would do. But you can also copy the formula into the formula bar and apply it to other cells requiring the same formula. First, start by writing the formula as it is on ChatGPT in the formula bar and selecting the columns the values are in after the equation signs. This is what we get for the outstanding amount owed by corporate EKL. Press ‘enter’ after writing the formula to get the answer. If you used the second method to generate the formula, copy it from the ChatGPT using the “copy code” function and paste it directly into the formula bar.

To Implement the same formula in other cells, select the cell with the formula, copy (Ctrl C) the result, and paste (Ctrl V) it into another cell. An easier way is to drag the cell via the small square on the right bottom edge, but this only works if the cells are directly below the cell where you first used the formula

Copy and Paste Method:

Scroll Down Method:

How To Troubleshoot Excel Formulas Using ChatGPT

What if you are unsure of the results generated from the ChatGPT Excel formulas or some of your own? ChatGPT can also troubleshoot formulas and check for errors in formulas and spreadsheets. Sometimes, wrong formulas in Excel will always result in a “#NUM!” error. In the prompt, you must include what you are working on, the cell constants (values in the cell), and the formula used when using ChatGPT to troubleshoot formulas. Or, ask ChatGPT if the formula used is written correctly, after which it will correct it then you can implement it in Excel.

Here’s an example from the spreadsheet we used earlier:

ChatGPT Prompt and response:

Explaining Excel Formulas and Functions

When working on a spreadsheet and you’re unsure of which formula to use, ChatGPT can explain Excel formulas, provide circumstances in which the formulas can be used and give an example of the formula in use. Here’s an example of ChatGPT demonstrating a mod formula in Excel:

ChatGPT can also explain Microsoft Excel Functions, but before getting to that, what is the difference between Excel formulas and functions? Excel formulas are instructions created by the user that perform calculations, while functions are pre-built formulas in Excel that also perform calculations. For instance, when finding modulus, you can type “=MOD(number, divisor),” which is the required formula, or get the mod function from the function library. Not all formulas have built-in functions, especially if you are using older Microsoft Office versions. Here are some examples of ChatGPT explaining the XLOOKUP function and how to use it:

Creating and Implementing Excel Macros

Professional Microsoft Excel users often use Macros for numerous purposes, with the most common uses being process automation and creating repetitive tasks. Beginners often find it challenging to write the commands, and this is where ChatGPT enters the conversation as you can use it to write Macros commands and implement them. However, you first have to learn how to launch the VBA editor in which to write the commands.

How to Launch VBA Editor

First, you have to enable developer mode using the following steps

1. Click the file tab
2. Select “Options” at the bottom left corner of the document
3. On the navigation panel, select “Customize Ribbon”
4. Enable developer mode by ticking the “Developer” option box and clicking OK. This will add a Developer tab on the ribbon.
5. Open the Developer tab on the ribbon, giving you access to the VBA editor on the top left-hand side. A new window should open upon clicking the VBA editor.
6. Right-click “this workbook” and select “insert module,” which will open a blank page where you write Macros commands.
7. Once developer mode is enabled, you can launch the VBA editor by pressing Alt and F11 keys.

How to generate and Implement Excel Macros Using ChatGPT

Using ChatGPT to generate Macros is just like using it to write Excel Formulas. But you have to be more careful and precise when writing the prompt. The best way to do it is to write everything, perhaps in a notebook or Word document, about the task at hand, including every little detail, especially if you’re working on a complex task. After that, write a well-composed prompt in ChatGPT’s chat box and crosscheck before hitting the send button. 

Using the same spreadsheet used earlier with some changes, here is an example of a ChatGPT-generated Excel Macro:

Excel spreadsheet before Macro implementation

We asked ChatGPT to generate a simple Excel Macro to color cells with negatives in red:

Excel Macro code generated by ChatGPT

To implement the generated Macro code, follow the following steps:

1. Copy the code from ChatGPT
2. Click on “Visual Basic,” which opens the VBA editor window
3. Right-click on “this workbook”> insert> module
4. Paste the Macro code into the module and close it

5. Click on “Macros” next to “Visual Basic”

6. Select the Macro under the Macro name list, then click “Run.”

Resulting spreadsheet changes from the implemented Macro:

Pressing Alt plus F8 keys launches a Macro dialogue box that you can use to debug (edit) Macros, create custom keyboard shortcuts, write macro descriptions, and delete macros if not used. You can also use it to change where the Macro applied reflects by applying it to all open workbooks, a workbook you’re working on, or a saved workbook. To keep the Macro Saved in Excel, save the workbook as an Excel Macro-Enabled Workbook. (insert recording)

How to create repetitive tasks in Excel using ChatGPT

It’s time-consuming having to write formulas and apply functions in Excel, and it’s not every time that you can copy and paste or drag the fill handle. ChatGPT is handy in such cases as it can generate Excel Macros from whatever you want to do, provided you describe it plainly and directly in the prompt. For example, we asked ChatGPT to write an Excel Macro to implement the formula E3=B3-C3-D3 in column E, skipping one cell after every two cells. Note that we didn’t specify on which cell the Macro starts.

Debugging ChatGPT Excel Macros

To debug the Macro, you can go back to ChatGPT and edit the prompt as we did:

Then open the Macro dialogue box and select the Macro to edit, which will open the VBA editor. Delete the entire Macro and paste the new Macro from ChatGPT. If you’re worried about messing up the Macro, it’s better to delete the old one and create a new module and paste the new Macro.

How to Upload Excel Spreadsheets To ChatGPT

To make working on a spreadsheet easier with ChatGPT, you can either copy and paste the spreadsheet into the prompt chat box or upload the workbook to ChatGPT. Copy-pasting is more straightforward, but sometimes the values in the cells go out of alignment and end up in different rows and columns. Trying to fix them is where the hard part is since you might press “enter,” after which ChatGPT will start generating answers. Users also forget to write the prompt before or after pasting and editing the spreadsheet’s values.

Document-to-text file converters reduce the risk of errors by making spreadsheets easy to paste without editing; most are free and uncomplicated to use. Here’s an example of an online text-to-file converter (document.online-convert.com) that we used to convert our spreadsheet into text.

After downloading the converted Excel file, you can use any Word software to open it; we used Notepad. Then copy the text and paste it into ChatGPT’s chat box. You should move around the values a bit after pasting, depending on your spreadsheet’s values. Do this by using the spacebar and backspace keys.

For now, there’s no way of uploading Excel spreadsheets directly to the OpenAI ChatGPT user interface. Fortunately, developers are making it possible through third-party websites that run GPT. The only downside, you can only use such websites to analyze and visualize Excel spreadsheets.

ChatDF is among the most frequently used analysis productivity tool across different industries and the only ChatGPT-based website we’ve tested for spreadsheet visualization and analysis. Its simple layout makes it easy to use, and it has a free subscription option which you don’t come across often with such tools. But before using it to analyze Excel spreadsheets, you must first convert the Excel file into a PDF and download it. There are free tools you can use for this, such as ilovepdf.com. Then drag and drop it in the designated box or select it from the Windows download folder. After uploading the Excel PDF, ChatPDF will give you a brief overview of the spreadsheet and questions you might have about the file.

Although it might not work as well as you’d expect, advancements in AI technology might soon make it a powerful ChatGPT Excel productivity tool. Others, like BookGPT by Huggingface, might also get analysis and visualization functions.

ChatGPT Excel Plugins

ChatGPT Excel plugins are AI-powered add-ins or extensions added to Microsoft Excel to enhance its functionality, with some also working in Google Sheets. With these, productivity increases, and you no longer have to use ChatGPT on browsers. This cuts short time spent referring to Excel spreadsheets while writing prompts and copying formulas, Macros, etc., to the spreadsheet. Efficiency and accuracy are also improved as ChatGPT Excel plugins generate and implement whatever you need, thus eliminating room for errors. Lastly, they are cost-effective as they reduce work hours and are cheaper than software alternatives. Which are the best ChatGPT Excel Plugins?

Numerous.ai

If you’re looking for the best and easiest-to-use ChatGPT Excel plugin, consider Numerous.ai. It works well for all mid-size businesses, small businesses, enterprises, and freelance Excel and Google Sheets users. Data safety is one of the deciding factors to consider when downloading, installing, and using a ChatGPT Excel plugin. But it’s a non-issue with Numerous.ai as it’s Microsoft certified for download in 2016 and later Office Versions. For installation, visit the Numerous.ai official website and select the Google Sheets or Excel Numerous.ai plugin.

The “install for Excel” backlink will take you to the add-ins section in Microsoft Store, and after downloading it, select “open in Excel”. You’ll find the Numerous.AI on the top right-hand side of your Excel workbook. Clicking it will open a Taskpane with Numerous.ai’s functions. Installing Numerous.ai in google sheets is also free, like installing in Microsoft Excel.

The “install for Google Sheets” backlink will divert you to Google’s Workspace Marketplace. Select the Google account to use with Numerous AI and grant the required permissions only, after which Numerous AI will be installed in google sheets. You’ll receive an email that Numerous.ai has access to your Google account. There’s nothing to worry about unless you didn’t install the add-in. To show the sidebar like in Excel, open the “Extensions” drop-down menu, select Numerous.ai, and then enable “show sidebar.”

How to use Numerous.AI in Google Sheets and Microsoft Excel:

The free subscription plan won’t last long if you’re a heavy Excel user or a beginner trying to learn Excel. It only gives you 60 prompt tokens, 30 formula generations, and 30,000 ChatGPT-generated characters. Nonetheless, paid plans are cheap, as $10 per month gets you 1,000 prompt tokens, 500 formula generations, and 500,000 ChatGPT-generated characters. Heavy Excel users can consider higher plans or custom plans. Click here to check Numerous.ai subscription plans.

Excelformulabot/formula bot

Excelformulabot is a Microsoft and Google-certified ChatGPT Excel and Google Sheets plugin based on OpenAI’s GPT-3 AI technology. Its creators claim a 97% accuracy when writing formulas and macros, among its other capabilities, and it can be installed in older Excel versions which is its most significant advantage. Downsides, Excelformulabot limits formula prompts to 5 per month, after which users can pay $10.99 or $84 per year for unlimited formula prompts. There’s also a business subscription plan that allows users to pay a lower amount per user, with the amount varying on the number of users. View Excelformulabot subscription plans here.

To download the ChatGPT Excel plugin, visit the Excelformulabot web page and click the “Add-ons” section on the navigation tab. Select the Excel option, redirecting you to download Excelformulabot from the Microsoft store. Click “open in Excel” after downloading and “trust this add-in” to use it. Unlike Numerous.ai, you have to sign up first before using Excelformulabot.

If you’re using Google Sheets, select the “Google Sheets” options after opening the add-ons section. This will direct you to the Google Workplace Marketplace to download the Formula Bot add-in for Google Sheets. Open the extensions tab, select Formula Bot, and click Launch to use the ChatGPT-powered plugin. You might receive a caution email after installation, but it’s nothing to fret about since it’s not a safety risk.

Ajelix

Advanced Excel users will love Ajelix as it has more advanced functions than most ChatGPT plugins and assistants. Still, it has a slightly different download and installation procedure, and there’s no web or Google Sheets add-in. You can only use Ajelix with Google Sheets via the website’s chat box. Features are limited to generating and explaining formulas and Appsript, a slight disadvantage for Google Sheets Users.

Create an account first before attempting to download Ajelix. Next, navigate to the site’s dashboard on the left, where the “download add-in” option is listed under the resources section. Download the Excel add-in file provided and run the setup file after completion, which will install Ajelix Assistant in Excel. Unlike other ChatGPT Excel plugins, the Ajelix Assistant is placed with other tabs on the ribbon.

A free Ajelix Subscription limits you to 5 monthly requests on all features, including translation. Depending on available discounts, monthly subscription charges vary between $5.95 and $9.99. Corporate users can get a custom subscription which discounts the monthly costs depending on the number of users.

Lumelixr.ai

Lumelixr.ai is a powerful Microsoft Excel and Google Sheets assistant, but it’s only available as a browser plugin and accessible via the extensions command toolbar. All the same, it can create and implement Macros, write formulas, and analyze spreadsheets alongside other functions like the ChatGPT Excel and Google Sheets plugins. To download it visit the Lumelixr.ai website and sign up, and you’ll get a 7-day free trial. After that, you can make a one-time payment with a lifetime usage guarantee or get a monthly or yearly subscription. 

Limitations, since it’s only available as a browser extension, using Lumelixr.ai slightly reduces productivity and efficiency compared to Excel and Google Sheets plugins. Other ChatGPT Excel and Google Sheets extensions similar to Lumelixr.ai include aiexcelbot.com, SheetGod, Arcwise AI, and Charm.ai.

PowerExcel.ai

Power Web5 has not yet released its ChatGPT Excel plugin, PowerExcel.ai, but they promise it will be a game changer in Excel Productivity, performing both simple and complex Excel functions. Will it be better than other plugins? For early access and updates on PowerExcel.ai, better sign up on the website.

Using BingAI with Google Sheets and Microsoft Excel Online

Using ChatGPT in a different window can sometimes be a pain in the neck, and not everyone can pay for ChatGPT Excel and Google Sheets plugins. Download Microsoft Edge and enable BingAI or a Chromium browser that supports the BingAI extension to avoid both. This way, you can work on an Excel or Google Sheets spreadsheet without moving across tabs, as the BingAI dialogue box opens on the same tab when activated. Also, the BingAI dialogue box doesn’t close when you switch tabs which improves efficiency when working on different sheets in multiple tabs. You’ll have to use the free online version of Microsoft Excel to use BingAI with Excel spreadsheets.

Advantages and Limitations of Using ChatGPT with Excel

Despite the concerns that AI might replace humans in some fields, embracing new technology, such as ChatGPT with Excel, is good. The advantages outweigh the disadvantages, which are more like limitations than disadvantages since using ChatGPT with Excel is still a relatively new concept and has limitations.

Advantages of using ChatGPT with Excel

1. Improved efficiency and accuracy: ChatGPT can quickly analyze data in Excel, help users avoid errors, and provide accurate and reliable insights from data analysis in real time, thus, reducing time spent when performing such tasks manually.
2. Easy to use and Customizable: ChatGPT to perform Excel tasks is easy and can be customized to perform simple or advanced tasks depending on the user’s prompt. It’s also easier to edit outputs in ChatGPT through the prompts than editing, for example, formulas in the formula bar.
3. Minimal downtimes: Using ChatGPT with Excel is seamless, thanks to minimal or no downtimes. ChatGPT is available 24 hours a day and seven days a week, which is favorable when working long hours and requiring Excel assistance at different or random times.
4. Reduces work-related costs: ChatGPT and ChatGPT Excel plugins eliminate the need to buy Excel productivity integrations and lessen hours worked by improving efficiency. This reduces working costs and allows users to focus on other tasks after completing Excel-related tasks.
5. ChatGPT makes working with Excel Fun for beginners: Learning how to use Excel isn’t easy, especially for someone who has never used it. For example, writing simple formulas and executing them might take a while to understand. With ChatGPT, all you have to do is write prompts of what task you’d like to perform, and it will generate an answer with a process of how to do it. You only have to learn how to write ChatGPT prompts precisely, which shouldn’t take more than a few minutes.

Limitations of ChatGPT in Microsoft Excel

1. There’s a slight chance of error: ChatGPT is designed to process natural language prompts and translate them to formulas, macros, or other Excel-related tasks. Sometimes the results are incompatible with the Excel formats, leading to wrong or failed implementation.
2. Finite capabilities: ChatGPT might be an excellent productivity tool for use with Excel, but it has limited capabilities, especially in the case of advanced Excel tasks. That’s why it’s important to test results first before implementing them in the main spreadsheet you’re working on.

Conclusion

Using ChatGPT with Excel has to be one of the biggest advancements in AI productivity, and what it can do is proof enough that it’s the only Excel productivity tool you need. Sure, it has its limitations, but being a natural language processing technology, ChatGPT will become more accurate and capable of understanding complex Excel queries. Right now, learn safe practices when using ChatGPT with Excel, which mainly revolves around writing precise prompts and testing formulas, macro codes, etc., before using them in Excel spreadsheets.

FAQs

• Author
• Recent Posts

Allen Hoffman

Editor at Excel.TV, I enjoy sharing my insights and tips on using Excel to make data analysis and visualization more efficient and effective.

Latest posts by Allen Hoffman (see all)

• ExcelTV Partners with the Financial Modeling World Cup - October 10, 2023
• How To Use ChatGPT with Excel - May 26, 2023
• How to Remove Apostrophe in Excel the Quick Way - May 21, 2023