If you've ever found yourself lost in a maze of nested formulas, scouring the web for solutions that only half-answer your question, or watching your tenth YouTube tutorial of the day just to figure out how to implement a single function into your specific task: you're not alone. Excel is powerful, but learning and mastering its formulas has traditionally been a slow and often painful process.
That is, until ChatGPT and other Large Language Models (LLMs) came along.
In this post, we'll explore how GPT is transforming the way we use Excel. We'll look at what used to be the norm, how AI is flipping the script, and walk through a real-world example using the SUMIF function to show how GPT can serve as your new Excel buddy.
The Old Way: How We Used to Learn Excel Formulas
Before AI, learning Excel formulas meant using one or more of the following:
- Textbooks that buried useful insights in walls of text
- Microsoft official help articles which generic examples that didn’t apply to you
- YouTube videos that meandered for 10 minutes before getting to the point
- Forum answers with cryptic advice, like "just wrap it in an IFERROR", often aimed at experienced users and so lacking the handholding required to help you get there
It was easy to end up with partial understanding, or worse: copy-pasting formulas you didn’t actually comprehend, setting yourself up for a headache later when they stopped working!
Enter LLMs: A Game-Changer for Excel Users
GPT has changed the game by making the process of gaining formula knowledge conversational. Need to know what a formula does? Ask. Need to troubleshoot an error? Just describe the problem. Want to combine two functions in a new way? GPT can walk you through it step by step. Want help completing a formula on YOUR worksheet? Let it know what the cell references are for your task and it will complete the formula for you.
Now when working with Excel, you can have a new relationship with the software when ChatGPT is sat beside you:
- Plain English explanations of functions and syntax
- Step-by-step formula building, guided by your needs
- Error debugging with clear reasoning
- Alternative approaches or optimizations
- Creative combinations of functions to solve complex tasks
Case Study: SUMIF and the Power of Pairing with GPT
Let’s look at a specific example - summating some data in a range for a subset of the dataset: let’s say you need to know the total Sales for Apples in the following dataset:
Now, I could jump you straight to the solution which is to apply the SUMIF() function. However, I knew this when I started. You may not know what to do next time. Ask GPT: “how do I add up the sales figures in column B for a subset of products listed in column A?” - see what response you get!
There are multiple approaches, so let’s say for the sake of example that SUMIF was the one proposed and the one you choose to use.
If you are confident in using Excel, you may now have enough information to complete the formula in your target cell and retrieve the answer. You may not - you can now ask GPT to provide an illustrated example with completed formula for you to copy and adapt.
You would be given the following (in this case) to help you get started and begin applying the concept to your own scenario:
=SUMIF(A2:A5, "Apple", B2:B5)
This returns the answer ‘300’ - sorted!
Now, let’s say you encounter an expanded version of this dataset which has sales for these products by region too:
If you are new to SUMIF, what you don’t yet realise is you have encountered the limit of its ability to total Revenue by multiple criteria (Product AND Region) - ask GPT what to do next! It will most likely offer you an explanation of the function SUMIFS() [allows multiple criteria] or to nest SUM() within a FILTER() function, which allows you to pre-filter a dataset before applying a function, SUM(), to the subset you created.
Did you spot that I altered the sample data too? If you carried over =”Apple” as a condition, it would no longer work - the Product value we are seeking is “Apples” - ChatGPT could have helped you debug this error. It can now even take a screenshot as an input, read the values it sees on your screen, and tell you that you have probably mistyped the formula and updating it to “Apples” will help the SUM find the correct values to total. Pretty cool right?
Try it yourself. Approach your next Excel task with an LLM sat next to you in your browser window. Don’t assume you know the solution - just describe your problem - and see if it comes up with an approach you didn’t expect!
This ‘SUMIF Apples Worked Example’ comes from the author’s book, “Ultimate Microsoft Excel Formula & Function Reference Guide” - a 500+ page book for your desk which contains one page per formula for the almost every function in Microsoft Excel. Not everyone wants to work from a screen - so if having the most complete Excel function guide sat on the desk next to you sounds useful, get it today by following the link below! Each page also contains a section called “Explore Further With GPT”, giving you some ideas as to what to ask to get even more from the function you’re looking at!
The author earns commission for purchases made following this link.
