15 Prompts for Excel and Google Sheets: Formulas, Macros, and Dashboards

15 Prompts for Excel and Google Sheets: Formulas, Macros, and Dashboards

You spend hours cleaning data, writing nested IFs, and rebuilding pivot tables. AI can cut that time by 70%—if you know the right prompts. I've tested dozens of prompts over the last year, and here are 15 that actually work. Each includes a real example and the exact formula or code you'll get.

1. Generate a VLOOKUP with Error Handling

Prompt: "Write an Excel formula that looks up the price in column B of Sheet2 based on product ID in cell A2. If the ID is not found, return 'Not Available' instead of #N/A."

Example: =IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Not Available")

Why it works: The prompt explicitly asks for error handling, which beginners often forget. The IFERROR wrapper turns a cryptic #N/A into a readable message.

2. Build a SUMIFS Across Multiple Criteria

Prompt: "Create a Google Sheets formula that sums sales in column C where region in column A is 'West' and month in column B is 'Jan'."

Example: =SUMIFS(C:C, A:A, "West", B:B, "Jan")

Why it works: SUMIFS is more flexible than SUMIF. This prompt forces you to list criteria in the right order (sum range first, then criteria ranges and values).

3. Create a Pivot Table from Raw Data

Prompt: "I have columns: Date, Product, Units Sold, Revenue. Create a pivot table that shows total revenue by product for each month. The output should have products in rows and months in columns."

Example output structure (in text):

Why it works: The prompt specifies layout (rows vs columns) and aggregation (total revenue). Without this, AI might guess averages or counts.

4. Write a VBA Macro to Remove Duplicates

Prompt: "Write a VBA macro for Excel that removes duplicate rows from the active sheet based on column A. Keep the first occurrence and delete the rest. Add a confirmation message before running."

Code:

Sub RemoveDuplicatesWithConfirmation()
    Dim response As VbMsgBoxResult
    response = MsgBox("Remove duplicates based on column A?", vbYesNo, "Confirm")
    If response = vbYes Then
        ActiveSheet.Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
        MsgBox "Done."
    End If
End Sub

Why it works: The prompt includes safety (confirmation) and scope (active sheet, column A, header).

5. Build a Dynamic Dashboard with Sparklines

Prompt: "Design a Google Sheets dashboard layout for monthly sales data. Include: a title, a KPI card for total sales, a line chart for trend, and sparklines in a table showing each product's 12-month trend. Give me the setup steps."

Example steps:
1. In cell A1, type "Sales Dashboard 2026".
2. In B3, use =SUM(Sales!C:C) for total sales.
3. Insert a chart from menu Insert > Chart, select line chart, range A2:B14.
4. In column D, add sparklines: =SPARKLINE(OFFSET(Sales!$C$1, MATCH($A4, Sales!$A$2:$A$100, 0), 0, 1, 12)).

Why it works: Dashboards often fail because people don't know how to combine static numbers with live charts. Sparklines let you pack trend data into a small space.

6. Convert Text Dates to Real Dates

Prompt: "I have a column of dates in text format like '2026-07-17'. Write an Excel formula to convert them to real date values that I can use in calculations."

Example: =DATEVALUE(A2) or =--(A2) if the text is already in a recognizable date format.

Why it works: Text dates break pivot tables, charts, and filters. This prompt solves the most common data cleaning issue.

7. Create a Conditional Formatting Rule Based on Another Cell

Prompt: "Write a conditional formatting rule for Excel that highlights cell B2 red if it is greater than the value in C2."

Steps:
1. Select B2:B100.
2. Home > Conditional Formatting > New Rule > Use a formula.
3. Enter: =B2>C2
4. Set fill color to red.

Why it works: Conditional formatting with formulas is powerful but non-intuitive. The prompt makes it explicit that the formula must be relative to the first selected cell.

8. Generate a Google Sheets Query to Aggregate Data

Prompt: "Use QUERY in Google Sheets to sum sales by region from a sheet with columns A (Region), B (Sales). Exclude rows where region is blank."

Example: =QUERY(A:B, "select A, sum(B) where A is not null group by A label sum(B) 'Total Sales'", 1)

