AI and Spreadsheets: A superior alternative to search results
How to use AI to generate, fix, and master spreadsheet formulas.
There’s a catch-22 to being good at spreadsheets. Not only is getting fluent with them hard, but getting precise, useful formulas can be incredibly time consuming. On the flip side, without getting good at building formulas, spreadsheet work is hell-ish.
Not only that, but learning them doesn’t do us a ton of good outside of spreadsheets (unless you’ve got a weird thing for logic puzzles).
Key Takeaways
AI can speed up the creation and troubleshooting of spreadsheet formulas—compared to search results
We describe what a new formula should do or describe examples of the inputs and outputs specific to our data
Through interactive prompting, we can converse with AI to level up on new types of formulas with examples customized to our jobs
The good news? With the right approach, you can create, fix, and understand Excel formulas faster. AI can help you become more proficient in three main ways. In this post, we’re going to look at:
Two options for creating new formulas from scratch with AI
How to debug formulas (and do so while still learning something)
Add new spreadsheet tools and formulas through an interactive conversation with AI
The best tools for generating formulas
Not all tools are created equal when it comes to generating formulas. If you’ve ever spent half an hour trying to piece together an INDEX-MATCH function, you know how frustrating trial and error can be. Here are the best two tools I’ve found for getting functional Excel (or Google Sheets) formulas:
Claude: Claude and its models (Sonnet, Haiku) are the most technically apt of the chat tools. This makes it the best option to generate formulas (and explanations too).
ChatGPT: OpenAI’s chat holds its own, and it’s the one I use more frequently for excel help. Largely because I generally just use it more frequently, because it offers longer threads and which I can continually revisit month after month.
You’ll notice I left out Google Gemini. I haven’t used it much for spreadsheets, and I owe that to a slight negative bias that I have after watching it fumble with creating slides in Google Slides.
The benefits of AI over search engines
Why use AI over asking a search engine? The first is obvious: you can get results while using natural language instead of the broken English of a Google search. Language like:
“Please write me a formula to…”
“How do I count…”
The second reason is, when given an incorrect solution, we can tell AI it’s wrong and have a conversation. Meaning I can explain to it where it messed up, thereby forcing myself to clarify my understanding of the problem. But in terms of time, iterating with AI beats the time it takes to find and review other search results.
And most important in my book, by personalizing our ask, we can personalize the formula that comes out. What do I mean by that? Take the cottage industry of excel spreadsheet wizards out there. Yes, they’ll show up in the search results and (pretty successfully) teach me to get the sum of 100 cells:
=SUM(A1:A100)
But they won’t have the context of the rows I’m actually trying to sum up. This is a rudimentary example (that would take seconds to alter), but it’s a little extra work if my cells are actually ‘E1:E100’. By working with AI to generate this formula, I can just tell it upfront that I’m working with the cells in column E.
What kind of formulas can you create?
Without providing a full primer on formulas, there are a lot of ways I’ve found to supercharge spreadsheets. They range from basic calculations to searching for content in other sheets to being able to grab data from the web.
Mathematical formulas: These range from your simple number-crunching formulas like averages and sums—the ones that keep budgets balanced—to those that are more statistics heavy.
String manipulation formulas: These are helpful when you’re trying to extract specific text from a cell. They’re especially useful when a cell has content that you don’t need, and you only want to get some of it. They also help to clean data (reducing errors in future calculations).
Checker formulas: Sometimes you want to validate the presence of something. Does a cell contain a specific word/phrase? Or does it contain anything at all? There are also formulas to check if a cell has an error or not.
Search formulas: Pulling data from other sheets or matching values across datasets? These are essential, and a core set of formulas to make a spreadsheet function more like a database.
Web search formulas: Spreadsheets can do more than just crunch numbers—they can pull in data from outside your spreadsheet (like metadata and content from a URL or stock prices).
I’ve provided a list of actual formulas that I use on a recurring basis at the end of this post.
Mastering formula creation
As with most AI use, there’s a nuance to getting better results sooner. How do we get more accurate formulas the first time around?
Use the most accurate words – Instead of “How do I find a value?”, ask “How do I find the last non-empty cell?”
Tell it the cells you’re working with – If you’re manipulating the content in cell ‘C4’, say that and the formula it provides will point to ‘C4’ instead of an arbitrary ‘A1’.
Define the output – Especially when dealing with numerical/mathematical formulas, specify when you want a date versus time versus percentages versus dollars.
Give it multiple input/output examples – If you can tell it what an input looks like and the exact end result you want, you’re leaving only a single blank that needs to be filled in (the formula).
The clearer your request, the better your results. Which leads me to two main structures I use when creating a new formula.
Describing the formula I want
Dictating the results I want
Option 1: Describe the formula I want
Best suited for: Web Search formulas, Mathematical formulas, Search formulas
It’s not an overly complex prompt. But it does rely on specificity (using the right words, giving it the cells or rows/columns I’m working with).
I need an Excel formula that [extracts all text in cell CELL_NUMBER before the first hyphen in a string].Between the two options, I go with this option 75% of the time.
It’s the easier way to go with as it requires less typing. It’s pretty darn accurate when there is little to no variability in the cell inputs I’m working with (i.e. there aren’t a lot of edge cases). If I know the input is going to be in a similar format for most or all cells, I’ll have more confidence in the first version of a formula I get.
Let’s take a look at an example of a search formula. In this example, I want to get a value from another sheet. Let’s say I’m a writer tracking guest article submissions to websites my audience reads and I have a spreadsheet with two sheets:
Website: A master list of websites that accept guest posts
Submissions: A sheet where I track submissions to those websites
I want a formula that can automatically pull in the editor’s contact email (which is stored with the website) when logging a new submission.
Let’s assume that Column A of “Websites” has the website, and so does Column A of “Submissions”. If I want to match the website names, and pull in the editor’s contact info from Column B of “Websites”. I’d include those details in my prompt.
I need an Excel formula that can look at the value in COLUMN_A in my sheet "Submissions", and then look at the sheet "Websites" to get the value in COLUMN_B for the corresponding value.
Option 2: Dictate the results I want
Best suited for: String manipulation formulas, Checker formulas
This approach helps when the structure of the data varies and you can’t rely on a simple formula. In cases like these, we need to generate a more robust formula that better handles variability (edge cases).
Please write me an excel formula that can manipulate the content of a cell to accomplish the following sets of inputs and outputs:
-- Test cases START --
[INPUT 1] -> [OUTPUT 1]
[INPUT 2] -> [OUTPUT 2]
[INPUT 3] -> [OUTPUT 3]
[INPUT 4] -> [OUTPUT 4]
-- Test cases END --A common example would be when we’re cleaning or extracting data and there are multiple conditions involved. In the prompt below, I was manipulating strings to pull out a subset of what was in the cell (NFL player names) from a longer string (Player name, team and position).
This got tricky because the player names I was looking for weren’t in a homogeneous format. Some inputs have more than a first name/last name (“Patrick Mahomes II”). Some even have extra characters (like the hyphen and period in “Amon-Ra St. Brown”). If I only asked for a formula by describing the type of formula I wanted (e.g. option 1), there’s a stronger chance the formula it spits out will miss some of these variations.
Here’s the prompt I ran, where I provided my inputs and my desired outputs:
Please write me an excel formula that can manipulate the content of a cell to accomplish the following sets of inputs and outputs:
-- Test cases START --
CeeDee Lamb (DAL - WR) -> CeeDee Lamb
Christian McCaffrey (SF - RB) -> Christian McCaffrey
Patrick Mahomes II (KC - WR) -> Patrick Mahomes II
Amon-Ra St. Brown (DET - WR) -> Amon-Ra St. Brown
-- Test cases END --
Debugging broken formulas
Of course, creating a new formula is just one way we can speed up our spreadsheet workflows. We can also use it on existing formulas to:
Ask for a step-by-step breakdown if a formula looks confusing
Experiment with alternative solutions to accomplish the same solution
Reverse-engineer formulas—like when you inherit a spreadsheet
This saves time, but can do so at the expense of learning what went wrong (never the ideal). That’s why the formula I use when debugging formulas is more than just passing a bad formula:
I have an Excel formula that isn't working as expected. I need you to help me debug it, explain how it's supposed to work, and identify what went wrong. Here’s what I need from you:
* Debug the formula – Find any errors or inefficiencies and provide a corrected version.
* Explain the logic – Break down how the formula should function, step by step.
* Identify the issue – Tell me what was wrong with the original formula and why it caused a problem.
* Analyze the error message – If applicable, explain what the error message means and how it relates to the formula's issue.
* Ask for clarification if needed – If you need more details (e.g., expected output, data structure, Excel version), prompt me for them.
If there are multiple ways to fix it, provide alternative solutions where possible. Also, suggest any optimizations or best practices that could improve its readability.
Here is the broken formula and the error message being received:
-- Broken formula START --
[PASTE BROKEN FORMULA HERE]
-- Broken formula END --
-- Error message START --
[PASTE ERROR MESSAGE HERE]
-- Error message END --Writing a prompt is a big time savings on the typical debug process, which would look something like this:
Checking for errors: Checking the output that explains what type of failure is happening
Break down the formula: Testing parts of the formula separately
Using helper cells: Running calculations or trying sub-steps in empty columns/rows
In other words, it’s a lot of trial and error. Great practice, for sure, but time consuming.
Troubleshooting bad formulas without sacrificing knowledge
How can we troubleshoot malfunctioning formulas in a shorter time, but not lose out on the additional knowledge that improves our skills in the long run? There are a few ways:
Pass any errors you’re receiving
Ask for responses to include a teaching element (give me the nuts and bolts)
Ask me for an explanation of the root cause (what went wrong in the first place)
See if there are alternative (or better) solutions to get the same result.
If you debug with the goal of learning what went wrong and what it was that fixed it, you’re still guaranteed to pick up some of the practical knowledge.
And regarding errors…
When you’re troubleshooting formulas, there are a few common errors. Here’s a quick description of the types of errors as well as common causes of each error.
Syntax errors – Missing commas, misplaced parentheses, or a wrong order of arguments.
Runtime errors – Dividing by zero, referencing an empty cell in a calculation, using a function incorrectly.
Logical errors – The formula technically works but doesn’t give the right result.
An interactive prompt to master spreadsheets
If we go beyond just generating or troubleshooting formulas, AI can help us learn new tricks for our spreadsheets.
I’ve previously written about the value of doing research with AI, where we can more tightly personalize what we’re trying to learn against what we already know. Prompting to explain what we already know can save time and effort on filling in the gaps.
With technical and syntax heavy topics like excel, we can take this to a new level. What if instead of asking AI to help me learn new excel formulas, I can create an interactive prompt where:
I explain what I want to learn (in this case, spreadsheet formulas) and how I want to learn it (starting basic and moving up to an expert level)
Additionally, have it ask me what my job is
If I already know something, we can move on to something harder
Keep reading with a 7-day free trial
Subscribe to AI Artistry to keep reading this post and get 7 days of free access to the full post archives.