How to Use ChatGPT With Excel (Writing Formulas, Macros, & Explaining Things)
ChatGPT is a powerful AI tool that can help you maximize Excel’s capabilities. This guide will show you how to use it for writing complex formulas, explaining Excel functions, and solving spreadsheet errors.
Christy Cañete
Updated October 9, 2023
using ChatGPT to write Excel formulas
Reading Time: 10 minutes
Excel is an essential tool for many of us. Whether you're a student or a professional, you've probably found yourself wrestling with Excel formulas at some point.
I've been there, too.
Excel is packed with hundreds of formulas. Even seasoned professionals will have a hard time remembering every single formula and its intricacies.
Now, here’s the good news. ChatGPT is designed to be your smart assistant. With its AI-powered capabilities, you can ask almost anything you want to know, including writing formulas in Excel.
Here’s how ChatGPT can assist:
- Recalling a particular formula and applying it accurately
- Troubleshooting an Excel formula that isn’t working
- Personalizing solutions to meet your needs
In the next few minutes, I’ll show you different ways to use ChatGPT to write Excel formulas, from simple operations to complex calculations.
Let's go through the steps to get you started.
Ways You Can Use ChatGPT in Excel
1) Creating Excel Formulas
For our purposes, I’ve prepared a simple inventory dataset for an electronics store.
Here are some Excel formulas you can have ChatGPT help you with:
Tip: To generate the correct Excel formulas, make sure to specify the columns you want to include in the formula. This ensures accuracy and relevance.
Performing Basic Calculations
First, let’s try to calculate the total cost for each product in our table.
ChatGPT Prompt:
I have prepared an inventory dataset for an electronics store in Excel. Write an Excel formula that calculates the total cost for each product based on the quantity in column B and unit price in column C.
Result:
ChatGPT will explain the steps and provide you with the formula to compute the total cost of each product. Copy the formula and paste it to a new column on your Excel table (in this case, column D). Then, drag the AutoFIll handle to apply the same formula to the entire column.
Keep in mind that ChatGPT is not perfect and it can produce wrong results. So always double-check the formulas and verify the results.
Locating the maximum value
Let’s say we want to find the product with the highest total cost. Here’s what your ChatGPT prompt might look like.
ChatGPT Prompt:
I have prepared an inventory dataset for an electronics store in Excel. Write an Excel formula that finds the product from column A with the highest total cost in column D.
Result:
ChatGPT will suggest functions to use to get the product name with the maximum value. In this example, ChatGPT suggested three different functions to use: INDEX, MATCH, and MAX.
Here’s the AI response containing the formula you can copy to your Excel table.
Getting the average value
Again, let’s ask ChatGPT to compute the average unit price from our inventory table.
ChatGPT Prompt:
I have prepared an inventory dataset for an electronics store in Excel. I want you to calculate the average unit price of all products in column C.
Result:
ChatGPT will recommend using the AVERAGE function to get the average unit price of the products. Here’s the generated Excel formula:
Now, your table in Excel should look like this:
2) Explaining How to Use Excel Functions
Excel comes with hundreds of different functions that can do basic arithmetic to complex statistics. And no matter how experienced you are, you can’t possibly memorize or even familiarize all.
Plus, you’re likely going to need complex functions to solve complex tables. And you might not know which one to use.
ChatGPT can help you figure out which Excel functions to use for solving problems. It will also provide you with explanations of how these functions work.
Now take this scenario. Let’s say you’re creating a dashboard and you want to get a summary of a company’s workforce. Here’s a sample table I’ve prepared:
Now, let’s ask ChatGPT to manipulate the data using the following Excel features:
Using Pivot Table
First, let’s try to compute the average salary of employees by Department and Gender. Enter the following ChatGPT Prompt:
I have a table of employee data containing the following information:
- ID
- Full Name
- Job Title
- Gender
- Ethnicity
- Age
- Hire Date
- Annual Salary
- Bonus
- Department
- Business Unit
- Country
- City
- Exit Date
How do I find the average salary by Department and Gender? Explain the steps and what Excel function I should use.
Here’s ChatGPT’s response:
I tried to follow ChatGPT’s alternative method by using the pivot table, and here’s the output:
I verified the results and they were all correct. For me, this is a handy solution especially if you’re not familiar with Excel’s pivot table.
Using VLOOKUP
Let’s say you want to know the full name of an employee based on a given ID. Here’s what the prompt would look like:
I have a table of employee data. I want to find the name of an employee using their ID. Explain the steps and what Excel function I should use.
Once you enter the prompt, you should get explanations like the one below:
I tried implementing the instructions above to look for an employee with the ID number of 4. So, my VLOOKUP formula looks like this:
=VLOOKUP(4, A:B, 2, FALSE)
Then, I entered the formula into my Excel table. It correctly provided the result, as shown here:
3) Writing VBA Macros
VBA macros in Excel are tools we use to automate tasks. If you find yourself doing the same thing repeatedly, like copying data or formatting cells, that's where VBA comes in.
Instead of manually performing those steps every time, we can write a small script in VBA. It's like teaching Excel new tricks tailored to our needs. So, instead of being stuck with mundane tasks, you can focus on more important things.
Now, you don’t need to be a coding expert to write VBA codes. Plus, with ChatGPT, generating VBA macros is easier and faster. Here’s how to do it.
Let’s have a look at a retail sales table. We want to create a VBA macro that automatically calculates the total sales for each product. Then, have those results placed in a column labeled ‘Total Sales.’
Here’s what your ChatGPT prompt would look like:
I have a retail sales dataset in Excel. I want to create a VBA macro to automatically calculate the Total Sales for each product by multiplying the Quantity and Unit Price. Can you provide the VBA code for this?
Here is ChatGPT’s response:
ChatGPT clearly explained the steps before providing the VBA code above. It also offered tips on customizing the code and running the macro, as shown here.
Now, you can copy this macro. Then, go to your Excel table and open the VBA editor (press ALT + F11 for a shortcut). Then, right-click and select Insert > Module. Paste your code here, and then close the VBA editor.
To run the module, simply press ALT + F8. And that’s it! The total sales table should now be filled with the results.
Note: Microsoft Excel now includes ChatGPT as an add-in. This integration allows you to tap into the AI's capabilities directly within Excel to streamline your tasks. There's no need to separately access your ChatGPT account and input prompts - the AI is right there in your Excel workspace. We might consider another tutorial on this new feature, so stay tuned.
4) Analyzing Data From Excel With Advanced Data Analysis Plugin
If you’re working on improving your site’s SEO, you can leverage ChatGPT, too.
To do that, you'll first need to download your site’s analytics report from Google Search Console. Your file should contain data such as demographics, pages, clicks, impressions, and queries.
Note that you need to have ChatGPT Plus to access the Advanced Data Analysis plugin. Once it’s enabled, go to the chat box and click the ‘plus’ button on the left to upload your Excel or CSV file.
Next, you’ll need to tell ChatGPT what to do with your file. This part needs to have a bit of creativity. And the initial results might not exactly match your expectations.
The trick is to make your instructions as detailed as possible. Then, keep iterating your prompt until you get the desired results.
For instance, our topic is about women’s dresses and we’re going to use ChatGPT to determine high-ranking but low-competition related terms.
Here’s a sample ChatGPT prompt I’ve created:
Uploaded is a report about my site’s performance in Google organic search results. I want you to identify any high-volume related terms with low organic competition that we might have missed.
And, here’s ChatGPT’s response:
First, it explained the columns included in the dataset. Then it explained the criteria and the necessary steps to find high-volume related terms with low competition. It then asked me whether I’d like to proceed with the proposed steps.
After the confirmation, here’s the result I got:
ChatGPT also explained how to use the results to boost our site’s page ranking. Plus, you have the option to request more in-depth analysis or suggestions based on the outcomes.
Tips to Effectively Use ChatGPT in Excel
Excel's intricate spreadsheets can sometimes be overwhelming. But with ChatGPT, you have a smart assistant to help you simplify those complexities. Now, this makes problem-solving faster and easier.
I also personally find these tips helpful:
- Rephrase your prompt as needed: ChatGPT might not grasp your prompt the first time. If the answer isn't what you expected, try asking the question in a different way.
- Edit your queries directly in ChatGPT: If you made a typo or you want to refine your question, then there’s no need to retype everything. Just click the ‘edit’ icon at the top right of your prompt and make your changes.
- Ask ChatGPT to troubleshoot formulas: If a formula isn't working as expected, ChatGPT can help. Describe the problem and provide the formula you’ve used.
- Break down complex tasks: Sometimes, it can be tempting to just use a single prompt and let ChatGPT solve everything. But this is not the best approach. If you have a multifaceted Excel task, break it down step by step. For example, instead of asking how to create a complex dashboard, start by asking how to create individual charts or tables.
Wrapping It Up
With ChatGPT into your Excel workflow, the possibilities are endless! So, don’t hesitate to experiment. We're only at the start of something way larger that's coming.
You can use ChatGPT to automate your monthly report in Excel. You can also seek out ChatGPT’s insights to uncover hidden trends on your datasheet. Or even use the AI tool to create visualizations and identify peaks and valleys based on the plots.
Want to Learn Even More?
If you enjoyed this article, subscribe to our free newsletter where we share tips & tricks on how to use tech & AI to grow and optimize your business, career, and life.