Chris Hatzis:
Excel Tips, a CPA Australia podcast.
Neale Blackwood:
Welcome to the Excel Tips Podcast. My name is Neale Blackwood, and in this episode we're going to look at the hot topic of artificial intelligence, AI, and more specifically, Excel and Copilot. So Copilot is Microsoft's AI platform, and with an extra subscription, so over and above the subscription that you pay for the Excel and Word, et cetera. So with an extra subscription, you can get AI built into Excel. So that means you can get Copilot to actually build spreadsheets for you.
Now, something to keep in mind is that the AI landscape is changing rapidly, so probably faster than any other computer-based software has changed. And so a lot of screens and terminology are all developing, so things change pretty quickly. As an example, when I wrote the article, one of the images has since changed in terms of the terminology used. So things do change quickly, so be aware of that.
So if you want to know if you have Copilot in Excel, look down the bottom right-hand corner of your Excel screen and you'll see the little Copilot icon. The other place you might see it is in the home ribbon in the top right. So that's actually where you can place it. If you right click the icon in the bottom right, one of the options is to actually put it into the ribbon. So you work with Copilot through a task pane, so that's on the right-hand side of the screen, and you use the normal chat interface.
The difference here is that there's three different modes that you can use for Copilot. So you can use Copilot in edit mode, which allows you to build spreadsheets from scratch. It also allows you to edit the file so you can get it to change formulas and functions and even get it to change formats. There's also a planning mode where you can ask it to actually specify what it's going to do before it does it, and then you can iterate with Copilot to work out exactly what you want it to do. And there's also the chat interface, which is the standard AI sort of question-and-answer interface.
Now, recent updates to Copilot in Excel have added a lot more personalization. So the personalization allows you to capture a number of rules that you might want Copilot to follow every single time. And there's also another set of rules that you can specify just for the workbook. Check out the companion video that takes you through all of the instructions on how to set up these personalizations. The rules for the workbook are in a specially named sheet called Dot Rules, with a capital R. And in column A of that sheet, you can specify all of the rules for that particular workbook.
Now, you can use formulas in that column, so you can actually build some interaction with the rest of the file, and you can have inputs that you might change that will update the rules that you want to use. As an example of some of the standard rules that you might want to apply, you might want to use centre across selection instead of the merge cells format, and you want to use XLOOKUP instead of VLOOKUP.
In terms of the editing, there are some limitations at the moment. So as I'm recording this, the limitations for Copilot are VBA, so macros. So Copilot can't access the VBA code or change it, so you might have to use some copying and pasting to actually make your changes.
Copilot can help you write your VBA code, but it can't actually put it back into the VBA window, so you need to do the copying and pasting. In a similar fashion, Power Query. At the moment, Copilot can't access Power Query code, but again, it can help you write it. So again, copying and pasting. And lastly, it doesn't really work with protection, so sheet protection or file protection.
Copilot can be an excellent Excel assistant, but you've got to remember that when you build a file using Copilot, you are the one who must take responsibility for it. You need to check it. You can get Copilot to build in validations to make that a little bit easier, but you do need to make sure that you check Copilot.
Because even though you can specify the rules, Copilot doesn't always follow them. So you need to be careful so that the files you create are doing what they're supposed to be doing. Now, in terms of assisting you, you can get Copilot to explain formulas to you, you can get it to explain a file to you. So if you get given a file by someone else and you're not quite sure what it does or how it works, you can ask Copilot to explain it to you and it can go through, analyse the file and come back with how it's working.
In the same vein, you can ask Copilot to create instructions for you. So you might've created a file and you need to add some instructions to it. So Copilot's pretty good at that. If you've got some formulas or functions that you don't really understand, Copilot can explain them. It can teach you about it. The same way with VBA, so Visual Basic for Applications, so macros.
Copilot's pretty good at writing VBA code and also explaining it. You can ask Copilot to find things for you or identify things, so maybe outliers in your data. Keep an eye out for next month's podcast and article where we'll be looking at how you can use Copilot to do some data analysis.
In the companion video, at the end, I did a demonstration of Copilot creating a file for you. And I did this four separate times with exactly the same data and exactly the same prompt, and I got four very different results. So that's one of the things you've got to realise that when you are dealing with something like a macro in Excel, you'll get the same result each time. The same with a formula or a function, you can rely on it. And as accountants, we've sort of gotten used to relying on Excel.
Now, the problem with AI is it changes. So nothing can change in the inputs, but the outputs can change simply because of the way it works. So the AI is basically a probability engine, and because of that, it seems to change a lot. Be very, very careful when you're using Copilot to build something from scratch.
You do need to check the formulas. So the four different results, I actually... The final result is the one that I've got in the companion video. The first time I ran it before, I actually tried it before I started recording, and that worked quite well. So I was quite confident that it would work when I was recording the video.
I was wrong. It didn't actually finish, and so I didn't use that one. And then I tried it again and I got some different results. And then I tried it again, and that's the one that I actually went with in the final video. So you will get varying results, especially if you're starting from scratch and building something with Copilot.
If you're doing anything with words, Copilot is really good. So writing instructions, maybe documentation or writing a narration about this month's results, Copilot can be really useful. At least it'll get you a first draft and speed up the process that way.
Another thing you can do is use one AI system to check another. So Claude in Excel is supposed to be really good. I haven't tried it yet, but you could get Claude to check Copilot and Copilot to check Claude. And that way you can get some feedback and that might save you a little bit of work as well.
If you're after a good book on AI, I've just finished one called Fair Game by Rob Collie. He's an Excel guy and a Power BI guy. He's also worked with Microsoft and he gives you a fairly honest view of AI. The book has a positive view on AI, and I must admit my views on AI are a bit of a roller coaster.
One thing I would suggest with AI is rather than getting AI to do automation for you, so like asking the AI to do something, consider getting AI to help you create code. That might be VBA code, it might be formulas and functions, could be Power Query code, or even Python in Excel code. That way, you don't need AI to actually work with the automation.
So Excel's had automation with VBA for decades, and that is really reliable. And you can get AI to write the VBA for you. One of the problems in the past with VBA was that you might get someone who's really good at VBA in your organisation, but when they leave, there's a big hole and the VBA they've written might not be easy to maintain.
Where now you've got AI that can help you maintain the VBA, write the VBA, update the VBA. And so you can create macros that you can then keep going using AI, but it's actually running as a macro rather than as AI, which requires the internet. And also can be a little bit slower than if you've automated something with Visual Basic. And again, I use macros a lot, but there's a lot of automation opportunities in Excel now with Python and with Dynamic Arrays and Excel's just general functions, formulas, pivot tables, Power Query, all of that stuff, AI can help you write.
And if you actually build the automation into Excel rather than to AI, then it can be a little bit quicker and a little bit more reliable. So don't think you've missed the boat with AI. There's a lot of posts on LinkedIn about how people are automating all of these processes, but I think you'll find that most businesses are using AI in a fairly basic way at the moment. They're sort of building up their skills, which is what you'll need to do.
And as I mentioned, AI can make mistakes, so you do need to build that checking and validation into your processes. And always remember to follow your organization's policies on AI in terms of confidentiality and the sharing of information. I hope you found that useful. Thanks for listening.
Jackie Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
