AI Prompts for Excel & Google Sheets

Spreadsheets are the most widely used data tool in the world. An estimated 750 million people use Excel regularly, and Google Sheets adds hundreds of millions more. Yet the vast majority of users barely scratch the surface of what these tools can do.

The barrier is not lack of interest — it is syntax. Memorizing hundreds of function names, their arguments, and the correct nesting order is tedious. AI removes this barrier entirely. With a well-structured prompt, you can describe what you want in plain English and get back a working formula, a complete macro, or a step-by-step pivot table configuration.

This guide covers the five most common spreadsheet tasks and the prompts that solve them. Every example was tested with ChatGPT and Claude against real spreadsheet data.

Formula Generation from Plain English

This is the most common spreadsheet request. A good formula prompt includes the data structure, the exact calculation, and any edge cases.

I need an Excel formula for this calculation:

DATA STRUCTURE:
- Column A: Product name
- Column B: Quantity sold
- Column C: Unit price (before discount)
- Column D: Discount percentage (0-100, some cells empty)
- Column E: Region (North, South, East, West)

CALCULATION NEEDED:
For each row (row 2 onwards):
1. If D is empty, apply 0% discount. Otherwise apply the stated percentage.
2. If discount is greater than 50%, cap it at 50%.
3. Calculate: Revenue = (Quantity × Unit Price) × (1 - Effective Discount)
4. In a separate cell (G1), calculate total revenue for the "North" region only.

Please provide:
1. The formula for the effective discount (Column F)
2. The formula for revenue (Column G)
3. The SUMIF/SUMIFS formula for North region total (G1)
4. An explanation of each function used

Why Context Matters

Spreadsheet formulas depend entirely on cell positions and data types. A prompt that says "calculate revenue after discount" produces a generic answer. A prompt that specifies column names, data formats, and edge cases produces a formula you can paste directly into your sheet.

If you are using Google Sheets instead of Excel, explicitly state it. Google Sheets uses different syntax for some functions (e.g., IMPORTRANGE vs Power Query) and supports features Excel does not (e.g., ARRAYFORMULA).

Pivot Table Configuration

Pivot tables are among the most powerful spreadsheet features, but the setup process confuses many users. AI can map a desired analysis into the exact field placements.

I have a sales dataset in Excel with these columns:
- Date (MM/DD/YYYY format)
- Salesperson (text)
- Region (North, South, East, West)
- Product Category (Electronics, Clothing, Home)
- Revenue (number)
- Cost (number)

I want a pivot table that shows:
1. Total revenue by region (rows)
2. Product category breakdown within each region (sub-rows)
3. Average profit margin by category (Revenue - Cost / Revenue)
4. Month-over-month trend for Q1 2026 (columns)

Please provide:
1. Step-by-step instructions to create this pivot table
2. The exact fields to place in Rows, Columns, Values, and Filters
3. The formula for calculated fields if needed
4. A description of what each part of the resulting table shows
5. One insight the table is likely to reveal

Data Cleaning Formulas

Real-world data is messy. Inconsistent formats, mixed case, extra spaces, and merged cells are standard. AI can generate the exact cleanup formulas you need.

I have messy data in an Excel spreadsheet with these problems:

Column A (Customer Name):
- Mixed case: "JOHN SMITH", "jane doe", "Alice Johnson"
- Some cells have leading/trailing spaces
- Some cells are empty

Column B (Phone Number):
- Formats vary: "(555) 123-4567", "555.123.4567", "5551234567", "555-123-4567"
- Some have country codes "+1", some do not

Column C (Date):
- Stored as text in multiple formats: "01/15/2024", "2024-01-15", "Jan 15, 2024"
- Some cells say "Pending" or "TBD" instead of dates

Column D (Amount):
- Currency symbols mixed: "$1,250.00", "EUR 980", "1,250"
- Some are stored as text, some as numbers

For each column, provide:
1. The Excel formula to clean and standardize the data
2. Instructions on how to apply it to the entire column (fill down)
3. How to convert formulas to static values after cleaning (Paste Special > Values)
4. A note on any data loss risk

