Use ChatGPT to Write Excel Formulas
Suppose I have a dataset containing the sales for a coffee shop selling 6 types of coffee. Now, let's say I want to find out, what the maximum sales revenue was from any coffee, but I have no clue which Excel function to use.
No problem, I can ask ChatGPT.
My prompt could be something like this: What
Excel formula should I use to find the maximum value in a column?
Within seconds ChatGPT returns the required function, i.e. the MAX function, along with an example of how to use it!
While using the suggested function, I might need to tweak it a little to specify the exact rows and columns containing your data, but apart from that, I'm good to go.
For example, in this case, the sales data
is stored in cells D4:D9, so the adjusted function would be =MAX(D4:D9)
Let's use the MAX function in the example dataset to find the maximum sales revenue for product sales.
I can also use this technique to look up the coffee that brought in the max sales– a.k.a, the best-selling coffee.
My prompt would be: What formula would I use to find the Product that
had the maximum sales, where the product is in cells C4:C9 and the sales values are in D4:D9? I'm using Microsoft 365.
Tip: Specifying the exact cell references helps avoid having to tweak the formula suggested by ChatGPT. I also specified the version of Excel, so it knows what functions I have access to.
Immediately, ChatGPT has returned the INDEX and MATCH formula to find the best-selling product.
But let's say I find this formula too complicated. I can also ask follow-up questions to ChatGPT for alternative ways of calculating the same number.
My prompt would be: Is there an easier formula I can use?
And because I told it I have Microsoft 365 it has suggested the newer XLOOKUP function.
Pro Tip: Asking follow-up questions to ChatGPT is a great way for beginners to rapidly become experts at Excel, for example, here within seconds we found two solutions to one problem, something that can often take many years
to discover.
Suppose I choose to use XLOOKUP to calculate the best-selling coffee. As I have already calculated the max sales in cell D11, I can use that as a reference in the XLOOKUP formula:
Applying the XLOOKUP formula, I have now found out that Frappé is the best-selling coffee in the coffee shop.
Use ChatGPT to Debug Excel Formulas
Chances
are, if you've worked with Excel, you've run into formula errors – I've definitely had my share! It happens to all of us. The great thing is, ChatGPT can help us out. It's not only good at creating formulas but also at fixing those pesky errors in your Excel work. It's like having an extra pair of eyes to make everything run smoothly.
Suppose beginning the new year, the same coffee shop is going to add 4 more types of
coffee to its menu. To incorporate them, I want to create a spreadsheet to record the sales of all 10 types. I have created a 4-column table as follows:
I want to calculate the average revenue per coffee in column D. However, as the 4 new coffee types have no prior sales, my formula returns the #DIV/0! error.
While I can let the error fix itself when the new coffees start showing sales, I can also use ChatGPT to deal with the error more gracefully.
My prompt would be:In my 'SalesData' table, I'm trying to
calculate the average revenue per item in range 'D4:D13'. The formula in 'D4' is '=B4/C4', but it's giving a #DIV/0! error when sales are zero in. How can I fix this?
Immediately, ChatGPT gives me a solution to use the IFERROR
function in my formula to avoid the #DIV/0! error.
Replacing the formula in cell D4 with the one using the IFERROR function from ChatGPT and copying it down the column hides the errors nicely:
Use ChatGPT to Explain Complex Excel Formulas
Returning the appropriate formula or debugging a formula is just the beginning. ChatGPT is capable of much more!
Imagine the coffee shop has expanded into 5 countries and now my spreadsheet contains consolidated sales for the last 2
months.
There's also another sheet that only shows the
sales from Canada for Frappé (the best-selling coffee).
When I go to the first cell, I find a daunting-looking formula that has been entered by someone else on the team:
=FILTER(Data!A:D,
(Data!A:A="Canada")*(Data!B:B="Frappé"),
"Canada had no Frappé sales")
I know that it's pulling this information from the larger dataset, but I have no clue about how it's doing this. And I want to use it to create more sales snapshots for other markets.
This is another scenario where ChatGPT comes in very handy. It can quickly explain this formula to you.
Your prompt would be: Explain this formula step by step: =FILTER(Data!A:D,(Data!A:A="Canada")*(Data!B:B="Frappé"),"Canada had no Frappé sales")
Tip: specifying 'step-by-step' ensures it gives a break down of each component in the formula, resulting in a more thorough explanation.
Immediately, ChatGPT explains the function and its components!
Now it doesn't look so daunting, and I can easily modify it to suit my requirements! Like here I changed it to USA, Turkish Coffee and added a third filter criteria of sales > 10,000
As you can see, ChatGPT is extremely helpful when you are working on a spreadsheet you inherited from someone else, making it super quick to get up to speed.
Speaking of help, if you're still finding Excel a bit daunting, I've got the
solution for you. Check out my range of Excel courses – designed to take you from beginner to pro in no time.
Use ChatGPT to Find Excel Shortcuts
One of the easiest ways to save time in Excel is with keyboard
shortcuts. But with over 200 shortcuts it's a lot to remember.
However, ChatGPT can recall them in seconds.
For example, AutoSum is one of the most versatile shortcuts but if you don't use it regularly, you can easily forget it.
I can quickly ask ChatGPT to share the shortcut with me using
the prompt: What's the Excel shortcut for quickly adding up numbers in a column?
It instantly returns the shortcut Alt + = and trying it on my dataset, it works like a charm!
Tip: this shortcut inserts the SUBTOTAL function in Tables and the SUM function in cells outside tables.
You could also ask ChatGPT to share a list of some of the most used Excel shortcuts to speed up your work even
more.
Your prompt would be: Give me a list of the most popular excel shortcuts.
If you like keyboard shortcuts download our 239 Keyboard Shortcuts PDF and Periodic Table of Keyboard Shortcuts here: Excel Keyboard Shortcuts
Use ChatGPT to Create Macros
Now that I have 2 months of coffee shop sales by country:
I have created a PivotTable to conduct regular sales analysis and I have an assistant updating it. I need to ensure the PivotTable is always up to date
because I share this file with some investors.
I know
VBA code can be used to achieve this, but unfortunately, VBA is not one of my strongest skills.
Thankfully, I no longer need to learn it from scratch. I can use ChatGPT to create the code for me!
My prompt would be: Create VBA code to refresh PivotTable1 located in the VBA_Pivot tab when Data tab in the same workbook is no
longer active.
And in no time, it spits out the code!
Tip: It's best to include the actual tab names in your prompt so that the resulting code uses them instead of generic tab names that you'd later need to edit, thus reducing the chance of you making an editing error.
Additionally, it gives me the instructions on pasting the code in the VBA editor,
which is very helpful for someone who has never done that before.
By pasting the above code in my spreadsheet's VBA editor, I was able to make the PivotTable1 in the VBA_Pivot tab respond to any changes made to the financials table in the Data tab.
Tip: The green lines in the code above are comments which don't affect the code but are there just for
understanding.
For example, when I added March data to the source data, the PivotTable automatically refreshed.
Tips:
- Save your file in the macro-enabled .xslm format for the macro to run.
- VBA changes are permanent. If
you accidentally delete anything in the financials table on the Data tab, you won't be able to undo it once you go to another tab.
- Editing tab names can break this VBA code. Instead using the constant internal sheet names such as Sheet1, Sheet2, etc. would be better as that would allow you to edit the tab names.
Best Practices, Benefits & Limitations
While ChatGPT is a huge help when using Excel, it is always advisable to have realistic expectations.
Knowing its benefits, limitations, and best practices can save you a lot of trouble, so here they are:
Tips & Best Practices:
- Be Specific: The more specific prompts you provide, the better results you get. Specifying row and column references helps the AI to output
the exact formula you need, or else you might need to tweak the output formula to suit your dataset.
- Dynamic results: Providing the same prompt multiple times can give you varying results, so it's best to prompt ChatGPT a few times to find the easiest or the best solution, or ask follow up questions as mentioned in the next point.
- Validate outputs: ChatGPT is still developing. At times, the output might not work as mentioned. It's best to try them yourself and validate them. But also, ask ChatGPT to validate itself. For example, ask it if this is the most efficient way, or is this best practice, or is there an easier way etc.?
Benefits:
- Tackles complex problems: ChatGPT is capable of giving out functions or a combination of functions or even VBA codes that help you solve much more difficult data analysis problems as we saw with XLOOKUP, FILTER, and the PivotTable auto-refresh macro
- Saves
time: ChatGPT saves a lot of your research time, by looking for the right function. You can simply start applying them.
- Enhances report quality: Using ChatGPT to debug errors, as explained above, enhances the quality of your reports, and makes them much more professional.
- Facilitates collaboration: You can work on a spreadsheet designed by someone else without spending too much time figuring out the existing calculations.
Limitations:
- Dated suggestions: While Excel releases more optimized functions all the time,
ChatGPT might not always keep up. For example, it still returns VLOOKUP or INDEX-MATCH combination when prompted, instead of the newer and simpler XLOOKUP. This can be overcome by asking follow-up questions before applying a suggestion.
- Omits instructions: While ChatGPT goes beyond the prompt to give the usage instructions, at times it skips some necessary information. For example, it
should always suggest that the VBA changes are irreversible, and you should save the Excel in macro-enabled .xlsm format, but it doesn't do that consistently each time it's prompted. It requires a few iterations to get the complete information.
- Model dependency: Most times ChatGPT gives you great answers to your prompts, but it is limited to the model version you use. As the latest
model GPT-4 is only available for Plus users, the response you get from older models may not be as accurate as the latest model.
Next Steps to Excel Mastery
While VBA is the original way to automate tasks in Excel and it's still used a lot, it can't execute in Excel online. Whereas the newer way to automate tasks using Office Scripts can, so check out how to write Excel Office Scripts with ChatGPT next.
Learn With Us
