Introduction
Spreadsheets are the backbone of data analysis, reporting, and business operations. Yet many professionals spend hours manually searching for formulas, writing VBA macros, or designing dashboards from scratch. The solution? Using AI-powered prompts to generate ready-to-use code, formulas, and layout ideas in seconds. This article provides 10 specific, copy-paste-ready prompts for Excel and Google Sheets that cover formulas, pivot tables, macros, and dashboards. Each prompt includes a short explanation and a practical example, so you can immediately apply it to your own work.
Whether you're a financial analyst, a logistics manager, or a marketer, these prompts will save you time and help you create more accurate, professional spreadsheets. Let's dive in.
1. Formula Generation Prompt
Prompt: "Generate an Excel formula to calculate the weighted average of values in column B, where weights are in column C. Assume data starts from row 2 and ends at row 100. Use SUMPRODUCT and SUM."
Explanation: This prompt is for any task where you need a custom formula but don't remember the exact syntax or logic. The AI provides the formula and often explains how it works.
Example output:
=SUMPRODUCT(B2:B100, C2:C100)/SUM(C2:C100)
2. Conditional Formatting Rule Prompt
Prompt: "Create a conditional formatting rule in Google Sheets that highlights cells in column A if they contain the word 'urgent' (case-insensitive). Apply a red fill with bold white text."
Explanation: Use this when you need to visually flag specific data based on text, numbers, or dates. The AI will give you the exact custom formula and formatting settings.
Example output:
- Custom formula: =REGEXMATCH(A1, "(?i)urgent")
- Format: Red background (#FF0000), bold white font.
3. Pivot Table Field Recommendation Prompt
Prompt: "I have sales data with columns: Date, Product, Category, Sales, Quantity. Suggest the best pivot table layout to show total sales by category and product, with a filter by month."
Explanation: This prompt helps you design a pivot table structure when you're unsure which fields go where. The AI recommends rows, columns, values, and filters.
Example output:
- Rows: Category, Product
- Values: Sum of Sales
- Filters: Month (derived from Date)
- Optionally add a slicer for Product.
4. VBA Macro for Data Cleaning Prompt
Prompt: "Write a VBA macro for Excel that removes all empty rows from the active sheet, trims leading/trailing spaces in all cells, and converts text in column C to proper case. Add comments to explain each step."
Explanation: Perfect for repetitive data cleaning tasks. The AI generates a ready-to-run macro with comments, making it easy to modify or debug.
Example output:
Sub CleanData()
Dim rng As Range
Dim cell As Range
' Remove empty rows
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
' Trim spaces in all cells
For Each cell In ActiveSheet.UsedRange
If Not IsEmpty(cell) Then
cell.Value = Trim(cell.Value)
End If
Next cell
' Convert column C to proper case
For Each cell In Range("C2:C" & ActiveSheet.UsedRange.Rows.Count)
If Not IsEmpty(cell) Then
cell.Value = StrConv(cell.Value, vbProperCase)
End If
Next cell
End Sub
5. Google Apps Script Prompt
Prompt: "Write a Google Apps Script that sends an email alert when a cell in column E of the active sheet exceeds 1000. The email should include the value, the row number, and the timestamp. Use the built-in MailApp service."
Explanation: Google Sheets does not support VBA, but you can use Apps Script to automate tasks. This prompt generates a script that you can paste into Extensions > Apps Script.
Example output:
function sendAlert() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var now = new Date();
for (var i = 0; i < data.length; i++) {
if (data[i][4] > 1000) { // column E (index 4)
var subject = 'Alert: Value exceeded 1000';
var body = 'Row ' + (i+1) + ' has value ' + data[i][4] + ' at ' + now;
MailApp.sendEmail('your-email@example.com', subject, body);
}
}
}
6. Dashboard Layout Prompt
Prompt: "Design a dashboard layout for tracking monthly sales performance. Include a KPI card for total revenue, a line chart for trend over 12 months, a bar chart comparing sales by product category, and a table showing top 5 products by revenue. Suggest the best chart types and placement."
Explanation: Use this to get a structured layout idea before building your dashboard. The AI will recommend chart types, which sheets to place them on, and even how to structure the data for each chart.
Example output:
- Sheet structure: One sheet for raw data, one for calculations, one for the dashboard.
- Layout: Top row: KPI cards (Total Revenue, Growth %, Orders). Left side: line chart (months on X, revenue on Y). Right side: bar chart (categories). Bottom: table with top 5 products.
7. Dynamic Named Range Prompt
Prompt: "Create a dynamic named range in Excel named 'SalesData' that automatically expands when new rows or columns are added. Use the OFFSET function with COUNTA. Data starts in cell A1 and has 5 columns."
Explanation: Named ranges are essential for formulas that need to remain valid as data grows. This prompt gives you the exact formula to define in Name Manager.
Example output:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5)
8. Data Validation Dropdown Prompt
Prompt: "Set up a data validation dropdown in Google Sheets for cell B2, with options from a list on another sheet named 'Lists' in column A. The list may grow, so make it dynamic using a named range."
Explanation: Use this when you need cascading or dynamic dropdowns. The AI will explain how to create a named range, then apply data validation.
Example output:
1. Go to Data > Named ranges, create a range OptionsList = Lists!A:A
2. Select cell B2, go to Data > Data validation, choose 'List from a range', enter =OptionsList.
9. Query Function (Google Sheets) Prompt
Prompt: "Write a Google Sheets QUERY formula that pulls data from Sheet2 where column A contains '2026' and column D is not empty. Show only columns A, B, C, and E, and sort by column B descending."
Explanation: The QUERY function is powerful but has a steep learning curve. This prompt generates the exact syntax for filtering, sorting, and selecting columns.
Example output:
=QUERY(Sheet2!A:E, "SELECT A, B, C, E WHERE A CONTAINS '2026' AND D IS NOT NULL ORDER BY B DESC", 1)
10. Error Handling Prompt
Prompt: "My Excel formula =VLOOKUP(A2, Products!A:B, 2, FALSE) returns #N/A when the product is not found. Modify it to show 'Not Found' instead of the error, and also handle any other errors gracefully."
Explanation: Use this when you need to make your formulas more robust. The AI will wrap your formula with IFERROR or IFNA.
Example output:
=IFNA(VLOOKUP(A2, Products!A:B, 2, FALSE), "Not Found")
Conclusion
These 10 prompts cover the most common tasks in Excel and Google Sheets: formula creation, data cleaning, automation, dashboard design, and error handling. By using AI to generate code and formulas, you can work faster, reduce mistakes, and focus on analyzing results rather than wrestling with syntax. Copy the prompts above, adapt them to your data, and watch your productivity soar.
Remember: always test generated code on a copy of your data first, especially when using VBA macros or Apps Script, as they can modify your spreadsheet irreversibly. Happy spreadsheeting!
Comments