Have you ever built an Excel formula? I mean really built one? Even the most seasoned Microsoft Excel users can spend hours developing their spreadsheets to calculate and show their data the way they want. While there are classes and countless YouTube videos you can watch to learn how to do specific functions, all that research takes time and tweaking in order for it to fit your spreadsheet’s specific data.
I’ve been using AI assistants to create the complicated Excel formulas I need to automate the reporting and display of data that is requested of me on a daily, weekly, and monthly basis. It’s been quintessential for me as I can prompt the AI in regular English for it to automatically translate it into an Excel formula. It makes the hundreds of rows and columns of data that I’m trying to make sure are covered by the formula, much less daunting.
Of the numerous applications that AI assistants are used for today, I will explore their capabilities in generating Excel formulas. I will discuss the advantages of leveraging these tools and how you might expand your usage of these AI assistants for other applications.
What are AI assistants?
There’s a new tool out on the market, you may have heard of it, AI. AI (Artificial Intelligence) in the simplest explanation is machine learning that takes in a prompt, gathers data from the internet (or other sources if provided), and generates a plain English response. It has grown exponentially and integrates into almost everything we encounter today. Companies are using it for automation, proofreading, chatbots, phone systems, etc. But there’s a specific task that I don’t think I’d be able to live without now.
There are a handful of AI assistants out in the world today; seems like there’s a new one every month. If you’re part of a business you’ve probably encountered Copilot (Microsoft), Gemini (Google), or ChatGPT. You may have even heard of Grok; X’s (Formerly Twitter) AI assistant. These are all capable of answering your questions about Excel, but Copilot, as it’s naturally integrated into the Microsoft 365 platform, will probably be your best bet.
AI formula generation
AI can take a common language and translate it into a functional Excel formula. Instead of trying to find what function might best suit your use case and then trying to intertwine it with other functions to pull the exact result you’re looking for, you can simply talk to the AI like you’re talking to a coworker.
Example 1:
=IF(AND(E2<>””, F2<>””, G2<>””), “Completed”, “”)
Above is a fairly complicated formula, that gives a result of “Completed” based on the contents of the three mentioned cells. I could have spent 20 minutes looking up how the IF function worked and then tried to get the AND function to work inside of that, but instead, I turned to AI.
I simply asked AI to “Give me a formula that enters “Completed” in Cell H2 if E2, F2, and G2 are not blank”. And in five seconds, I had a working formula that I could test and verify in one shot.
Example 2:
=COUNTIFS(‘Firewalls’!$J$2:$J$430,”201F”,’Firewalls’!$K$2:$K$430,{“n/a”,”Ordered”,”Scheduled”})
This formula is complicated. It’s using a COUNTIFS formula, looking in a different sheet for a certain value that also has a certain value, then another value, then another value, and then laying out the results in 3 adjacent cells in a different sheet. I’m not going to lie; I created this formula before the AI tools were available to me and it took me the better part of an hour to figure this out. And that’s with already knowing how this function works!
Utilizing AI today, I could simply ask, “Give me a COUNTSIFS formula that counts the number of cells in Column J if the cell contains “201F” and then checks for n/a, Ordered, and Schedule in the adjacent cell in Column K. Column J and K are on a different sheet called Firewalls.”
This did take me a few minutes to figure out exactly what I wanted and how to phrase it, but it saved me much more time than trying to research and figure it out myself.
Copilot is directly integrated into Excel in the M365 Environment. Some of the other features directly built into Excel, per Microsoft’s site, include, report generation, trend identification, data cleaning, smart suggestions, and that list will only continue to grow as AI learns and develops. Utilizing these tools and features could result in:
- Increased efficiency through automated generation and suggestions
- Improved accuracy through referenced and researched results
- Scalability through compilations and auto-review of data
- User-friendly through simple call-and-response communication
Use your intelligence to utilize artificial intelligence
In this day and age, with all the technology at our fingertips, it seems like there’s nothing we can’t do, and AI is further accelerating that. Whether it’s for work or for personal endeavors, think about how AI could be integrated and support your operations—while always making sure to use it ethically and have human checkpoints in the system as well, especially since the technology is so new. However, AI is only going to learn and develop with time, becoming increasingly relevant, so you should do the same and try to learn and develop your usage of AI at the same time.
