Introduction
You’re staring at a spreadsheet with 50,000 rows, your manager wants a forecast by tomorrow, and the data is messier than a teenager’s bedroom. Sound familiar? You don’t need to hire a data science team or spend weeks learning Python. With the right prompts, AI can clean, transform, analyze, and even visualize your data—right inside Excel or Google Sheets. In this article, I’ll share 10 battle-tested prompts that will make you feel like you have a personal analyst on demand. No fluff, just copy-paste-and-run.
Why AI + Spreadsheets = Match Made in Heaven
Spreadsheets are the lingua franca of business data. But they’re also a pain: formulas break, pivots confuse, and VLOOKUPs haunt your dreams. AI models like GPT-4 and Claude can read your data, understand context, and generate formulas, scripts, and insights in seconds. According to a 2024 Gartner survey, 80% of organizations plan to use generative AI in their analytics workflows by 2026. And you can start today—without a budget. The key is knowing how to talk to the AI. That’s where these prompts come in.
The Prompts
1. Data Cleaning: The “Spot the Mess” Prompt
Task: Identify and fix common data quality issues in a messy dataset.
Prompt:
You are a data cleaning expert. Here is a sample of my dataset (paste 10-20 rows). Identify all data quality issues: duplicates, inconsistent formatting, missing values, outliers, and typos. For each issue, give a specific formula or step to fix it in Excel and Google Sheets.
Example result:
For a dataset with names like “JOHN SMITH”, “john smith”, and “J. Smith”, the AI will suggest:
- Use TRIM and PROPER to standardize names.
- Use COUNTIF to find duplicates.
- Use conditional formatting to highlight missing values.
- Use IF with ISNUMBER to catch numeric fields stored as text.
Why it works: It turns a vague request into a structured audit, giving you a to-do list of fixes.
2. Formula Generation: The “I’m Too Lazy to Google” Prompt
Task: Generate complex Excel or Google Sheets formulas from a plain-English description.
Prompt:
Write an Excel formula that calculates the average sales for the last 30 days, excluding weekends, for product categories that start with “Pro”. Assume data is in columns A (date), B (category), C (sales). Provide the formula for both Excel and Google Sheets.
Example result:
AI outputs:
=AVERAGEIFS(C:C, A:A,">="&TODAY()-30, A:A,"<"&TODAY(), B:B,"Pro*", WEEKDAY(A:A,2)<6)
Why it works: It saves you from syntax rabbit holes and gives you a working formula instantly.
3. Data Transformation: The “Pivot Without Pain” Prompt
Task: Transform a flat table into a pivot table or summary report.
Prompt:
Here is my sales data (paste a sample or describe columns). Create a pivot table in Excel to show monthly revenue by region and product category. Also suggest a chart type that best visualizes the trend.
Example result:
AI tells you to insert a pivot table, drag fields, and suggests a stacked column chart to show regional breakdown. It even writes a macro to automate the pivot creation:
Sub CreatePivot()
' ... code ...
End Sub
Why it works: It bridges the gap between raw data and insight, giving you both manual steps and automation.
4. Data Visualization: The “Make It Pretty” Prompt
Task: Create charts and dashboards from data.
Prompt:
I have a table with columns: Date, Sales, Profit, Region. Suggest 3 different chart types to visualize this data, explaining what each highlights. Then write the VBA code to create a dashboard with these charts in Excel.
Example result:
- Line chart for sales over time.
- Bar chart for profit by region.
- Scatter plot for sales vs. profit.
- VBA code that generates these charts and places them on a new sheet.
Why it works: It automates the repetitive part of dashboard building.
5. Text Extraction: The “Get the Numbers Out” Prompt
Task: Extract structured data from unstructured text.
Prompt:
Extract dates, amounts, and vendor names from the following text. Output as a table.
[Paste email or invoice text]
Example result:
AI returns a markdown table with columns: Vendor, Date, Amount. You can even ask it to generate a formula to parse similar text in Excel.
Why it works: It transforms your inbox into a database.
6. Statistical Analysis: The “What Does It Mean?” Prompt
Task: Perform statistical analysis and interpret results.
Prompt:
Here is a sample of my data (paste). Calculate the mean, median, standard deviation, and correlation between columns A and B. Explain what these stats mean in plain English and whether the correlation is significant.
Example result:
AI computes the numbers (or gives you the Excel functions) and says: “The correlation is 0.82, which is strong positive. However, your sample size is small, so consider more data.”
Why it works: It bridges the gap between raw numbers and business insight.
7. Forecasting: The “Crystal Ball” Prompt
Task: Forecast future values based on historical data.
Prompt:
Here is my monthly sales data for the last 24 months (paste). Forecast the next 3 months using Excel’s FORECAST.ETS function. Show the formula and parameters. Also, explain any seasonality you see.
Example result:
AI writes:
=FORECAST.ETS(DATE(2026,9,1), B2:B25, A2:A25, 7, 90)
And notes a seasonal peak in December.
Why it works: It gives you a statistically sound forecast without needing a statistics degree.
8. Automation: The “Set It and Forget It” Prompt
Task: Automate repetitive tasks with Excel macros or Google Apps Script.
Prompt:
Write a VBA macro that loops through all rows in column A, highlights cells with values greater than 100 in yellow, and then creates a summary sheet with the count of highlighted cells. Also provide the Google Sheets Apps Script equivalent.
Example result:
You get two working scripts. The VBA one:
Sub HighlightAndSummarize()
Dim rng As Range
Dim cell As Range
Dim count As Long
count = 0
For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If cell.Value > 100 Then
cell.Interior.Color = vbYellow
count = count + 1
End If
Next cell
Sheets.Add
Range("A1").Value = "Count of highlighted cells: " & count
End Sub
Why it works: It turns a tedious manual task into a one-click solution.
9. Data Integration: The “Combine Everything” Prompt
Task: Merge data from multiple sources (e.g., CSV, Google Sheets, SQL).
Prompt:
I have two datasets: sales in sheet1 (columns: Date, Product, Revenue) and product info in sheet2 (columns: Product, Category, Price). Write a formula or macro to combine them into a single table with all columns, matching by Product. Also show how to do this in Google Sheets with QUERY.
Example result:
AI gives a VLOOKUP formula:
=VLOOKUP(B2, Sheet2!A:C, 2, FALSE)
And a Google Sheets QUERY:
=QUERY({Sheet1!A:C, VLOOKUP(Sheet1!B:B, Sheet2!A:C, {2,3}, FALSE)}, "select *")
Why it works: It saves you from manual copy-pasting and formula headaches.
10. Insight Generation: The “So What?” Prompt
Task: Generate business insights from data.
Prompt:
Here is my sales data (paste or describe). Identify the top 5 trends, anomalies, and actionable insights. Be specific and suggest what to do next.
Example result:
AI might say: “Sales in the West region dropped 20% in March due to a product recall. Recommend a targeted promotion to regain momentum.” It’s like having a consultant on demand.
Why it works: It converts data into decisions.
Conclusion
These 10 prompts are your ticket to becoming a data ninja. They won’t replace a full analytics team, but they’ll make you faster, more accurate, and more confident. The best part? You can start using them today. Copy, paste, and watch the magic happen. If you want to dive deeper into AI-powered data analysis, check out the courses at asibiont.com—they’ll turn you into a pro in no time.
Comments