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

Introduction

Excel and Google Sheets are powerful tools, but many users barely scratch the surface. Whether you're a data analyst, a marketer, or a small business owner, the ability to automate repetitive tasks, build dynamic dashboards, and write complex formulas can save hours each week. This article provides a curated collection of prompts for Excel and Google Sheets, organized by skill level: Basic, Advanced, and Expert. Each prompt includes a specific task, the exact prompt to use, and a realistic example result. By the end, you'll have a ready-to-use library of prompts that can be copied directly into your spreadsheet or AI assistant.

Basic Prompts

1. Task: Create a simple SUM formula across multiple sheets

Prompt: "Write an Excel formula that sums the values in cell A1 from Sheet1, Sheet2, and Sheet3."
Example result: =SUM(Sheet1:Sheet3!A1) — This formula adds the values from A1 across three consecutive sheets.

2. Task: Generate a VLOOKUP for product pricing

Prompt: "Create a VLOOKUP formula in Google Sheets that looks up a product ID in column A of the 'Pricing' sheet and returns the price from column C."
Example result: =VLOOKUP(A2, Pricing!A:C, 3, FALSE) — This returns the exact price for the product ID in cell A2.

3. Task: Highlight duplicate values with conditional formatting

Prompt: "Write a conditional formatting rule for Excel that highlights duplicate values in the range A1:A100 with a yellow fill."
Example result: Use the rule =COUNTIF($A$1:$A$100, A1)>1 and set the fill color to yellow.

4. Task: Create a dropdown list for data validation

Prompt: "How do I create a dropdown list in Google Sheets that allows users to select from 'Yes', 'No', or 'Maybe'?"
Example result: Select cell B2, go to Data > Data validation, choose 'List of items', and enter Yes, No, Maybe.

5. Task: Calculate the average of a filtered range

Prompt: "Write a formula to calculate the average of visible cells in column D after applying a filter in Excel."
Example result: =SUBTOTAL(1, D:D) — The number 1 specifies the AVERAGE function for visible cells only.

Advanced Prompts

6. Task: Create a dynamic named range that expands with data

Prompt: "Define a named range in Excel that automatically expands when new rows are added to column A."
Example result: Use the OFFSET formula in Name Manager: =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1) — This creates a range starting at A1 that grows as data is added.

7. Task: Build a dashboard with sparklines for monthly trends

Prompt: "Insert sparklines in Google Sheets to show the trend of monthly sales data in cells B2:M2, placed in cell N2."
Example result: Select cell N2, go to Insert > Sparkline, and set the data range to B2:M2. The resulting mini-chart shows the sales trend.

8. Task: Automate a PivotTable refresh on file open

Prompt: "Write a VBA macro in Excel that automatically refreshes all PivotTables when the workbook is opened."
Example result: Insert this code in the ThisWorkbook module:

Private Sub Workbook_Open()
    Dim pt As PivotTable
    For Each pt In ThisWorkbook.PivotTables
        pt.RefreshTable
    Next pt
End Sub

9. Task: Generate a report with INDIRECT and SUMIF across sheets

Prompt: "Create a formula that sums sales for a specific month from multiple monthly sheets (Jan, Feb, Mar) using a cell reference for the month name."
Example result: =SUMIF(INDIRECT(B1&"!A:A"), "ProductX", INDIRECT(B1&"!B:B")) — Where B1 contains "Jan", the formula sums sales of ProductX in January's sheet.

10. Task: Use QUERY in Google Sheets to filter and sort data

Prompt: "Write a Google Sheets QUERY that selects columns A, B, and C from a sheet named 'Data' where column C is greater than 100, sorted by column B descending."
Example result: =QUERY(Data!A:C, "SELECT A, B, C WHERE C > 100 ORDER BY B DESC", 1) — The last parameter indicates the data has a header row.

Expert Prompts

11. Task: Create a custom VBA function to extract email domains

Prompt: "Write a VBA function in Excel that extracts the domain from an email address (e.g., 'user@example.com' returns 'example.com')."
Example result: Insert this in a standard module:

