If you've ever stared at a spreadsheet with thousands of rows of messy, inconsistent data, you know the pain. Cleaning, transforming, and visualizing data manually can eat up hours—or even days—of your week. But what if you could delegate that grunt work to an AI? With the right prompts, you can turn Excel and Google Sheets into a powerful analytics engine, whether you're a beginner or a seasoned data analyst.
In this guide, I'll share 15 battle-tested prompts that cover the entire data workflow: from initial cleaning and transformation to advanced visualization and forecasting. Each prompt includes a real-world example and the output you can expect. By the end, you'll have a toolbelt of AI-assisted techniques to supercharge your data analysis.
1. The Data Detective: Profiling Your Dataset
Task: Understand the structure, quality, and quirks of a new dataset.
Prompt:
"Act as a data analyst. I have a dataset in Excel with columns [list columns or paste headers]. Analyze the data types, missing values, and unique counts for each column. Summarize the data quality issues you spot, such as duplicates, inconsistent formats, or outliers. Provide a structured report with bullet points."
Example: Paste a sample of your column headers and a few rows. The AI will return a brief profile, helping you spot problems before they break your analysis.
2. The Purge: Cleaning Up Missing Values
Task: Decide how to handle gaps in your data.
Prompt:
"I have a column 'Sales' with 5% missing values. What are the best strategies to handle them? Consider whether to delete rows, fill with mean/median, or use interpolation. Explain the pros and cons for each, and give me a formula or script to implement the recommended approach in Excel."
Example: For a time series, the AI might suggest linear interpolation and provide =FORECAST or a small VBA snippet.
3. The Great Unifier: Merging Duplicate Rows
Task: Combine duplicate customer records without losing critical information.
Prompt:
"My CRM export has duplicate customer entries. For each duplicate, the 'Last Purchase' date may differ, and 'Total Spent' should be summed. Write an Excel formula or a Google Sheets query to merge duplicates, keeping the latest date and sum of spending."
Example: The AI might give you a QUERY function like =QUERY(A:D, "select A, MAX(B), SUM(C) group by A", 1).
4. The Shape-Shifter: Reshaping Data from Wide to Long
Task: Transform data from wide format (multiple columns) to long format (rows) for analysis or visualization.
Prompt:
"I have quarterly sales data in columns Q1-Q4 and want to convert it to long format with columns: Product, Quarter, Sales. Provide a step-by-step guide for doing this in Excel Power Query or with a Google Sheets formula."
Example: The AI can outline the Power Query steps: Unpivot Columns, or give you a formula using FLATTEN and SPLIT.
5. The Date Whisperer: Parsing Inconsistent Date Formats
Task: Normalize dates that come in various formats (e.g., 01/02/2026, 2026-02-01, Feb 1, 2026).
Prompt:
"My 'Date' column has mixed formats. How can I convert them all to YYYY-MM-DD in Excel? Provide a formula or Power Query step, and also handle text dates like 'February 1, 2026'."
Example: The AI might suggest =DATEVALUE(SUBSTITUTE(A2," "," ")) or a Power Query custom function.
6. The Formula Forger: Writing Complex Excel Formulas
Task: Create a formula that calculates a conditional metric, like rank within a category.
Prompt:
"In my table, I need to rank each salesperson within their region based on sales, but only for active employees. Write an Excel formula using RANK or SUMPRODUCT that handles this and explain how it works."
Example: The AI provides =SUMPRODUCT((Region=range)*(Sales>cell)*(Active=TRUE))+1 and explains the logic.
7. The Pivot Pro: Automating Pivot Table Creation
Task: Summarize data with a pivot table that is ready to use.
Prompt:
"I have sales data with columns: Date, Region, Product, Units, Revenue. Generate a pivot table that shows total revenue by region and product for each month. For Excel, give me the steps to create this manually, and for Google Sheets, provide a QUERY formula that mimics it."
Example: The AI offers =QUERY(A1:E100, "select B, C, sum(E) where A is not null group by B, C pivot month(A)").
8. The Chart Whisperer: Selecting the Right Visualization
Task: Choose a chart type that best represents your data.
Prompt:
"I have data on monthly website traffic and conversion rates over the last two years. Which chart types are most effective to show trends and correlation? Suggest 2-3 options and describe how to create them in Excel or Google Sheets, including any advanced features like trendlines or secondary axes."
Example: The AI might recommend a combo chart (line for traffic, bars for conversion) and guide you through inserting it.
9. The Color Coder: Applying Conditional Formatting with AI Insights
Task: Highlight important patterns automatically.
Prompt:
"In my spreadsheet, I want to highlight cells in the 'Revenue' column that are above the average in green and below average in red. Give me the conditional formatting rule for Excel and Google Sheets, and suggest other useful formatting rules for financial data."
Example: The AI provides the exact rule: =B2>AVERAGE(B:B) with green fill, and =B2<AVERAGE(B:B) with red.
10. The Insight Miner: Generating Written Insights from Data
Task: Turn numbers into a narrative.
Prompt:
"Based on the following data summary [paste pivot table or stats], write a 3-paragraph executive summary highlighting key trends, anomalies, and actionable recommendations. Use plain language that a non-technical audience can understand."
Example: The AI will produce a business-friendly summary you can paste into a report.
11. The Statistician: Running Statistical Tests
Task: Determine if differences between groups are significant.
Prompt:
"I have two columns of data: test scores from Group A and Group B. Perform a two-sample t-test in Excel or Google Sheets. Explain which test to use (assuming equal or unequal variances) and how to interpret the p-value. Provide step-by-step instructions using built-in functions or the Analysis ToolPak."
Example: The AI walks you through T.TEST and discusses assumptions.
12. The Forecaster: Predicting Future Trends
Task: Forecast future values based on historical data.
Prompt:
"I have monthly sales data for the past 24 months. Use Excel's FORECAST.ETS function to predict the next 3 months. Explain how to set seasonality and confidence intervals, and provide the formulas to use."
Example: The AI gives =FORECAST.ETS(A25, B2:B25, A2:A25, 12, 1) and explains how to adjust parameters.
13. The Macro Maestro: Writing VBA Macros
Task: Automate repetitive tasks with a one-click macro.
Prompt:
"I need to clean up a report every morning: remove blank rows, format headers in bold, and apply a filter. Write a VBA macro that does this and explain how to install it in Excel."
Example: The AI provides a complete VBA subroutine and instructions for adding it to the Personal Macro Workbook.
14. The Data Validator: Ensuring Data Quality Before Analysis
Task: Set up rules to prevent bad data entry.
Prompt:
"I'm creating a data entry sheet for my team. Suggest data validation rules for columns like 'Email', 'Age', 'Date', and 'Status'. Provide step-by-step instructions for setting them up in Excel."
Example: The AI lists rules such as custom formula for email containing '@', whole number between 18 and 99, and a dropdown list for Status.
15. The Dashboard Builder: Creating an Interactive Dashboard
Task: Build a dashboard that updates automatically.
Prompt:
"I have sales data in a table. I want to create a dashboard with a summary of total revenue, top products, and a monthly trend chart. Describe how to build it using Excel PivotCharts and Slicers, or with Google Sheets and pivot tables. Include layout suggestions and how to make it dynamic."
Example: The AI outlines a layout with a header, KPIs, and charts, and explains how to use slicers to filter across all visuals.
Final Thoughts
With these prompts, you can dramatically reduce the time spent on routine data tasks and focus on what truly matters: deriving insights and making decisions. Remember, the key to effective AI prompting is clarity and specificity. The more context you give, the better the output.
Start with one prompt, try it on your own data, and adapt it to your workflow. Soon, you'll wonder how you ever managed without an AI data analyst by your side. Happy analyzing!
Comments