How to Use ChatGPT with Excel: 4 Essential Ways

Ivana Kotorchevikj

Written by Ivana Kotorchevikj AI for Business & Education

Linkedin Published 1 year ago / updated 1 year ago - 15 min read

“Yes, I have experience working with Excel” is the most frequent lie told in a job interview. If you are also guilty of resorting to this white lie, but know darn well the only thing you can do in Excel is fill in and highlight cells, don’t worry; you are not the only one.

Luckily, ChatGPT is your AI best buddy that will revolutionize how you navigate Excel tables and numbers.

In today's world, where data is the new oil, Microsoft Excel is an indispensable tool. But, as many of us know, it can also be complicated and time-consuming to master. That's where ChatGPT comes into play.

In this article, we're going to delve into how to use ChatGPT for Excel. From simple tasks such as formatting cells and creating pivot tables to more complex ones like implementing macros or dissecting complex formulas, you'll find that ChatGPT can lend you a helping hand in your Excel journey.

Ask ChatGPT to explain Excel terms

ChatGPT can create and explain complex Excel formulas and functions, but it can also help explain basic Excel concepts.

For example, if you have to use a SUM function, but you’ve found out that there are several: SUM function, SUMIF function, SUMIFS function (there are several more SUM functions, but for the sake of this example, we’ll look at these three. If you’re not sure which one you should use, you can ask ChatGPT:

I need to add values of several cells in Excel, but I’m not sure what SUM function to use between the SUM function, SUMIF function, and SUMIFS function. Could you please explain what each function is used for and provide examples.

ChatGPT provided a rundown of each function, explaining its use, syntax, and different elements of the syntax:

Understanding these different functions can certainly make your Excel experience much more efficient and productive. Let's dive in!

1. SUM function

This is the most basic of the three functions, and it simply adds up all the values in a specified range of cells.

Example: =SUM(A1:A10) - this function will add up all the values in cells A1 through A10.

2. SUMIF function

This function is a step up from the SUM function, as it adds up values in a range of cells, but only if they meet a certain condition.

Example: =SUMIF(A1:A10,">20",B1:B10) - this function will sum the values in the B1 to B10 range but only for the rows where the corresponding value in A1 to A10 is greater than 20.

The syntax of SUMIF is as follows: =SUMIF(range, criteria, [sum_range]).

• range: The range of cells you want evaluated by your criteria.
• criteria: The condition that must be met for the cell to be included in the sum.
• [sum_range]: This is optional. If you don't include it, Excel will sum the cells in your specified range. If you do include it, Excel will sum the cells in the sum_range where the corresponding cell in the range meets the criteria.

3. SUMIFS function

The SUMIFS function is the most flexible of the three, as it allows you to sum values based on multiple conditions.

Example: =SUMIFS(B1:B10, A1:A10, ">20", C1:C10, "<30") - this function will sum the values in the B1 to B10 range but only for the rows where the corresponding value in A1 to A10 is greater than 20 AND the corresponding value in C1 to C10 is less than 30.

The syntax of SUMIFS is as follows: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)

• sum_range: The range of cells to be summed.
• criteria_range1: The range of cells you want evaluated by your first criteria.
• criteria1: The condition that must be met in criteria_range1.
• criteria_range2, criteria2: These are optional. You can add as many criteria as you need.

Remember, SUM is the simplest and doesn't consider any conditions, SUMIF is great for single condition sums, and SUMIFS is your best friend when you want to sum based on multiple conditions. Each has its place depending on the task at hand. Happy number crunching!

Ask ChatGPT to provide formulas and functions

If you don’t know the exact formula or function, you can also explain to ChatGPT what you want to do with the cells, ask it to write the Excel formula, and how to use it.

Prompt:

I need to extract the average value of column C. What formula should I use in Excel to do this?

This is what ChatGPT replied:

The formula to calculate the average of a column in Excel is quite straightforward, you would use the AVERAGE function.

If you want to calculate the average of the entire column C, you can use:

=AVERAGE(C:C)

However, this would include all cells in the column, which might not be what you want if there are blank cells or non-numerical data in the column.

If you want to calculate the average of a specific range, for example, from C1 to C100, you would use:

=AVERAGE(C1:C100)

This will calculate the average value of all numbers within cells C1 to C100. Please replace 'C1' and 'C100' with your desired starting and ending cells.

Keep in mind that the AVERAGE function in Excel ignores text, logical values (like TRUE and FALSE), and empty cells. However, it does consider cells with zero (0) in its calculation.

Clearly, I forgot to add the column range for which I want to calculate the average value, so ChatGPT was kind enough to point to it and give me a tip. 😀

Then simply copy and paste the formula in the cell below the column of numbers for which you want to calculate the average value, and Excel will provide the numbers. Also, you can use this formula for the other columns where you need to calculate the average value by replacing the column letter.

🤔 Explore also how to create tables in ChatGPT.

Use ChatGPT with VBA and Macros

Excel VBA, also known as Visual Basic for Applications, is a handy tool that makes it easier to do repeat tasks and make your own functions. On the other hand, macros serve as a predefined list of directions for Excel to complete specific tasks in a particular sequence.

Macros allow you to capture a precise series of operations and then run them later with just one click, bypassing the need to type out the code each time.

If you're finding it hard to make Excel macros or are just starting with VBA, Chat GPT can help you out.

Just like asking ChatGPT for help with formulas, you can also ask Chat GPT for help with making the macro. Don’t worry - ChatGPT can't make changes to your Excel workbook itself, but it can give you the steps you need to take.

For example, you can ask ChatGPT to filter data according to certain criteria. Let’s say you have stores in different locations in a country, and they’ve made different sales numbers. You want to filter and sort the stores according to the sales.

Prompt:

