20 Prompts for Excel and Google Sheets: Formulas, Macros, and Dashboards
Spreadsheets are the backbone of business analysis — but even seasoned users spend hours on repetitive tasks, complex lookups, and clunky dashboards. With the right prompts, you can turn a language model into a personal spreadsheet assistant. This article delivers 20 ready-to-use prompts for Excel and Google Sheets, covering formulas, macros (VBA and Google Apps Script), pivot tables, conditional formatting, and interactive dashboards.
Each prompt includes:
- Task description — what it solves
- The prompt — copy-paste ready (replace placeholders in brackets)
- Usage example — real scenario with expected output
Note: All prompts assume you are using a capable AI assistant (like GPT-4, Claude, or similar). Adjust complexity if needed.
1. Basic Formulas and Functions
Prompt 1: Generate a Nested IF Formula
Task: Create a nested IF formula for tiered discounts based on order value.
Prompt:
Write an Excel formula for column D that calculates a discount percentage based on the value in cell B2 (order amount):
- If B2 < 100 → 0%
- If 100 <= B2 < 500 → 5%
- If 500 <= B2 < 1000 → 10%
- If B2 >= 1000 → 15%
Use nested IF. Also show the equivalent IFS formula for Excel 365.
Usage Example:
Paste the prompt into your AI assistant. It returns:
- =IF(B2<100,0,IF(B2<500,5,IF(B2<1000,10,15)))
- =IFS(B2<100,0,B2<500,5,B2<1000,10,B2>=1000,15)
Prompt 2: VLOOKUP / XLOOKUP with Error Handling
Task: Look up product price from a table and avoid #N/A errors.
Prompt:
Generate an XLOOKUP formula (or VLOOKUP if preferred) that finds the price of a product in cell A2 from a table in Sheet2!A:B. Return "Not found" if the product doesn't exist. Assume Excel 365 or Google Sheets.
Usage Example:
- =IFERROR(XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B),"Not found")
- For VLOOKUP: =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not found")
Prompt 3: SUMIFS Across Multiple Criteria
Task: Sum sales where region = "East" and month = "January".
Prompt:
Write a SUMIFS formula that sums values in column C (Sales) if column A = "East" and column B = "January". Assume headers in row 1, data from row 2 to 100.
Usage Example:
=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"January")
Prompt 4: Extract Text with LEFT, RIGHT, MID
Task: Extract first name from a full name in cell A2.
Prompt:
Given a full name "John A. Doe" in cell A2, write a formula that extracts the first name only, assuming names are separated by spaces. Also show how to extract the last name.
Usage Example:
- First name: =LEFT(A2,FIND(" ",A2)-1) → "John"
- Last name: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))) → "Doe"
Prompt 5: Date Calculations (Working Days, Age)
Task: Calculate the number of working days between two dates, excluding weekends and holidays.
Prompt:
Write a formula that returns the number of working days between date in A2 (start) and B2 (end), excluding weekends and holidays listed in range H2:H10. Use NETWORKDAYS for Excel and Google Sheets.
Usage Example:
=NETWORKDAYS(A2,B2,$H$2:$H$10)
2. Pivot Tables and Data Summaries
Prompt 6: Create a Pivot Table Structure
Task: Build a pivot table that shows total sales by region and product category.
Prompt:
I have a table with columns: Date, Region, Product, Category, Sales, Quantity. Create a pivot table layout:
- Rows: Region
- Columns: Category
- Values: Sum of Sales
- Filters: Date (by month)
Provide step-by-step instructions for Excel and Google Sheets.
Usage Example:
The AI returns a bullet list:
1. Select your data range → Insert → PivotTable.
2. Drag Region to Rows, Category to Columns, Sales to Values.
3. Add Date as a filter → group by month.
Prompt 7: Pivot Calculated Field
Task: Add a profit margin column to an existing pivot table.
Prompt:
My pivot table has Sum of Revenue and Sum of Cost. Create a calculated field named "Profit Margin" that computes (Revenue - Cost) / Revenue. Show the exact formula and where to enter it.
Usage Example:
- In Excel: PivotTable Analyze → Fields, Items & Sets → Calculated Field → = (Revenue - Cost) / Revenue
- In Google Sheets: Add a calculated field in the pivot editor: = (Revenue - Cost) / Revenue
Prompt 8: Group Dates in Pivot Table
Task: Group daily data into months and years.
Prompt:
I have a pivot table with dates in rows. How do I group them by month and year in Excel? Show the shortcut keys and the menu path.
Usage Example:
- Right-click a date → Group → select Months and Years → OK.
- Shortcut: Alt + D + G + G (Excel 2019+).
3. Conditional Formatting
Prompt 9: Highlight Duplicates in a Column
Task: Color all duplicate values in column A.
Prompt:
Write a conditional formatting rule for Excel/Sheets that highlights cells in range A2:A100 if the value appears more than once. Provide the formula and step-by-step.
Usage Example:
- Formula: =COUNTIF($A$2:$A$100,A2)>1
- Apply fill color of your choice.
Prompt 10: Color Scale Based on Value
Task: Apply a red-yellow-green gradient to sales numbers.
Prompt:
Create a conditional formatting color scale for range C2:C100 (sales values):
- Lowest value: red
- Midpoint: yellow
- Highest value: green
Give exact instructions for Excel and Google Sheets.
Usage Example:
- Excel: Home → Conditional Formatting → Color Scales → choose 3-color scale.
- Google Sheets: Format → Conditional formatting → Color scale → set min/mid/max colors.
Prompt 11: Highlight Entire Row Based on Cell Value
Task: Highlight the whole row if column B contains "Overdue".
Prompt:
I have data in A2:F100. Write a conditional formatting formula that highlights the entire row (A2:F2) if cell B2 equals "Overdue". Apply to range $A$2:$F$100.
Usage Example:
- Formula: =$B2="Overdue" (apply to $A$2:$F$100)
4. Charts and Dashboards
Prompt 12: Create a Combo Chart (Bar + Line)
Task: Show monthly sales as bars and profit margin as a line on the same chart.
Prompt:
I have data: Month (A2:A13), Sales (B2:B13), Profit Margin (C2:C13). Create a combo chart with:
- Sales: column chart (primary axis)
- Profit Margin: line chart (secondary axis)
Provide steps for Excel and Google Sheets.
Usage Example:
- Excel: Select data → Insert → Combo Chart → choose Clustered Column - Line on Secondary Axis.
- Google Sheets: Insert → Chart → Chart type → Combo chart → set series.
Prompt 13: Dynamic Dashboard with Slicers
Task: Build a simple sales dashboard with slicers for region and product.
Prompt:
Design a one-page dashboard in Excel with:
- A pivot table showing sales by month
- Two slicers: one for Region, one for Product Category
- A pivot chart (bar chart) linked to the slicers
- Instructions on how to connect slicers to multiple pivot tables
Usage Example:
The AI explains: create pivot tables from the same data source, insert slicers for Region and Category, then use "Report Connections" to link each slicer to all pivot tables/charts.
Prompt 14: Sparklines for Trends
Task: Add mini line charts inside cells to show monthly trends.
Prompt:
I have monthly sales data for each product in columns B:M (Jan–Dec). Write a formula or menu instruction to insert sparklines in column N that show the trend for each row.
Usage Example:
- Excel: Select N2 → Insert → Sparklines → Line → Data range B2:M2 → OK.
- Google Sheets: Use =SPARKLINE(B2:M2) in N2.
5. Macros and VBA (Excel)
Prompt 15: Record and Edit a Simple Macro
Task: Automate formatting of a header row.
Prompt:
Write VBA code for a macro that:
1. Selects row 1 (header)
2. Makes the font bold
3. Sets background color to light blue
4. Auto-fits column widths
Also show how to assign the macro to a button.
Usage Example:
Sub FormatHeader()
Rows(1).Font.Bold = True
Rows(1).Interior.Color = RGB(173, 216, 230)
Columns.AutoFit
End Sub
Then: Developer → Insert → Button → assign macro.
Prompt 16: Loop Through Rows and Apply Logic
Task: Mark rows where sales exceed target.
Prompt:
Write a VBA macro that loops through rows 2 to 100 in Sheet1. If cell C2 > cell D2 (target), color cell E2 green and write "Achieved". Otherwise, color E2 red and write "Missed".
Usage Example:
Sub CheckSales()
Dim i As Integer
For i = 2 To 100
If Cells(i, 3).Value > Cells(i, 4).Value Then
Cells(i, 5).Interior.Color = RGB(0, 255, 0)
Cells(i, 5).Value = "Achieved"
Else
Cells(i, 5).Interior.Color = RGB(255, 0, 0)
Cells(i, 5).Value = "Missed"
End If
Next i
End Sub
Prompt 17: Create a User-Defined Function (UDF)
Task: Build a custom function to calculate tax based on income.
Prompt:
Create a VBA function called TaxCalc(income) that returns:
- 0% if income <= 10000
- 10% if income <= 40000
- 20% if income <= 80000
- 30% for anything above
Show how to use it in a cell.
Usage Example:
Function TaxCalc(income As Double) As Double
Select Case income
Case Is <= 10000: TaxCalc = 0
Case Is <= 40000: TaxCalc = income * 0.1
Case Is <= 80000: TaxCalc = income * 0.2
Case Else: TaxCalc = income * 0.3
End Select
End Function
Usage: =TaxCalc(A2)
6. Google Apps Script (Google Sheets)
Prompt 18: Custom Function to Fetch Exchange Rate
Task: Get live exchange rate from an API.
Prompt:
Write a Google Apps Script custom function called EXCHANGE(currency_from, currency_to) that uses the ExchangeRate-API (or free alternative) to fetch the latest rate. Include error handling if the API fails.
Usage Example:
function EXCHANGE(from, to) {
var url = "https://api.exchangerate-api.com/v4/latest/" + from;
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response.getContentText());
if (json.rates[to]) return json.rates[to];
else return "Rate not found";
}
Usage in sheet: =EXCHANGE("USD","EUR")
Prompt 19: Send Email Alert When Value Changes
Task: Automatically email yourself when a cell exceeds a threshold.
Prompt:
Write an Apps Script that runs onEdit trigger. If cell B2 in Sheet1 changes and its value > 1000, send an email to your email address with the subject "Alert: High Value" and the body containing the cell value and timestamp.
Usage Example:
function onEdit(e) {
var range = e.range;
if (range.getSheet().getName() == "Sheet1" && range.getA1Notation() == "B2") {
var val = range.getValue();
if (val > 1000) {
MailApp.sendEmail("you@example.com", "Alert: High Value", "Value " + val + " at " + new Date());
}
}
}
Prompt 20: Generate a Summary Report via Menu
Task: Add a custom menu that creates a summary sheet.
Prompt:
Write Apps Script code that adds a custom menu "Reports" with an item "Create Summary". When clicked, it creates a new sheet named "Summary" with:
- Total sales (sum of column C)
- Average order value
- Count of orders > $500
Use data from the active sheet.
Usage Example:
function onOpen() {
SpreadsheetApp.getUi().createMenu("Reports")
.addItem("Create Summary", "createSummary")
.addToUi();
}
function createSummary() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
// ... calculate and write to new sheet
}
Best Practices for Prompting Spreadsheet Help
- Be explicit about the app — mention "Excel" or "Google Sheets" because formulas differ (e.g.,
XLOOKUPvsFILTER). - Provide sample data structure — column names, row numbers, expected output.
- Specify version — Excel 2019, Excel 365, or Google Sheets; some functions are version-specific.
- Ask for alternatives — e.g., "show both Excel and Sheets versions".
- Use step-by-step — especially for macros and scripts, ask for explanation of each line.
Conclusion
These 20 prompts cover the most common spreadsheet tasks — from basic formulas to advanced automation. By using them as templates, you can save hours of manual work and reduce errors. The key is to describe your exact data layout and desired outcome. Whether you're a financial analyst, marketer, or project manager, integrating AI prompts into your workflow makes you dramatically more efficient.
Remember: the best prompt is one that includes context, examples, and constraints. Start with the prompts above, adapt them to your data, and soon you'll be building dashboards and macros in minutes instead of hours.
Ready to level up? Copy any prompt, replace the placeholders, and watch your spreadsheet transform.
Comments