Excel and ChatGPT

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.

Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neil Blackwood, and today I wanted to talk to you about ChatGPT and Excel. Now, there's been a lot of coverage this year about artificial intelligence, and ChatGPT is one of the reasons because it's a chat-based interface that allows you to type in your questions and receive fairly long form answers from the artificial intelligence engine. So I thought I'd investigate it as an assistant for Excel. So basically if you have an Excel question, you could type it in and get an answer, so like an Excel help desk. What I did was I developed 10 questions that I wanted to pose to the AI. And five of them were pretty straightforward. I can figure out if it's a correct answer.

And the other five were more opinions. And so I thought that I'd review the more long form answers as well. The first two questions were on keyboard shortcuts. So again, they're pretty cut and dry. They either work or they don't. So the first one was how you can insert the current date in a cell, and it was correct. It used control and the semicolon. So you hold the control key down and press the semicolon. But what was interesting was that it also mentioned the today function. So when you use the control semicolon, you enter the date into a cell, and that won't change. It's a data entry.

But the today function will display today's date, but that updates because it's a formula. So it was interesting that it came back with extra information to what I had actually asked for, because I just asked for a keyboard shortcut. So yeah, so you can get more than you think you're going to get, which is a good thing. So it's actually bringing up extra information. The next keyboard shortcut was how you open the data validation dialogue using the keyboard, and it gave me two solutions. And again, both of those worked, and they involved the alt key. So with these ones, you press the alt key and you press D and L. You press these in sequence. You don't hold them down. So you press the alt, you press the D, and then you press the L.

And that's actually an old-fashioned way of opening it up. So that works in the really old versions of Excel. And it gave me another shortcut, one that works with the ribbon interface at the top of the screen, and that is alt A, V, V. So you press the alt, then you press the A, and then you press the V twice. That interacts with the ribbon to open up the data validation through the ribbon interface. When you press the alt key, on the ribbon at the top of the screen, you'll see a whole lot of letters and numbers displayed, and that allows you to access those particular features. So it was using that to activate the data validation dialogue. So both shortcuts work. So two out of two so far, so that's all good. Then I thought I'd try three formulas.

Okay, so the first one was rounding to 5 cents. Now, rounding to 5 cents, you can do that with the round function, that's typically what I use, but there's also an M round function, and that's what it recommended. So it used the M round function, and that allows you to specify a specific value to round two, so 0.05 in the case of 5 cents. So again, that worked, so happy with that. The next formula was a little bit more advanced, and I wanted it to extract the sheet name based on a formula. Now, that needs to use multiple functions together to get the result. And it got it right. It used the old fashioned way of doing it. There is a new way of doing it. I have covered the sheet name function in a few articles recently, so there's some examples there, but it used the old one that'll work in all versions. There is a newer versions now that will work using the text after function, which is a little bit simpler, but it created a formula that worked, so I was happy with that. So far, four out of four. Then I gave it a little bit more information. I had a list of numbers in column A, and I wanted to find the top five numbers and list them.

And it used the large function, which was valid. It used what's called an array constant or array syntax to get the top five. And funnily enough, it created a formula that listed the five top numbers in a single cell. It used the text join function to join the top five numbers together, and so they were separated by commas. So I thought that was an interesting way to do it. So five out of five so far. So all of the fact-based responses have all been correct. Now, I wanted to look at the five opinions. Here we go.

The first was, what are the top 10 Excel functions that accountants should learn? And it came back with a top 10 list. I wasn't totally happy with it because it had some functions that have limited uses for accountants. It did include the V lookup. That's a function that accountants use a lot, but it's been replaced by X lookup. So it hadn't actually picked up the fact that really X lookup now is a better alternative, or it could have listed them together, because in the list, it did include min and max as a single entry. And they're two separate functions, but they work sort of similarly.

And so it could have put V lookup and X lookup together and you use the one based on the version that you have, because X lookup is only available in the recent versions of Excel, whereas V lookup works in all versions of Excel. So I did respond back to the AI, because you can respond back to it, and I mentioned that x lookup is the more recent function to use for looking up. And it responded back that yes it is, and then that if you do have access to X lookup, you should use that in preference to V lookup. So that's correct. So that was okay. Some of the older functions, it did mention average and concatenate, so I wasn't that impressed by the top 10 functions, so I'd probably give that a half rather than a full point.

