Hi ,

Less Than 24 Hours Left . Get 20% off all of our courses. That includes Course Renewals and Download Upgrades for existing members.

Sale ends at 8pm Pacific time, Thu Feb 16th.

Exciting News : Power Query for Mac Now Available

Microsoft 365 users for Mac (running version 16.69 (23010700) or later) now have the same time saving Power Query tools us PC users have had for years. Check out the announcement here.

See how Power Query can streamline your work by automating laborious tasks in this video.

With 20% off until February 16, now is a great time to get up to speed with our Power Query course

Quick Tip : Avoid Calc. ERRORS - Scale Numbers in Excel the RIGHT WAY - $K, $M, $B

Scaling numbers to thousands, millions, billions etc. in Excel is often done the wrong way, which can lead to spreadsheet errors with massive ramifications. In this video I show you the right way to scale numbers so you won't get caught out.

Note: if the image above isn't animated, you'll find it on our social media channels:

ChatGPT for Excel

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 Video

Watch on YouTube

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.

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 to 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 to 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:

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.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub