Introduction
Spreadsheets are the backbone of data-driven decision-making, yet most users only scratch the surface—using basic SUM or AVERAGE formulas while complex tasks remain manual. In 2026, with AI assistants like ChatGPT, Claude, and Gemini, you can generate complex formulas, VBA macros, and interactive dashboards in seconds. This article presents 30 carefully crafted prompts—organized by skill level—that will transform how you work with Excel and Google Sheets.
Whether you are an analyst building a revenue dashboard, a marketer cleaning customer lists, or a manager automating weekly reports, these prompts save hours and reduce errors. Each prompt includes a task description, the exact prompt text, and a realistic example of the output you can expect.
Basic Prompts for Everyday Tasks
These prompts help beginners automate routine operations like cleaning data, applying conditional formatting, and creating simple charts.
| # | Task | Prompt | Example Result |
|---|---|---|---|
| 1 | Remove duplicates | Write an Excel formula to remove duplicate rows from column A, keeping only the first occurrence. | =UNIQUE(A:A) (Excel 365) or =SORT(UNIQUE(A:A)) |
| 2 | Conditional formatting rule | Create a Google Sheets conditional formatting rule that highlights cells in column B if they are greater than 100 and less than 200. | Custom formula: =AND(B1>100, B1<200) applied to range B:B |
| 3 | Text splitting | Write a formula to split full names in column A (format: 'Last, First') into two separate columns. | =SPLIT(A2, ", ") (Sheets) or =TEXTSPLIT(A2,",") (Excel) |
| 4 | Basic IF logic | Create an IF formula that marks orders as 'High Priority' if the value in C2 exceeds 500, otherwise 'Normal'. | =IF(C2>500, "High Priority", "Normal") |
| 5 | Date extraction | Write a formula to extract the month name from a date in cell D2. | =TEXT(D2, "MMMM") (Excel) or =TEXT(D2, "MMMM") (Sheets) |
| 6 | Sum with criteria | Sum all values in column E where column F equals 'Approved'. | =SUMIF(F:F, "Approved", E:E) |
| 7 | Count non-blank | Count how many cells in range G1:G100 are not empty. | =COUNTA(G1:G100) |
| 8 | Average ignoring zeros | Average numbers in H1:H50 while ignoring zero values. | =AVERAGEIF(H1:H50, "<>0") |
| 9 | VLOOKUP example | Look up the price of product ID 'P123' from a table in columns A and B. | =VLOOKUP("P123", A:B, 2, FALSE) |
| 10 | Convert text to number | Convert text-formatted numbers in column I to actual numeric values. | =VALUE(I1) and copy down |
Intermediate Prompts for Data Analysis and Pivot Tables
These prompts tackle pivot tables, array formulas, conditional sums, and data validation—core skills for analysts.
| # | Task | Prompt | Example Result |
|---|---|---|---|
| 11 | Create pivot table | Write VBA code to create a pivot table from range A1:C100 with rows as 'Region', values as sum of 'Sales'. | VBA macro that adds a new sheet with the pivot table |
| 12 | Dynamic named range | Create a named range in Excel that automatically expands when new data is added to column A. | =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) |
| 13 | INDEX-MATCH | Replace a VLOOKUP with INDEX-MATCH for a two-way lookup (find price by product ID and region). | =INDEX(C2:C100, MATCH(1, (A2:A100="P123")*(B2:B100="North"), 0)) |
| 14 | Array formula for total | Sum the product of two columns (quantity * unit price) without a helper column. | =SUMPRODUCT(A2:A100, B2:B100) |
| 15 | Data validation list | Create a dropdown list in cell D1 that shows unique values from column E. | Data Validation → List → =UNIQUE(E:E) |
| 16 | SUMIFS with dates | Sum sales in column F where the date in column G is in January 2026. | =SUMIFS(F:F, G:G, ">=1/1/2026", G:G, "<=1/31/2026") |
| 17 | COUNTIFS multiple criteria | Count orders where status is 'Shipped' and value > 200. | =COUNTIFS(H:H, "Shipped", I:I, ">200") |
| 18 | XLOOKUP example | Use XLOOKUP to find the email of a user by their ID, returning 'Not Found' if missing. | =XLOOKUP("U789", A:A, B:B, "Not Found") |
| 19 | Conditional sum across sheets | Sum values from cell B2 across sheets named Jan, Feb, Mar. | =SUM(Jan:Mar!B2) |
| 20 | Remove extra spaces | Clean text in column J by removing leading/trailing spaces and extra internal spaces. | =TRIM(J1) |
Expert Prompts for Macros, VBA, and Automated Dashboards
For power users: these prompts generate VBA macros, Google Apps Script, dynamic dashboards, and advanced data transformations.
| # | Task | Prompt | Example Result |
|---|---|---|---|
| 21 | VBA to email report | Write VBA code that sends an email with the active sheet as PDF attachment via Outlook. | Sub SendPDF() ... End Sub using ActiveSheet.ExportAsFixedFormat |
| 22 | Google Apps Script import | Write a script in Google Sheets that imports data from a public CSV URL every hour. | function importCSV() { ... UrlFetchApp.fetch(...) } with time trigger |
| 23 | Dynamic dashboard chart | Create a combo chart (bar + line) that updates automatically when a slicer is used. | PivotChart with Slicer connected to timeline |
| 24 | Power Query merge | Write a Power Query M formula to merge two tables by matching CustomerID. | Table.NestedJoin(Table1, {"ID"}, Table2, {"ID"}, "NewCol") |
| 25 | VBA to protect all sheets | Create a macro that protects all sheets with a password, except a summary sheet. | Loop through Worksheets, apply Protect Password:="mypass" |
| 26 | Google Sheets API read | Write Apps Script to fetch data from a REST API and write it into a sheet. | function getData() { ... JSON.parse(response) ... range.setValues() } |
| 27 | VBA user form | Generate VBA code for a user form that takes two inputs and inserts them into a database sheet. | UserForm with TextBoxes, CommandButton, Range("A1").Value = TextBox1.Text |
| 28 | Conditional formatting with VBA | Write VBA to apply conditional formatting to a range based on a cell value in another column. | Range("A1:A100").FormatConditions.Add Type:=xlExpression, Formula1:="=$B1>100" |
| 29 | Excel to PowerPoint | Create VBA that copies a chart from Excel and pastes it into a new PowerPoint slide. | Late binding to PowerPoint.Application, ppApp.Presentations.Add |
| 30 | Real-time dashboard refresh | Build a Google Sheets dashboard that uses IMPORTRANGE and QUERY to consolidate data from 5 sheets and auto-refresh every minute. | =QUERY(IMPORTRANGE("key", "Sheet1!A:Z"), "select Col1, sum(Col3) group by Col1") |
Real-World Case Study: Sales Dashboard in 20 Minutes
A mid-size e-commerce company needed a weekly sales dashboard showing revenue by region, top products, and order status. Using prompt #30, the analyst built a Google Sheets dashboard that:
- Pulls data from Shopify via CSV import (prompt #22)
- Cleans product names with TRIM and SPLIT
- Creates a pivot table summarizing sales by region (prompt #11)
- Adds a dynamic chart with slicers for date range
Result: manual 3-hour task reduced to 20 minutes of prompt engineering. The dashboard is now used by 12 managers every Monday.
How to Customize Prompts for Your Data
The prompts above are templates. To adapt them:
1. Replace placeholder ranges (e.g., A1:A100) with your actual data range.
2. Change criteria values (e.g., 'Approved' to 'Pending').
3. For VBA/Apps Script, adjust sheet names and file paths.
4. Test on a copy of your data before applying to production.
For complex automations, combine multiple prompts: first clean data, then create pivot, then generate chart. Each step can be a separate prompt.
Tools and Resources to Extend Your Skills
- Microsoft Excel official documentation
- Google Sheets function list
- VBA reference on Microsoft Learn
- Google Apps Script documentation
- Power Query M formula reference
ASI Biont supports integration with Excel and Google Sheets through API connectors for automated data pipelines—learn more at asibiont.com/courses.
Conclusion
Prompt engineering is not just for text generation—it is a superpower for spreadsheet users. The 30 prompts in this article cover everything from basic formulas to advanced VBA macros and dashboard automation. By copying and adapting these prompts, you can eliminate repetitive tasks, reduce errors, and focus on analysis.
Start with the basic prompts to build confidence, then move to intermediate and expert levels as your needs grow. Bookmark this article and revisit it whenever you face a new spreadsheet challenge. The best part: you don't need to memorize every function—just know how to ask the right prompt.
Comments