20 Best Excel AI Prompts for Formulas, Data & Analysis

To get reliable solutions, provide clear context: paste your exact column names, cell ranges, sample data layouts, and the specific result you want. While some native AI tools can edit workbooks directly, standard chatbots simply provide generated formulas, scripts, or instructions for you to copy and apply manually.

Keep in mind that AI can make mistakes or misinterpret logic. Always test and verify generated formulas and calculations inside your workbook before using them - especially when handling financial models, tax reports, salary data, or core accounting files.

Why Use AI Prompts for Excel?

Working in spreadsheets often involves two distinct challenges: knowing what calculation you need to perform, and remembering the exact syntax or tool sequence required to execute it in Excel.

Using structured ChatGPT Excel prompts and conversational AI assistants bridges this gap effectively:

• Eliminate Syntax Frustration: Translate plain-English business logic into working nested functions, conditional aggregates, and modern dynamic arrays without missing commas or parentheses.
• Accelerate Data Cleaning: Quickly generate cleaning formulas or Power Query transformation steps to standardize dates, names, phone numbers, and mixed text casing.
• Simplify Complex Workflows: Get direct, step-by-step guidance on structuring multi-level sorts, advanced filters, PivotTable layouts, and interactive executive dashboards.
• Demystify Legacy Spreadsheets: Paste existing, unfamiliar formulas into AI to get an instant, plain-English breakdown of how the logic works and where potential edge-case errors lie.

How to Use These Excel Prompts

Follow this step-by-step workflow to get accurate results when using AI assistants like ChatGPT, Google Gemini, or Microsoft Copilot:

1. Open your AI assistant: Access ChatGPT, Gemini, or Microsoft Copilot in your browser, desktop application, or spreadsheet sidebar.
2. Provide your table structure or upload sample data: Attach your spreadsheet (.xlsx or .csv) if your AI platform supports file uploads, or paste a small, representative 3-to-5-row text sample directly into the chat.
3. Choose the appropriate prompt shortcode: Find the prompt in this guide that matches your task, from data cleaning and lookup formulas to financial math or PivotTables.
4. Paste the prompt template: Copy the prompt text into the chat interface.
5. Replace all [placeholders] with your actual sheet details: Swap out bracketed items like [Column Name], [Range], or [Conditions] with your real sheet coordinates and business rules.
6. Review and verify the result in Excel: Copy the generated formula or follow the step-by-step instructions in a test copy of your sheet. Always test calculations on sample rows before deploying them across critical master files.

What Information Should You Give AI?

Quick Excel Prompt Index

20 Excel Prompts You Can Use Every Week

/clean - Clean Messy Excel Data

What it does

This prompt helps you inspect messy spreadsheet data to spot irregular spacing, mixed text casing, missing fields, non-standard formatting, and duplicate rows. Using AI for Excel data cleaning generates a structured review report and cleaning formulas so you can audit changes before updating your sheets.

2. /duplicates — Find Duplicate Data

What it does

This prompt helps you inspect Excel duplicate data across full rows, key identifier columns, and shared attributes. Finding duplicates with AI lets you count repeated occurrences and flag potential data entry errors without accidentally deleting or merging valid records.

Copy & paste prompt

Example

Sample Input Data:

Result: The AI reports that Rows 2 and 5 are exact duplicate rows; Row 4 contains a conflicting duplicate Order ID (ORD-101 assigned to a different customer and amount); and the $50 amount in Rows 2, 3, and 5 is a legitimate shared value. It provides =IF(COUNTIF($A$2:$A$5, A2)>1, "Duplicate ID", "Unique") to flag repeated IDs in Column D.

Best for

• Customer relationship management (CRM) databases
• Product inventory catalogs
• Sales transaction logs
• Employee ID rosters

Important note

Duplicate detection does not automatically mean data is incorrect; two clients can share a name or amount, while duplicate unique IDs require verification before you delete any rows.

3. /formula — Generate Excel Formulas

What it does

This prompt turns an AI assistant into an AI Excel formula generator by converting plain-English logic into working spreadsheet formulas. It structures correct cell references and conditions like IF, SUMIFS, or COUNTIFS, then breaks down how the logic works so beginners can apply it easily.