Okay, then I asked it for the top 10 features in Excel that accountants should learn. Now, these were pretty good. So out of the ten, seven, I would totally agree with the three I wasn't impressed with was Solver, Scenario Manager, and Goal Seek. Now, they're all good features, but accountants necessarily don't use them a lot. They used a lot in financial modelling, which accountants do financial modelling, but overall, accountants don't... I don't think Solver and Goal Seek are used a lot. It did include Power Query, which was good. And the one that did miss out was Power Pivot.

So it included things like pivot tables, conditional formatting, charts. So with the features, I'd probably give that another half as well because it got, say seven out of the 10 that I agreed with. So I'll give that about a half. So now we come down to some questions that are on topics that sort of split the Excel community. So what I was trying to do was to maybe get a neutral response to this because most people are in one camp or the other. So the first question was, should you use range names in Excel? I'm a proponent of range names, but I know a lot of people avoid range names because they think it adds sort of extra complexity to a file. The AI decided to list all of the advantages of using range names. It didn't list any disadvantages.

And so all of the advantages were valid, and so I would agree with that, mainly because it sort of backed up my belief in using range names. I'll give that one a tick as well. Now, when you use range names, there's a technique of capitalising the letters in the range names. Now, I've been doing that forever, and I wanted to find out if it recommended that you should. So the question was should you capitalise a letter in an Excel range name, and it came back and basically said you should. The reason is when you're typing a name in, if you've got a capital letter in the name, and when you press enter, if Excel recognises that name, it will capitalise the letter. So if you type it in a lower case, when you press enter, Excel will capitalise it.

Then that Excel has recognised the name. If you get a name error in a cell because you made a mistake typing it in, if you typed it in lower case, it will still be in lower case. It's a really easy way to pick up that you've made a typing error when you are entering the name. So that's why I recommend using a capital letter. So it agreed with me, so I suppose I'd better give that a tick as well. The final question, and again, this is another topic that does split the Excel community, is the indirect function. So I asked, should you use the indirect function? The problem with indirect is that it breaks the links that you can use for formula auditing. So if you're trying to track where a cell is coming from, if there's an indirect function in the middle of that chain, if you like, then it stops at the indirect function. The indirect function is probably the worst named function in Excel. It allows you to create a reference to a cell or range using text.

And so it can allow you to create some fairly creative solutions to problems. But, as I mentioned, it does break that ability to follow and track where values are coming from. It does have another issue in that it's what's called a volatile function, and that just means it calculates every time Excel calculates. Not all functions do. Most functions only calculate when something in their range changes, but the indirect function calculates every single time. That's less of an issue now. Because our PCs and laptops are so fast, that speed issue isn't really valid these days. If you use a lot of indirects, it can affect your speed. But as I said, with the speed of PCs these days, it's less of an issue.

The response to this one was you should. If there's any other way to do a calculation, you should do it that way. And if there's no other way other than indirect, then use the indirect. So it was sort of saying... And it's something that I use indirect as a function of last resort basically. So if I can't solve a solution with the other functions, then I will use the indirect, which is sort of what it said. This one did come up with a list of pros and cons for indirect. So it had advantages and disadvantages, and there was three of each. So it sort of sat on the fence a little bit on whether you should. When I did a question on this earlier when I was writing the initial article, it actually said to use it sparingly, which I thought was a really good response.

So I think I'd give that a tick. So overall, I think I'm giving it about a nine, probably, out of 10 for use in Excel. The functions were okay. The features were not too bad. I said there was about seven that I agree with out of the 10 out of that. So the thing to keep in mind is that the AI engine is being constantly updated, so keep an eye on it. And probably, it's going to improve, so we might get closer to a 10 out of 10 as an Excel help desk. The other advantage with ChatGPT, it's not just for Excel. It handles a whole lot of different... It's a general type of AI, so you can ask it any sort of question you like and it can help you. And even if it can't provide the full solution, it might be able to suggest or give you some ideas that he hadn't thought of. ChatGPT has a lot of uses apart from Excel, so it is something to add to your toolbox, and you can use it for Excel as well as asking questions on other topics. Thanks for listening.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode and we hope you can join us next time for another episode of Excel Tips.