Function ExtractDomain(email As String) As String
    Dim atPos As Integer
    atPos = InStr(email, "@")
    If atPos > 0 Then
        ExtractDomain = Mid(email, atPos + 1)
    Else
        ExtractDomain = ""
    End If
End Function

Then use =ExtractDomain(A1) in a cell.

12. Task: Build a real-time stock dashboard with GOOGLEFINANCE

Prompt: "Create a Google Sheets dashboard that shows the current price, change, and volume for a list of stock tickers using GOOGLEFINANCE."
Example result: In cells:
=GOOGLEFINANCE(A2, "price") for price
=GOOGLEFINANCE(A2, "changepct") for percentage change
=GOOGLEFINANCE(A2, "volume") for volume
With tickers in column A.

13. Task: Automate data import from a CSV file using Power Query

Prompt: "Write a Power Query M script in Excel to import a CSV file from a specified folder, remove the first 5 rows, and load only columns A and C."
Example result: In Power Query Editor:

let
    Source = Csv.Document(File.Contents("C:\Data\sales.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Top Rows" = Table.Skip(Source,5),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column3"})
in
    #"Removed Other Columns"

14. Task: Create a dynamic SUMIFS with multiple criteria using arrays

Prompt: "Write an Excel formula that sums sales for multiple products (ProductA, ProductB) in the region 'North' using an array constant."
Example result: =SUMPRODUCT((A2:A100={"ProductA","ProductB"})*(B2:B100="North")*C2:C100) — This uses array multiplication to sum only matching rows.

15. Task: Build a custom macro to send an email with a PDF attachment

Prompt: "Write a VBA macro in Excel that saves the active sheet as PDF and sends it via Outlook with a specific subject and body."
Example result:

Sub SendPDF()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim pdfPath As String
    pdfPath = ThisWorkbook.Path & "\Report.pdf"
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath

    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
    Dim olMail As Object
    Set olMail = olApp.CreateItem(0)
    With olMail
        .Subject = "Monthly Report"
        .Body = "Please find the attached report."
        .Attachments.Add pdfPath
        .Send
    End With
End Sub

16. Task: Use LAMBDA function in Excel for custom calculations

Prompt: "Create an Excel LAMBDA function that calculates the compound annual growth rate (CAGR) given start value, end value, and number of periods."
Example result:

=LAMBDA(start, end, periods, (end/start)^(1/periods)-1)

Then name it CAGR in Name Manager and use =CAGR(100, 200, 5) to get 14.87%.

17. Task: Generate a waterfall chart from a pivot table

Prompt: "Create a waterfall chart in Excel that visualizes monthly profit and loss using data from a pivot table."
Example result: After creating a pivot table with months in rows and profit in values, select the pivot table, go to Insert > Waterfall Chart. Adjust the data series to mark increases and decreases.

18. Task: Write a Google Apps Script to send daily email reminders

Prompt: "Write a Google Apps Script that checks a Google Sheet column for tasks due today and sends an email reminder for each."
Example result:

function sendReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var today = new Date();
  for (var i = 1; i < data.length; i++) {
    var dueDate = new Date(data[i][2]);
    if (dueDate.toDateString() === today.toDateString()) {
      MailApp.sendEmail(data[i][1], "Task Reminder", "Task: " + data[i][0] + " is due today.");
    }
  }
}

Set a trigger to run daily.

19. Task: Create a two-way lookup using INDEX and MATCH

Prompt: "Write an Excel formula that looks up a value at the intersection of a specified row and column from a table."
Example result: =INDEX(B2:E10, MATCH("ProductX", A2:A10, 0), MATCH("March", B1:E1, 0)) — This returns the value where ProductX meets March.

20. Task: Build a macro to unpivot data from a cross-tab format

Prompt: "Write a VBA macro that converts a cross-tab table (months as columns, products as rows) into a flat list with columns: Product, Month, Value."
Example result:

Sub Unpivot()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim lastRow As Long, lastCol As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    Dim outRow As Long
    outRow = 2
    For i = 2 To lastRow
        For j = 2 To lastCol
            ws.Cells(outRow, 5).Value = ws.Cells(i, 1).Value  ' Product
            ws.Cells(outRow, 6).Value = ws.Cells(1, j).Value   ' Month
            ws.Cells(outRow, 7).Value = ws.Cells(i, j).Value   ' Value
            outRow = outRow + 1
        Next j
    Next i
End Sub

21. Task: Use XLOOKUP with multiple criteria in Excel

Prompt: "Write an XLOOKUP formula that searches for a product ID and region simultaneously, returning the sales amount."
Example result: =XLOOKUP(1, (A2:A100="Prod1")*(B2:B100="East"), C2:C100) — This uses a binary array as the lookup value.

22. Task: Create a dynamic chart that updates with new data

Prompt: "Set up an Excel chart that automatically includes new rows added to the source data range."
Example result: Convert the data range to an Excel Table (Ctrl+T). Any chart based on the table will automatically expand when new rows are added.

23. Task: Write a Google Sheets script to merge duplicates

Prompt: "Write a Google Apps Script that merges rows with duplicate IDs, summing the values in column B."
Example result:

function mergeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var merged = {};
  for (var i = 1; i < data.length; i++) {
    var id = data[i][0];
    var val = data[i][1];
    if (merged[id]) {
      merged[id] += val;
    } else {
      merged[id] = val;
    }
  }
  var output = Object.keys(merged).map(function(key) {
    return [key, merged[key]];
  });
  sheet.getRange(2, 4, output.length, 2).setValues(output);
}