Copy & paste prompt

Example

Sample Input Data:

Result: In cell E2, the formula =(C2-B2)*D2 yields $50 total profit (($30 - $20) * 5). For row 3, =(C3-B3)*D3 correctly yields $0. The AI explains that subtracting unit cost from unit selling price calculates unit margin before multiplying by the quantity sold.

Best for

• Multi-condition business reports
• Sales revenue and margin calculations
• Custom attendance tallies
• Tiered bonus or commission structures

Important note

Always test generated Excel formula prompts against 2–3 rows of known data before copying them across your entire sheet, as syntactically valid formulas can still produce incorrect totals if underlying references or logic conditions are misaligned.

4. /vlookup — VLOOKUP & XLOOKUP Formulas

What it does

This prompt helps you write an Excel lookup formula to match and pull information between two tables using a common key, such as a Product ID or Employee ID. It determines whether modern XLOOKUP or classic VLOOKUP is best for your sheet layout and builds error-handled formulas that return clean results instead of broken errors.

Copy & paste prompt

Example

Sample Tables:

Main Table on Sheet1: Product ID P-10 in cell A2.
Price Table on Sheet2: Range $A$2:$C$50, where Column A has Product ID and Column C has Unit Price $45.00.

Result:
Modern Excel (365 / 2021+): =XLOOKUP(A2, Sheet2!$A$2:$A$50, Sheet2!$C$2:$C$50, "Not Found")
Legacy Excel: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$50, 3, FALSE), "Not Found")

XLOOKUP vs. VLOOKUP: XLOOKUP is preferred because it can look in any direction (left or right), does not break when columns are inserted, and handles missing values natively. VLOOKUP searches only the leftmost column of a table array and requires exact column index counting.

Best for

• Product price and SKU catalog matching
• Employee department and salary retrieval via Employee ID
• Customer contact matching across CRM exports
• Inventory stock-level reconciliation

Important note