I have an Excel table with the following columns: “Store Location,” “Quarter,” and “Total Sales.” Please create a macro filtering the store that made more than $4,000,000 in the first two quarters.

ChatGPT provided an example of how you could create a macro in VBA to perform this filtering task:

ChatGPT also reminds you to replace the worksheet reference and the cell range ("A1") with the actual locations in your Excel workbook. You are also advised to adjust the implementation based on the specific layout and format of your Excel data and test the macro in a copy of your workbook so you don’t accidentally lose any data.

Using ChatGPT to analyze data

No matter if you are a beginner in data analysis or need a refresher on more advanced topics, ChatGPT can be of big help with everything from basic statistics to complex machine learning algorithms; it can provide easy-to-understand explanations and examples.

Additionally, you can feed your data to ChatGPT and ask it to create a chart based on certain values. Based on your data and the problem you're trying to solve, ChatGPT can suggest appropriate statistical or machine learning models to use.

Finally, once you have results from your analysis, ChatGPT can help you interpret them, explaining what different statistical measures mean or helping you understand why a machine learning model made certain predictions.

For example, if you have a dataset in a csv, json or xml, you can paste it into ChatGPT and ask it to create a chart:

Please create a chart based on the provided data, presenting the trends of sales over one year.

Code Interpreter

In early July, OpenAI launched the Code Interpreter plugin specifically designed to solve math problems, and analyze data, create charts, convert files, create QR codes, convert GIFs into videos, and a whole array of other things.

Code Interpreter also allows you to upload data files and perform actions on them using Python scripts. Python, a popular programming language, offers various libraries like Numpy and Pandas, which enable users to manipulate Excel files efficiently.

How to integrate ChatGPT with Excel

Using ChatGPT to get answers and generate formulas and VBA code is an efficient and smart way to use Excel and improve your skills. But there’s also the option to integrate ChatGPT into Excel and use it in your workbook.

There are many ChatGPT Excel add-ins for Excel that enable you to use ChatGPT in Excel without having to open another tab. The choice is vast, but the most popular on the app store are ChatGPT for Excel, Numerous.ai - ChatGPT for Excel, ChartExpo™ for Excel, chatSheets, and GPT in Excel. The add-ins are compatible with Excel 2016 and later versions.

Here’s how to insert a ChatGPT add-in in Excel:

1. Open Excel.
2. Click on the ‘Insert’ tab in the top toolbar.

3. Select ‘Add-ins.’

4. In the search bar on the left, type in ‘ChatGPT’ or ‘ChatGPT for Excel.’

5. Check out the available options. (Note: For this test, I chose the ‘ChatGPT for Excel’ add-in. The steps may differ a bit for other add-ins.) You can also filter them by selecting categories.
6. Click on ‘Add’ to the right of your desired add-in.

7. In the next window, click ‘Continue.’

8. Next, a pop-up window will appear on the right, offering a video tutorial and asking for you to add an API key you can generate in your OpenAI account. Paste your API key and click ‘Save.’

9. The ChatGPT for Excel add-in will provide instructions on how to use it.

The ChatGPT for Excel add-in is inserted in the toolbar on the right. Click on it at any time to access the tutorial and the instructions for its use.

Guidelines on using ChatGPT for Excel

ChatGPT is a fantastic resource that can enhance your proficiency in Excel.

Like any other software, it is essential to use it correctly and provide accurate information to achieve optimal outcomes.

Here are some guidelines to help you get the most out of ChatGPT for Excel:

• Give clear and precise prompts - Make sure the prompts you give contain specific information for what you need. For example, add the fact that you are working with an Excel table, or you need an Excel formula. Also, if you ask ChatGPT to create a formula, add the exact range of cells it needs to take into consideration, for example, cells A2 through A6, so it provides the accurate formula.
• Provide as many details as possible when creating Macros - The effectiveness of the macros generated by ChatGPT depends on the quality of the description provided. It is crucial to specify the desired macro name, the range of the table, the particular worksheet, or whether the macro should be applied to all worksheets in a workbook. Clearly defining these parameters can be challenging but is essential when working with ChatGPT and Macros.
• Rephrase and repeat questions - Sometimes ChatGPT can misunderstand your prompt or provide non-working formulas or results. If this is the case, don’t give up or get frustrated. The AI chatbot learns through your input. So try rephrasing your question and asking ChatGPT to provide an answer again. You can always go back and edit your question and see what the response will be. Also, don’t be afraid to point out to ChatGPT that the formula doesn’t work and ask it to
• Ask ChatGPT to provide an explanation - ChatGPT is not only efficient in creating formulas and analyzing data, but it can also be helpful in explaining how formulas or functions work if you want to go deeper into math. You can copy and paste an Excel spreadsheet and ask ChatGPT to explain the workings behind it. Additionally, if an old formula or macros is not working, ask ChatGPT to review and fix it.
• Check formula and code accuracy - Although ChatGPT is generally accurate in providing answers, one of its biggest limitations is that it’s not so great at doing math. This is because ChatGPT is primarily a large language model built for understanding and processing text. However, as we’ve seen, despite this limitation, ChatGPT provides accurate responses for Excel queries. But having this limitation in mind, it’s always a good idea to check any formula or code it generates.

📊 You might also want to learn how to integrate and use ChatGPT with Google Sheets.

Get ready to crunch those numbers using ChatGPT for Excel

ChatGPT offers valuable assistance in navigating and utilizing Microsoft Excel. It can explain Excel terms, provide formulas and functions, help with VBA and macros, analyze data, and even utilize the Code Interpreter plugin for advanced tasks.

By following some guidelines, such as providing clear prompts, adding details for macros, rephrasing questions if needed, asking for explanations, and double-checking formula accuracy, users can make the most out of ChatGPT's capabilities and enhance their Excel proficiency.