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

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

Unlock the full potential of your spreadsheets with ready-to-use AI prompts. Save hours of manual work and build professional dashboards in minutes.

Spreadsheets are the backbone of modern business analytics, yet many professionals spend up to 30% of their workweek on repetitive data manipulation tasks (McKinsey Global Institute, 2023). While Excel and Google Sheets have powerful built-in functions, writing complex formulas, debugging macros, or designing interactive dashboards often requires deep expertise. Enter AI-powered prompts: by describing your goal in natural language, you can generate precise formulas, VBA code, or dashboard layouts—instantly.

This guide provides 10 copy-paste-ready prompts for Excel and Google Sheets, each with a clear use case, a working example, and practical tips. Whether you're a data analyst, marketer, or small business owner, these prompts will streamline your workflow and help you avoid costly errors.

How to Use These Prompts Effectively

Before diving into the prompts, keep these best practices in mind:

• Be specific: Include column names, sheet names, and the exact logic you need. Vague prompts produce generic results.
• Provide context: If you're working with a large dataset, mention the range (e.g., A1:D100) and any edge cases (e.g., blank cells, duplicates).
• Iterate: If the first output isn't perfect, refine your prompt—add constraints or ask for alternatives.
• Test on a copy: Always test generated formulas or macros on a sample of your data to avoid unintended changes.

1. Prompt for Complex Nested Formulas (e.g., IF with VLOOKUP, INDEX-MATCH)

Task: Create a formula that returns a discount rate based on customer tier and purchase amount, using nested IF and XLOOKUP.

Prompt:

"Write an Excel formula for cell C2 that looks up the discount percentage from Sheet2 based on the customer tier in A2 and the purchase amount in B2. Tiers are 'Gold', 'Silver', 'Bronze' in column A of Sheet2. If the amount is over $500, apply a 5% bonus discount. If the tier is not found, return 'No discount'."

Example Output:

=IFERROR(XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B) + IF(B2>500, 0.05, 0), "No discount")

Explanation: This formula first retrieves the base discount for the customer tier using XLOOKUP, then adds a 5% bonus if the purchase exceeds $500. If the tier is missing, IFERROR returns a custom message.

2. Prompt for Pivot Table Automation (Google Sheets)

Task: Automatically create a pivot table that summarizes sales by region and product category.

Prompt:

"In Google Sheets, generate a pivot table from the sheet 'SalesData' (columns: Date, Region, Product, Quantity, Revenue). Place the pivot table starting at cell F1. Rows: Region, Columns: Product, Values: Sum of Revenue. Also show a filter for Date range."

Example Output (manual steps described):
1. Select SalesData range.
2. Go to Data > Pivot table.
3. Set rows to 'Region', columns to 'Product', values to 'Revenue' (sum).
4. Add 'Date' as a filter.

Pro Tip: For repeated reports, record a macro while creating the pivot table, then reuse it.

3. Prompt for Conditional Formatting Rules (Highlight Duplicates, Top 10%)

Task: Apply conditional formatting to highlight duplicate entries in a column and also mark the top 10% values.

Prompt:

"Give me the conditional formatting rules for Excel to highlight duplicate values in column A (light red fill with dark red text) and also highlight the top 10% values in column B (green fill). Apply to the range A1:B100."

Example Output:
- Rule 1 (Duplicates): Select A1:A100 → Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values → choose 'Light Red Fill with Dark Red Text'.
- Rule 2 (Top 10%): Select B1:B100 → Home > Conditional Formatting > Top/Bottom Rules > Top 10% → choose 'Green Fill with Dark Green Text'.

Explanation: Conditional formatting rules are applied in order; you can manage their priority in the Conditional Formatting Rules Manager.

4. Prompt for VBA Macro to Clean Data (Remove Blanks, Trim Spaces)

Task: Create a VBA macro that cleans the active worksheet by removing blank rows, trimming spaces, and converting text to proper case.

Prompt:

"Write a VBA macro named 'CleanData' that does the following on the active worksheet: (1) Delete all rows that are completely blank, (2) Trim leading/trailing spaces from all cells in columns A-C, (3) Convert all text in column B to proper case. Show the full code."