Lookup formulas fail if key formats do not match; an ID stored as text (e.g., '1001) will not match a true numeric value (1001). Verify that your identifier columns share identical formatting and contain no unintended duplicate keys.

5. /sum — SUM, SUMIF & SUMIFS

What it does

This prompt helps you write aggregate summation formulas. Use SUM to add an entire range, SUMIF to sum values meeting a single condition, and SUMIFS to calculate totals filtered by multiple criteria across different columns.

Copy & paste prompt

Example

Sample Input Data (A2:C5):

Calculations:

• Total Sales (SUM): =SUM(C2:C5) yields $3,250
• Single Condition (SUMIF for North): =SUMIF(B2:B5, "North", C2:C5) yields $2,050 ($1,000 + $50 + $1,000)
• Multiple Conditions (SUMIFS for Laptop in North): =SUMIFS(C2:C5, A2:A5, "Laptop", B2:B5, "North") yields $2,000 ($1,000 + $1,000)

Best for

• Total sales and revenue reporting
• Regional sales breakdowns
• Product-specific revenue summaries
• Department expense tracking

Important note

In an Excel SUM formula using SUMIFS, the sum range and every criteria range must share identical row dimensions (e.g., C2:C50 and A2:A50), or Excel returns a #VALUE! error. Check for trailing spaces or spelling mismatches if a formula returns zero unexpectedly.

6. /count — COUNTIF & COUNTIFS

What it does

This prompt helps you build an Excel counting formula to tally rows meeting specific criteria. Use COUNTIF when evaluating a single condition (such as status flags like "Completed" or "Pending") and COUNTIFS when filtering across multiple criteria simultaneously (such as department and status).

Copy & paste prompt

Example

Sample Input Data (A2:C5):

Calculations:

• Total Completed Tasks: =COUNTIF(C2:C5, "Completed") yields 2 (Alex, Morgan)
• Completed Tasks in Sales: =COUNTIFS(B2:B5, "Sales", C2:C5, "Completed") yields 2 (Alex, Morgan)

Best for

• Employee status tracking (Active, On Leave, Resigned)
• Order fulfillment workflows (Pending, Shipped, Delivered)
• Sales lead pipeline management
• Attendance and absentee record tallies

Important note

A COUNTIFS formula requires all criteria ranges to have identical dimensions (e.g., B2:B50 and C2:C50). Inconsistent text casing with trailing spaces or numbers formatted as text can lead to zero counts; inspect your source data if the formula returns unexpected results.

7. /percentage — Calculate Percentages

What it does

This prompt helps you write formulas for percentage calculation Excel tasks. It clearly differentiates between calculating a simple share of a total (part÷whole), percentage change (originalnew−original​), and periodic growth rate without confusing formulas with visual formatting.

Copy & paste prompt

Example

Sample Calculations:

• Share of Total: Product revenue in cell B2 is $125, and Total Revenue in cell $B$10 is $500. Formula: =B2/$B$10 returns 0.25. Applying Excel's Percentage format displays 25%.
• Percentage Change: Sales in 2025 (cell B2) were $10,000 and in 2026 (cell C2) are $12,000. Formula: =(C2-B2)/B2 returns 0.20 (+20.0% increase).
• Safe Growth Formula: =IF(B2=0, "N/A", (C2-B2)/B2) prevents #DIV/0! errors when base period sales are zero.

Best for

• Month-over-month (MoM) and YoY sales growth
• Product category contribution to total revenue
• Budget variance and expense tracking
• Marketing conversion and discount calculations

Important note

Percentage change is mathematically undefined if the base value is zero; ensure formulas include error-handling conditions and verify all financial outputs against manual calculations.

8. /profit — Profit & Profit Margin

What it does

This prompt generates formulas for Excel business calculations, clearly distinguishing between revenue (gross sales earnings), total cost, profit (revenue minus specified costs), and profit margin (profit divided by revenue, expressed as a percentage). It ensures margin calculations are never confused with markup.

Copy & paste prompt

Example

Sample Input Data: Row 2 contains Selling Price in B2 ($50), Unit Cost in C2 ($30), and Quantity in D2 (10).

• Total Revenue: =B2*D2 yields $500
• Total Cost: =C2*D2 yields $300
• Gross Profit in E2: =(B2-C2)*D2 yields $200
• Gross Margin in F2: =IF((B2*D2)=0, 0, E2/(B2*D2)) yields 0.40 (40.0%)
• Total Profit across all rows: =SUM(E2:E50)

Best for

• SKU-level product profitability analysis
• Monthly sales revenue and margin reports
• E-commerce order profitability tracking
• Evaluating wholesale vs. retail pricing models

Important note

A simple price-minus-cost calculation reflects gross margin, not net profit, if overhead expenses like shipping, marketing, or taxes are excluded; always verify formula logic before making pricing or financial decisions.

9. /gst — Calculate GST

What it does

This prompt creates working formulas for GST calculation Excel tasks using a user-specified tax rate. It clearly differentiates between forward tax calculations (adding GST to an exclusive base price) and reverse tax extractions (separating base price and GST from an inclusive total) without providing formal tax advice.

Copy & paste prompt

Example

Scenario: Cell B2 contains a user-supplied base price of $1,000, and user-provided GST rate is 18% (entered as 0.18 or 18% in cell $D$1).

• GST-Exclusive: GST Amount = =ROUND(B2*$D$1, 2) yields $180.00; Total Price = =ROUND(B2*(1+$D$1), 2) yields $1,180.00.
• GST-Inclusive: If cell B2 contains an inclusive price of $1,180.00, Base Taxable Value is =ROUND(B2/(1+$D$1), 2) ($1,000.00) and Extracted GST is =ROUND(B2-(B2/(1+$D$1)), 2) ($180.00).

Best for

• Generating sales invoice line items
• Reverse-calculating base costs from inclusive retail receipts
• Verifying vendor billing statements
• Organizing monthly tax reconciliation ledgers

Important note

Verification warning: GST rates, exemptions, and rounding rules vary strictly by jurisdiction and item category; always verify the applicable rate with an authoritative source or tax advisor before relying on calculations for statutory accounting or tax compliance.

10. /date — Excel Date Calculations

What it does

This prompt creates working formulas for Excel date formula tasks, helping you calculate intervals, project milestones, and business schedules. It properly distinguishes between standard calendar periods, completed years, and business working days while accounting for serial date storage.

Copy & paste prompt

Example

Sample Setup: Start Date in A2 is 01-Jan-2026; End Date in B2 is 30-Jun-2026.

• Total Calendar Days: =B2-A2 yields 180 days
• Add 30 Calendar Days: =A2+30 yields 31-Jan-2026
• Add 3 Calendar Months: =EDATE(A2, 3) yields 01-Apr-2026 (avoids month-length inconsistencies)
• Working Days (excluding weekends & holidays in $H$2:$H$10): =NETWORKDAYS(A2, B2, $H$2:$H$10)

Best for

• Project deadline and milestone tracking
• Invoice payment due dates and aging
• Employee tenure and service period records
• Contract duration and renewal schedules

Important note

Excel stores dates as serial numbers; if dates are stored as text strings (e.g., '01/01/2026), formulas return #VALUE! errors. Furthermore, total calendar days differ from business working days, as weekends and custom holiday schedules directly alter workday counts.

11. /attendance — Attendance Calculator

What it does

This prompt helps you build summary formulas for an attendance sheet Excel register. It automates counting Present days, Absent days, and Leaves, while calculating attendance percentages based on your organization's specific attendance policy without altering raw logs.

Copy & paste prompt

Example

Sample Input Data: Row 2 tracks 20 recorded working days across B2:U2, containing 16 "P", 2 "A", and 2 "L" entries.

• Present Days in V2: =COUNTIF(B2:U2, "P") yields 16
• Absent Days in W2: =COUNTIF(B2:U2, "A") yields 2
• Leave Days in X2: =COUNTIF(B2:U2, "L") yields 2
• Attendance % in Y2: =V2/COUNTA(B2:U2) yields 0.80 (80.0%)

Best for

• Monthly employee attendance registers
• Student academic attendance logs
• Shift-based workforce tracking
• Pre-payroll attendance verification

Important note

Attendance percentages vary significantly depending on whether approved leave, holidays, or half-days are counted in the total base; always define your organization's attendance policy before finalizing numbers for payroll or compliance.

12. /salary — Salary Calculation

What it does

This prompt helps you structure and calculate payroll components in a salary sheet Excel template. It organizes earnings into Gross Salary (Basic+Allowances) and subtracts withholdings to compute Net Take-Home Salary (Gross−Deductions) using your organization's specific pay structure.

Copy & paste prompt

Example

Sample Input Data (Row 2):

• Basic Salary in B2: $3,000
• Housing Allowance in C2: $1,000
• Transport in D2: $200
• Total Deductions in E2: $600

Formulas:

• Gross Salary in F2: =SUM(B2:D2) yields $4,200
• Net Salary in G2: =F2-E2 yields $3,600
• Total Company Net Payout: =SUM(G2:G50)

Best for

• Monthly salary calculation Excel workflows
• Employee compensation and increment modeling
• HR payroll registers and expense reporting
• Allowance and deduction variance audits

Important note

Verification warning: Real-world payroll involves statutory tax withholding, retirement contributions, and jurisdiction-specific labor laws; always reconcile formulas against your organization's legal policies before processing live salary disbursements.

13. /invoice — Create an Excel Invoice

What it does

This prompt helps you structure and build an automated Excel invoice template. It sets up dynamic formulas for line-item subtotals, optional trade discounts, user-defined tax amounts, and the final balance, making it easy to create invoice in Excel workflows that update automatically when inputs change.

Copy & paste prompt

Example

Sample Invoice Setup:

• Item 1 in Row 12: Quantity 2 in B12, Rate $100.00 in C12
• Item 2 in Row 13: Quantity 1 in B13, Rate $200.00 in C13
• Discount Rate: 10% in cell D15; Tax Rate: 5% in cell D17

Formulas:

• Line Totals in D12 and D13: =B12*C12 ($200.00) and =B13*C13 ($200.00)
• Subtotal in D14: =SUM(D12:D13) ($400.00)
• Discount Amount in D15: =D14*0.10 ($40.00)
• Taxable Subtotal in D16: =D14-D15 ($360.00)
• Tax Amount (5%) in D17: =D16*0.05 ($18.00)
• Grand Total in D18: =D16+D17 ($378.00)

Best for

• Freelancer and contractor billing sheets
• Small business product sales invoices
• Consulting and professional service receipts
• Client quotation and proforma invoice generation

Important note

Verification warning: Invoice compliance rules, required legal disclosures, and tax calculations vary strictly by jurisdiction and business type; always verify statutory invoice requirements and mathematical outputs before issuing invoices to clients.

14. /pivot — Create a PivotTable

What it does

This prompt acts as an AI PivotTable assistant, analyzing your raw dataset to recommend the optimal field layout across Rows, Columns, Values, and Filters. It translates your reporting goals into step-by-step setup instructions, helping you summarize complex transactions and compute metrics like percentage of totals without manual formulas.

Copy & paste prompt

Example

Scenario: A transaction table in A1:E100 tracks Date, Product, Region, Salesperson, and Sales.

• Field Configuration: Place Region in Rows, Product in Columns, Sales (Sum) in Values, and Salesperson in Filters.
• Market Share Display: Right-click any value cell in the PivotTable → Show Values As → % of Grand Total to display proportional revenue shares.

Best for

• Multi-region sales performance analysis
• Product category revenue breakdowns
• Monthly financial expense aggregations
• Departmental employee headcount summaries

Important note

PivotTables require clean tabular data with single-row headers, unmerged cells, and consistent numeric formats. Note that "Percent of Total" can represent the Grand Total, Row Total, or Column Total; specify your baseline to ensure the correct relative comparison.

15. /chart — Choose & Create Excel Charts

What it does

This prompt acts as an AI Excel charts advisor, recommending the best visual format—such as Column, Bar, Line, or Pie charts—based on your data structure and reporting goals. It helps with data visualization Excel tasks by providing clear, step-by-step setup instructions to ensure your visuals communicate trends, category comparisons, or composition clearly.

Copy & paste prompt

Example

Scenarios & Recommendations:

• Monthly revenue over 12 months: Use a Line chart to show chronological trend progression over time.
• Revenue comparison across 5 discrete product categories: Use a Clustered Column or Bar chart to make category differences immediately scannable.
• Market share across 3 regional territories: Use a Pie chart (recommended only when displaying parts of a single whole with 5 or fewer slices).

Best for

• Monthly revenue and growth trend reports
• Product category sales performance comparisons
• Regional market-share presentations
• Executive dashboard summaries

Important note

Visual appeal should never compromise data integrity; using pie charts for time-series data or truncating vertical axes can mislead viewers. Always verify that your chart's scale and category mappings accurately represent the underlying numbers.

16. /sort — Sort Excel Data

What it does

This prompt guides you on how to sort data in Excel correctly across single or multiple levels. It helps organize text (A–Z/Z–A), numbers (smallest to largest/largest to smallest), and dates (oldest to newest/newest to oldest) while ensuring full rows stay locked together to prevent scrambled data.

Copy & paste prompt

Example

Scenario: An HR dataset in A1:D50 with Employee, Department, Salary, and Joining Date.

• Configuration: In Excel's Custom Sort dialog, set Level 1 to Department (A–Z) and Level 2 to Salary (Largest to Smallest).
• Outcome: Groups all employees by their department alphabetically, while listing the highest earners at the top of each department group.

Best for

• Employee rosters and payroll rankings
• Sales lead and pipeline prioritization
• Product inventory stock-level ordering
• Task tracking lists by urgency and deadline

Important note

Sorting a single selected column instead of the entire dataset will detach values from their corresponding rows and corrupt your data; always select the entire table range or convert it into an Excel Table (Ctrl + T) before applying sorts.

17. /filter — Filter Excel Data

What it does

This prompt helps you structure and apply targeted criteria to Excel filter data workflows across text, numeric, and date fields. It translates complex filtering goals into step-by-step instructions, allowing you to isolate relevant subsets of records temporarily without deleting or altering underlying rows.

Copy & paste prompt

Example

Scenario: Sales dataset in A1:F50.

• Filter Setup: Click the Region drop-down filter and select "North"; click the Sales filter → Number Filters → Greater Than... and enter 1000.
• Modern Formula Alternative (Microsoft 365): =FILTER(A2:F50, (C2:C50="North") * (D2:D50>1000), "No Records Found") extracts matching records dynamically into a separate summary range.

Best for

• Isolating high-value sales transactions
• Reviewing customer records by geographical territory
• Tracking pending or overdue project tasks
• Auditing inventory items below reorder thresholds

Important note

Applying a filter merely hides non-matching rows from view; it does not delete records. Be careful when combining multiple column filters, as standard column drop-downs apply AND logic by default (narrowing results), rather than OR logic.

18. /dashboard — Build an Excel Dashboard

What it does

This prompt helps you plan and design a functional, decision-focused Excel dashboard with AI. It converts raw tabular data into a structured layout featuring high-level KPI cards, targeted charts, and interactive slicers, prioritizing clear data storytelling and actionable business insights over clutter.

Copy & paste prompt

Example

Scenario: Sales dataset in A1:F100 (Date, Product, Region, Salesperson, Revenue, Cost).

• Top Row: 3 KPI cards showing Total Revenue (=SUM(E2:E100)), Total Cost (=SUM(F2:F100)), and Gross Profit (=E2-F2 aggregated).
• Middle Row: A Line Chart displaying monthly revenue trends over time.
• Bottom Row: A Bar Chart comparing revenue across regions.
• Interactive Panel: Slicers for Region and Product connected to the supporting PivotTables.

Best for

• Monthly executive sales and revenue reporting
• Departmental operational efficiency tracking
• Marketing campaign ROI and conversion summaries
• Financial budget vs. actual performance reviews

Important note

A dashboard should solve a specific business question; adding excessive decorative visuals reduces readability. Verify that all underlying formulas and PivotTable source connections are accurate before presenting metrics to stakeholders.

19. /format — Professionally Format Excel Data

What it does

This prompt helps you establish clean, professional Excel formatting across your workbook to maximize readability without changing underlying cell values. It provides structured guidance on standardizing headers, currency symbols, date layouts, alignment conventions, subtle borders, and targeted conditional formatting rules.

Copy & paste prompt

Example

Sample Recommendations:

• Headers (A1:E1): Dark slate fill, bold white text, center-aligned.
• Data Alignment: Left-align Product names (Column A); center Sales Dates (YYYY-MM-DD) and Status (Columns B & E); right-align Quantity and Revenue (Columns C & D).
• Number Formatting: Set Quantity to #,##0 and Revenue to $#,##0.00.
• Conditional Formatting: Apply soft-green fill to rows where Status is "Delivered" using the formula =$E2="Delivered".

Best for

• Executive sales and financial summary sheets
• Client-facing invoice schedules and cost estimates
• Monthly departmental budget tracking logs
• Formal HR employee rosters and audit reports

Important note

Applying AI Excel formatting alters only the visual appearance of your cells, not their underlying values or data types; formatting text-stored numbers as currency will not fix broken calculation formulas until the raw data is cleaned.

20. /summary — Summarize Excel Data

What it does

This prompt turns raw spreadsheet numbers into a structured, evidence-based narrative using AI spreadsheet analysis. It computes core statistical aggregates—such as totals, averages, and min/max ranges—while identifying genuine periodic trends, categorical distributions, and potential outliers without inventing unsupported business conclusions.

Copy & paste prompt

Example

Sample Input Data:

Result:

• Total Revenue: $96,000
• Average Revenue: $19,200
• Range: Minimum $10,000 (Jan); Maximum $45,000 (May)
• Observations: Demonstrates consistent monthly growth from Jan to Apr ($10k to $15k), followed by a substantial spike in May ($45,000) flagged as an outlier for manual review to verify whether it represents a bulk order or a data entry error.

Best for

• Monthly and quarterly executive sales reviews
• Departmental operational performance reports
• Financial budget variance and expense audits
• Inventory stock movement evaluations

Important note

AI-generated summaries highlight patterns and mathematical correlations, but correlation does not prove causation; always verify highlighted metrics, averages, and flagged outliers against your source workbook before using them for strategic decision-making.

How to Get Better Results From Excel AI Prompts

Excel AI Prompt Safety & Accuracy Tips

Can You Use These Prompts With ChatGPT, Gemini and Copilot?

Frequently Asked Questions