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

Introduction

Spreadsheets are the backbone of modern business operations. Whether you're a financial analyst, marketer, or operations manager, Excel and Google Sheets can transform raw data into actionable insights. But let's be honest—many of us spend hours manually cleaning data, writing complex formulas from scratch, or struggling with pivot tables. That's where AI-powered prompts come in.

This article is a battle-tested collection of 50 prompts you can use right now to automate repetitive tasks, generate formulas, create macros (VBA for Excel or Google Apps Script for Sheets), and build professional dashboards. These prompts are designed to work with any AI assistant like ChatGPT, Claude, or Copilot. I've used most of them daily in my workflow, and they save me hours every week.

Why Use Prompts for Spreadsheets?

Instead of memorizing every function or debugging VBA code for hours, you can describe what you need in plain English. The AI generates the formula, script, or chart configuration. This approach is especially useful for:
- Speeding up formula creation (e.g., nested IFs, XLOOKUP, QUERY)
- Automating data cleaning (removing duplicates, formatting dates)
- Building interactive dashboards with conditional formatting and slicers
- Writing macros for repetitive tasks without knowing VBA or Apps Script

All examples below are tested on Excel 2026 (Microsoft 365) and Google Sheets (July 2026). Note that some Google Sheets functions (like IMAGE or QUERY) are unique to Sheets, while others (like XLOOKUP) are now available in both platforms.

Section 1: Formula Generation Prompts

1.1 Basic Lookups and References

Prompt: "Write an Excel formula that looks up the price of a product in column B where the product name in column A matches cell D2. Use XLOOKUP."

Result: =XLOOKUP(D2, A:A, B:B, "Not found")

This is straightforward but saves typing. For Google Sheets, you can prompt: "Use INDEX-MATCH instead because XLOOKUP is not available in my older Excel version." The AI will adjust.

Prompt: "I have two sheets: 'Sales' and 'Products'. In 'Sales', column A has product IDs. I want to pull the product name from 'Products' column B where IDs match. Generate a formula using VLOOKUP."

Result: =VLOOKUP(A2, Products!A:B, 2, FALSE)

1.2 Conditional Logic and Nested IFs

Prompt: "Create a nested IF formula in Excel that categorizes order values: if value is over 1000, 'High'; if between 500 and 1000, 'Medium'; else 'Low'. Column is B2."

Result: =IF(B2>1000, "High", IF(B2>=500, "Medium", "Low"))

Prompt: "In Google Sheets, I want to check if a date in cell C2 is a weekend. If yes, return 'Weekend', else 'Weekday'. Use WEEKDAY function."

Result: =IF(OR(WEEKDAY(C2)=1, WEEKDAY(C2)=7), "Weekend", "Weekday")

1.3 Text Manipulation

Prompt: "I have a column of full names in format 'Last, First' (e.g., 'Smith, John'). Write an Excel formula to extract the first name only."

Result: =TRIM(MID(A2, FIND(",", A2)+1, LEN(A2)))

Prompt: "In Google Sheets, combine first name in A2 and last name in B2 with a space, but if last name is blank, just return first name."

Result: =IF(B2="", A2, A2&" "&B2)

1.4 Date and Time Calculations

Prompt: "Calculate the number of working days between two dates in Excel, excluding weekends and holidays listed in H2:H10. Start date in A2, end date in B2."

Result: =NETWORKDAYS(A2, B2, H2:H10)

Prompt: "In Google Sheets, I need to add 30 days to a date in cell C2, but only if the result falls on a weekday. If it's a weekend, move to next Monday."

Result: =WORKDAY(C2, 30) (This already handles weekends; the AI can explain that WORKDAY skips weekends by default.)

1.5 Statistical and Aggregate Functions

Prompt: "Generate a Google Sheets formula to calculate the average sales per region from a table where column A is region and column B is sales. Use QUERY."

Result: =QUERY(A:B, "select A, avg(B) where A is not null group by A label avg(B) 'Avg Sales'")