Example Output:

Sub CleanData()
    Dim rng As Range
    Dim cell As Range
    ' Delete blank rows
    On Error Resume Next
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    ' Trim spaces in columns A-C
    Set rng = Range("A1:C" & ActiveSheet.UsedRange.Rows.Count)
    For Each cell In rng
        If Not IsEmpty(cell) Then
            cell.Value = Application.Trim(cell.Value)
        End If
    Next cell
    ' Proper case in column B
    Set rng = Range("B1:B" & ActiveSheet.UsedRange.Rows.Count)
    For Each cell In rng
        If Not IsEmpty(cell) Then
            cell.Value = Application.Proper(cell.Value)
        End If
    Next cell
    MsgBox "Data cleaned!"
End Sub

Usage: Press Alt+F11 to open the VBA editor, insert a new module, paste the code, then run CleanData from the Macros menu.

5. Prompt for Google Apps Script to Send Email Alerts

Task: Create a script that sends an email alert when a cell value in a Google Sheet exceeds a threshold.

Prompt:

"Write a Google Apps Script function 'sendAlert' that checks the value in cell B2 of sheet 'Dashboard'. If B2 is greater than 1000, send an email to 'user@example.com' with subject 'Alert: threshold exceeded' and body 'Value is ' + B2. Also add a custom menu item to run the script."

Example Output:

function sendAlert() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard");
  var value = sheet.getRange("B2").getValue();
  if (value > 1000) {
    var recipient = "user@example.com";
    var subject = "Alert: threshold exceeded";
    var body = "Value is " + value;
    MailApp.sendEmail(recipient, subject, body);
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Custom")
    .addItem("Send Alert", "sendAlert")
    .addToUi();
}

Pro Tip: You can trigger this script on a time-driven schedule (e.g., every hour) using the Google Apps Script triggers.

6. Prompt for Dynamic Dashboard Layout (Charts + KPIs)

Task: Design a dashboard layout with KPIs, a line chart for monthly trends, and a table for top products.

Prompt:

"Design a one-page Excel dashboard layout for sales data. Include: (a) top row with 3 KPI cards: Total Revenue, Avg Order Value, and Conversion Rate (use formulas), (b) a line chart showing monthly revenue for the current year, (c) a table listing top 10 products by quantity sold. Use a clean blue-and-white color scheme."

Example Output:
- KPI formulas:
- Total Revenue: =SUM(SalesData[Revenue])
- Avg Order Value: =AVERAGE(SalesData[Revenue])
- Conversion Rate: =COUNTIF(SalesData[Converted],"Yes")/COUNTA(SalesData[Converted])
- Chart: Insert a line chart with months on the X-axis and revenue on the Y-axis.
- Table: Use =SORT(SalesData[[Product]:[Quantity]], 2, -1) to get top 10.

Pro Tip: Use named ranges and Excel Tables (Ctrl+T) to make formulas automatically expand as data grows.

7. Prompt for Data Validation Drop-Down Lists (Dependent)

Task: Create dependent drop-down lists where selecting a category (e.g., 'Fruits') limits the second list to items (e.g., 'Apple', 'Banana').

Prompt:

"Create a dependent data validation list in Excel. Sheet1 has a category list in A1:A3 (Fruits, Vegetables, Dairy). Sheet2 has two columns: Category and Item. When a user selects 'Fruits' in cell A1 of Sheet1, cell B1 should show only fruits (Apple, Banana, Orange). Use the INDIRECT function."

Example Output:
1. On Sheet2, create named ranges: Fruits = Sheet2!$B$2:$B$4, Vegetables = Sheet2!$C$2:$C$4, etc.
2. On Sheet1, cell A1: Data Validation → List → Source: =Sheet2!$A$1:$A$3.
3. Cell B1: Data Validation → List → Source: =INDIRECT(A1).

Explanation: The INDIRECT function converts the text in A1 into a valid range reference, dynamically changing the options.

8. Prompt for Power Query (Get & Transform) to Combine Multiple Sheets

Task: Combine data from multiple sheets (or files) into one table using Power Query.

