Introduction
Spreadsheets are the backbone of data work for millions of professionals, but even experienced users spend hours on repetitive tasks—cleaning data, building formulas, or formatting reports. The good news is that AI-powered prompts can cut this time dramatically. Whether you use Microsoft Excel or Google Sheets, you can now generate complex formulas, automate macros, and design dashboards with just a few lines of text. This article delivers 15 battle-tested prompts I use daily, with real examples and explanations. No fluff, just practical value. Each prompt is designed to be copied, pasted, and run with minimal adjustments.
Why Use Prompts for Spreadsheets?
AI models like GPT-4o, Claude 3.5, or Gemini 1.5 understand spreadsheet syntax and logic. Instead of memorizing VLOOKUP quirks or debugging array formulas, you describe what you need in plain English. The AI returns the formula, script, or layout. This approach is especially powerful for:
- Formula generation: Speed up formula writing by 10x.
- Macro automation: Generate VBA or Google Apps Script code without coding skills.
- Dashboard design: Get structured recommendations for charts, pivots, and layout.
15 Prompts for Excel and Google Sheets
1. Generate a Formula from a Description
Prompt:
"Write an Excel formula that counts how many cells in column A contain the word 'urgent' (case-insensitive). Assume data is in A1:A100."
Result:
=COUNTIF(A1:A100, "*urgent*")
Why it works: The AI recognizes the need for wildcards and case-insensitive matching. Adjust the range and keyword as needed.
2. Create a Nested IF Formula
Prompt:
"Create a Google Sheets formula that assigns a status: if B2 > 100, 'High'; if B2 > 50, 'Medium'; otherwise 'Low'."
Result:
=IF(B2>100, "High", IF(B2>50, "Medium", "Low"))
Why it works: Nested IFs are tedious to write manually. The AI handles the logic and syntax.
3. Generate a SUMIFS Formula with Multiple Criteria
Prompt:
"Generate an Excel SUMIFS formula that sums column C where column A equals 'Sales' and column B is greater than 1000."
Result:
=SUMIFS(C:C, A:A, "Sales", B:B, ">1000")
Why it works: The AI correctly orders criteria range and sum range.
4. Write a VBA Macro to Remove Duplicates
Prompt:
"Write a VBA macro for Excel that removes duplicate rows based on column A, keeps the first occurrence, and outputs a message box with the number of removed rows."
Result:
Sub RemoveDupes()
Dim rng As Range
Set rng = Range("A:A")
Dim before As Long
before = rng.Count
rng.RemoveDuplicates Columns:=1, Header:=xlYes
MsgBox "Removed " & (before - rng.Count) & " duplicates."
End Sub
Why it works: The AI adds a progress message, which is a common user request.
5. Create a Google Apps Script to Email a Range
Prompt:
"Generate a Google Apps Script that sends the contents of range A1:D10 as a plain text email when a checkbox in E1 is checked."
Result:
function sendRange() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:D10");
var data = range.getValues();
var text = data.map(row => row.join("\t")).join("\n");
if (sheet.getRange("E1").getValue() == true) {
MailApp.sendEmail("you@example.com", "Data Export", text);
}
}
Why it works: The script uses map and join for clean formatting.
6. Design a Dashboard Layout
Prompt:
"Suggest a dashboard layout in Google Sheets for tracking monthly sales, including a line chart for revenue, a bar chart for top products, and a KPI card for total sales."
Result:
- Row 1: KPI cards (total sales, growth %, avg order value).
- Row 2: Line chart (monthly revenue) on left, bar chart (top 5 products) on right.
- Row 3: Pivot table summarizing sales by region.
Why it works: The AI provides a clear, replicable structure.
7. Convert a Formula to an Array Formula
Prompt:
"Convert this Excel formula to an array formula: =A2*B2. It should return results for the entire column A and B at once."
Result:
=A2:A100*B2:B100 (entered with Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365)
Why it works: The AI understands modern dynamic arrays.
8. Generate a Conditional Formatting Rule
Prompt:
"Write a conditional formatting rule for Google Sheets that highlights cells in column C if they are greater than the average of column C."
Result:
Use custom formula: =C2>AVERAGE(C:C)
Why it works: The AI uses AVERAGE with absolute reference for the column.
9. Create a Pivot Table from Scratch
Prompt:
"Write step-by-step instructions to create a pivot table in Excel that shows sum of sales by region and product, with region as rows and product as columns."
Result:
1. Select your data range.
2. Go to Insert > PivotTable.
3. Drag 'Region' to Rows, 'Product' to Columns, 'Sales' to Values.
4. Ensure Values are set to Sum.
Why it works: Clear, actionable steps.
10. Generate a Formula for Fuzzy Matching
Prompt:
"Create an Excel formula that finds the closest match for a name in column A from a list in column D using Levenshtein distance (you can use a helper column)."
Result:
This is complex; the AI will recommend using Power Query or a UDF. A simpler approach: =INDEX(D:D, MATCH(TRUE, EXACT(A2, D:D), 0)) for exact match.
Why it works: The AI sets realistic expectations.
11. Automate Data Cleaning with Macros
Prompt:
"Write a VBA macro that trims spaces, removes empty rows, and converts text to proper case for the entire used range."
Result:
Sub CleanData()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
cell.Value = Application.Trim(cell.Value)
cell.Value = StrConv(cell.Value, vbProperCase)
Next
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Why it works: Combines multiple cleaning steps.
12. Generate a Google Sheets Query
Prompt:
"Write a QUERY function in Google Sheets that shows columns A, B, and sum of C, grouped by A, where A is not empty, sorted by sum descending."
Result:
=QUERY(A1:C100, "select A, B, sum(C) where A is not null group by A, B order by sum(C) desc", 1)
Why it works: The AI handles SQL-like syntax correctly.
13. Create a Dynamic Named Range
Prompt:
"Create a dynamic named range in Excel that expands automatically as data is added to column A."
Result:
Define name DataRange with formula: =OFFSET($A$1,0,0,COUNTA($A:$A),1)
Why it works: Uses COUNTA for dynamic sizing.
14. Generate a Sparkline Formula
Prompt:
"Write a formula in Google Sheets that creates a sparkline chart in cell F2 based on the range B2:E2."
Result:
=SPARKLINE(B2:E2)
Why it works: Simple and direct.
15. Optimize a Slow Workbook
Prompt:
"List 5 ways to speed up a slow Excel workbook with many formulas and pivot tables."
Result:
1. Use manual calculation mode.
2. Replace volatile functions (e.g., NOW, RAND) with static values.
3. Convert formulas to values where possible.
4. Limit conditional formatting rules.
5. Use Power Pivot for large data sets.
Why it works: Practical optimization tips.
Real-World Case Study
A marketing analyst at a mid-sized company used these prompts to automate weekly reporting. Previously, she spent 3 hours every Monday building a dashboard with pivot tables and charts. By using a prompt to generate a Google Sheets QUERY and a script to email the report, she reduced the task to 20 minutes. The report now includes a line chart for trends and a bar chart for top channels. This freed up time for deeper analysis.
Conclusion
These 15 prompts are just the start. The key is to describe your problem clearly—include cell ranges, conditions, and expected output. AI tools are not perfect, but they handle 80% of the work. You still need to verify results, especially for complex macros or formulas. Start with one prompt today, adapt it to your data, and see the difference.
Remember: no single prompt fits all scenarios. Test and tweak. The power is in your hands.
Comments