12 Unmissable Prompts for Excel and Google Sheets: Formulas, Macros, Dashboards

12 Unmissable Prompts for Excel and Google Sheets: Formulas, Macros, Dashboards

Spreadsheets are the backbone of business reporting, but even advanced users lose time wrestling with complex formulas, repetitive macro writing, and dashboard layout decisions. AI assistants like ChatGPT, Microsoft Copilot, and Gemini can act as your on-demand spreadsheet expert—if you know how to ask. These 12 copy-paste-ready prompts will help you generate formulas, explain logic, automate with VBA and Apps Script, and design dashboards in both Microsoft Excel and Google Sheets.

I’ve structured each prompt so you can adapt it instantly. For every one, you’ll find the exact prompt text, why it works, and a concrete output example. By the end, you’ll have a reusable toolbox that cuts hours off your weekly spreadsheet work.

How to Write a High-Quality Spreadsheet Prompt

Before we dive into the list, understand the anatomy of a useful prompt:

• Provide context — Describe your columns, data types, and what you’re trying to achieve.
• Specify the output format — Ask for a formula, script, or textual explanation.
• Show an example — If possible, include sample data or the expected result.
• State your environment — Excel vs. Google Sheets, because functions and scripting differ.

With that in mind, let’s go through the 12 prompts.

1. Generate a Multi-Condition SUMIFS Formula

Prompt:

I have an Excel sheet with columns: Date (A), Region (B), Salesperson (C), Sales Amount (D). I need to sum Sales Amount for rows where Region is "West" and Date falls between January 1, 2024 and March 31, 2024. Write the formula and explain how to adapt it.

Why it works: The AI nails the syntax and caveats (e.g., using >= and <= with DATE()). You get a formula you can copy straight into your sheet.

Example output:

=SUMIFS(D:D, B:B, "West", A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,3,31))

2. Explain a Complex Formula in Plain English

Prompt:

Explain this Excel formula in plain English, step by step: =INDEX(A1:C10, MATCH("Alice", A1:A10, 0), 3). What is each part doing, and what would this return?

Why it works: Instead of staring at a cryptic formula, you get a clear mental model — perfect for learning and debugging.

Example output:

• MATCH("Alice", A1:A10, 0) finds the row where “Alice” appears in column A. The 0 means exact match.
• INDEX(A1:C10, row_number, 3) then looks in that row and returns the value from column C.
• Net result: Alice’s info from the third column of the range.

3. Generate Power Query M Code for Data Cleaning

Prompt:

I’m working in Power Query (Excel). Write an M code snippet that does the following: removes duplicate rows based on the ID column, trims all text columns, and converts the Price column to currency type. Also explain where to paste the code.

Why it works: Data cleaning is the most common productivity drain. The AI generates a repeatable, transparent transformation script.

Example output (M code):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Price", Currency.Type}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type", {{col, Text.Trim, type text}} for … ),
    #"Removed Duplicates" = Table.Distinct(#"Trimmed Text", {"ID"})
in
    #"Removed Duplicates"

(Note: The exact code depends on your column names. A good prompt includes them.)

4. Write a VBA Macro to Send Email Reminders

Prompt:

I have a worksheet called “Tasks” with columns: Task Name (A), Assigned To (B), Email (C), Due Date (D). Write a VBA macro that loops through rows 2 to 100. If the due date is between now and 3 days from now, it sends an Outlook email to the person in column C reminding them the task is due. Include error handling.

Why it works: This prompt gives the AI everything it needs: the sheet structure, the condition, and the delivery mechanism. You get a working macro that you can paste into the VBA editor.

Example output snippet:

Sub SendReminders()
    Dim ws As Worksheet
    Dim i As Integer
    Dim oApp As Object
    Dim oMail As Object
    Set ws = ThisWorkbook.Sheets("Tasks")
    Set oApp = CreateObject("Outlook.Application")

    For i = 2 To 100
        If ws.Cells(i, 4).Value >= Date And ws.Cells(i, 4).Value <= Date + 3 Then
            Set oMail = oApp.CreateItem(0)
            With oMail
                .To = ws.Cells(i, 3).Value
                .Subject = "Task Due Soon: " & ws.Cells(i, 1).Value
                .Body = "Hi " & ws.Cells(i, 2).Value & ", your task is due on " & ws.Cells(i, 4).Value
                .Send
            End With
        End If
    Next i
End Sub

5. Create a Google Apps Script for Cell-Change Alerts

Prompt:

In Google Sheets, I need a script that watches cell B2 on the sheet “Dashboard”. Whenever the value changes, it should send an email to me with the old and new values. Write an Apps Script function and tell me how to install the trigger.

Why it works: Apps Script can be intimidating. This prompt delivers a copy-paste script plus trigger instructions.

Example output (Google Apps Script):

function onEdit(e) {
  const range = e.range;
  if (range.getSheet().getName() === "Dashboard" && range.getA1Notation() === "B2") {
    const oldValue = e.oldValue;
    const newValue = range.getValue();
    const email = Session.getActiveUser().getEmail();
    MailApp.sendEmail(email, "Dashboard cell B2 changed", "Old: " + oldValue + "\nNew: " + newValue);
  }
}

Install as an onEdit trigger, but note that e.oldValue only works on edit triggers—both simple and installable — in most cases, though for a simple onEdit it won't capture old value reliably. Use the installable trigger with onChange or you can store the previous value in script properties. Your AI assistant will give you the more robust version if you ask for it.

