How to Use ChatGPT for Excel: Tips, Tricks, and Best Practices

What is ChatGPT for Excel?

ChatGPT for Excel is a plugin designed to automate repetitive tasks in Excel. It forms part of ChatGPT for Work, a set of extensions for Microsoft Excel and Word, Google Sheets, and Docs that enable you to prompt ChatGPT and other generative AI models to power through repetitive tasks, such as writing, translating, categorizing, extracting, reformating, summarizing, drafting, and much more. 

GPT for Excel provides two key features to improve efficiency: 

• Bulk tools. They allow you to run large-scale operations with simple prompts. You can run any operation on a whole column at once, without writing any formula. 
• GPT functions. GPT functions allow you to perform complex operations that can be chained together for maximum flexibility.

ChatGPT for Excel comes with a free trial. After that, you need to buy a pack to add money to your balance. The pricing is proportional to your input and output tokens. A token is a small piece of text that can represent a word or subword, a punctuation sign, or a symbol. Models split all input and output into tokens for efficient processing. Read our separate article if you want to learn more about tokenization. 

Getting Started with ChatGPT for Excel

Installing the ChatGPT plugin for Excel

Installing ChatGPT for Excel is an easy, straightforward process. To get started, go to the GPT for Excel Word extension installation page and click on the “Open in Excel” button to launch the installation process. You will have to grant some permission to Microsoft to use the extension.

Installing GPT for Excel

Once you have installed the plugin on your Microsoft Account, you should access it from the Home tab on the desktop app and the Web version of Excel, as shown in the image below:

GPT for Excel button

Setting up ChatGPT for Excel

Using ChatGPT for Excel couldn’t be easier. The first step is choosing your preferred AI model. ChatGPT for Excel supports integrations with some of the most advanced generative AI models, including GPT-4o, GPT-4o-mini, GPT-4 Turbo, and Claude-3.5 Sonnet.

GPT for Excel models

Furthermore, you can use the plugin via an API key to access more models. In particular, You can use your OpenAI API keys to benefit from your own rate limits, and cost savings, or to use your fine-tuned models and Assistants. You can also set up Anthropic API keys to benefit from Claude's models. Read our separate article to learn more about Open AI API and how to obtain your personal API key.

How to Use ChatGPT for Excel Formulas

ChatGPT comes with a series of functions that allow you to write ChatGPT’s prompts and insert the results right into your spreadsheets. ChatGPT for Excel formulas can help you with a wide range of tasks, including writing, editing, translating, classifying, extracting, summarizing, and much more. 

The whole list can be found in the GPT for Excel add-in. 

GPT functions

Let’s analyze how ChatGPT for Excel formulas work with some of its most notable functions: 

GPT() 

It’s the simplest function to start using GPT in Excel. You just have to write your prompt as a parameter and Excel will output the result in a single cell.

Write a prompt in the function.

Like any other Excel formula, =GPT() is highly flexible. For example, you can use a cell as a parameter for your GPT() formula:

Use a cell as a prompt.

Equally, you can apply a prompt to many cells by dragging the formula down:

Apply a simple prompt to many cells

GPT_TRANSLATE()

With GPT_TRANSLATE(), you can leverage large language models for your machine translation tasks with high performance and support for more than 80 languages. To start using the function, you just have to add the text to translate, the target language and the source language. You could also add additional instructions for the translation with the “instructions” parameter.

 GPT_TRANSLATE() in action

GPT_SUMMARIZE()

GPT_SUMMARIZE() allows instant summaries of long texts, even in different languages. Interestingly, with the “format” parameter you can give instructions on the format of the summary.

GPT_SUMMARIZE() in action

Common Use Cases of ChatGPT for Data Analysis

Excel is specifically designed to be accessible and easy to use. However, it can be complex and time-consuming to perform certain data-related tasks (that’s one of the reasons modern data practitioners have traditionally preferred other tools for data analysis, like Python and R).

ChatGPT for Excel will likely revolutionize how data analysts and data scientists use Excel, easing and speeding up daily data analysis processes. Let’s analyze some of the most compelling use cases:

Data preparation​

With ChatGPT for Excel, you can free up your teams from time-consuming data preparation so they can focus on high-value data analysis tasks. Some common data preparation tasks are:

• Data formatting. With functions like GPT_FORMAT(), you can easily format dates, currencies, addresses, names, fix capitalization, and so much more. In the following image you can find an example of how to standardize dates based on ISO rules:

GPT_FORMAT() in action

• Data extraction. The GPT_EXTRACT() function allows you to get data from text based on underlying regular expressions. Examples of extracted data include company names or email addresses, as shown below:

GPT_EXTRACT() in action

Data analysis

Once your data is clean, it’s time for analysis. Here, ChatGPT for Excel also has a lot to say. Here is a list of some data analysis tasks that can be potentially automated:

• Sentiment analysis. Sentiment analysis is a data classification task where a classifier is supposed to tell if the sentiment behind that is positive, negative or neutral. This analysis can be easily conducted with the GPT_CLASSIFY(). For example, in the case below, we analyze customer feedback:

GPT_CLASSIFY() in action

• Image analysis. Computer vision, also known as image analysis, is a field of AI that enables machines to derive meaningful information from digital images, videos, and other visual inputs. The latest version of ChatGPT for Excel comes with a novel GPT_VISION() function that allows you to work with images. For example, you can generate a description based on an image, extract information from an image (e.g., an ID from an invoice), explain elements of an image, or generate alt text from an image. Note that this function only works with the GPT-4-turbo model.

GPT_VISION() in action

Tips for Using ChatGPT in Excel