Prompt: "In Excel, find the second largest value in a range D2:D100 that is above 50."

Result: =LARGE(IF(D2:D100>50, D2:D100), 2) (entered as array formula with Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365)

Section 2: Macro and Script Prompts

2.1 Excel VBA Macros

Prompt: "Write a VBA macro that loops through all cells in column A of Sheet1 and deletes the entire row if the cell contains the word 'Delete'. Stop at the last used row."

Result:

Sub DeleteRowsWithDelete()
    Dim lastRow As Long
    Dim i As Long
    lastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = lastRow To 1 Step -1
        If InStr(1, Cells(i, 1).Value, "Delete", vbTextCompare) > 0 Then
            Rows(i).Delete
        End If
    Next i
End Sub

Prompt: "Create a VBA function that converts a range of cells from US date format (MM/DD/YYYY) to European format (DD/MM/YYYY) in place."

Result:

Sub ConvertDateFormat()
    Dim rng As Range
    Dim cell As Range
    Set rng = Selection
    For Each cell In rng
        If IsDate(cell.Value) Then
            cell.Value = Format(cell.Value, "DD/MM/YYYY")
        End If
    Next cell
End Sub

2.2 Google Apps Script

Prompt: "Write a Google Apps Script function that sends an email notification when a cell in column E of the active sheet changes to 'Overdue'. The email should include the row data."

Result:

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var col = range.getColumn();
  var row = range.getRow();
  if (col == 5 && e.value == 'Overdue') {
    var data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
    var subject = 'Task Overdue: ' + data[0];
    var body = 'Row details: ' + data.join(', ');
    MailApp.sendEmail('manager@example.com', subject, body);
  }
}

Prompt: "Create a script that imports a CSV file from a given URL into a new sheet in Google Sheets. Name the sheet based on today's date."

Result:

function importCSVFromURL(url) {
  var response = UrlFetchApp.fetch(url);
  var csv = response.getContentText();
  var rows = Utilities.parseCsv(csv);
  var sheetName = 'Data_' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.insertSheet(sheetName);
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

Section 3: Dashboard and Visualization Prompts

3.1 Conditional Formatting Rules

Prompt: "In Excel, I want to highlight entire rows in a table (A2:G100) where the value in column C is greater than 100. Write the conditional formatting rule using a formula."

Result: Select range A2:G100, go to Home > Conditional Formatting > New Rule > Use a formula. Enter: =$C2>100 and set your formatting.

Prompt: "Google Sheets: Apply a color scale to column D (sales) from green (low) to red (high), but only for rows where column A contains 'Active'."

Result: Use custom formula with conditional formatting: =$A1="Active" for the range $D$1:$D$100, then apply color scale manually (Google Sheets doesn't support conditional color scale with formula directly, but you can use two rules: one for green fill, one for red, based on thresholds).

3.2 Chart Configuration Prompts

Prompt: "Generate an Excel chart configuration (VBA) that creates a clustered column chart from data in A1:B12, with titles and data labels."

Result:

Sub CreateChart()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=50, Height:=225)
    With chartObj.Chart
        .SetSourceData Source:=Range("A1:B12")
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Sales by Month"
        .SetElement (msoElementDataLabelOutSideEnd)
    End With
End Sub

Prompt: "In Google Sheets, I want to create a sparkline in cell F2 that shows a mini line chart of values in B2:E2. The sparkline should be red if the last value is lower than the first."

Result: =SPARKLINE(B2:E2, {"charttype","line"; "color", IF(E2<B2, "red", "green")})

3.3 Pivot Table Prompts

Prompt: "Write a VBA macro that creates a pivot table from range A1:D100 on Sheet1, with rows = Product, columns = Region, values = Sum of Sales, and places it on a new sheet named 'Pivot'."

Result:

Sub CreatePivot()
    Dim ptCache As PivotCache
    Dim pt As PivotTable
    Dim ws As Worksheet
    Set ws = Sheets.Add
    ws.Name = "Pivot"
    Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!A1:D100")
    Set pt = ptCache.CreatePivotTable(TableDestination:=ws.Range("A3"), TableName:="SalesPivot")
    With pt
        .PivotFields("Product").Orientation = xlRowField
        .PivotFields("Region").Orientation = xlColumnField
        .PivotFields("Sales").Orientation = xlDataField
    End With
End Sub

Prompt: "Google Sheets: Use a formula to create a pivot-like summary of sales by category. Data in A:B (Category, Sales). Use QUERY to show total sales per category sorted descending."

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

Section 4: Data Cleaning and Transformation Prompts

Prompt: "I have a column of phone numbers in various formats. Write an Excel formula that removes all non-numeric characters and returns only digits."

Result: (if using Excel 365) =TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)*1, "")) (array formula) or simpler with newer functions: =LET(s, A2, CONCAT(IFERROR(MID(s, SEQUENCE(LEN(s)), 1)*1, "")))

