Working with spreadsheets often means repeating the same manual steps — cleaning data, building reports, or debugging formulas. Large language models (LLMs) like GPT-4 and Claude can dramatically speed up these tasks if you know how to ask the right questions. This collection covers 12 practical prompts for Excel and Google Sheets, organized from basic formula generation to advanced VBA and dashboard automation. Each prompt includes a real-world example and the output you can expect.
1. Generate a Complex Formula from a Description
Task: You need a formula but don’t remember the exact syntax. Describe what you want in plain English.
Prompt:
I have a column A with dates and column B with sales amounts. I want to sum sales for the current month only, but only for rows where column C equals "Online". Write an Excel formula that does this without helper columns.
Example Result:
=SUMIFS(B:B, A:A, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), A:A, "<"&DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1), C:C, "Online")
This uses SUMIFS with date boundaries generated by DATE and TODAY. The same logic works in Google Sheets. You can replace TODAY() with a cell reference if you prefer a fixed month.
2. Explain and Fix a Broken Formula
Task: You inherited a spreadsheet with a formula that returns an error. Paste it and ask for a fix.
Prompt:
This Excel formula gives #VALUE! error: =VLOOKUP(A2, D:E, 2, FALSE) & " - " & TEXT(B2, "0.00"). Column A has text, column D has numbers stored as text. What's wrong and how do I fix it?
Example Result:
The VLOOKUP fails because the lookup value (text) doesn’t match the lookup column (numbers stored as text). Fix by converting either side:
- Convert lookup value to number: =VLOOKUP(VALUE(A2), D:E, 2, FALSE) & " - " & TEXT(B2, "0.00")
- Or convert the lookup column to text with an extra helper column.
The AI explains the root cause and provides the corrected formula.
3. Create a Pivot Table Instruction Step by Step
Task: You want to summarize data but aren’t sure which fields to use.
Prompt:
My sheet has columns: Date, Region, Product, Sales, Quantity. I need a pivot table that shows total sales per region per month, with months as columns and regions as rows. Give me the exact steps for Excel.
Example Result:
1. Select your data range.
2. Insert > PivotTable > New Worksheet.
3. Drag Region to Rows.
4. Drag Date to Columns — Excel will automatically group by month. If not, right-click a date > Group > Months.
5. Drag Sales to Values (sum by default).
6. Optionally add a slicer for Product to filter interactively.
For Google Sheets: Data > Pivot table, then set rows, columns, and values similarly.
4. Write an Array Formula for a Dynamic Calculation
Task: You need to compute something across a range without dragging down.
Prompt:
In Google Sheets, I have a list of prices in A2:A100 and quantities in B2:B100. I want to calculate the subtotal for each row (price * quantity) and sum only those rows where the subtotal is greater than 100. Use a single array formula.
Example Result:
=SUM(FILTER(A2:A100 * B2:B100, A2:A100 * B2:B100 > 100))
This uses FILTER to keep only rows where the product exceeds 100, then sums them. In Excel, you could use SUMPRODUCT with a condition: =SUMPRODUCT((A2:A100)*(B2:B100)*(A2:A100*B2:B100>100)).
5. Build a Simple Dashboard with Sparklines and Conditional Formatting
Task: You want a compact dashboard that shows trends and highlights problems.
Prompt:
I have monthly sales data for 12 months in B2:M2. I want:
- A sparkline showing the trend in cell N2.
- Conditional formatting that highlights the cell red if the last month's sales dropped more than 10% compared to the previous month.
- A text formula in O2 that says "Decline" or "Stable" based on the same condition.
Write the formulas and formatting rules for Excel.
Example Result:
- Sparkline in N2: =SPARKLINE(B2:M2) (Google Sheets) or use Insert > Sparkline in Excel.
- Conditional formatting rule for B2:M2 (or a summary cell): Use a formula rule: =M2 < L2*0.9.
- Formula in O2: =IF(M2 < L2*0.9, "Decline", "Stable").
This creates a mini dashboard that updates automatically when you add new data.
6. Clean and Normalize Messy Data
Task: Your imported data has inconsistent formatting, extra spaces, and mixed case.
Prompt:
I have a column of customer names with leading/trailing spaces, some in uppercase, some lowercase, and occasional double spaces. Write a formula in Google Sheets that cleans all of them in one step.
Example Result:
=ARRAYFORMULA(PROPER(TRIM(REGEXREPLACE(A2:A100, "\s+", " "))))
TRIMremoves leading/trailing spaces.REGEXREPLACEcollapses multiple spaces into one.PROPERcapitalizes first letters.
In Excel, use =PROPER(TRIM(A2)) and drag down, or use TEXTJOIN with FILTERXML for an array alternative.
7. Create a VBA Macro to Automate Repetitive Tasks
Task: You regularly export a report and need to format it the same way.
Prompt:
Write a VBA macro for Excel that does the following:
1. Finds the last row with data in column A.
2. Adds a total row below it that sums columns B through D.
3. Formats the total row in bold with a light yellow background.
4. Saves the file with a new name that includes today's date.
Example Result:
Sub AutoFormatAndSave()
Dim lastRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Add total row
ws.Cells(lastRow + 1, 1).Value = "Total"
ws.Cells(lastRow + 1, 2).Formula = "=SUM(B2:B" & lastRow & ")"
ws.Cells(lastRow + 1, 3).Formula = "=SUM(C2:C" & lastRow & ")"
ws.Cells(lastRow + 1, 4).Formula = "=SUM(D2:D" & lastRow & ")"
' Format total row
With ws.Range(ws.Cells(lastRow + 1, 1), ws.Cells(lastRow + 1, 4))
.Font.Bold = True
.Interior.Color = RGB(255, 255, 200)
End With
' Save with date
Dim newName As String
newName = ThisWorkbook.Path & "\Report_" & Format(Date, "yyyymmdd") & ".xlsm"
ThisWorkbook.SaveCopyAs newName
End Sub
8. Summarize a Large Dataset with Natural Language
Task: You have a huge sheet and want a quick overview without writing formulas.
Prompt:
Using the data in range A1:Z5000 of my Excel sheet, where column A is "Customer", column B is "Order Date", column C is "Amount", and column D is "Category", give me a bullet-point summary of:
- Total number of orders
- Average order amount
- Top 3 categories by total sales
- Month with the highest sales in 2025
Example Result:
You can use PivotTables for a manual answer, but the AI suggests:
- Total orders: =COUNTA(A2:A5000)
- Average amount: =AVERAGE(C2:C5000)
- Top categories: Create a PivotTable with Category in rows, Amount sum in values, then sort descending.
- Highest month: Group dates by month, sum amounts, and use MAX.
The AI can also generate a single formula using LET and SORT in Excel 365:
=LET(
cats, UNIQUE(D2:D5000),
sums, SUMIFS(C2:C5000, D2:D5000, cats),
SORT(HSTACK(cats, sums), 2, -1)
)
9. Translate a Formula Between Excel and Google Sheets
Task: You know a formula in one platform but need it in the other.
Prompt:
Convert this Google Sheets formula to Excel: =ARRAYFORMULA(IF(A2:A100="",, LEFT(A2:A100, FIND(" ", A2:A100)-1)))
Example Result:
In Excel, you need to use a different approach since ARRAYFORMULA doesn’t exist. For Excel 365:
=IF(A2:A100="", "", LEFT(A2:A100, FIND(" ", A2:A100 & " ")-1))
This works as a spilled array formula. For older Excel, you’d drag down: =IF(A2="","",LEFT(A2, FIND(" ", A2 & " ")-1)).
10. Generate a Dynamic Chart with Named Ranges
Task: You want a chart that automatically expands as new data is added.
Prompt:
I have monthly data: dates in column A (A2:A), values in column B (B2:B). I want a line chart that automatically includes new rows as I add them. Create named ranges in Excel and explain how to set up the chart.
Example Result:
1. Define named ranges using OFFSET:
- Name: Dates → =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)
- Name: Values → =OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1)
2. Insert a line chart and in the Select Data Source dialog, replace the series values with =Sheet1!Dates and =Sheet1!Values.
3. Now any new row added to the bottom will be included automatically.
11. Write a Custom Google Sheets Function
Task: You need a reusable function that doesn’t exist in Sheets.
Prompt:
Write a Google Apps Script function called REMOVE_OUTLIERS that takes a range and a threshold (in standard deviations), and returns the range with cells replaced by blank if they are more than threshold SDs from the mean.
Example Result:
function REMOVE_OUTLIERS(range, threshold) {
var values = range.flat();
var sum = values.reduce((a, b) => a + b, 0);
var mean = sum / values.length;
var variance = values.reduce((a, b) => a + (b - mean) ** 2, 0) / values.length;
var sd = Math.sqrt(variance);
return values.map(function(v) {
return Math.abs(v - mean) > threshold * sd ? "" : v;
});
}
Use in Sheets: =REMOVE_OUTLIERS(A2:A100, 2).
12. Create an Interactive Dashboard with Checkboxes and Slicers
Task: You want a dashboard where users can filter data without editing formulas.
Prompt:
In Google Sheets, I have a table with columns: Product, Region, Sales, Month. I want to add checkboxes for each region and a chart that updates to show only sales for checked regions. Write the formulas and explain the steps.
Example Result:
1. List unique regions in, say, E2:E5. Put checkboxes via Insert > Checkbox next to each (F2:F5).
2. Create a filtered list with FILTER:
=FILTER(A2:D100, COUNTIFS(E2:E5, B2:B100, F2:F5, TRUE))
3. Build a chart from the filtered range. The chart updates automatically when checkboxes change.
For Excel, use Slicers connected to a PivotChart for a similar no-formula experience.
Conclusion
These 12 prompts cover the most common spreadsheet tasks — from writing formulas and cleaning data to building macros and interactive dashboards. The key is to describe your goal clearly, mention the platform (Excel vs Sheets), and include sample data structure. With practice, you'll be able to generate working solutions in seconds instead of searching through forums. Pick one prompt, adapt it to your current project, and see how much time you save.
Comments