If you've ever spent an entire afternoon wrestling with a pivot table or writing a VLOOKUP that just won't cooperate, you know the pain. Spreadsheets are powerful, but they can be unforgiving. And in 2026, with data growing faster than ever, the ability to extract insights quickly is a superpower. But here's the thing: you don't need to be a formula wizard or a programming expert anymore. With AI models like those powering ASI Biont, you can simply describe what you need in plain English and get a working formula, a clean dataset, or even a full dashboard plan. This isn't about replacing your skills—it's about amplifying them, freeing you to focus on the 'why' behind the numbers.
In this guide, I've curated 15 practical prompts that target the most common spreadsheet pain points: cleaning messy data, writing complex formulas, building reports, and forecasting trends. Each prompt is designed to work with both Excel and Google Sheets, and I've included concrete examples of inputs and outputs so you can see exactly how to use them. Whether you're a financial analyst drowning in quarterly reports, a marketer trying to make sense of campaign metrics, or a product manager tracking feature adoption, these prompts will help you cut through the noise. Let's dive in.
1. Cleaning Messy Data: The Universal Fixer
The Task: Your raw data is a mess—extra spaces, inconsistent date formats, mixed text cases, and maybe some stray punctuation. You need a formula to clean it all up.
The Prompt:
"I have a column of customer names in Excel that includes extra spaces, inconsistent capitalization, and some entries with trailing punctuation like 'Acme Corp.' or 'acme corp'. I need a formula that cleans these up: removes extra spaces, converts to proper case (first letter of each word capitalized), and strips any trailing periods. Please provide a single formula that works in both Excel and Google Sheets."
Example Result:
- Input: " acme corp. ", "GLOBEX INC.", "Initech, LLC"
- Output: "Acme Corp", "Globex Inc", "Initech, LLC" (Note: the formula should preserve the comma and 'LLC')
- Formula (Excel 365): =TRIM(SUBSTITUTE(PROPER(A1),".","")) — but this also removes periods from 'LLC', so a more robust version: =TRIM(PROPER(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," "," "))) then optionally re-add periods if needed. A better approach: use =TRIM(PROPER(A1)) and then =SUBSTITUTE(A1,". "," ") — but honestly, for a single cell, you might need a more complex nested formula. For Google Sheets, you could use =TRIM(PROPER(SUBSTITUTE(A1,"."," "))) and then =SUBSTITUTE(B1," "," ").
Pro Tip: For more complex cleaning, consider using Power Query in Excel or the REGEXREPLACE function in Google Sheets. Example: =REGEXREPLACE(PROPER(TRIM(A1)), "\.$", "") to remove a trailing period.
2. The VLOOKUP That Never Fails: XLOOKUP and INDEX/MATCH
The Task: You need to look up values across two different sheets, but VLOOKUP keeps breaking because the columns aren't in the right order or you need to return a column to the left.
The Prompt:
"I need to combine data from two sheets in Excel. Sheet1 has employee IDs in column A and names in column B. Sheet2 has employee IDs in column C and department in column D. I want to add a department column to Sheet1 by matching the employee IDs. Please provide a formula using XLOOKUP (or INDEX/MATCH if XLOOKUP isn't available) that works in both Excel and Google Sheets. Also, explain how to handle cases where an ID doesn't exist in Sheet2."
Example Result:
- Formula (Excel 365): =XLOOKUP(A2, Sheet2!$C$2:$C$100, Sheet2!$D$2:$D$100, "Not Found")
- Formula (Google Sheets): =INDEX(Sheet2!$D$2:$D$100, MATCH(A2, Sheet2!$C$2:$C$100, 0)) — but this returns #N/A if not found, so wrap with IFERROR: =IFERROR(INDEX(...), "Not Found")
- Explanation: XLOOKUP is simpler and more powerful than VLOOKUP; it doesn't require the lookup column to be the first column, and it allows you to return any column. INDEX/MATCH is the classic alternative that works everywhere.
3. Conditional Formatting: Highlight the Outliers
The Task: You have a column of sales figures and you want to quickly spot which ones are above or below a certain threshold, or are anomalies.
The Prompt:
"I have a column of monthly sales figures in Google Sheets (A1:A12). I want to apply conditional formatting to highlight cells that are above the average in green and below the average in red. Please provide the exact steps to do this using the built-in conditional formatting rules, and also suggest a custom formula if I want to highlight values that are more than 2 standard deviations away from the mean."
Example Result:
- Steps: Select A1:A12, go to Format > Conditional formatting. Set 'Format cells if' to 'Custom formula is' and enter =A1>AVERAGE($A$1:$A$12) (green), then add another rule with =A1<AVERAGE($A$1:$A$12) (red).
- For standard deviations: Use =ABS(A1-AVERAGE($A$1:$A$12))>2*STDEV($A$1:$A$12) to highlight outliers.
4. Summing with Multiple Conditions: SUMIFS and SUMPRODUCT
The Task: You need to sum sales for a specific product in a specific region, but SUMIFS is giving you trouble when the criteria are not in the same row.
The Prompt:
"I have a sales data table in Excel with columns: Product (A), Region (B), Sales (C). I want to sum the sales for 'Widgets' in 'East' region. Please provide a SUMIFS formula. Additionally, show me how to use SUMPRODUCT for a more flexible calculation, e.g., summing sales where the product is either 'Widgets' or 'Gadgets'."
Example Result:
- SUMIFS: =SUMIFS(C2:C100, A2:A100, "Widgets", B2:B100, "East")
- SUMPRODUCT (for OR condition): =SUMPRODUCT(C2:C100 * ((A2:A100="Widgets") + (A2:A100="Gadgets"))) — but this double-counts if both are true, so use =SUMPRODUCT(C2:C100 * ((A2:A100="Widgets") + (A2:A100="Gadgets") > 0)) — or better: =SUMPRODUCT(C2:C100 * ( (A2:A100="Widgets") + (A2:A100="Gadgets") ) ) but that's wrong. Use =SUMIFS(C2:C100, A2:A100, "Widgets") + SUMIFS(C2:C100, A2:A100, "Gadgets") — that's simpler.
5. Pivot Tables: Summarize Like a Pro
The Task: You have a large dataset with hundreds of rows, and you need a quick summary report. Pivot tables are the answer, but creating them manually can be tedious.
The Prompt:
"I have a dataset of 5000 rows with columns: Date, Product, Category, Sales. I want to create a pivot table that shows total sales by product and category, with the ability to drill down by date. Please provide step-by-step instructions for creating this pivot table in both Excel and Google Sheets, and explain how to add a slicer to filter by date range."
Example Result:
- Excel: Select the data, go to Insert > PivotTable, then drag 'Product' to Rows, 'Category' to Columns, and 'Sales' to Values (sum). Add a timeline or slicer for the Date field.
- Google Sheets: Select data, Data > Pivot table, then set rows, columns, and values similarly. Use the filter option to add a date range.
6. Forecasting: Predict Next Month's Sales
The Task: You need to forecast sales for the next quarter based on historical data. Excel and Google Sheets have built-in forecasting tools.
The Prompt:
"I have monthly sales data for the past 24 months in a column (A1:A24). I want to use Excel's FORECAST.ETS function to predict the next 3 months. Please provide the formula and explain how to interpret the confidence interval. Also, is there a similar function in Google Sheets?"
Example Result:
- Excel: =FORECAST.ETS(A25, A1:A24, B1:B24) where B is the timeline (dates). To get a range, use FORECAST.ETS.CONFINT.
- Google Sheets: Use =FORECAST(A25, A1:A24, B1:B24) for a linear forecast, but for seasonal, you might need to use the FORECAST.ETS equivalent, which is not available. You can use the LINEST or TREND functions. For a simple linear trend: =TREND(A1:A24, B1:B24, B25:B27).
7. Dashboard Design: From Data to Visuals
The Task: You want to create a dashboard that shows key metrics at a glance, but you're not sure which chart types to use or how to structure it.
The Prompt:
"I need to create a dashboard in Google Sheets for a SaaS company. The metrics I want to track are: MRR (monthly recurring revenue), churn rate, number of active users, and customer acquisition cost (CAC). I have monthly data for the past year. What chart types would you recommend for each metric, and how should I lay out the dashboard? Please provide a textual description or mock-up."
Example Result:
- Layout: Top row: three KPI cards with sparklines for MRR, churn, and active users. Below: a line chart for MRR trend, a column chart for CAC, and a pie chart for revenue share by plan.
- Specifics: Use sparklines for at-a-glance trends, line charts for time series, and bar charts for comparisons. Group related metrics together.
8. Text Manipulation: Extract, Split, and Combine
The Task: You have a column with full names, and you need to split them into first and last names, or extract the domain from an email address.
The Prompt:
"I have a list of email addresses in column A. I need to extract the domain name (the part after '@') into a separate column. Please provide a formula that works in both Excel and Google Sheets. Also, show how to split full names (in column B) into first and last names, assuming the format is 'First Last'."
Example Result:
- Extract domain: =RIGHT(A1, LEN(A1) - FIND("@", A1)) — or better, =MID(A1, FIND("@", A1)+1, LEN(A1))
- Split names: Use the Text to Columns feature in Excel (Data > Text to Columns) or =SPLIT(B1, " ") in Google Sheets.
9. Data Validation: Stop Typos in Their Tracks
The Task: You want to ensure that users only enter valid data, like a specific set of values or a date within a range.
The Prompt:
"I want to add a dropdown list to a cell in Excel that allows only the values 'Active', 'Inactive', or 'Pending'. Please provide steps to do this using data validation. Also, how can I set up a custom validation rule to prevent duplicate entries in a column?"
Example Result:
- Dropdown: Select cell, Data > Data Validation, choose 'List' and enter Active,Inactive,Pending.
- Prevent duplicates: Use a custom formula: =COUNTIF($A$1:$A$100, A1)=1 in the validation rule.
10. Regex for the Win: Advanced Text Cleaning
The Task: You need to extract specific patterns from text, like phone numbers or hashtags, that are too complex for simple formulas.
The Prompt:
"I have a column of social media posts in Google Sheets, and I want to extract all hashtags from each post into separate columns. Please provide a Google Sheets formula using REGEXEXTRACT or REGEXREPLACE to accomplish this. Also, show me how to use regex in Excel 365 (which now supports regex functions like REGEXTEST, REGEXEXTRACT, etc.)."
Example Result:
- Google Sheets: =REGEXEXTRACT(A1, "#\w+") will extract the first hashtag. To get all of them, you might need to use =SPLIT(REGEXREPLACE(A1, "[^#\w]", " "), " ") but that's messy. Better: Use =REGEXEXTRACT(A1, "#\w+") for the first, and then use =REGEXEXTRACT(A1, "#\w+", 2) for the second (if supported).
- Excel 365: New functions like =REGEXEXTRACT(A1, "#\w+") are now available in Beta. In Google Sheets, you can also use =REGEXEXTRACT(A1, "#\w+") which returns the first match. To get all, you might need a custom function or use =SPLIT(REGEXREPLACE(A1, "(^|\s)#\w+", "$1"), " ") which is convoluted. Better: Use =REGEXEXTRACT(A1, "#\w+") and then drag to fill across columns if you have multiple.
11. Data Consolidation: Merge Multiple Sheets
The Task: You have multiple sheets with similar data (e.g., monthly reports) and you want to combine them into one master sheet.
The Prompt:
"I have 12 sheets in an Excel workbook, each named 'Jan' through 'Dec', with the same column structure (Date, Product, Sales). I want to create a 'Master' sheet that combines all the data, with a column indicating the month. Please provide a formula or method to do this without VBA."
Example Result:
- Manual approach: Use Power Query to combine files, or use a formula like =VSTACK(Jan:Dec!A2:C100) in Excel 365. For Google Sheets, you can use ={Jan!A2:C; Feb!A2:C; ...} but that's tedious. Better: use the QUERY function with IMPORTRANGE if sheets are separate, or simply use a script.
12. Error Handling: Make Your Formulas Bulletproof
The Task: Your formulas return #N/A or #DIV/0! when data is missing. You want them to show a friendly message instead.
The Prompt:
"I have a formula that divides two cells, A1/B1, but sometimes B1 is blank or zero, causing a #DIV/0! error. I want to return 'N/A' instead. Please provide the IFERROR version of the formula. Also, show how to handle #N/A in VLOOKUP with IFERROR."
Example Result:
- Division: =IFERROR(A1/B1, "N/A")
- VLOOKUP: =IFERROR(VLOOKUP(...), "Not Found")
13. Dynamic Ranges: Formulas That Adapt
The Task: Your data range grows over time, and you want formulas to automatically update to include new rows.
The Prompt:
"I have a list of sales in column A, and I want to sum the entire column, but the number of entries changes frequently. I want to use a dynamic range so I don't have to update the formula each time. Please provide a formula that sums all numeric values in column A, ignoring blanks, and works in both Excel and Google Sheets."
Example Result:
- Excel: =SUM(A:A) — this sums the entire column, but be careful if there are headers. For a dynamic range that stops at the last non-empty cell, use =SUM(A2:INDEX(A:A, COUNTA(A:A))).
- Google Sheets: =SUM(A2:A) works similarly, but =SUM(A2:INDEX(A:A, COUNTA(A:A))) is more precise.
14. Collaboration: Track Changes and Comments
The Task: You're working on a shared spreadsheet, and you need to know who changed what and when, or you want to leave comments for teammates.
The Prompt:
"I'm using Google Sheets with my team. I want to set up notifications when a specific cell or range is edited. How can I do this using the built-in notification rules? Also, is there a way to track the edit history in Excel?
Example Result:
- Google Sheets: Click on Tools > Notification rules, then select 'When changes are made' and choose 'Email'.
- Excel: For shared workbooks, use Track Changes (Review > Track Changes) — but it's deprecated. Better: use Version History in OneDrive or SharePoint.
15. Advanced Analytics: What-If Analysis
The Task: You want to see how changing an input (like price or cost) affects your profit, using Excel's Goal Seek or Data Tables.
The Prompt:
"I have a business model with revenue and costs. I want to use Excel's Goal Seek to find the break-even point (where profit = 0) by changing the price. Please provide steps and explain how to set it up. Also, show how to create a one-variable data table to see the profit at different price points."
Example Result:
- Goal Seek: Set cell containing revenue formula to 0, change cell with price.
- Data Table: Create a column of prices, then use Data > What-If Analysis > Data Table to calculate profit for each price.
Conclusion
These 15 prompts are just the tip of the iceberg. The real power of AI in spreadsheets is that it can take your vague request and translate it into the exact formula or procedure you need—saving you hours of trial and error. As you get comfortable, you'll start writing your own prompts, combining techniques, and even asking AI to explain why a formula works, which deepens your own understanding. The goal isn't to replace your analytical skills but to give you more time to think strategically about the data. So, open your spreadsheet, pick a prompt, and see what you can create. The future of data analysis is conversational—and it's already here.
Comments