24. Task: Create a custom Excel add-in with VBA

Prompt: "Describe the steps to create a simple Excel add-in that adds a custom ribbon button to run a macro."
Example result: Save the workbook as an .xlam file. Use the Ribbon XML editor or a third-party tool to add a button. Then go to File > Options > Add-ins and load the add-in.

25. Task: Use Power Pivot to create a measure with DAX

Prompt: "Write a DAX measure in Power Pivot that calculates the year-over-year sales growth percentage."
Example result: YoY Growth % := DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date])))

26. Task: Build a real-time collaborative dashboard in Google Sheets

Prompt: "Create a Google Sheets dashboard that uses IMPORTRANGE to pull data from multiple sheets and updates every hour."
Example result: Use =IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C10") in a cell. The dashboard will refresh automatically, but you can manually trigger a refresh by editing any cell.

27. Task: Write a macro to protect all sheets except one

Prompt: "Write a VBA macro that protects all sheets in the workbook except a sheet named 'Input', with a password."
Example result:

Sub ProtectAllExceptInput()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Input" Then
            ws.Protect Password:="mypassword"
        End If
    Next ws
End Sub

28. Task: Use TEXTJOIN with multiple conditions

Prompt: "Write an Excel formula that concatenates all product names in column A where the status in column B is 'Active', separated by commas."
Example result: =TEXTJOIN(", ", TRUE, IF(B2:B100="Active", A2:A100, "")) — Enter as an array formula (Ctrl+Shift+Enter in older Excel).

29. Task: Create a macro to export each sheet as a separate PDF

Prompt: "Write a VBA macro that saves each sheet in the workbook as a separate PDF file in the same folder."
Example result:

Sub ExportSheetsToPDF()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & ws.Name & ".pdf"
    Next ws
End Sub

30. Task: Use Power Query to merge multiple CSV files from a folder

Prompt: "Write a Power Query script that combines all CSV files from a folder into a single table, adding a column with the source filename."
Example result: In Power Query, use Data > Get Data > From File > From Folder. Then combine files with the 'Combine & Load' option. The resulting query automatically adds a 'Source.Name' column.

Conclusion

These 30 prompts cover a wide range of tasks from basic formulas to advanced macros and dashboards. By integrating these into your workflow, you can significantly reduce manual effort and improve data accuracy. Start with the basic prompts to build confidence, then gradually explore advanced and expert techniques. Remember, the key to mastery is consistent practice and adaptation to your specific needs. For more hands-on guidance, consider exploring structured courses that walk you through real-world projects step by step. Happy spreadsheeting!