Common Data Cleaning Functions

These functions appear in almost every cleanup workflow:

• TRIM() — removes leading and trailing spaces
• CLEAN() — removes non-printable characters
• PROPER() / UPPER() / LOWER() — standardizes text case
• TEXT() — formats numbers and dates consistently
• VALUE() — converts text that looks like numbers into actual numbers
• SUBSTITUTE() — replaces specific characters or substrings
• IFERROR() — handles errors gracefully instead of showing #VALUE!

Conditional Formatting Rules

Conditional formatting turns static spreadsheets into visual dashboards. The challenge is expressing the rules correctly.

I need conditional formatting rules for an Excel project tracker:

DATA:
- Column A: Task name
- Column B: Due date
- Column C: Status (Not Started, In Progress, Complete, Blocked)
- Column D: Owner (person name)
- Column E: Completion percentage (0-100)

RULES I WANT:
1. Highlight the entire row yellow if due date is within 3 days and status is not Complete
2. Highlight the entire row red if due date has passed and status is not Complete
3. Highlight rows with status "Blocked" in orange regardless of date
4. Apply a green gradient data bar to Column E (completion percentage)
5. Bold the task name if the owner is "Unassigned"

Please provide:
1. The exact conditional formatting formula for each rule
2. Whether to apply it to the entire row or specific columns
3. The color codes (RGB or hex) for each highlight
4. The order in which rules should be applied (since Excel processes rules top-down)

Google Apps Script Automation

For tasks that formulas cannot handle, use Apps Script (Google Sheets) or VBA (Excel). These are lightweight programming environments built into the spreadsheet.

I need a Google Apps Script for Google Sheets.

TASK:
When a new row is added to Sheet "Form Responses":
1. Check if Column E contains "Urgent" (case-insensitive)
2. If yes, send an email to [email protected] with:
   Subject: "Urgent Form Submission — " + value from Column B
   Body: Full row data formatted as a simple list
3. Copy that row to a sheet called "Urgent Items"
4. Add a timestamp in Column G of the original row
5. Log the action in a "Logs" sheet with: timestamp, row number, action taken

ADDITIONAL REQUIREMENTS:
- Handle the case where "Urgent Items" sheet does not exist yet
- Do not send duplicate emails if the same row is edited multiple times
- Include error handling so one bad row does not break the entire script

Please provide:
1. The complete, commented script
2. Instructions on how to install it (Extensions > Apps Script)
3. How to set up the trigger (On form submit vs On edit)
4. How to test it safely before going live

Spreadsheet Design Review

Before building a complex spreadsheet, use AI to evaluate your structure. A poorly designed sheet becomes unmaintainable as it grows.

Review this spreadsheet structure for best practices:

CURRENT STRUCTURE:
- Sheet 1 "Data": raw import from CRM (5000 rows, 30 columns)
- Sheet 2 "Dashboard": 15 charts referencing Data!A1:AD5000
- Sheet 3 "Summary": pivot table and manual totals
- Sheet 4 "Archive": copy-pasted old data from previous quarters

CONCERNS:
- File size is 45MB and opening takes 20 seconds
- Formulas break when rows are inserted
- Multiple people edit simultaneously

Please evaluate:
1. Is the data normalized, or are there redundancy risks?
2. Are there formulas that might break when rows are inserted/deleted?
3. Could this be structured better for maintainability?
4. What 2-3 improvements would have the biggest impact on performance?
5. Should any of this be moved to a database instead of a spreadsheet?

Excel vs Google Sheets: When to Use Which

Feature	Excel	Google Sheets
Large datasets (100K+ rows)	Better	Slower
Real-time collaboration	Limited	Excellent
Scripting/automation	VBA (powerful, dated)	Apps Script (modern, cloud)
Advanced data analysis	Power Query, Power Pivot	Connected Sheets, BigQuery
Offline access	Full	Limited (chrome extension)

Next Steps

Now that you can build powerful spreadsheets, explore our Data Analysis Prompts guide to learn how to analyze and visualize data beyond the spreadsheet.