Prompt: "In Google Sheets, I need to split a column of full addresses (like '123 Main St, Springfield, IL 62701') into separate columns: Street, City, State, Zip. The pattern is comma-separated but state and zip have a space."

Result: Use SPLIT and REGEXEXTRACT. First split by comma: =SPLIT(A2, ",") gives three parts. Then for the state/zip part, use =REGEXEXTRACT(C2, "(\\w+) (\\d+)") to extract state and zip separately.

Prompt: "Remove duplicate rows from a sheet based on column A and B combined. Write a Google Apps Script that deletes duplicates."

Result:

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  var seen = {};
  for (var i = 0; i < data.length; i++) {
    var key = data[i][0] + '|' + data[i][1];
    if (!seen[key]) {
      seen[key] = true;
      newData.push(data[i]);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Section 5: Advanced and Niche Prompts

5.1 Google Sheets Unique Functions

Prompt: "In Google Sheets, use the IMAGE function to insert a product image from a URL in column C next to each product name in column A. But only if column D (in stock) is 'Yes'."

Result: =IF(D2="Yes", IMAGE(C2, 1), "No image")

Prompt: "Use GOOGLEFINANCE to get the current price of AAPL and MSFT in cells B2 and B3, and calculate the difference."

Result: B2: =GOOGLEFINANCE("AAPL", "price"), B3: =GOOGLEFINANCE("MSFT", "price"), B4: =B2-B3

5.2 Array Formulas and LAMBDA (Excel 365)

Prompt: "Write a LAMBDA function in Excel that takes a range and a threshold, and returns the sum of all values above that threshold."

Result: =LAMBDA(range, threshold, SUM(FILTER(range, range>threshold)))(A2:A10, 100)

Prompt: "Use MAP function to apply a 10% discount to all prices in column B and return the result in column C as a single array formula."

Result: =MAP(B2:B10, LAMBDA(price, price*0.9))

5.3 Integration with External Data

Prompt: "Write a Google Apps Script that fetches data from a public API (JSON) and populates a sheet. For example, get current weather for a city."

Result:

function getWeather() {
  var city = "London";
  var apiKey = "YOUR_API_KEY";
  var url = "https://api.openweathermap.org/data/2.5/weather?q=" + city + "&appid=" + apiKey;
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange("A1").setValue("Temperature (K): " + json.main.temp);
  sheet.getRange("A2").setValue("Humidity: " + json.main.humidity);
}

Conclusion

These 50 prompts cover the most common spreadsheet tasks I encounter daily. The key is to describe your problem clearly—include column names, sheet names, and expected output. AI models are now very good at generating correct formulas and scripts, but always test on a copy of your data first.

Start with the formula prompts if you're new to AI-assisted work, then move to macros and scripts for deeper automation. For dashboards, combine conditional formatting with chart generation prompts. Over time, you'll build your own library of prompts tailored to your specific workflows.

Remember, the best prompt is the one that saves you time. Don't hesitate to iterate—if the first result isn't perfect, refine your description. Happy spreadsheeting!