AI for Excel: A Practical Guide to Formulas, Analysis and Automation
Use AI for formulas, cleaning, receivables, duplicate review and dashboards—with worked examples, eight copyable prompts and Microsoft learning resources.
AI can turn a plain-language question into a formula, explain an unfamiliar workbook, propose a cleaning workflow or help prepare a financial dashboard. Its value increases when the source data is structured and the result can be checked.
This guide focuses on Excel: Copilot, external assistants, practical accounting examples, reusable prompts and a safe path from a one-off task to repeatable automation.
1. Three ways to use AI with Excel
| Approach | Use it for | What to check |
|---|---|---|
| Copilot inside Excel | Working with workbook data, formulas and analysis through supported Copilot features. | Your licence, app version, administrator settings and feature availability. |
| An external assistant | Explaining formulas, planning analysis or drafting code from supplied examples. | It sees only the information you provide unless an authorised integration exists. |
| AI-assisted workflow design | Drafting Power Query steps, VBA or a repeatable reporting process. | Review and test the generated logic before running it on real workbooks. |
Microsoft states that the preview worksheet function was withdrawn on 14 September 2026. Copilot’s pane remains the supported alternative. Older tutorials featuring that function may no longer apply.
Read Microsoft’s function-retirement notice ↗For setup and current availability, use Microsoft’s getting-started guide and licensing and availability FAQ. A Microsoft 365 subscription alone does not establish that every Copilot feature is available to your account.
2. Prepare the workbook first
- Make a working copy and preserve the source export.
- Use one header row, consistent column names and one transaction per row.
- Convert the working range to an Excel Table and give it a meaningful name.
- Check dates, numeric types, blanks and duplicate keys.
- Record row counts and control totals before transformations.
- Describe the reporting period, currency, sign convention and expected output.
3. Worked examples you can verify
These examples are illustrative. They use Excel Tables and English function names with comma separators; adapt the table names and locale to your workbook.
Case 1 — Gross margin
In a table named Sales, use columns Revenue and Cost. Ask AI to propose the calculation and explain the zero-revenue case.
=IF([@Revenue]=0,"",([@Revenue]-[@Cost])/[@Revenue])Revenue ₹1,00,000 and cost ₹70,000 produce a 30% margin. Revenue zero produces a blank to show that the ratio is undefined. Investigate negative revenue rather than silently treating it as a normal sale.
Case 2 — Receivables days overdue
Put the reporting date in Control!B1. In a table with DueDate and Outstanding:
=IF(OR([@DueDate]="",[@Outstanding]<=0),"",MAX(0,Control!$B$1-[@DueDate]))At 31 July 2026, an unpaid invoice due on 15 July is 16 days overdue. A future due date gives zero. Blank due dates and non-positive balances return blank for separate review. Ensure the due date is an actual Excel date.
Case 3 — Potential duplicate invoices
=COUNTIFS(Payments[Supplier],[@Supplier],Payments[InvoiceNo],[@InvoiceNo],Payments[Amount],[@Amount])>1Two rows with the same supplier, invoice number and amount both return TRUE. This is a screening rule: instalments, reversals and credit notes can create valid repeats. Normalise supplier names and invoice formatting before matching.
Case 4 — Budget variance
=[@Actual]-[@Budget]
=IF([@Budget]=0,"",([@Actual]-[@Budget])/ABS([@Budget]))Actual expense ₹1,20,000 against budget ₹1,00,000 produces a ₹20,000 adverse variance and 20%. For revenue the same positive sign may be favourable. Define account type before assigning a performance label.
Case 5 — Monthly dashboard
Ask AI to propose a PivotTable layout and chart plan using the actual fields available. For a sales dataset, begin with revenue by month, revenue by category and top customers. Profit needs reliable costs; cash flow needs receipt and payment data. A sales table cannot establish liquidity by itself.
Case 6 — Bank reconciliation
Match exact references and amounts first, then use date windows or normalised descriptions to propose candidates. Keep exact, suggested and unmatched records separate. Reconcile unmatched totals and obtain evidence before posting adjustments.
4. Eight reusable Excel prompts
Replace the brackets and use anonymised sample rows. Good prompts explain the workbook structure and what a correct result should look like.
Write an Excel formula for [task] using table [name] and columns [headers]. My Excel version is [version] and locale is [locale]. Explain handling of blanks, zero values and errors. Give three expected results from [sample rows]. Do not assume column positions.
Inspect [anonymised sample and schema]. Identify inconsistent dates, numbers stored as text, whitespace and duplicates. Suggest reversible Power Query steps and an exception log. Preserve original data and distinguish missing values from zero.
Using [invoice-level data] and reporting date [date], calculate open balances after receipts and credits. Age by contractual due date. Reconcile to the control total, list missing dates and flag credit balances separately.
Screen [payment data] for duplicate supplier, invoice and amount combinations. Explain normalisation and matching rules. Return candidate pairs with row IDs; treat them as review alerts, not established duplicate payments or fraud.
Using [actual, budget and comparative data], reconcile totals, calculate margins and rank material variances. State the variance sign convention, show source figures and distinguish observed movements from possible causes.
Design an Excel dashboard for [audience and decision] from [available fields]. Define six KPIs, formulas, reporting grain and filters. Suggest charts and reconciliation checks. Identify measures that cannot be derived from the data.
Draft VBA for [task] on an explicitly selected workbook and range. Validate headers, preserve formulas, avoid silent deletion and restore application settings after errors. Include a dry-run option and a sample-data test plan.
Explain why [formula] returns [error] using [sample inputs and Excel version]. Identify the cause before proposing a fix. Do not conceal problems by wrapping everything in IFERROR. Show expected results for normal and edge cases.
5. Power Query, VBA or formulas?
| Need | Practical starting point |
|---|---|
| Calculations that update with rows | Excel formulas and Tables. |
| Repeated imports and data cleaning | Power Query with documented transformation steps. |
| Summarisation and exploration | PivotTables, charts and supported analysis features. |
| Workbook formatting or desktop actions | Reviewed VBA, with explicit workbook references and backups. |
AI can draft the instructions; it does not remove compatibility limits. A macro designed for desktop Excel will not automatically work in Excel for the web. Ask for the simplest approach that meets the task.
6. Watch and learn
An official Microsoft demonstration. The interface may differ from your version.
Watch directly on YouTube ↗Also explore Analyze Data in Excel and our five spreadsheet cases and video lessons.
7. A five-point review before relying on results
- Completeness: reconcile record counts and totals to the original data.
- Formula accuracy: inspect references and test normal, blank, zero and negative inputs.
- Interpretation: separate factual results from AI-generated explanations.
- Confidentiality: use approved services and minimise client or personal information.
- Repeatability: retain the input definition, transformation steps and reviewer approval.
Start with one familiar task, such as a monthly variance report. Measure correction effort as well as time saved, then turn the proven method into a reusable workflow.