Prompt:

"Write step-by-step instructions for using Power Query in Excel to combine all sheets from a workbook named 'Sales_2026.xlsx' into a single query. Each sheet has the same columns: Date, Product, Revenue. Load the result to a new worksheet."

Example Output:
1. Go to Data > Get Data > From File > From Excel Workbook.
2. Select the file and click Import.
3. In the Navigator, select the first sheet and click Transform Data.
4. In Power Query Editor, go to Home > Append Queries > Append Queries as New.
5. Add all other sheets from the dropdown.
6. Click Close & Load to load the combined data into a new worksheet.

Pro Tip: Use Table.Combine in the Advanced Editor for more control, especially when dealing with dozens of sheets.

9. Prompt for Array Formulas (e.g., SUMIFS with Multiple Criteria)

Task: Calculate total sales for a specific product in a specific region using SUMIFS.

Prompt:

"Write an Excel array formula (or SUMIFS) that sums the Revenue column (column D) where Product (column B) equals 'Widget A' and Region (column C) equals 'North'. Use the dynamic range D2:D100, B2:B100, C2:C100. Also show how to make it dynamic with structured references."

Example Output:

=SUMIFS(D2:D100, B2:B100, "Widget A", C2:C100, "North")

For dynamic ranges (using Excel Tables):

=SUMIFS(Table1[Revenue], Table1[Product], "Widget A", Table1[Region], "North")

Explanation: SUMIFS is more efficient than array formulas (no need to press Ctrl+Shift+Enter) and works with both static and table ranges.

10. Prompt for Error Handling in Formulas (IFERROR, IFNA)

Task: Handle #N/A errors from VLOOKUP gracefully by showing 'Not Found' instead.

Prompt:

"Write an Excel formula that uses VLOOKUP to find a product price in Sheet2!A:B, but returns 'Not Found' if the product ID in A2 does not exist. Also handle cases where the price cell is blank by returning 'Price missing'."

Example Output:

=IFNA(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), IF(VLOOKUP(A2, Sheet2!A:B, 2, FALSE)="", "Price missing", "Not Found"))

Simpler version:

=IFERROR(IF(VLOOKUP(A2, Sheet2!A:B, 2, FALSE)="", "Price missing", VLOOKUP(A2, Sheet2!A:B, 2, FALSE)), "Not Found")

Pro Tip: Use IFNA (available in Excel 2013+) instead of IFERROR if you only want to catch #N/A errors and let other errors (like #VALUE!) surface for debugging.

Choosing Between Excel and Google Sheets for Automation

Both platforms offer robust automation, but they differ in key areas:

For most small-to-medium business tasks, Google Sheets offers easier sharing and scripting via Apps Script. Excel remains superior for heavy data processing (Power Query) and complex financial models.

ASI Biont supports connecting to both Excel and Google Sheets via API, enabling automated data syncing and reporting workflows—learn more at asibiont.com/courses.

Frequently Asked Questions

Q: Can I use these prompts with any AI assistant?
A: Yes—all prompts are designed to work with ChatGPT, Claude, Gemini, or Copilot. Some AI tools may have context length limits; keep prompts concise.

Q: Where can I find more advanced VBA examples?
A: Microsoft’s official VBA documentation (learn.microsoft.com/en-us/office/vba) and the Excel MVP community (excelmvv.com) are excellent resources.

Q: How do I debug a generated macro?
A: Use the VBA editor’s debug tools: set breakpoints (F9), step through code (F8), and use Debug.Print to output values to the Immediate window.

Conclusion

Mastering spreadsheet automation doesn’t require memorizing hundreds of functions or learning to code from scratch. With the right prompts, you can generate complex formulas, macros, and dashboard layouts in seconds—freeing up time for analysis and decision-making.

Start with the prompt most relevant to your current task, adapt it to your data, and iterate. Over time, you’ll build a personal library of prompts that can handle 80% of your repetitive spreadsheet work.

Ready to take your automation further? Explore how ASI Biont can connect your spreadsheets to external APIs and automate reporting workflows. Visit asibiont.com/courses for hands-on tutorials.