Why Spreadsheets with AI Are a Game Changer
Excel and Google Sheets are among the most widely used programs in the world. Whether in the office, in a club, for personal financial planning, or in a small business: spreadsheets are everywhere. Yet many people use only a fraction of their capabilities because formulas and functions seem too complicated.
This is exactly where AI comes into play. Imagine being able to simply describe what you want to calculate in plain language, and AI delivers the right formula. No more googling, no more puzzling over the correct syntax, no more frustration over cryptic error messages. This is not science fiction; it works excellently today.
In our previous article about code prompts for non-programmers, you already learned how to use AI to solve technical tasks. Spreadsheets are a perfect application for this: you describe your problem in natural language, and AI translates it into spreadsheet language.
The special thing about it: you do not need to know how to program. You do not even need to know which function you need. AI figures that out for you. All you need is the ability to clearly describe your problem. And that is exactly what you have practiced extensively in the previous modules of this course.
In this article, I will show you step by step how to use AI to truly master spreadsheets. From simple formulas to pivot tables and automatic calculations. Let us get started!
Creating Formulas: Your AI Formula Assistant
Creating formulas is probably the most common hurdle when working with spreadsheets. The good news: AI is exceptionally good at generating formulas. You just need to learn how to properly describe your requirements.
The key lies in structuring your prompt. Always describe three things: first, where your data is located (columns and rows), second, what you want to calculate, and third, which program you are using (Excel or Google Sheets, as there are small differences).
Here is an example of a good prompt:
Example prompt (structured):
"I am working in Google Sheets. Column A contains product names, Column B contains sales prices, Column C contains quantities. Create a formula for cell D2 that calculates the total revenue per product (price times quantity). The formula should be copyable downward."
AI will not only deliver the formula =B2*C2 but also explain why it works and how you can drag it down. For more complex requirements, it gets even more exciting.
More complex example:
"In my Excel spreadsheet, Column A has dates, Column B has the category (groceries, transportation, entertainment), Column C has the amounts. Create a formula that sums all amounts in the 'groceries' category from January 2026."
Here, AI will suggest a SUMIFS formula and explain exactly how the different conditions work together. The beautiful thing is: you do not need to know that SUMIFS is the right function. AI figures that out from your description.
An important tip: if you do not understand the formula, just ask! Write something like: "Explain the formula to me step by step, as if I were a beginner." AI will explain each component of the formula individually.
For recurring tasks, you can build a small formula catalog. Ask AI to compile the ten most important formulas for your use case, each with an explanation and example. This way, you gradually build your own reference guide.
VLOOKUP, IF, and COUNTIF: The Power Functions Explained
There are three functions that come up again and again and challenge many people: VLOOKUP (also XLOOKUP in newer versions), IF, and COUNTIF. With AI, these functions suddenly become accessible, even if you have never used them before.
VLOOKUP/XLOOKUP: Looking Up Data
Imagine you have two tables: one with item numbers and prices, another with orders. You want to automatically insert the price for each order. That is exactly what VLOOKUP is for.
Prompt example:
"I have two worksheets in Excel. On Sheet 1, Column A has item numbers and Column B has order quantities. On Sheet 2 (named 'PriceList'), Column A has the same item numbers and Column B has unit prices. Create a formula for Sheet 1, Column C, that looks up the matching price from the price list."
AI will offer you both the VLOOKUP and the more modern XLOOKUP variant and explain when to use which. It will also point out typical pitfalls, such as exact versus approximate matching.
IF: Making Decisions
The IF function is like a fork in the road: "If this condition is true, then do this, otherwise do that." It is incredibly versatile and can also be nested.
Prompt example:
"Column D contains the total revenue per customer. Create a formula for Column E that assigns the following customer categories: under 1,000 euros is 'Bronze', between 1,000 and 5,000 euros is 'Silver', above 5,000 euros is 'Gold'. Use the IF function in Excel."
AI creates the nested IF formula and explains the logic behind it. If the nesting becomes too complex, it may suggest the IFS function as an alternative.
COUNTIF: Counting and Summarizing Data
How often does a specific value appear? How many customers have more than three orders? COUNTIF and COUNTIFS answer these questions.
Prompt example:
"In Column B of my Google Sheets table, there is the status of each task (open, in progress, completed). Create three formulas that count how many tasks have the status 'open', 'in progress', and 'completed' respectively."
A particularly helpful trick: ask AI to show you all related functions for a task. This way, you learn not just the one solution but also discover alternatives like SUMIF, AVERAGEIF, or MAXIFS that you might also need.
Cleaning Data: Creating Order with AI Help
Before you can work with data, it needs to be clean. This sounds trivial but is often the most time-consuming task in practice. Typos, different spellings, missing values, duplicate entries: all of this must be cleaned up before meaningful analyses are possible.
AI can help you with data cleaning on multiple levels. First, it can provide you with the right formulas and functions for typical cleaning tasks.
Typical cleaning tasks and matching prompts:
"Column A of my Excel table contains names, but some have extra spaces at the beginning or end. Some are written entirely in uppercase. Create formulas that: 1) remove unnecessary spaces, 2) display the name in proper capitalization (first letter uppercase, rest lowercase)."
AI will suggest functions like TRIM (for spaces) and PROPER (for capitalization). Often it combines multiple functions into a single formula that solves all your problems at once.
Finding and removing duplicates:
"My customer list in Google Sheets probably has duplicate entries. Column A contains the email address. Show me how to find and mark duplicates without deleting them immediately. I want to check before removing anything."
This cautious approach is important: let AI help you identify problems, but do not delete automatically. Always check yourself before removing data.
Converting data to a uniform format:
"My table has dates in different formats: sometimes '01/15/2026', sometimes 'January 15, 2026', sometimes '2026-01-15'. Create a formula that converts all of them to MM/DD/YYYY format."
For larger datasets, it is worth asking AI for a complete cleaning script. In Google Sheets, it can even create a Google Apps Script that performs multiple cleaning steps automatically. And for Excel, there are Power Query instructions that AI can explain step by step.
A practical workflow for data cleaning: first copy a few sample rows of your data and show them to AI. Describe the problems you see. Ask for a cleaning strategy. Have the formulas or steps created. Test on a small dataset before processing the entire table.
Pivot Tables: Understanding Data at a Glance
Pivot tables are one of the most powerful tools in Excel and Google Sheets, and at the same time one of the most feared. Yet the concept is actually simple: a pivot table summarizes large amounts of data and shows you the essentials.
Imagine you have a table with 1,000 sales. Each row contains the date, the salesperson, the product, and the amount. A pivot table can show you in seconds which salesperson had the highest revenue, which product performed best, or how sales developed over the months.
Prompt for pivot table help:
"I have a sales table in Excel with the columns: Date, Salesperson, Product Category, Region, Quantity, Unit Price, Total Amount. Explain step by step how to create a pivot table that shows me the total revenue by salesperson and product category. Please describe which fields I need to drag where."
AI will provide you with a detailed guide: which field goes in the row area, which in the column area, which in the values area. It also explains how to customize the display, for example showing percentages instead of absolute numbers.
Advanced pivot tables:
"My pivot table shows revenue per month. I would additionally like: 1) a rolling 3-month average, 2) the percentage change from the previous month, 3) conditional formatting that highlights months with revenue decline in red. How do I do this in Excel?"
Even if you have never created a pivot table, AI will guide you through the process step by step. The important thing is: always describe what you want to see at the end. Not how it should be technically implemented, but what question your analysis should answer.
Another tip: let AI suggest different analysis possibilities. Say for example: "Here is the structure of my sales data (columns: ...). What are the five most interesting pivot tables I could create with this?" You will be surprised at the ideas AI comes up with that you would not have thought of yourself.
Automatic Calculations and Dynamic Dashboards
Now it gets really exciting: we combine everything you have learned so far into automatic calculations and small dashboards. A dashboard is essentially an overview page where the most important key figures are visible at a glance.
Prompt for a simple dashboard:
"I track my monthly expenses in Google Sheets. Column A: Date, Column B: Category (rent, groceries, transportation, leisure, other), Column C: Amount. Create a dashboard on a separate worksheet with the following metrics: 1) Total expenses for the current month, 2) Expenses per category, 3) Comparison to the previous month (spent more or less), 4) Average daily expenses. Give me all necessary formulas."
AI will provide you with a complete set of formulas that update automatically when you enter new data. This is the real magic: set up once, always current.
Conditional formatting with AI:
Numbers only become truly meaningful when they are visually prepared. Conditional formatting automatically colors cells based on their value and makes trends immediately visible.
"Create conditional formatting rules for my revenue table: values above the average should have a green background, values below the average should be red. Additionally, the top 3 values should be bold with a gold background."
Automatic notifications (Google Sheets):
In Google Sheets, you can even set up automatic notifications. AI can create a small Google Apps Script that notifies you by email when certain conditions are met.
"Create a Google Apps Script for my spreadsheet that notifies me every Monday by email if a value in Column D (inventory) falls below 10. The email should list the affected items."
Such automations save an enormous amount of time and ensure you do not miss anything important. And you do not need to be a programmer: AI creates the code, you simply copy it into the script editor.
Practical Prompt Strategies for Spreadsheets
After all these examples, I want to give you some proven strategies that make your work with AI and spreadsheets particularly effective.
Strategy 1: Always describe the table structure
AI cannot create formulas if it does not know where your data is. Begin every prompt with a clear description of your table structure: what columns are there? What is in them? From which row do the data start (do they have a header row)?
Strategy 2: Provide sample data
Even better than a description is concrete sample data. Copy three to five rows of your table and include them in the prompt. This way, AI can recognize the data formats and create matching formulas.
Strategy 3: Describe the desired result
Do not say "Create a VLOOKUP formula." Instead, say: "I want Column C to automatically display the customer name when I type the customer number in Column B." This way, AI can choose the best solution, not just the one you assume.
Strategy 4: Work step by step
Do not try to create a huge spreadsheet in a single prompt. Work in small steps: first the basic structure, then the formulas, then the formatting, then the dashboard. This way, you maintain oversight and can find errors more easily.
Strategy 5: Use errors as learning opportunities
If a formula does not work, copy the error message and show it to AI. Describe what should happen and what happens instead. AI will find the error and explain what went wrong. This way, you learn how the functions actually work.
Strategy 6: Have templates created
For recurring tasks, it is worth asking AI for a complete template. Whether budget planner, project tracker, inventory list, or time tracking: describe your needs, and AI creates a finished template including all formulas.
Exercise: Create a Vacation Planning Spreadsheet with the Prompt Generator
Now it is your turn! In this exercise, you will create a complete vacation planning spreadsheet using the prompt generator at optiprompt.io. Use the structured variant of the prompt generator, as a clear structure is especially important for spreadsheets.
Your task:
Create a spreadsheet for vacation planning for a family (2 adults, 2 children) with the following requirements:
- An overview page with budget, destination, and travel dates
- A detailed cost breakdown (travel, accommodation, meals, activities, souvenirs)
- Automatic calculation of total costs per category and overall
- A daily budget that calculates from the total budget and trip duration
- A packing list with checkboxes
- Conditional formatting that warns when the budget is exceeded
How to proceed:
- Open the prompt generator at optiprompt.io
- Select the structured variant
- Describe your goal: a vacation planning spreadsheet
- Add the above requirements as details
- Specify whether you use Excel or Google Sheets
- Generate the prompt and use it in your preferred AI
Bonus task: Ask AI to suggest three different versions of the spreadsheet: a minimalist one for a quick overview, a detailed one for thorough planning, and one with visualizations (charts for cost distribution). Compare the results and choose the best version.
You will see how easy it is to create professional spreadsheets with AI. And with each time you do it, you learn more about the functions and possibilities.
In the next article, we will look at how you can understand and use simple automations to completely delegate recurring tasks. From spreadsheets to entire workflows: the possibilities keep getting more exciting!