6. Design a Pivot Table Layout

Prompt:

I have transaction data with columns: Order ID, Customer, Product, Category, Quantity, Unit Price, Order Date. What pivot table layout would best answer: “Which product categories generate the most revenue in each region?” (Assume a Region column.) Include row/column labels, values, and any filters.

Why it works: Instead of guessing pivot fields, you get a structured design you can implement in one click.

Example output:

To compute revenue, create a helper column =Quantity*UnitPrice before pivoting.

7. Generate Conditional Formatting Rules

Prompt:

For Excel range A2:A100, create three conditional formatting rules: (1) highlight duplicates in red, (2) highlight the top 10 values in green, (3) highlight values above the average in blue. Use formulas where appropriate, and explain how to set the rules using the conditional formatting dialog.

Why it works: This prompt gives you exact rules and the step-by-step UI navigation — no more hunting through menus.

Example formulas:

• Duplicates: =COUNTIF($A$2:$A$100, A2)>1 (red fill)
• Top 10: =A2>=LARGE($A$2:$A$100, 10) (green fill)
• Above average: =A2>AVERAGE($A$2:$A$100) (blue fill)

8. Create a Dynamic Data Validation Dropdown

Prompt:

In Google Sheets, I want a dropdown in cell F1 that shows only unique values from column A (A2:A100), and the list should update automatically when I add new values. What is the formula for the data validation range, and how do I set it up?

Why it works: Dynamic ranges are tricky. The AI gives you the exact custom formula approach.

Solution: Use a helper column with =UNIQUE(A2:A100) and set the data validation range to that helper column. Alternatively, use this formula as a custom formula: =FILTER(A2:A, COUNTIF(A2:A, A2:A)>0) but the UNIQUE approach is simpler.

9. Recommend the Best Chart Type

Prompt:

I have monthly columns: Sales and Profit for 12 months. I want to show the sales trend and the profit margin visually. What Excel chart type should I use, and what specific series should I set up?

Why it works: AI acts as a chart advisor—saving you from bad visualization choices.

Example output: Use a combo chart: columns for Sales (primary axis) and a line for Profit Margin % (secondary axis). Right-click the Profit series → Change Series Chart Type → Line.

10. Extract Patterns with Regex in Google Sheets

Prompt:

In Google Sheets, column A contains email addresses like “john.doe@example.com”. I want column B to show only the domain part (e.g., “example.com”). Write a REGEXEXTRACT formula and explain the regex pattern.

Why it works: Regex is powerful but easily forgotten. This gives you a ready-to-use formula.

Example formula:

=REGEXEXTRACT(A2, "@([^@]+)$")

The pattern @([^@]+)$ matches the @ symbol, then captures everything after it that is not another @ until the end of the string.

11. Refactor a Slow VLOOKUP to INDEX/MATCH

Prompt:

My sheet has 50,000 rows, and I’m using =VLOOKUP(A2, Data!A:C, 3, FALSE) repeatedly. This is too slow. Rewrite it as an INDEX/MATCH formula and explain why it’s faster.

Why it works: Performance optimization is a real need. The AI explains the mechanics, not just the formula.

Example output:

=INDEX(Data!C:C, MATCH(A2, Data!A:A, 0))

INDEX/MATCH is faster when the lookup column is not the first column, and it avoids recalculating entire volatile ranges.

12. Design a KPI Dashboard Layout

Prompt:

I need a sales dashboard for an executive review. The data includes: Monthly Sales, Average Order Value, Customer Count, Product Category breakdown, and Sales by Region. Suggest a layout with specific chart types, KPI cards, and the recommended placement on a Google Sheets tab. Also provide sample formulas for the KPI cards.

Why it works: You get a complete wireframe plus formulas — perfect for turning raw data into an executive-ready view.

Example output:

Pro Tips: Using Your New Prompt Library

• Always validate outputs. AI can produce perfect syntax but wrong logic. Test on a copy of your data.
• Ask for explanations. Every prompt can be followed by "explain this in simple terms" to build your own knowledge.
• Combine prompts. Use the formula generator to create a helper column, then feed that into the dashboard prompt.
• Respect Excel vs. Sheets differences. Functions like SUMIFS work in both, but =DAYS in Sheets and =NETWORKDAYS in Excel have slight differences. The AI will adapt if you specify the platform.

Sources and Further Reading

For extra context, here are official resources referenced by the AI outputs above:

• Microsoft Support. “SUMIFS function in Excel.” https://support.microsoft.com/en-us/office/sumifs-function-9c617740-442f-412e-b3c8-f3d9b8b1e3f4
• Microsoft Support. “INDEX and MATCH.” https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
• Google Support. “Google Sheets function list.” https://support.google.com/docs/answer/3093275?hl=en
• Google Developers. “Google Apps Script triggers.” https://developers.google.com/apps-script/guides/triggers

These are the same docs that high-quality AI models use to train on, so you’re getting reliable, up-to-date formulas.

Conclusion

You now have 12 powerful prompts that turn any AI assistant into your personal spreadsheet architect. Whether you need a quick formula, a full dashboard wireframe, or a VBA macro to automate your morning report, start with these templates and adapt them to your data.

Don’t just copy the prompts — learn the patterns. The best AI prompt is one that gives clear context, specifies the format, and leaves no ambiguity. Bookmark this page, and the next time you stare at a blank formula bar, you’ll know exactly what to type.