Working with spreadsheets is a core skill across industries, but the real magic happens when you combine structured prompts with the powerful features of Excel and Google Sheets. In 2026, the ability to craft precise prompts—whether for generating formulas, automating tasks with macros, or building interactive dashboards—can save dozens of hours per week. This article is a curated collection of 30 prompts, organized by skill level: Basic, Advanced, and Expert. Each prompt includes a clear task, the exact prompt you can use (with placeholders), and a realistic example output. Whether you are a financial analyst, a marketing manager, or a data engineer, these prompts will help you get more done in less time.
Why Prompts Matter for Spreadsheets
Spreadsheet software has evolved far beyond simple row-and-column entry. Modern Excel (Microsoft 365) and Google Sheets support dynamic arrays, LAMBDA functions, Apps Script, and Power Query. However, many users still manually type formulas or record macros step by step. A well-structured prompt—whether you feed it to an AI assistant like ChatGPT or use it as a blueprint for your own logic—can:
- Reduce formula creation time by up to 70%
- Eliminate syntax errors
- Generate reusable VBA or Apps Script code in seconds
- Design dashboard layouts with pre-configured charts and conditional formatting
The prompts below are written to be universal: you can use them with any AI tool that understands natural language (GPT-4, Claude, Gemini) or as a mental checklist when building your own solutions.
Basic Prompts: Formulas and Conditional Logic
These prompts are designed for everyday tasks: summing ranges, applying IF logic, cleaning text, and basic date calculations. No scripting knowledge required.
1. Calculate Weighted Average
Task: Compute a weighted average from two columns (values and weights).
Prompt: "Generate an Excel formula that calculates the weighted average of values in column B (B2:B100) with corresponding weights in column C (C2:C100). Assume no blank cells. Use SUMPRODUCT and SUM."
Example Result: =SUMPRODUCT(B2:B100, C2:C100)/SUM(C2:C100)
2. Conditional Formatting for Overdue Dates
Task: Highlight cells in column D that contain a date older than today.
Prompt: "Write a conditional formatting rule (as a formula) for Google Sheets that highlights cells in D2:D100 if the date is before today. The rule should ignore blank cells."
Example Result: =AND(D2<>"", D2<TODAY())
3. Extract First Name from Full Name
Task: Split a full name into first name only.
Prompt: "Create an Excel formula that extracts the first name from a full name in cell A2. Assume the first name is everything before the first space. Handle cases where there might be a middle initial."
Example Result: =LEFT(A2, FIND(" ", A2)-1)
4. Count Orders Above a Threshold
Task: Count how many values in a range exceed a target number.
Prompt: "Give me a COUNTIF formula for Google Sheets that counts cells in column F (F2:F200) that are greater than 1000."
Example Result: =COUNTIF(F2:F200, ">1000")
5. VLOOKUP with Error Handling
Task: Look up a product price and return "Not Found" if missing.
Prompt: "Write an Excel VLOOKUP formula that searches for the value in cell A2 in the range ProductTable (columns A:D), returns the value from column 4, and shows 'Not Found' if the value is missing."
Example Result: =IFERROR(VLOOKUP(A2, ProductTable, 4, FALSE), "Not Found")
6. Calculate Age from Birthdate
Task: Compute age in years as of today.
Prompt: "Create a Google Sheets formula that calculates age in years from a birthdate in cell B2. Use DATEDIF."
Example Result: =DATEDIF(B2, TODAY(), "Y")
7. Remove Extra Spaces
Task: Clean text in a column by removing leading, trailing, and double spaces.
Prompt: "Provide an Excel formula that trims spaces from cell A2 and replaces multiple spaces with a single space."
Example Result: =TRIM(A2)
8. Combine First and Last Name with Separator
Task: Merge first name (column A) and last name (column B) with a comma.
Prompt: "Write a formula for Google Sheets that combines A2 and B2 with a comma and a space."
Example Result: =A2 & ", " & B2
9. Convert Text to Proper Case
Task: Transform a sentence into title case.
Prompt: "Create an Excel formula that converts the text in cell A2 to proper case (first letter of each word capitalized)."
Example Result: =PROPER(A2)
10. Generate a Unique ID
Task: Create a sequential ID with a prefix.
Prompt: "Give me a formula that generates a unique ID like 'ORD-001', 'ORD-002' in column A when data is entered in column B. Use ROW()."
Example Result: ="ORD-" & TEXT(ROW()-1, "000")
Advanced Prompts: Array Formulas, Nested Functions, and Data Validation
These prompts require understanding of array operations, dynamic arrays (Excel 365), and data validation techniques. They produce multi-cell outputs or complex logic.
11. Unique List from Duplicate Data
Task: Extract all distinct values from a column that may have duplicates.
Prompt: "Generate an Excel dynamic array formula that returns a unique list of values from column A (A2:A500). Use the UNIQUE function."
Example Result: =UNIQUE(A2:A500)
12. Filter Data Based on Multiple Criteria
Task: Return rows where column B equals "Active" and column C is greater than 100.
Prompt: "Write a FILTER formula for Google Sheets that returns columns A through D from the range A2:D200 where column B is 'Active' and column C is >100."
Example Result: =FILTER(A2:D200, (B2:B200="Active")*(C2:C200>100))
13. Running Total with Dynamic Array
Task: Calculate a cumulative sum that updates automatically as new rows are added.
Prompt: "Create an Excel formula using SCAN or MMULT that produces a running total of values in column B (B2:B100)."
Example Result (using SCAN): =SCAN(0, B2:B100, LAMBDA(a, b, a+b))
14. Nested XLOOKUP with Multiple Tables
Task: Look up a value in one table, then use that result to look up in another table.
Prompt: "Write an XLOOKUP formula that first finds the department ID for an employee name in Table1 (A2:B100), then uses that ID to return the department name from Table2 (D2:E20)."
Example Result: =XLOOKUP(XLOOKUP(G2, A2:A100, B2:B100), D2:D20, E2:E20)
15. Data Validation Dropdown with Dynamic Range
Task: Create a dropdown list that automatically expands when new items are added.
Prompt: "Provide the steps and formula to set up a data validation dropdown in Google Sheets that references a dynamic named range using OFFSET or FILTER. Assume the source list is in column H starting at H2."
Example Result: Use a named range with formula =OFFSET(Sheet1!$H$2, 0, 0, COUNTA(Sheet1!$H:$H)-1, 1)
16. Conditional Sum Across Multiple Sheets
Task: Sum values from the same cell in multiple sheets based on a condition.
Prompt: "Write a 3D SUMIF formula in Excel that sums cell C2 across sheets named Jan, Feb, Mar if the corresponding value in column B on each sheet equals 'Product A'."
Example Result: =SUMPRODUCT(SUMIF(INDIRECT("'" & {"Jan","Feb","Mar"} & "'!B:B"), "Product A", INDIRECT("'" & {"Jan","Feb","Mar"} & "'!C:C")))
17. Extract Numbers from Mixed Text
Task: Isolate numeric characters from a string like "Order1234".
Prompt: "Give me an Excel formula that extracts only the digits from cell A2. Use TEXTJOIN and MID with an array constant."
Example Result: =TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), "")) (enter as array formula in older Excel)
18. Random Sample Without Duplicates
Task: Pick 10 random rows from a dataset without repetition.
Prompt: "Create a Google Sheets formula that returns 10 random unique values from column A (A2:A200). Use SORTN with RANDARRAY."
Example Result: =SORTN(A2:A200, 10, 0, RANDARRAY(ROWS(A2:A200)), TRUE)
19. Two-Way Lookup (Matrix)
Task: Find the value at the intersection of a given row and column header.
Prompt: "Write an Excel formula that looks up the value where the row header in column A equals 'Product X' and the column header in row 1 equals 'Q1'. Use INDEX and MATCH."
Example Result: =INDEX(B2:Z100, MATCH("Product X", A2:A100, 0), MATCH("Q1", B1:Z1, 0))
20. Convert Rows to Columns (Unpivot)
Task: Transform a wide table with months as columns into a tall table.
Prompt: "Generate an Excel formula using TOCOL and WRAPROWS or a combination of INDEX and ROW to unpivot a table. Assume data in A1:D10 with headers in A1:D1."
Example Result (using Power Query is more robust, but with formulas): Use a helper column and =LET(data, A2:D10, ...) – a full solution is beyond a single formula; recommend Power Query for large datasets.
Expert Prompts: Macros, Scripts, and Interactive Dashboards
These prompts generate VBA code (Excel) or Google Apps Script (Sheets) to automate repetitive tasks, build custom functions, and create dynamic dashboards with charts and slicers.
21. VBA Macro to Merge Duplicate Rows and Sum Values
Task: Consolidate rows with the same ID and sum numeric columns.
Prompt: "Write a VBA macro that loops through column A in the active sheet, identifies duplicate values, and merges them into one row by summing columns B through E. Delete the duplicate rows afterward."
Example Result (snippet):
Sub MergeDuplicates()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim lastRow As Long, i As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastRow To 2 Step -1
If dict.exists(Cells(i, 1).Value) Then
' sum logic
Else
dict.Add Cells(i, 1).Value, i
End If
Next i
End Sub
22. Google Apps Script to Send Email Alerts
Task: Automatically send an email when a cell value exceeds a threshold.
Prompt: "Create a Google Apps Script function that triggers on edit. If column D (D2:D) contains a value greater than 1000, send an email to a specified address with the row details."
Example Result (snippet):
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
if (range.getColumn() == 4 && range.getValue() > 1000) {
var row = range.getRow();
var data = sheet.getRange(row, 1, 1, 4).getValues()[0];
MailApp.sendEmail("alert@example.com", "Threshold Reached", data.join(", "));
}
}
23. Build a Live Dashboard with Sparklines and Slicers
Task: Create a dashboard that updates automatically with new data.
Prompt: "Provide step-by-step instructions to build an Excel dashboard that includes: a line sparkline for monthly trends, a slicer for category filtering, and a dynamic chart using named ranges with OFFSET. Assume data in a table named SalesData."
Example Result (summary): Insert sparklines via Insert > Sparklines, create named ranges using =OFFSET(SalesData[Month],0,0,COUNTA(SalesData[Month]),1), add slicers from Table Design tab.
24. Custom LAMBDA Function for Tax Calculation
Task: Create a reusable function that computes progressive tax.
Prompt: "Write an Excel LAMBDA function named TAXBRACKET that takes income as input and returns tax based on brackets: 10% up to $10,000, 15% on $10,001–$40,000, 25% above $40,000."
Example Result: =LAMBDA(income, IF(income<=10000, income*0.1, IF(income<=40000, 1000+(income-10000)*0.15, 5500+(income-40000)*0.25)))
25. Google Sheets Script to Archive Old Rows
Task: Move rows older than 90 days to a separate sheet.
Prompt: "Write a Google Apps Script that runs daily (time-driven trigger) and moves all rows in 'Main' sheet where column A (date) is older than 90 days to an 'Archive' sheet. Delete the original rows."
Example Result (snippet):
function archiveOldRows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var main = ss.getSheetByName("Main");
var archive = ss.getSheetByName("Archive");
var data = main.getDataRange().getValues();
var cutoff = new Date();
cutoff.setDate(cutoff.getDate() - 90);
var rowsToMove = [];
for (var i = data.length-1; i >= 1; i--) {
if (data[i][0] < cutoff) {
rowsToMove.push(data[i]);
main.deleteRow(i+1);
}
}
if (rowsToMove.length > 0) {
archive.getRange(archive.getLastRow()+1, 1, rowsToMove.length, rowsToMove[0].length).setValues(rowsToMove);
}
}
26. VBA to Export Sheet as PDF
Task: Save the active sheet as a PDF with a dynamic filename.
Prompt: "Write a VBA macro that exports the active sheet to a PDF file in the same folder as the workbook, with a filename that includes the current date and the sheet name."
Example Result (snippet):
Sub ExportToPDF()
Dim path As String
path = ThisWorkbook.path & "\" & ActiveSheet.Name & "_" & Format(Date, "yyyymmdd") & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path
End Sub
27. Interactive Dashboard with Google Sheets QUERY
Task: Build a dashboard that filters data based on a dropdown selection.
Prompt: "Create a Google Sheets dashboard using QUERY function that references a validation dropdown in cell A1. The QUERY should return sales data from range Sheet2!A:E where column B (region) matches the dropdown selection."
Example Result: =QUERY(Sheet2!A:E, "select * where B = '"&A1&"'", 1)
28. Automate Chart Creation with VBA
Task: Generate a bar chart from a selected range with custom formatting.
Prompt: "Write a VBA macro that creates a clustered bar chart on a new sheet from the currently selected range. Set the chart title to 'Sales by Category' and apply a blue color scheme."
Example Result (snippet):
Sub CreateBarChart()
Dim cht As ChartObject
Set cht = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=50, Height:=300)
cht.Chart.SetSourceData Source:=Selection
cht.Chart.ChartType = xlColumnClustered
cht.Chart.HasTitle = True
cht.Chart.ChartTitle.Text = "Sales by Category"
End Sub
29. Google Sheets Script to Sync Data Between Sheets
Task: Copy new rows from one Google Sheet to another automatically.
Prompt: "Write an Apps Script that runs every hour and copies rows from 'Source' sheet (columns A–D) to 'Target' sheet in another spreadsheet (by ID), only if the row ID (column A) does not already exist in the target."
Example Result (snippet): Use SpreadsheetApp.openById() and compare IDs.
30. Full Dashboard with Real-Time Data Refresh
Task: Design a dashboard that pulls data from an external API and refreshes every 5 minutes.
Prompt: "Provide a Google Apps Script that uses UrlFetchApp to fetch JSON data from a public API (e.g., crypto prices), parses it, and writes it to a sheet. Then create a dashboard with a refresh button."
Example Result (snippet):
function refreshData() {
var response = UrlFetchApp.fetch("https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd");
var json = JSON.parse(response.getContentText());
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Prices");
sheet.getRange("A1").setValue(new Date());
sheet.getRange("B1").setValue(json.bitcoin.usd);
}
Note: ASI Biont supports connecting to external APIs for real-time data enrichment in your spreadsheets—learn more at asibiont.com/courses.
Best Practices for Using Prompts with Spreadsheets
- Be specific about ranges and sheet names. The more context you provide, the more accurate the generated formula or code will be.
- Use named ranges in Excel to make formulas easier to read and maintain.
- Test on a copy first. Especially with macros and scripts, always run on duplicate data.
- Combine prompts with AI tools. You can paste the prompt into ChatGPT or Copilot, then copy the result directly into your spreadsheet.
- Document your logic. Add comments in VBA or Apps Script so others (or future you) understand what each block does.
Conclusion
Mastering prompts for Excel and Google Sheets is a superpower in 2026. From simple formula generation to complex dashboard automation, the 30 prompts in this article cover the most common and impactful tasks. Start with the basic prompts to build confidence, then move to advanced formulas and finally to expert macros and scripts. As you integrate these patterns into your daily workflow, you will find yourself spending less time on repetitive tasks and more time on analysis and decision-making. Remember: the best prompt is the one that gives you exactly the result you need with minimal editing. Copy, paste, adapt, and save hours every week.
For more advanced integrations and automation courses, explore the full curriculum at ASI Biont.
Comments