Everyone’s talking about using ChatGPT for Excel and how it can improve your productivity with claims it’ll make you n times more productive and the like. I can tell you it’s not all it’s cracked up to be!
If you want to be more productive in Excel, learn Power Query.
That’s not to say ChatGPT isn’t worth using, but you have to know what to use it for and what not to waste your time on, 'cause believe me, you can waste a lot of time on it.
In this post I’ll show you what the best uses of ChatGPT are for Excel, what not to waste your time on and most importantly, what to watch out for, because it can return a lot of inaccuracies and because it comes across so authoritative, it’s easy to assume it’s right.
Note: these tests were done with ChatGPT-3.
Watch the ChatGPT for Excel Video
ChatGPT for Excel Function Explanations
Beginners will probably get the most out of ChatGPT because it’s good at answering simple questions.
For intermediate to advanced Excel users, it becomes less useful because it struggles with complex questions.
For example, ask it to explain how a function works and it does a pretty good job. I asked it to explain the XLOOKUP function:
It explains the purpose, the syntax and the various arguments. It stops short of an example, but you could ask a follow up question to get that:
It provides a simple example, but there’s a lot more to XLOOKUP not covered here and without knowing what it’s capable of, you wouldn’t know to ask about it.
ChatGPT for Excel Formula Writing
If you have a fairly straight forward formula question, it can usually give you a solution. We get a lot of questions about IF formulas like this one left in the comments on our blog:
“A formula I currently have is as follows; =IF(B45<=50,450,450+((B45-50)*D45))
I still need the above but I also want to add to it that if B45 is left blank, a value of 0 is shown.”
I plugged that question into ChatGPT and it returned the correct answer:
We can see ChatGPT is great at giving you instant answers to simple questions. This is going to save Excel beginners a lot of time. But it comes at a cost.
You’re not learning if you just ask ChatGPT to do the work for you.
Therefore, I recommend you try to find a solution yourself and then if you get an error, a better question to ask is ‘what’s wrong with my formula, it should be doing xyz’.
This way ChatGPT will not only fix your formula, but it’ll also explain where you went wrong and what the correct solution should be.
ChatGPT Troubleshoot Excel Formula Errors
Let’s say you’re trying to write a formula but it’s returning an error. You can simply ask ChatGPT what’s wrong with the formula and it will explain the issues and the solution:
Keep in mind that ChatGPT doesn’t know what data you’re working with, so if the formula is returning a result, but not the one you were expecting, then it’s probably not going to be able to help with that.
ChatGPT Explain Excel Formulas
If you’ve inherited an Excel file with a complex formula, ChatGPT is good at explaining what that formula does in layman’s terms.
However, it was less good at simplifying that formula and even tries to avoid doing it:
In hindsight, I should have been more explicit with my question and asked it to simplify it, which I tried next.
The formula in the screenshot above is truncated, but here’s the complete formula ChatGPT returned:
It may be shorter, but it returned the wrong result by a factor of nearly 6. It also included 3 pairs of parentheses that weren’t required.
From this we can see that its knowledge of Excel functions isn’t as advanced as it appears. One way to simplify this nested IF formula is like so:
XLOOKUP([@[Reference station]],$A$4:$A$5,$B$4:$B$5,,0)
-[@[Applicable discount]]
+([@[Nature of Delivery]]="Consignment")*20
+([@[Transport Rate]]>10)*[@[Transport Rate]]
+([@[Credit days]]>30)*([@[Provision for Financial Charges]]-AGO/365*30*IntRate),10)
/FXRate
ChatGPT for Power Query Questions
Similar to Excel formula questions, ChatGPT is also ok at simple Power Query formula questions. For example, I asked it:
“How do you round to the nearest 0.5 in Power Query?”
ChatGPT got confused at first and gave me a formula containing Excel functions. But it returned the wrong answer even then:
I asked again and it got it right:
Of course, as a beginner you need to know what to do with the code above and while you can ask ChatGPT where to put the code, it’s unlikely to give you the answer you actually need. For example, I only wanted the formula to use in a custom column, so I needed this part of it:
The rest is redundant.
ChatGPT for Power Pivot Questions
ChatGPT did better with the Power Pivot DAX question I asked it. I found a question on our Excel Forum and copied and pasted it word for word to see how ChatGPT would fare.
I’m pleased to say it did quite well and even wrote the formula using the table and column names mentioned in the question:
However, I suspect that like Excel and Power Query formulas, as the questions get more complex, the answers get less reliable.
ChatGPT for VBA
ChatGPT also did well with the VBA question I found on our Excel VBA forum, returning an almost identical answer to one of our experienced VBA forum moderators:
However, I know it doesn’t always get it right, so don’t count on it being able to automate everything you do 😉
Things to Avoid Asking ChatGPT
I really want to love ChatGPT, and I do for some things but there are some things you should never use it for, well at least not with the current version, which is ChatGPT-3.
First, don’t get it to do math or check math results. It will give you an answer and assure you it’s correct, but unless the result is two digits or smaller, it’s unlikely to get it right, and even then, it might not.
I was horrified at the math results it returned (see screenshot below). It clearly doesn’t have a calculator built into its logic, which I’d have thought is pretty simple to do. Even my Google speaker gets the correct answer.
The bottom line is ChatGPT is a natural language AI tool, and it doesn’t have a built in calculator, yet. Maybe it’ll get one in later versions. Until then it should be taught to decline answering math questions.
Asking ChatGPT Leading Questions
If you give it a leading question, it will follow. For example, I asked ChatGPT if Excel has a BETWEEN function. We can see in the screenshot below that there is RANDBETWEEN function in Excel, but no BETWEEN function:
However, because I asked it in a leading way, it went along with me and gave me the answer it thought I wanted, even though it isn’t true. It even went as far as to make up the arguments and then explain them!
It’s therefore important to phrase your questions in a neutral way to ensure you don’t get a biased result. That said, even when I started a new chat with an unbiased question, it still assured me there was a BETWEEN function (maybe it was influenced by my previous question):
ChatGPT for Excel – The Bottom Line
ChatGPT is a bit like a politician, you have to fact check everything it tells you. Some of it will be right, some of it will be embellished and some of it will be plain wrong.
I’m sure with each iteration of ChatGPT and other large language models we’ll see improvements on the limitations I’ve covered here, and let’s hope improvements in math capabilities arrive soon.
can anyone clear my below doubt?
How can I highlight the duplicate numbers in different colors in one column?
If the duplicate number is between 1 to 10 one color
If the duplicate number is between 11-20 one color
If the duplicate number is between 21-30 one color
If the duplicate number is between 31-40 one color
If the duplicate number is between 41-50 one color
What is the formula for that?
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Just like when talking to humans, the AI needs some context, or prompting, to arrive at the answer you are expecting. Read through the first couple of articles at learnprompting.org to see what I mean. Your ChatGPT experiences will be much better for it!
P.S. I love the Excel Newsletter from myOnlineTrainingHub. I will never give it up, no matter how the chat bots evolve!
Thanks for sharing the link, Ivan! And great to know you’re a devout follower
ChatGPT replied me with a code sample as shown below to explan the class module but failed to fix the problems with red highlights on first and last lines on VBE. Could you please dig out for me where the errors existed?
Let’s create a BankAccount class with three properties (AccountNumber, Balance, and AccountType) and two methods (Deposit and Withdraw). We then create a new instance of the BankAccount class and assign it to the variable myAccount, set its properties, and call its Deposit and Withdraw methods to modify the account balance. Finally, we display the current account balance in a message box.
Public Class BankAccount
Public AccountNumber As Long
Public Balance As Double
Public AccountType As String
Public Sub Deposit(amount As Double)
Balance = Balance + amount
End Sub
Public Sub Withdraw(amount As Double)
If amount <= Balance Then
Balance = Balance – amount
Else
MsgBox "Insufficient funds."
End If
End Sub
End Class
With this class module, you can create new instances of the BankAccount class and use its properties and methods to manage the account balance.
Dim myAccount As New BankAccount
myAccount.AccountNumber = 123456789
myAccount.AccountType = "Savings"
myAccount.Deposit 1000
myAccount.Withdraw 500
MsgBox "Current balance: " & myAccount.Balance
Hi Julian, please post your question on our Excel forum in the VBA group where you can also upload a sample file/your code and we can help you further
done as instructed. Thanks a lot.
Thanks for the examples. I found similar issues with OpenAI when I was experimenting with it. You definitely need to double-check everything it says because it can just make things up. For example, I told it to “Explain how to make a Gantt chart in Excel” and this is what it returned:
1. Create a new spreadsheet in Excel.
2. Enter the project tasks in the first column.
3. Enter the start and end dates of each task in the second and third columns.
4. Select the entire data range and click on the Insert tab.
5. Click on the “Gantt Chart” option under the Charts section.
6. A Gantt chart will be generated in the spreadsheet.
7. Customize the chart to change the colors and other settings.
8. Save the spreadsheet.
Because of all the tutorials on the internet about how to make a Gantt chart using a stacked bar chart, I thought it would probably explain that method, but instead it just made up a fictitious feature. But maybe Microsoft could learn something from this and add this new chart type. 🙂
great idea for a new chart type, Jon! I suspect the AI has found examples of add-ins that create Gantt charts and it assuming all Excel users have this option. I don’t know how it will ever be able to distinguish between add-ins and built-in Excel features
I asked ChatGPT for a LAMBDA formula to calculate the moving range of a Dynamic Array, and I actually collected a dozen or so responses. The descriptions all sounded knowledgeable, but many of the results didn’t work. Some didn’t even use Excel formula syntax, some couldn’t be entered as written, and the ones that didn’t seem to have errors produced calculations that were definitely not moving averages.
GPT in, GPT out.
I suspect its lack of knowledge post 2021 is limiting its ability to write LAMBDAs for Excel. Well, I hope that’s the reason.
ChatGPT was really wrong with your formula
=SUMIFS(Table1[Sales],Table1[Year],>=&2000)
You specified both ranges properly, but the inequality condition has to be expressed as “>=”&2000 or “>=2000″. ChatGPT corrected this inequality without stating that it had done so, missing a teaching moment.
Worse, ChatGPT added a spurious additional range of A1:A100 to
=SUMIFS(Table1[Sales],Table1[Year],A1:A100,”>=2000″)
But it sounded so knowledgeable that even an expert like yourself missed it. Pity the poor beginner who cahn’t get ChatGPT’s formula to work.
Oh, yeah. I think that was the second or third time I asked it the SUMIFS question. I didn’t take a screenshot the first time and it lost the thread, so I had to ask it again. The first time I tested the result and it worked, and I didn’t notice the erroneous extra argument referencing A1:A100 in the answer I took the screenshot of! Good catch.