AI for Formulas in Excel | Course Module

Key Takeaways

When chat GPT was first released, there was an initial panic that AI was going to take all our jobs. But that was soon replaced with this realization that AI isn't going to change your job, but an analyst that is using it might. And so with AI being the leading technology that's going to help you be a more efficient analyst, it's important to understand how it can help you in your role. And in this case study in particular, we're going to look at how we can use it to make us more efficient at working with formulas in Microsoft Excel. One thing you'll quickly work out as we work through these different problems is that ChachiPT is not relevant in all situations. It's certainly far better at some things than at others. So after each exercise, I'll give you a few of my thoughts on what parts of the exercise maybe are more relevant to AI than the others. We're also going to use different AI tools. We're going to compare the outputs from Google's Gemini to OpenAI's Chat GPT and see how they perform. We're going to look at some prompt engineering tips specific to formulas. So, we can go from something like this, which actually doesn't return the answer we want, to something much more brief, which gets us straight to the point. So join me in this quick case study where you're going to use ChatgPT and Google's Gemini to see how you can solve formulabased tasks in Microsoft Excel. You're going to be doing things like simplification of formulas, explanation of what formula logic is doing, and quickly using it to solve errors in your formulas. Let's get started. Chat GPT and Gemini are both language-based models, so they're generally pretty good at summarizing, writing, or ideuliating with text. Often we forget though that coding languages and formula languages are languages in their own right. They have a specified syntax that we have to use with functions, logic, and arguments that have to be used in certain places. And so these language models that we have available to us do seem fairly capable at interpreting these formulas. We're going to be working through a few formulabased tasks in Microsoft Excel so that you can start to understand which tasks these AI models are good at and which they are not. Use this as a chance to experiment. We're going to be using two different tools and I fully expect you to get different answers to me. The goal here is to learn how to use these tools with the right prompts in an Excelbased formula environment. We'll work through tasks where we don't quite know what formula to use in advance and we need some inspiration. We'll work through three examples where we have complex formulas that we just don't have time to evaluate and we want someone to explain to us what that formula is doing. We'll have formulas with errors that need to be corrected. And then we've got three more challenging examples using dates, text functions, and referencing data from other sheets. All of which chat GPT can help us do if we're not quite sure where to start. We're going to be using two different language-based AI models throughout this case study. The two I want you to set up are Chat GPT and Gemini. Just search for ChatgPT, click on the link from OpenAI and follow the instructions to set yourself up with a basic account. Now, inside ChatGpt, if you have the paid version, you'll be on ChatGpt 4. And if you have the free version, you'll be on 3.5. They do have some variations in terms of the functionality. And so, I'm going to be testing a bit of both throughout the course. Obviously, use the one that you have. You'll still be able to follow along. If you're looking for Gemini, search the same thing, Gemini. This is Google's version of ChatGpt. And again, follow the links. You should just be able to sign in with your Google account. And there you'll find yourself within the Gemini platform. Get yourself set up in each of those, and I want us to end up with one tab for each so that you can keep testing things in the different tools. One of our learning goals today is to work out what each of these tools is better at. And I want you to have that experience so that you know which tool to go to for what tasks. If you want to follow along with Excel, Chad GPT, and Gemini, you'll need to download this one Excel workbook before we begin. In it, you'll find one tab per exercise that we're going to work through. In each exercise, just look at the tab that I'm working on before you begin. As we go through each of these tasks, I may suggest that you watch along with what I do first so that you can learn from my mistakes or I may simply suggest that you have a go yourself. Either way, this is designed to be interactive and for you to have a go at these problems in chat GPT and Gemini and to learn which tool works for you. Whichever approach you choose to go through this course, after each question, we will review possible solutions. we're almost certainly going to get slightly different answers, and that's fine. That's part of learning how these tools work. For each task, we'll ask which part of the task specifically was the AI useful for. And most importantly, we're going to consistently look at how we can engineer our prompts to get us to the right answer more quickly. Before we dive into the first exercise, I want to quickly recap the top tips we gave you on some earlier AI content of how to interact with these language-based AI models. The first of those is to be as specific as possible in terms of what you're asking. Better questions will lead to better answers. And to help us be specific, it can help to break down our queries or our questions into stepbystep prompts. Instead of throwing an entire long task at our AI in one go, we might be able to break it down into different steps so that each one is more specific. It can often help to give our AI assistant a sense of identity. Are they a primary school teacher? Are they a data analyst or a BI engineer? Or are they a professor of statistics perhaps? Once the model understands what role they are supposed to play and also what role you as the audience want to play, it gives it a better sense of the type of language and the tone and the complexity of the solution that it's going to give you. For data analysis tasks in particular, it's going to help to define what format you want your results in. Do you want it as a list, as a table, or as a paragraph, for example? Remember that using these AI tools is not like doing a Google search. It's not a case of one question, one response. With these AI assistants, it's more like a discussion. Put in your initial question, it'll return something back and you can work with it to help improve the solution. The last thing I want to remind you of with these AI tools is security. Depending on the tool you're working with, your account type, and your settings, any data that you put into that AI might or might not be used to train the AI. And so that data may end up somewhere else at some point. So be very careful if you're working with confidential company data or data that you simply just don't want to share. So that's a super quick recap on what AI is and how best to use it. Let's dive into the first example for data analysis in Excel. Continue learning. Join CFI today.