If you've ever spent hours wrestling with VLOOKUP errors, manually cleaning duplicate rows, or rebuilding the same pivot table every Monday morning, you know the pain. According to a study by Forrester Research, data analysts spend up to 80% of their time on data preparation rather than actual analysis. But what if you could instruct an AI to handle the grunt work in seconds? This is where well-crafted prompts for Excel, Google Sheets, and data analysis tools come in. They can transform your workflow, letting you focus on insights, not mechanics. In this guide, I'll share 12 battle-tested prompts that cover everything from data cleaning and formula generation to building dynamic dashboards and forecasting—complete with examples and practical tips. Whether you're a financial analyst, a marketer, or a data enthusiast, these prompts will save you hours and reduce errors. Let's dive in.
1. Data Cleaning: The "Spot the Mess" Prompt
Task: Automatically identify and suggest fixes for common data quality issues in your dataset.
Prompt:
Act as a data quality analyst. I'll provide a sample of my dataset (first 10 rows). Identify potential issues such as duplicates, missing values, inconsistent formatting, and outliers. For each issue, explain why it matters and give a specific Excel/Google Sheets formula or feature to fix it. Be concise and actionable.
Example Result:
Given a sample with duplicate customer IDs, you'd get:
- Duplicates: Use =COUNTIF(A:A,A2)>1 to flag duplicates, then remove them via Data > Remove Duplicates.
- Missing values: Use =IF(ISBLANK(B2),"N/A",B2) to fill blanks.
- Inconsistent dates: Use =TEXT(D2,"YYYY-MM-DD") to standardize.
This prompt turns the AI into your data-cleaning consultant, saving you from manual audit.
2. Formula Generation: The "Explain Like I'm 5" Prompt
Task: Get a complex formula written and explained in simple terms.
Prompt:
You are an Excel formula expert. I need a formula to calculate the weighted average of sales, where weights are in column A and values in column B. Write the formula, explain how it works step by step, and provide an alternative using SUMPRODUCT. Also mention potential pitfalls like #DIV/0! errors.
Example Result:
- Formula: =SUMPRODUCT(A2:A10,B2:B10)/SUM(A2:A10)
- Explanation: SUMPRODUCT multiplies each weight by its value and sums the products, then divides by total weight.
- Pitfall: If weights sum to zero, you get #DIV/0!. Use =IF(SUM(A2:A10)=0,"N/A", ...).
This prompt is perfect when you know what you need but can't articulate the syntax.
3. Pivot Table Strategy: The "What Story Is My Data Telling?" Prompt
Task: Get suggestions for pivot table configurations to answer key business questions.
Prompt:
I have a sales dataset with columns: Date, Region, Product, Salesperson, Units, Revenue. I want to analyze monthly trends by region. Suggest three pivot table setups (rows, columns, values, filters) that would give me the most insight, and explain what each reveals. Also include a recommendation for a chart type.
Example Result:
- Setup 1: Rows: Region, Columns: Date (grouped by month), Values: Sum of Revenue. Shows regional monthly performance.
- Setup 2: Rows: Product, Columns: Region, Values: Count of Units. Reveals product popularity by region.
- Setup 3: Rows: Salesperson, Values: Sum of Revenue, Filter: Date. Tracks individual performance.
- Chart: Use a stacked column chart for setup 1 to compare regions over time.
This prompt helps you explore your data without trial and error.
4. Dashboard Design: The "Blueprint Builder" Prompt
Task: Get a structured plan for building an interactive dashboard in Excel or Google Sheets.
Prompt:
I need to build a KPI dashboard for a SaaS company. Key metrics: MRR, churn rate, active users, customer acquisition cost. Suggest a layout for a single-page dashboard in Google Sheets, including which charts to use (e.g., line for trends, bar for comparisons), which cells to place them in, and how to make it interactive with slicers or dropdowns. Also list the formulas needed for each KPI.
Example Result:
- Layout: Top row: 4 KPI cards (MRR, Churn, Active Users, CAC). Below: line chart for MRR trend, bar chart for churn by cohort, and a dropdown to filter by month.
- Formulas: MRR: =SUMIF(...), Churn: =.../..., etc.
- Interactivity: Use Data Validation for dropdowns and QUERY function to filter data.
This prompt gives you a blueprint, not just a vague idea.
5. Google Sheets Automation: The "Google Apps Script" Prompt
Task: Generate a Google Apps Script to automate repetitive tasks like sending email reports.
Prompt:
Write a Google Apps Script that sends an email with a PDF of the current sheet to a list of recipients every Monday at 9 AM. The script should create a PDF from the sheet, attach it, and include a summary of key metrics in the email body. Provide the complete code and instructions on how to set the trigger.
Example Result:
function sendReport() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetId = sheet.getSheetId(); // assume first sheet
var url = 'https://docs.google.com/spreadsheets/d/' + sheet.getId() + '/export?format=pdf&gid=' + sheetId;
var blob = UrlFetchApp.fetch(url, {headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}}).getBlob();
var recipients = ['team@example.com'];
MailApp.sendEmail(recipients.join(','), 'Weekly Report', 'Here is the report.', {attachments: [blob]});
}
Then set a time-driven trigger in the script editor. This prompt automates your reporting pipeline.
6. Data Visualization: The "Chart Chooser" Prompt
Task: Get advice on which chart type to use for a given dataset and message.
Prompt:
I have data on customer satisfaction scores by product category (ordinal scale). I want to show which category is best and how scores vary. Recommend 2-3 chart options, explain pros and cons, and provide the exact steps to create them in Excel. Consider whether a bar chart or a box plot would be more informative.
Example Result:
- Bar chart: Simple, but hides distribution.
- Box plot: Shows median, quartiles, and outliers. In Excel, use a stock chart or add a box plot via the new chart types in Excel 2016+.
- Recommendation: Use a horizontal bar chart for easy comparison, and add error bars to show variability.
This prompt ensures your visuals are effective, not just pretty.
7. Complex Formulas: The "Nested IF Nightmare" Prompt
Task: Simplify or debug a complex nested formula.
Prompt:
I have this formula: =IF(A2>100,"High",IF(A2>50,"Medium","Low")) but I need to add a condition for negative values. Write an improved version using IFS or SWITCH, explain why it's better, and test it with sample values.
Example Result:
- Improved: =IFS(A2<0,"Negative",A2>100,"High",A2>50,"Medium",TRUE,"Low")
- Why better: IFS avoids multiple IF statements, making it easier to read and maintain.
- Test: -10 → Negative, 75 → Medium, 150 → High.
This prompt is a lifesaver when formulas become unmanageable.
8. Data Transformation: The "Melt & Pivot" Prompt
Task: Reshape data from wide to long format or vice versa, using Power Query or formulas.
Prompt:
I have data in wide format: columns for each month (Jan, Feb, Mar) and rows for products. I need to convert it to long format: Product, Month, Sales. Provide a step-by-step guide using Power Query in Excel, and also a formula-based solution using INDEX/MATCH if Power Query is not available.
Example Result:
- Power Query: Select the month columns, go to Transform > Unpivot Columns.
- Formula: Use a combination of INDEX and MATCH in a new sheet, but it's complex. Recommend Power Query.
This prompt helps you handle messy data layouts.
9. Statistical Analysis: The "Hypothesis Tester" Prompt
Task: Perform a statistical test (e.g., t-test, chi-square) and interpret results.
Prompt:
I'm comparing conversion rates between two landing pages (A/B test). I have sample sizes and conversions. Write a step-by-step guide to perform a two-proportion z-test in Excel, including which functions to use (e.g., NORM.S.DIST) and how to interpret the p-value. Also mention assumptions and limitations.
Example Result:
- Steps: Calculate pooled proportion, standard error, z-score, then p-value using =NORM.S.DIST(z, TRUE).
- Interpretation: If p-value < 0.05, reject null hypothesis.
- Limitations: Assumes large samples, random assignment.
This prompt brings statistical rigor to your analysis.
10. Forecasting: The "Crystal Ball" Prompt
Task: Use Excel's forecasting functions (FORECAST.ETS) and interpret the results.
Prompt:
I have monthly sales data for the past 24 months. Use Excel's FORECAST.ETS function to forecast the next 3 months. Provide the formula, how to create a forecast sheet via the Forecast Sheet feature, and how to evaluate forecast accuracy using MAPE. Also discuss seasonality detection.
Example Result:
- Formula: =FORECAST.ETS(A25, B2:B24, A2:A24, seasonality=12)
- Forecast Sheet: Select data, go to Data > Forecast Sheet, choose options.
- MAPE: Use =AVERAGE(ABS((Actual-Forecast)/Actual)).
This prompt helps you predict future trends with built-in tools.
11. Data Validation: The "Error Buster" Prompt
Task: Identify and fix common errors like #N/A, #DIV/0!, and #VALUE!.
Prompt:
My spreadsheet is full of #N/A errors from VLOOKUP. Write a formula that handles these gracefully, and explain how to use IFERROR and VLOOKUP together. Also provide a solution using XLOOKUP if available, and a macro to find all errors.
Example Result:
- Formula: =IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not Found")
- XLOOKUP: =XLOOKUP(A2, Table[Key], Table[Value], "Not Found")
- Macro: Use VBA to loop through cells and highlight errors.
This prompt ensures your reports are clean and professional.
12. Interactive Dashboards with Google Sheets: The "QUERY Master" Prompt
Task: Generate a QUERY function to create dynamic reports based on user input.
Prompt:
I have a Google Sheet with raw data (columns: Date, Region, Sales). I want to create a report that shows total sales by region for a selected month. Write a QUERY formula that uses a cell reference for the month (e.g., G1), and explain how to set up a dropdown for month selection using Data Validation.
Example Result:
=QUERY(A:C, "SELECT B, SUM(C) WHERE A = '"&TEXT(G1,"YYYY-MM")&"' GROUP BY B LABEL SUM(C) 'Total Sales'", 1)
Add a dropdown with unique months from column A. This turns your sheet into an interactive report.
These 12 prompts are just the tip of the iceberg. The key is to be specific about your data, your goal, and the tool you're using. Remember to always validate the AI's output—it's a powerful assistant, but you're the expert. Start by copying these prompts, tweak them to fit your data, and soon you'll wonder how you ever worked without them. If you have a favorite prompt that works wonders, share it in the comments below!
Comments