Despite its simple, user-friendly interface, the ChatGPT for Excel is an incredibly powerful plugin. It has many functionalities besides the GPT functions that will allow you to leverage the full power of generative AI in your sheets.

ChatGPT for Excel bulk prompt tool 

In addition to the GPT functions, ChatGPT for Excel comes with a second powerful feature: the bulk prompt tool. This flexible, intuitive tool can handle large-volume runs of simple prompts. You can run any operation on a whole column at once, without writing any formula, saving you a significant amount of time.

ChatGPT bulk prompt tool 

ChatGPT for Excel comes with several bulk prompts, including the custom prompt functionality, which allows you to run a custom prompt for each row in your sheet, and domain-specific functionalities for bulk translation, classification, extraction, search, and much more. 

Using the ChatGPT for Excel bulk prompt tool is fairly easy and requires no code, as shown in the image below, where we use the custom prompt tool to create SEO-optimized descriptions for bikes:

ChatGPT bulk prompt tool in action

Leverage Excel Formula Assistant

It also includes an AI-powered Excel Formula Assistant that allows you to:

• Generate formulas. Prompt an instruction and get an Excel formula. To ensure you get the right formula, be as specific as possible in your description, cells, and ranges.
• Explain formulas. Prompt a certain formula, including the values of the parameters, and get a detailed explanation of how the formula works. 

Excel Formula Assistant

Best Practices and Limitations of ChatGPT in Excel

Ensuring accuracy in formulas

As already mentioned, ChatGPT for Excel is not free. You will need to buy a pack to run prompts, and will be charged according to your use. Hence, applying the right formulas is mandatory if you don’t want to waste money.

In this vein, if you are to use the bulk prompt tool, test first with some rows before applying it to the entire column to save tokens:

Replace GPT formulas

Equally, a good tip is converting GPT formulas to static values to prevent any further recalculation and save money. You can easily replace formulas with results manually or automatically, either on a selected number of cells or across an entire sheet. Check out the ChatGPt for Excel documentation to know more about replacing GPT formulas. 

Another way to prevent automatic recalculation is to enable GPT for Excel cache in your workbook; GPT formulas do not reload. Instead, the results are retrieved from the cache, preventing any extra cost or loss of results.

Understanding the limitations

ChatGPT for Excel comes with the same limitations you would expect in ChatGPT and other generative AI models. For example, ChatGPT can sometimes produce plausible-sounding but incorrect or nonsensical answers. It may also reflect biases present in its training data and lacks true understanding or reasoning capabilities.

Against this backdrop, prompt engineering is key to minimizing potential problems. Prompt engineering is the practice of designing and refining prompts (questions or instructions) to elicit specific responses from AI models. Think of it as the interface between human intent and machine output. In the context of Excel, this means being as specific as possible in your description, input data, cells, and ranges. Check out our separate article to know everything about prompt engineering.

Using ChatGPT for Excel Without the Extension

After ChatGPT's release and before the launch of the ChatGPT for Excel add-in, many people started using ChatGPT to assist them with Excel-related tasks. Despite the ChatGPT extension being particularly well-suited for the job, it comes with an important drawback: it is not free. 

Therefore, if you are not willing or are unable to pay to use the extension, using ChatGPT and copying its outputs into Excel may be a better choice for you. In the end, the results won’t differ much, for you will always be using the same tool: ChatGPT.

ChatGPT can help you with many Excel-related tasks, including formula generation, data analysis, and data visualization. 

For example, below you can find some basic prompts that can be useful for simple tasks, like calculating a profit column based on the revenues and costs columns of the following spreadsheet.

As you can see, ChatGPT outputs the formula with some explanation on how to implement it in Excel. You just have to click on the “Copy code” button and then paste the formula in the Excel cell.

Another basic example with the same data as asking ChatGPT to get the total cost in the time series:

These are just a couple of simple prompts to illustrate how to use ChatGPT for Excel tasks, but there is much more you can do. Honestly, the sky's the limit. Yet, as you move towards more complex tasks, you must be more careful with your prompts so ChatGPT doesn't get confused. 

Writing ChatGPT prompts is an art. Effective prompt engineering is essential for getting the most accurate and relevant responses from ChatGPT. By following a few key tips, you can enhance the quality and usefulness of the generated content. Here are the core high-level tips to keep in mind:

• Clarity and sepecificity. Your prompts should clearly define what you want the model to do. Avoid ambiguity and try to be as precise with the operations you want ChatGPT to perform.
• Context matters. Closely related to the previous tip, ChatGPT responds to the immediate context of the prompt. So, establishing a clear context is crucial. For example, when asking ChatGPT to write an Excel formula, be specific about the rows and columns to work with.
• Iterative refinement. As often occurs in regular conversations, you may need to ask several questions to get the information you need. The same applies when you speak to ChatGPT. Iterative refinement is the process of refining and adjusting your prompts throughout the conversation to improve the relevance, accuracy, and depth of its outputs. 

Conclusion

ChatGPT for Excel, both as a concept and a plugin, is revolutionizing the way data practitioners use Excel. 

There are compelling reasons to implement ChatGPT in your Excel operations. To ensure a successful incorporation of ChatGPT in your data science toolkit, data literacy is key. Fortunately, DataCamp is here to help. We hope the following dedicated materials will help you learn and leverage the full power of ChatGPT and Excel:

• ChatGPT Fundamental
• A Guide to Using ChatGPT For Data Science Projects 
• ChatGPT Prompt Engineering for Developers
• A Beginner's Guide to Prompt Engineering with ChatGPT
• Excel Fundamentals
• Data Analysis in Excel Course
• Data Visualization in Excel Course
• Learn Excel in 2024: A Complete Guide for Beginners