Excel has been the backbone of business data management for decades. But let's be honest—many of us only use a fraction of its power. We spend hours wrestling with formulas, cleaning messy data, and trying to figure out why that VLOOKUP isn't working.
Enter AI prompts. With the rise of AI-powered Excel tools like Microsoft Copilot, Numerous.ai, GPT for Work, and SheetAI, you can now describe what you want in plain English and let AI handle the heavy lifting.
This guide provides 50 powerful AI prompts organized by category, with real examples and an interactive app to test them.
What You'll Learn
| Section | Content |
|---|---|
| Data Cleaning | 8 prompts to fix messy data automatically |
| Formula Generation | 10 prompts to write complex formulas instantly |
| Data Analysis | 8 prompts to extract insights from datasets |
| Reporting & Visualization | 7 prompts to create professional reports |
| Automation | 6 prompts to automate repetitive tasks |
| Financial & Business Analysis | 6 prompts for business intelligence |
| VBA & Macros | 5 prompts to write code without coding |
Part 1: Data Cleaning Prompts (8)
Prompt 1: Remove Duplicates
Prompt: "Find and remove all duplicate rows in my dataset based on column A. Keep only the first occurrence."
Example: You have a customer list with 5,000 rows, and some email addresses appear multiple times.
Result: AI will write a formula or script that removes duplicates while preserving your original data.
Manual Equivalent: Data → Remove Duplicates
Prompt 2: Fix Inconsistent Capitalization
Prompt: "Convert all text in column B to proper case (first letter capitalized, everything else lowercase). Skip empty cells."
Example: Your data has "john doe", "JOHN DOE", and "jOhN dOe". You want them all to be "John Doe".
Result: AI generates a formula like =PROPER(B2) or a VBA script.
Prompt 3: Remove Extra Spaces
Prompt: "Trim all extra spaces from column C. Remove leading, trailing, and multiple spaces between words."
Example: Your product names have inconsistent spacing (e.g., "LED TV 55 inch").
Result: AI writes =TRIM(C2) or a cleaning script.
Prompt 4: Extract Numbers from Text
Prompt: "Extract only the numeric values from column D. If a cell contains 'Order #12345', return '12345'."
Example: Your order references combine letters and numbers that you need to separate.
Result: AI writes a custom formula or script to extract digits.
Prompt 5: Standardize Date Formats
Prompt: "Convert all dates in column E to the format 'YYYY-MM-DD'. Handle different input formats like MM/DD/YYYY, DD/MM/YYYY, and text dates."
Example: Your data has dates in multiple formats from different sources.
Result: AI creates date conversion formulas.
Prompt 6: Find and Replace with Pattern Matching
Prompt: "Find all cells in column F that contain phone numbers (10 digits, possibly with dashes) and format them as (XXX) XXX-XXXX."
Example: You have phone numbers in multiple formats that need standardization.
Result: AI generates a formula using REGEX or a text processing script.
Prompt 7: Fill Empty Cells with Previous Value
Prompt: "For column G, fill any empty cell with the value from the cell directly above it."
Example: Your sales data has missing values that should inherit from previous rows.
Result: AI writes =IF(ISBLANK(G2), G1, G2) or a fill-down script.
Prompt 8: Flag Outliers
Prompt: "In column H, flag any value that is more than 3 standard deviations from the mean. Add a new column with 'Outlier' or 'Normal'."
Example: You want to identify unusual transactions in your financial data.
Result: AI creates a statistical formula with conditional logic.
Part 2: Formula Generation Prompts (10)
Prompt 9: VLOOKUP with Error Handling
Prompt: "Write a VLOOKUP formula that looks up the value in cell A2 from the range 'Sheet2'!$A$2:$B$100. If the value is not found, return 'Not Found' instead of an error."
Example: You need to match customer IDs to their names, but some IDs don't exist.
Result: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$B$100, 2, FALSE), "Not Found")
Prompt 10: XLOOKUP for Modern Excel
Prompt: "Use XLOOKUP to find the price in column C that matches the product code in column A, where the lookup value is in cell E2."
Example: You're using modern Excel and want the simpler XLOOKUP function.
Result: =XLOOKUP(E2, A:A, C:C, "Not Found")
Prompt 11: SUMIFS with Multiple Conditions
Prompt: "Write a SUMIFS formula that sums the values in column D where column A equals 'Product X' AND column B is greater than 100."
Example: You want total sales for a specific product above a certain threshold.
Result: =SUMIFS(D:D, A:A, "Product X", B:B, ">100")
Prompt 12: COUNTIFS for Multiple Criteria
Prompt: "Count how many rows in column A contain 'Completed' AND column B has a date in January 2026."
Example: You want to track completion counts by month.
Result: =COUNTIFS(A:A, "Completed", B:B, ">=1/1/2026", B:B, "<=1/31/2026")
Prompt 13: Nested IF with Multiple Conditions
Prompt: "Write a nested IF formula that returns 'Excellent' if score in A2 is >=90, 'Good' if >=70, 'Average' if >=50, and 'Poor' if below 50."
Example: You need to grade student or employee performance.
Result: =IF(A2>=90, "Excellent", IF(A2>=70, "Good", IF(A2>=50, "Average", "Poor")))
Prompt 14: INDEX-MATCH for Two-Way Lookup
Prompt: "Use INDEX-MATCH to find the value where column A matches the value in cell E2 and row 1 matches the value in cell F2."
Example: You need to find sales figures for a specific product and month combination.
Result: =INDEX(B2:D10, MATCH(E2, A2:A10, 0), MATCH(F2, B1:D1, 0))
Prompt 15: Dynamic Array Formula (SORT)
Prompt: "Create a dynamic array formula that sorts the range A2:B100 by column B in descending order."
Example: You want a live, auto-updating sorted list.
Result: =SORT(A2:B100, 2, -1)
Prompt 16: FILTER with Multiple Conditions
Prompt: "Use the FILTER function to return all rows from A2:C100 where column B equals 'Active' AND column C is greater than 500."
Example: You want to extract a filtered subset of data.
Result: =FILTER(A2:C100, (B2:B100="Active") * (C2:C100>500))
Prompt 17: TEXTJOIN for Combining Values
Prompt: "Use TEXTJOIN to combine all values in column A that have 'Yes' in column B, separated by commas."
Example: You need to create a comma-separated list of qualifying items.
Result: =TEXTJOIN(", ", TRUE, IF(B2:B100="Yes", A2:A100, ""))
Prompt 18: Conditional Formatting Formula
Prompt: "Write a formula for conditional formatting that highlights cells in column C where the value is greater than the average of column C."
Example: You want to visually identify above-average values.
Result: =C2>AVERAGE(C:C)
Part 3: Data Analysis Prompts (8)
Prompt 19: Calculate Percentage Change
Prompt: "For each row, calculate the percentage change from the previous year's value in column B. The previous year's value is in column A."
Example: You want to show year-over-year growth.
Result: =IF(A2=0, "N/A", (B2-A2)/A2) formatted as percentage.
Prompt 20: Running Total
Prompt: "Create a running total in column E starting from cell E2, adding the value in D2 and continuing down the column."
Example: You need a cumulative sum for sales or expenses.
Result: =SUM($D$2:D2)
Prompt 21: Top N Values with Rank
Prompt: "Add a rank column that shows the rank of each value in column B, with the highest value ranked #1."
Example: You want to identify your top-performing products or employees.
Result: =RANK.EQ(B2, $B$2:$B$100, 0)
Prompt 22: Unique Count
Prompt: "Count the number of unique values in column A."
Example: You need to know how many distinct customers you have.
Result: =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) or =COUNTA(UNIQUE(A2:A100))
Prompt 23: Moving Average
Prompt: "Calculate a 7-day moving average for values in column B. The moving average should be the average of the current day and the previous 6 days."
Example: You want to smooth out daily sales fluctuations.
Result: =AVERAGE(B2:B8) then drag down.
Prompt 24: Correlation Between Two Columns
Prompt: "Calculate the correlation coefficient between values in column A and column B."
Example: You want to see if there's a relationship between advertising spend and sales.
Result: =CORREL(A2:A100, B2:B100)
Prompt 25: Forecast Future Values
Prompt: "Use Excel's FORECAST.ETS function to predict the next 3 values in the series in column B, based on the historical data in rows 2 to 50."
Example: You want to forecast future sales.
Result: =FORECAST.ETS(51, B2:B50, A2:A50, 3)
Prompt 26: Break Down by Category
Prompt: "Calculate the total, average, and count for each unique category in column A, using values from column B."
Example: You need a summary of sales by region.
Result: Using PivotTable or formulas like =SUMIF(A:A, "Category1", B:B)
Part 4: Reporting & Visualization Prompts (7)
Prompt 27: Create a PivotTable with AI
Prompt: "Create a pivot table that shows total sales by region (column A) and by month (column B), with the sum of sales values (column C)."
Example: You want to quickly summarize sales data.
Result: AI will guide you to create the pivot table or generate VBA to build it automatically.
Prompt 28: Dashboard Summary
Prompt: "Create a dashboard summary at the top of this sheet that shows: total sales, average sale, number of transactions, and the top performing product."
Example: You need a quick overview of key business metrics.
Result: AI generates formulas and arranges a summary section.
Prompt 29: Conditional Icon Sets
Prompt: "Apply an icon set (green checkmark, yellow exclamation, red X) to column C based on: green if value > 100, yellow if between 50 and 100, red if below 50."
Example: You want visual indicators for performance.
Result: AI creates conditional formatting rules with icon sets.
Prompt 30: Dynamic Chart Creation
Prompt: "Create a bar chart showing monthly sales from the data in columns A (months) and B (sales). Make the chart update automatically when new data is added."
Example: You want a live, updateable sales chart.
Result: AI generates chart creation instructions or code.
Prompt 31: Percentage of Total
Prompt: "Add a column that shows the percentage of total each value in column B represents."
Example: You want to see market share or contribution percentages.
Result: =B2/SUM($B$2:$B$100) formatted as percentage.
Prompt 32: Top 10 List Extraction
Prompt: "Extract the top 10 values from column B along with their corresponding values in column A, sorted by highest to lowest."
Example: You need to identify your best-selling products.
Result: =INDEX(A:A, MATCH(LARGE($B$2:$B$100, ROW(1:1)), $B$2:$B$100, 0))
Prompt 33: Aging Analysis
Prompt: "Create an aging report that shows values in column B categorized by the number of days since the date in column A: 0-30 days, 31-60 days, 61-90 days, 90+ days."
Example: You need to analyze accounts receivable aging.
Result: AI creates formulas with nested IF statements or VLOOKUP with a lookup table.
Part 5: Automation Prompts (6)
Prompt 34: Auto-Refresh Data
Prompt: "Write a VBA macro that refreshes all data connections and pivot tables in this workbook every 5 minutes."
Example: You have a dashboard that needs to stay up to date.
Result: AI generates VBA code.
Prompt 35: Send Email Alerts
Prompt: "Create a VBA macro that sends an email alert to 'manager@company.com' when any value in column B exceeds 10,000."
Example: You want to be notified of large transactions.
Result: AI generates VBA with Outlook integration.
Prompt 36: Batch File Processing
Prompt: "Write a VBA script that loops through all .xlsx files in a folder, copies the data from Sheet1 of each, and pastes it into a master workbook."
Example: You receive weekly reports from multiple departments that need consolidation.
Result: AI generates folder and file processing VBA.
Prompt 37: Data Validation Rules
Prompt: "Create a data validation rule for column C that only allows values from a list in column D, and shows a dropdown for selection."
Example: You want to ensure data consistency by restricting inputs.
Result: AI provides data validation setup instructions.
Prompt 38: Auto-Backup VBA
Prompt: "Write a VBA macro that automatically saves a backup copy of the workbook to a specified folder every hour, with a timestamp in the filename."
Example: You want to protect against data loss.
Result: AI generates backup automation code.
Prompt 39: Compare Two Sheets
Prompt: "Create a VBA macro that compares two sheets (Sheet1 and Sheet2) and highlights any differences in green (added) or red (changed), using the ID in column A as the key."
Example: You need to reconcile two versions of the same dataset.
Result: AI generates a comparison script with diff highlighting.
Part 6: Financial & Business Analysis Prompts (6)
Prompt 40: Build a Depreciation Schedule
Prompt: "Create a depreciation schedule using the straight-line method. For each asset, calculate annual depreciation based on cost, salvage value, and useful life. Show year-by-year carrying value."
Example: You need to calculate fixed asset depreciation.
Result: AI builds a complete schedule with formulas.
Prompt 41: Loan Amortization Table
Prompt: "Create a full loan amortization table showing payment number, payment amount, interest paid, principal paid, and remaining balance for a 30-year, $250,000 mortgage at 5% annual interest."
Example: You're calculating mortgage payments.
Result: AI creates a detailed amortization schedule using PMT, IPMT, and PPMT functions.
Prompt 42: Break-Even Analysis
Prompt: "Create a break-even analysis that calculates how many units need to be sold to cover fixed costs of $50,000, with a variable cost of $15 per unit and a selling price of $25 per unit."
Example: You're evaluating a new product launch.
Result: AI builds formulas and may create a chart showing the break-even point.
Prompt 43: Budget Variance Analysis
Prompt: "Compare actual values in column B to budgeted values in column C. Calculate the variance amount and variance percentage, and highlight any variance over 10% in red."
Example: You need to analyze monthly budget performance.
Result: AI creates variance calculation and conditional formatting.
Prompt 44: Project Valuation (NPV/IRR)
Prompt: "Calculate the Net Present Value (NPV) and Internal Rate of Return (IRR) for a project with the following cash flows: initial investment of -$100,000, then $30,000, $35,000, $40,000, $45,000, $50,000 over 5 years, with a 10% discount rate."
Example: You're evaluating an investment opportunity.
Result: =NPV(0.10, B2:B6) and =IRR(B1:B6) where B1 is -100,000.
Prompt 45: KPI Dashboard
Prompt: "Create a KPI dashboard section that shows: total revenue (sum of column B), average order value (average of column B), conversion rate (count of 'Completed' in column C divided by total rows), and customer acquisition cost (total marketing spend from column D divided by count of new customers)."
Example: You need a quick view of key business metrics.
Result: AI creates formulas and an organized summary section.
Part 7: VBA & Macros Prompts (5)
Prompt 46: Auto-Format Columns
Prompt: "Write a VBA macro that automatically formats column A as bold, column B as currency with 2 decimal places, and column C as percentage with 1 decimal place."
Example: You want consistent formatting across reports.
Result: AI generates VBA code with formatting commands.
Prompt 47: Sort Data by Multiple Columns
Prompt: "Create a VBA macro that sorts the data range A2:G100 first by column A (ascending), then by column B (descending), and then by column C (ascending)."
Example: You need multi-level sorting automation.
Result: AI generates VBA sort code.
Prompt 48: Generate and Save PDF
Prompt: "Write a VBA macro that prints the selected range to PDF and saves it to 'C:\Reports\Report_' + today's date + '.pdf'."
Example: You need to generate daily reports automatically.
Result: AI generates PDF export code.
Prompt 49: Copy Data to New Workbook
Prompt: "Write a VBA macro that copies all data from Sheet1 of the current workbook and creates a new workbook with that data, saving it as 'Extract.xlsx'."
Example: You need to extract data for sharing.
Result: AI generates copy-to-new-workbook code.
Prompt 50: User Form Automation
Prompt: "Create a VBA macro that displays a user form with fields for 'Product Name' and 'Sales Amount', and when submitted, adds the data to the next empty row in columns A and B."
Example: You want to create a simple data entry interface.
Result: AI generates user form code with add functionality.
Browse, search, and test AI prompts to automate your Excel workflow.
Post a Comment