Why it works: QUERY is a Swiss Army knife in Sheets. This prompt includes grouping, filtering, and labeling.

9. Write a Macro to Email a Report from Excel

Prompt: "Create an Excel VBA macro that sends an email via Outlook with the subject 'Weekly Report' and attaches the active workbook. The email body should be 'Please find attached.'"

Code:

Sub EmailReport()
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
    Dim olMail As Object
    Set olMail = olApp.CreateItem(0)
    With olMail
        .Subject = "Weekly Report"
        .Body = "Please find attached."
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
End Sub

Why it works: Automating repetitive email tasks saves hours. This basic macro can be extended to add recipients dynamically.

10. Combine Multiple Sheets into One with Power Query

Prompt: "I have 12 monthly sheets in an Excel workbook. Write Power Query M code to combine all sheets into a single table, adding a column for the sheet name."

Code (M):

let
    Source = Excel.CurrentWorkbook(),
    Tables = Table.SelectRows(Source, each Type.Is(Value.Type(Table.Column([Content], "Column1")), type table)),
    AddSheetName = Table.AddColumn(Tables, "SheetName", each [Name]),
    Combine = Table.Combine(AddSheetName[Content])
in
    Combine

Why it works: Power Query is the most efficient way to merge many sheets, but its M language is cryptic. This prompt gives you a working script.

11. Create a Drop-Down List Dependent on Another Cell

Prompt: "In Google Sheets, create a drop-down in cell B1 that shows products from column A only if the category in cell A1 equals 'Electronics'. Use data validation with a filter formula."

Formula for data validation: =FILTER(A:A, B:B = A1) (assuming categories are in B:B).

Why it works: Dependent dropdowns make data entry faster and reduce errors. The prompt specifies the dependency and the tool (FILTER, which is unique to Sheets).

12. Write an Array Formula to Extract Unique Values

Prompt: "Write an Excel dynamic array formula that extracts a unique list of values from column A, excluding blanks."

Example: =UNIQUE(FILTER(A:A, A:A<>""))

Why it works: Dynamic arrays (Excel 365) eliminate the need for complex CSE formulas. This prompt handles the common issue of blank cells breaking the list.

13. Build a Gantt Chart from Task Data

Prompt: "I have columns: Task, Start Date, End Date. Create a Gantt chart in Google Sheets using conditional formatting. Write the formula for the formatting rule."

Formula for conditional formatting (applied to range C2:Z100, where column C is the first date):
=AND(C$1>=$B2, C$1<=$C2)

Why it works: Gantt charts are incredibly useful for project management. The prompt explains how to use a simple formula to fill cells between start and end dates.

14. Create a Macro to Highlight Rows Based on a Cell Value

Prompt: "Write an Excel VBA macro that highlights the entire row if the value in column D is 'Overdue'. Use yellow fill."

Code:

Sub HighlightOverdue()
    Dim rng As Range
    For Each rng In Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        If rng.Value = "Overdue" Then
            rng.EntireRow.Interior.Color = vbYellow
        End If
    Next rng
End Sub

Why it works: Conditional formatting is static; macros let you apply formatting dynamically based on changing data.

15. Generate a Data Cleaning Script in Google Apps Script

Prompt: "Write a Google Apps Script function that removes all rows from the active sheet where the email in column B does not contain '@'."

Code:

function CleanInvalidEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var cleanData = data.filter(function(row) {
    return String(row[1]).includes('@');
  });
  sheet.clearContents();
  sheet.getRange(1, 1, cleanData.length, cleanData[0].length).setValues(cleanData);
}

Why it works: Apps Script automates what formulas can't—like deleting rows. The prompt specifies the column (B) and the condition (contains '@').

Conclusion

These 15 prompts cover the most frequent tasks I face in Excel and Google Sheets: data cleaning, formulas, automation, and visualization. Copy them, modify the column names and ranges, and you'll save hours each week. The key is being specific—include the column letters, the error handling, and the layout. Try one today on your own data. You'll be surprised how fast AI can write a formula you'd spend 10 minutes debugging.