Go Back

What’s New CFI: AI for Formulas in Excel

June 18, 2024 / 13:02 / E14

Join us in the latest episode of the “What’s New at CFI” podcast as we discuss artificial intelligence and how it can help users to manage complex formulas in Excel. In this discussion, we shed light on the increasing demand for AI integration in financial analysis.

We discuss practical examples of using AI tools like ChatGPT and Gemini to simplify and optimize Excel formulas. Learn how these AI models can break down complex formulas, suggest more efficient alternatives to nested IF statements, and even offer step-by-step solutions for your tasks. Discover the advantages of the paid version of ChatGPT and unlock the secrets to supercharging your AI queries with expert techniques!

Tune in for practical tips and encouragement to get started with AI in Excel. Whether you’re a novice or an advanced user, there’s something for everyone in this insightful conversation.


Asim (00:14)
Hello and welcome to the What’s New at CFI podcast. I’m Asim Khan and I’m joined today by my colleague Sebastian Taylor. Welcome Sebastian.

Seb Taylor (00:23)
Thanks for having me.

Asim (00:25)
Always great. So you’ve recently published a course entitled AI for Formulas in Excel. And I was wondering, you know, what was the motivation behind this? Were we getting requests from our users on how they might integrate artificial intelligence into their work?

Seb Taylor (00:44)
I think there’s a couple of reasons really. One is, as you just said, the demand for the course, but I suppose the other side of it is why is there the demand for that? And I guess when you think about the tools and techniques that analysts can use in their day -to -day job, there’s various different tools that people can use to make themselves more efficient and more productive. But by far and away, the tool that has the most potential to do that is all of these AI tools.

ChatGPT being the most obvious one and most well known of those. But there’s plenty of other tools out there that can either operate with a language interface like that or there’s AI tools that can actually do tasks for you.

Asim (01:29)
That brings up an excellent transition point because you went through some worked examples using both chat GPT and Gemini. And we had a bit of a comparison between the two and what they can do and the types of results that they crank out. Could you walk us through just a couple of examples on what you had the AI do in this course?

Seb Taylor (01:36)

Yeah, of course. One of my favourite examples is trying to break down complex formulas. So the number of times you open up someone else’s model in Excel and you dive into a couple of formulas and…

maybe they haven’t followed best practice per se or broken down formulas in a simple way. And so you’re left looking at a formula that’s four or five lines long and you really have no idea what this thing does. And sure, you could sit there for an hour and try and break it down and work out where the inputs come from and what exactly this formula is doing. Or you could just give it to one of these AI models and ask it, what is this formula doing? And it will give you a pretty good answer

right off the bat and at least it will give you enough information to point you in the right direction and save you probably half of that time.

Asim (02:47)
Right, now I think there’s, you’re absolutely correct, there’s this knee -jerk, almost default response to writing a formula to a problem you don’t totally fully understand, and that’s to use if statements, like these huge nested if statements, right? And we talk about this, and our colleague Duncan talks about it in some of his courses, where you should think of using perhaps max and min functions rather than these long if statements.

Seb Taylor (03:04)

Asim (03:17)
Could these AI then, could you describe a problem to the AI and have it kick out the most efficient formula that you should be using?

Seb Taylor (03:27)
Yeah, exactly. So that’s probably the next step in that journey of what does this if statement do. The next thing you could ask is, well, is there a more efficient way I could write this formula? And so, yeah, you mentioned min and max there. Definitely a solution depending on what you’re trying to do with the if statement. Another common one is switch, which we can use if you’ve got multiple different conditions that you want to work with.

And yet the AI tools are great at offering up ideas and potential solutions that might give you inspiration to solve a problem in a different way.

Asim (04:06)
Right, and along those same lines, just going a little bit deeper into it, you used ChatGPT, the free version, and also Gemini, and then you did some exercises using the paid version of ChatGPT. What advantages would the paid version offer? Keeping in mind we’re not marketing it, but it had some additional functionality which I found useful.

Seb Taylor (04:30)
Yes, and I’ll caveat this by saying that ChatGPT is changing so fast that this changes every month. But generally what the paid version of ChatGPT is giving you is a greater ability to customise and remember your specific tasks. So if there’s a task that you do four or five times a week…

Instead of asking ChatGPT from a blank page each time, I want you to do this task, I want you to operate in this role and give me the answer in this format. All of that information can be remembered as part of a task in what we call a custom GPT. You can think of it like a pre -programmed bot for a particular task. And so…

