EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
AI in Excel is a subject that I’m being asked about more and more regularly.
On the one hand, it’s a somewhat scary idea that artificial intelligence can create formulas, macros and more in the blink of an eye – yet I can’t help but find myself being fascinated and cheering it on (under the right circumstances).
In this article, I’ll talk you through how to get the most out of implementing AI into your Excel usage.
There are actually a surprising number of scenarios where AI could help you ‘crack the code’ when it comes to your spreadsheet.
As AI is still fairly new, I’m still experimenting with it myself, but below are a couple of ACE uses I’ve found for it already!
With just a few clicks, you can ask AI to create formulas and macros to help you get your sheets functioning how you need them.
(I’ll show you how I got AI to write a formula for me just a little bit further down this article.)
Excel formulas and macros sometimes misbehave which causes headaches and frustration.
AI comes to the rescue by helping you pinpoint the errors. It can be used to analyse problematic areas and offer suggestions and solutions, making troubleshooting a breeze.
Excel functions can be intimidating, but AI, with its encyclopaedic knowledge, can break down these functions into bite-sized pieces, explaining their purpose and usage.
You’ll no longer scratch your head wondering what ‘VLOOKUP’ or ‘IFERROR’ really mean.
Testing your formulas and macros often requires data to work on. AI can generate dummy data sets tailored to your specifications, allowing you to test your Excel creations comprehensively.
Plus, these dummy data sets can also be used for presentations, showcasing your Excel awesomeness in a visually appealing manner.
Ever wondered how to add context to your numerical data?
AI can generate descriptive text, explaining the results of your spreadsheet in a language that anyone can understand. Now, your data will not only be accurate but also easy to interpret and share with others.
Now that we’ve discussed some of the ways AI can help you, let’s take one of these examples and walk through it together.
I’ve used Open AI (also known as ChatGPT) v.3.5, which is a free AI tool to generate this example. I’m going to walk you through each of the prompts I gave, my intention behind the prompt, and the responses I was given, to show you how I developed a formula.
I started by asking the AI the following question:
“Please write a formula that returns the word ‘OK’ if the value in cell B1 is less than 50, the word ‘Check’ if the value in cell B1 is between 50 and 100, and the word ‘Authorise’ if the value in cell B1 is greater than 100.”
The AI quickly produced the following formula:
=IF(B1<50, “OK”, IF(B1<=100, “Check”, “Authorise”))
This works perfectly! But I want to take it a step further.
The IF formula provided works perfectly for the needs that I described in my original prompt. But now I want to make things more complex. So I asked the AI for the following:
“How would this formula need to change if I only wanted to apply this to items assigned to the ‘Travel Expenses’ category which can be found in column A?”
In the blink of an eye, the AI provided me not only with a modified formula but also with a breakdown of the process the formula goes through to check my query. This is the formula it suggested:
=IF(AND(A1=”Travel Expenses”, B1<50), “OK”, IF(AND(A1=”Travel Expenses”, B1<=100), “Check”, IF(A1=”Travel Expenses”, “Authorise”, “”)))
Once again, this formula works perfectly BUT this time I happen to know that there’s another way that this can be written.
Now, I already know the answer to my next question, but not everyone would, so I recommend asking this regardless of your prior knowledge to make sure that your results are the most efficient. Next, I asked:
“Is there a more efficient formula that can be used here instead?”
As we know – there was. The AI offered a new formula using the IFS function in Excel, which would allow me to evaluate multiple conditions in a more structured way. The new formula it provided was as follows:
=IFS(A1=”Travel Expenses”, B1<50, “OK”, B1<=100, “Check”, B1>100, “Authorise”, TRUE, “”)
Once again (shock… not), this formula works perfectly, but I still happen to know that there’s another alternative. So I decided I was going to push it one step further.
At this stage, I have a specific formula in mind that I’ve been anticipating as a result. You might not and if that’s the case that’s fine – everything that’s been suggested so far will work!
But I decided to give my AI the following, more specific prompt:
“Can I just ask the formula to ignore anything that does NOT have “Travel Expenses” in column A?”
=IF(A1=”Travel Expenses”, IF(B1<50, “OK”, IF(B1<=100, “Check”, “Authorise”)), “”)
THIS is the formula I’ve been expecting the whole time and once again it works perfectly.
However, I could have come to this conclusion on my own in a much quicker pace of time (though I appreciate that for many people this wouldn’t be the case), so whether AI was necessary or not is still up for debate.
So what do you do if your AI has given you multiple formulas and you don’t know which to choose?
The answer is simple – I’m going to let the AI pick for me:
“Which one of these is the best formula to use?”
What’s ACE about the response in this instance is that the AI doesn’t just pick a formula for me. Instead, it gives me a list of the pros and cons to help me make an informed decision about which formula to use.
I’m now able to make an educated choice between the formulas and pick the one that’s best suited to the scenario in which I’m going to implement them.
Now that I’ve shown you how simple it is to get answers from AI when it comes to Excel, here are some additional handy tips if you haven’t used Open AI before.
Every time you generate a new chat, it’ll automatically save to the menu on the left-hand side so you can revisit it again and again. If you want to come back and edit or continue the conversation, just select it from the menu and continue to send prompts. You can also rename or delete by pressing the pencil and bin options respectively.
If you don’t want your chats to be used by the AI for future learning (keeping them more unique), then click the three dots alongside your username at the bottom of the left-hand menu.
Once in settings go to ‘Data controls’ and turn off ‘Chat history & training’.
Ok, so this one isn’t a tip or a trick per se, but AI does have a (sort of) sense of humour. For example, when I asked it to tell me a joke about spreadsheets it said:
“Why did the spreadsheet stay up all night? Because it had too many rows to count!”
Which admittedly isn’t very funny at all, but give it time, I’m sure it’ll get there – everything else was perfect!
While AI is incredibly powerful, it’s essential to remember that its knowledge might not always be up-to-date with the latest Excel developments.
ChatGPT for example only has a knowledge base up to 2021 – so any releases or changes to Excel since then won’t be accounted for in the answers it gives you.
Always double-check the accuracy of the answers it provides – A quick test ensures that your Excel solutions are foolproof and ready to impress.
EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
