Introduction
If you’ve ever spent hours manually cleaning a dataset, building a pivot table from scratch, or trying to make a chart that doesn’t look like a toddler’s art project, you know the pain. Excel and Google Sheets are powerful, but the gap between “I can sum a column” and “I can automate a weekly report” feels like a canyon. The good news? Large language models (LLMs) like GPT-4 and Claude can bridge that gap if you know the right prompts.
In this guide, I’ll share 15 tested prompts for Excel and Google Sheets, organized by skill level: basic (formulas and cleanup), advanced (pivot tables, conditional formatting, and macros), and expert (dynamic arrays, custom functions, and dashboard design). Each prompt includes a concrete example and an explanation of why it works. By the end, you’ll be able to turn a blank spreadsheet into a productivity machine with just a few sentences.
How to Use These Prompts
Before we dive in, a quick note: these prompts assume you’re using a text-based AI assistant (like ChatGPT, Claude, or a similar model). Paste the prompt, then replace the placeholders (e.g., [your data range]) with your actual details. The AI will generate the formula, script, or steps you need. Always test generated code in a copy of your spreadsheet first — AI can hallucinate functions that don’t exist or produce incorrect references.
Basic Prompts: Formulas and Data Cleaning
1. Generate a VLOOKUP or XLOOKUP Formula
Task: You need to look up a value in one table and return a corresponding value from another.
Prompt:
“I have a list of employee IDs in column A of Sheet1 and a master table in Sheet2 with employee IDs in column A and names in column B. Write an XLOOKUP formula for cell B2 in Sheet1 that returns the name for the ID in A2. Use exact match.”
Example result:
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not Found", 0)
Why it works: XLOOKUP is more robust than VLOOKUP — it doesn’t require the lookup column to be the first column, and it handles errors gracefully with the fourth argument.
2. Clean Text with TRIM and PROPER
Task: Your dataset has inconsistent spacing, uppercase, and lowercase text.
Prompt:
“I have names in column A with extra spaces and mixed case (e.g., ‘ john DOE ’). Give me a formula for column B that removes extra spaces and capitalizes the first letter of each word. Also include a formula to remove all non-alphabetic characters.”
Example result:
=TRIM(PROPER(A2)) // For names
=TEXTJOIN("", TRUE, IF(ISNUMBER(MATCH(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), {"A","B",...,"Z","a",...,"z"," "}, 0)), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), "")) // Remove non-alpha (advanced)
Why it works: TRIM removes extra spaces, PROPER capitalizes. The second formula uses an array trick to filter characters, but for most users, the first formula is enough.
3. Count Unique Values in a Column
Prompt:
“I have a column of order IDs in A2:A100. I want to count how many unique order IDs there are. Write a formula for Excel and one for Google Sheets.”
Example result:
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) // Excel
=COUNTA(UNIQUE(A2:A100)) // Google Sheets
Why it works: SUMPRODUCT with COUNTIF is a classic array formula. Google Sheets’ UNIQUE function is simpler and more intuitive.
Advanced Prompts: Pivot Tables, Conditional Formatting, and Macros
4. Create a Pivot Table from Raw Data
Prompt:
“I have a sales table with columns: Date, Region, Product, Salesperson, and Amount. I want a pivot table that shows total sales by region (rows) and by product (columns). Also include a filter for year. Give me step-by-step instructions for Excel and Google Sheets.”
Example result:
- Excel: Select your data → Insert → PivotTable → Drag ‘Region’ to Rows, ‘Product’ to Columns, ‘Amount’ to Values (sum). Add Date to the Filters area and group by year.
- Google Sheets: Select data → Data → Pivot table → Rows: Region, Columns: Product, Values: SUM of Amount. Add Date as a filter.
Why it works: Pivot tables are the fastest way to summarize data, but the drag-and-drop interface can be confusing. The prompt gives a clear blueprint.
5. Apply Conditional Formatting to Highlight Top 10 Items
Prompt:
“I have a list of monthly sales figures in B2:B13. I want to highlight the top 3 values in green and the bottom 3 values in red. Give me the conditional formatting rules for Excel.”
Example result:
1. Select B2:B13 → Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items → change 10 to 3 → set green fill.
2. Repeat for Bottom 10 Items → change to 3 → red fill.
Why it works: Conditional formatting is visual and immediate. This prompt saves you from writing complex formulas.
6. Write a VBA Macro to Copy and Rename a Worksheet
Task: You need to automate repetitive tasks like creating a new sheet from a template.
Prompt:
“Write a VBA macro that copies the worksheet named ‘Template’ to the end, renames the new sheet to ‘Report_YYYY-MM-DD’ (using today’s date), and then clears the contents of cells A1:C10 in the new sheet.”
Example result:
Sub CopyAndRename()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Template")
ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = "Report_" & Format(Date, "yyyy-mm-dd")
Range("A1:C10").ClearContents
End Sub
Why it works: This is a common automation task. The macro uses the built-in Copy method and dynamic naming.
7. Google Apps Script to Send an Email When a Cell Changes
Prompt:
“I have a Google Sheet where column A contains task statuses (e.g., ‘Done’, ‘Pending’). Write an Apps Script function that sends an email to me (myemail@gmail.com) whenever a cell in column A changes to ‘Done’. The email body should include the task name from the same row in column B.”
Example result:
function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
if (range.getColumn() == 1 && range.getValue() == "Done") {
var task = sheet.getRange(range.getRow(), 2).getValue();
MailApp.sendEmail("myemail@gmail.com", "Task Completed", "Task: " + task);
}
}
Why it works: The onEdit trigger runs automatically. This is a real-world use case for project management sheets.
Expert Prompts: Dynamic Arrays, Custom Functions, and Dashboards
8. Build a Dynamic Array Formula for a Running Total
Task: You want a running total that updates automatically as you add new rows.
Prompt:
“I have monthly expenses in column A (A2:A). I want a dynamic array formula in column B that calculates a running total. Use the SCAN function in Excel 365 or Google Sheets.”
Example result:
=SCAN(0, A2:A, LAMBDA(a, b, a + b)) // Excel 365
=SCAN(0, A2:A, LAMBDA(a, b, a + b)) // Google Sheets
Why it works: SCAN is a modern function that returns an array of intermediate results. No more dragging formulas down.
9. Create a Custom LAMBDA Function in Excel
Prompt:
“Define a custom LAMBDA function in Excel that converts a temperature from Celsius to Fahrenheit. Name it CtoF. Then show how to use it in a cell.”
Example result:
1. Go to Formulas → Name Manager → New. Name: CtoF, Refers to: =LAMBDA(c, (c * 9/5) + 32).
2. Usage: =CtoF(100) returns 212.
Why it works: LAMBDA lets you create reusable formulas without VBA. It’s a game-changer for complex calculations.
10. Design an Interactive Dashboard with Slicers and Sparklines
Prompt:
“I have a sales dataset with columns: Date, Region, Product, and Revenue. I want an interactive dashboard in Excel with a slicer for Region, a line chart showing monthly revenue trends, and sparklines in a summary table showing revenue per product over the last 6 months. Give me step-by-step instructions.”
Example result:
1. Create a pivot table with Date (grouped by month) in Rows, Region in Columns, Revenue in Values.
2. Insert a slicer for Region (PivotTable Analyze → Insert Slicer).
3. Build a pivot chart (line) from the same pivot table.
4. Below, create a summary table with Product names in rows and last 6 months as columns. Use =GETPIVOTDATA or SUMIFS to pull values. Insert sparklines (Insert → Sparklines → Line) for each row.
Why it works: Slicers and sparklines turn static data into an interactive experience. This is the foundation of many business dashboards.
11. Use QUERY in Google Sheets for SQL-Like Data Analysis
Prompt:
“My Google Sheet has a sheet called ‘Orders’ with columns: date, region, product, amount. Write a QUERY formula that returns total amount by region for the year 2025, sorted descending.”
Example result:
=QUERY(Orders!A:D, "SELECT B, SUM(D) WHERE YEAR(A) = 2025 GROUP BY B ORDER BY SUM(D) DESC LABEL SUM(D) 'Total'", 1)
Why it works: QUERY is arguably the most powerful function in Google Sheets, and this prompt teaches you the syntax in context.
12. Automate Data Import from a CSV File Using Power Query
Prompt:
“I receive a CSV file every week with sales data. I want to import it into Excel, clean the headers (remove spaces, make them lowercase), and load it as a table. Write the Power Query M code for this.”
Example result:
let
Source = Csv.Document(File.Contents("C:\Sales.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
CleanHeaders = Table.TransformColumnNames(PromoteHeaders, each Text.Lower(Text.Replace(_, " ", "_")))
in
CleanHeaders
Why it works: Power Query is Excel’s hidden superpower for ETL. This prompt gives a real-world snippet.
13. Create a Custom Google Sheets Function with Apps Script
Prompt:
“Write a Google Apps Script custom function called GETSTOCKPRICE that takes a ticker symbol (e.g., ‘AAPL’) and returns the current stock price from Google Finance. Then show how to use it in a cell.”
Example result:
function GETSTOCKPRICE(ticker) {
var price = GoogleFinance.getPrice(ticker);
return price;
}
Note: GoogleFinance.getPrice is a simplified representation; in reality, you’d use GOOGLEFINANCE(ticker, "price") directly in a cell. The script approach is useful for combining with other logic.
Why it works: Custom functions extend Google Sheets’ capabilities. This example is the tip of the iceberg.
14. Build a Data Validation Dropdown That Changes Based on Another Cell
Prompt:
“I have a sheet with two columns: Category (A) and Item (B). In cell D1, I select a category from a dropdown. I want the dropdown in E1 to show only items from that category. Give me the steps for Excel (using INDIRECT) and Google Sheets (using FILTER).”
Example result:
- Excel: Create named ranges (e.g., Fruits = Sheet1!$B$2:$B$5). Use Data Validation with list =INDIRECT(D1).
- Google Sheets: Use Data Validation with custom formula =FILTER(B:B, A:A=D1) (requires a helper range or script for dynamic lists).
Why it works: Dependent dropdowns are a staple of interactive sheets. This prompt covers both platforms.
15. Generate a Weekly Report Template with Charts and Summary Stats
Prompt:
“I need a weekly report template in Google Sheets for my marketing team. The raw data is in a sheet called ‘RawData’ with columns: Date, Channel, Clicks, Conversions, Spend. I want a dashboard sheet with: (1) a weekly summary table with total clicks, conversions, and CPA by channel, (2) a stacked bar chart showing clicks by channel per week, and (3) a scorecard showing total spend with conditional formatting (green if under budget, red if over). Give me step-by-step instructions and formulas.”
Example result:
1. In the dashboard sheet, use =QUERY(RawData!A:E, “SELECT B, SUM(C), SUM(D), SUM(E)/SUM(D) WHERE A >= DATE ’2026-07-07’ AND A <= DATE ’2026-07-13’ GROUP BY B LABEL SUM(C) ‘Clicks’, SUM(D) ‘Conversions’, SUM(E)/SUM(D) ‘CPA’”).
2. Select the summary data → Insert → Chart → Stacked bar chart.
3. In a cell, =SUMIFS(RawData!E:E, RawData!A:A, “>=”&DATE(2026,7,7), RawData!A:A, “<=”&DATE(2026,7,13)). Apply conditional formatting: Format → Conditional formatting → Greater than 5000 → red, else green.
Why it works: This prompt combines data extraction, visualization, and conditional formatting into a single workflow. It’s a template you can reuse every week.
Conclusion
Mastering Excel and Google Sheets isn’t about memorizing every function — it’s about knowing what’s possible and asking the right questions. The 15 prompts above cover everything from a simple VLOOKUP to a full dashboard template. Use them as a starting point, and adapt them to your own data.
Remember, AI is a tool that accelerates your work, not a replacement for understanding your data. Always verify formulas in a copy, and keep learning. The next time you face a tedious spreadsheet task, ask yourself: “What prompt would solve this?” The answer might save you hours.
ASI Biont supports connecting Excel and Google Sheets via API for automated workflows — learn more at asibiont.com/courses
Comments