The paid version is giving you access to that functionality so that you can save yourself a ton of time writing the same tasks over and over again.

Asim (05:29)
That’s really helpful. And I think, and you know this very well, there are other GPTs on the site that users have created and basically contributed as intellectual property that’s free once you’re in the paid version. Is that correct? You can hunt around for something that you may not need to reinvent the wheel is what I’m saying. You might find somebody else’s GPT would do the job for you.

Seb Taylor (05:52)
Yeah, exactly. So even on the free version, actually, now, as long as you click on the 4 .0 model version of the EarthChat GPT, you can interact with other people’s GPTs, but you can’t then create your own in the free version. And as you say, that means that you may save yourself a lot of time because someone else has already created a GPT to do this. Yeah.

Asim (06:18)
And is there any qualitative difference that you’ve noticed thus far? Major qualitative difference between chat GPT and Gemini. So, or should people be indifferent as to which one they use?

Seb Taylor (06:31)
Yeah, it’s interesting. I find it very much depends on the type of question that you’re asking. The questions that we’ve tended to ask in this particular course, AI for Formulas in Excel, we tried to keep it super simple. The course is really designed for people who are using Chat2Beauty for the first time. And so because we’re asking relatively simple questions, I found both of them performed pretty well.

If you start to ask more complex questions where actually ChatGPT or Gemini need to come up with a step-by-step plan to solve something, then I find that ChatGPT is better at those sorts of tasks. I find Gemini is quite good at summarizing information. I know a lot of that model was built or trained upon the information it has from Google search. So…

It is exceptionally good at summarizing information from different websites, but I find it’s less good at giving you that kind of interactive step-by-step solution that ChatGBT might be good at.

Asim (07:41)
Okay, fair enough. And you had spoken of certain best practices in querying these various AI engines. Could you tell us what those are?

Seb Taylor (07:47)

Yeah, of course. I think the first one I would say is be as specific as possible. And this is a big lesson that you learn as you start to work with these AI tools is they will, I mean, it’s a computer at the end of the day and it’s trying its best to give you what you asked for.

And therefore if you ask bad questions you will get bad answers and so you realise the value of asking for exactly what you want and saying exactly what you mean so be specific I think being concise also helps because if you’re not concise you end up with so much information in your initial paragraph

that it doesn’t know where to focus its attention. It doesn’t know what are the most important parts of what you’re saying. So be concise and stick to the important parts. And finally I would say try and break down your tasks into individual steps. So.

And it’s often hard to realise maybe that your task has three steps instead of one. But if you can really think about, okay, what’s the process in this calculation? And how can I better break that down? Ask it one thing at a time. I think it will generally give you better answers.

Asim (09:10)
That’s super helpful. So you just, you can’t like write an old essay on something. And so it’s better to be specific and concise. These are the two big things. And do you have to, does it help to identify yourself in terms of what kind of user you are? Like, you know, you can say I’m a novice at this and I’m, or advanced at this.

Seb Taylor (09:31)
Yeah, that’s definitely a tip you hear a lot and I think in certain situations that can help. I think particularly where the context is not obvious. For example, in this course we are working in Excel and we want to write formulas and ultimately formulas that are correct and that work. And so saying that you are an analyst working in Excel doesn’t necessarily add anything…

to its intent to provide you a formula that works. Whereas if maybe you’re talking about a task like creating a content strategy for your new business, there’s all sorts of directions you could go with that. And so actually saying act as an experienced marketer

in that case, would help it focus on giving you an answer that an experienced person would give you.

Asim (10:30)
Okay, great. And so in closing, are there any pieces of advice you can give or encouragement, I should say? Because I think we all need to try our hand at artificial intelligence. It’s certainly not going away. So what can you tell our learners about getting started?

Seb Taylor (10:50)
I would say just get stuck in. Just start using some of these tools. There will be tasks that you find that they’re exceptionally good at and there will be tasks that you find that they’re useless at. And you’ll have some moments of frustration where they really won’t give you the answer that you want even though it seems so simple to you. But the more you practice like that, the more you’ll get a feel for what they’re good at and what they’re not. And you’ll start to understand where you can use them to save yourself time.

Asim (11:18)
And I suppose by the same token, you’ll become better at prompting the AI. Okay, great. So I encourage everyone to check out this course, AI for Formulas in Excel. Sebastian, thank you so much for your time and we look forward to seeing you again.

Seb Taylor (11:24)
Absolutely. Yeah.

Thanks for having me.

0 search results for ‘