THE PRACTICAL AI PLAYBOOK AI for Microsoft Excel A practical 25-page guide to making difficult spreadsheet tasks easier Formulas • Clean data • Analysis • Automation 25 pages | Updated October 2026
Your route through the guide Choose a task, then use its workflow and copy-ready prompt AI is useful when you can describe a business rule but struggle to translate it into formulas, transformations or code. Use it to draft the method and explain the result. Keep Excel calculations and your source records as the evidence. Pages What you will learn 3-5 Choose an AI workflow, prepare data and write better prompts 6-9 Build formulas, debug errors, create dynamic lists and reconcile files 10-15 Find duplicates, clean data, handle dates, classify text and join files 16-20 Build reports, explain changes, forecast, model scenarios and use Python 21-24 Automate with VBA or Office Scripts and apply HR/payroll and close workflows 25 Review results and open the Microsoft documentation index Two ways to follow each recipe Inside Excel: use the Copilot pane and select the appropriate chat, plan or editing option available in your account. External assistant: provide approved sample rows, headers and rules, then ask for an Excel formula, Power Query steps or code. Apply the response yourself in a test copy. Example conventions All datasets and amounts in this guide are fictional. Formula examples use English function names and commas. Your locale may use semicolons. Table names and column headings must match exactly. Newer functions require a compatible Excel version. CHECK THE RESULT Begin with one recurring task you already understand. Record the time required and the checks you use today. Compare the AI-assisted result against that baseline. Copilot in Excel: current workflows AI FOR MICROSOFT EXCEL 02 / 25
The AI options available to you The interface, the calculation engine and the automation tool do different jobs Route Best use What you do Copilot in Excel Workbook changes and analysis Describe the change in the pane and review the workbook External AI assistant Formula or code drafting Provide approved context, then paste or run the draft yourself Power Query + AI Repeatable file preparation Use AI to design steps or M code, then refresh the query Python + AI More advanced analysis Review the code and execute in the supported environment VBA / Office Scripts + AI Repeatable actions Test the script before using it on live work Getting started Open a copy of the workbook and locate Copilot. Current Microsoft documentation describes edit, plan and chat modes on Windows, Mac and the web. Start in chat or plan for an unfamiliar task. Available features depend on your license, app version and organization settings. Check the linked FAQ if a command is missing. An important change The =COPILOT() worksheet function retired on September 14, 2026. Use the Copilot pane instead. Old workbooks may keep cached results but return #NAME? when that function recalculates. For workplace data Use your organization’s approved AI account and data policy. For an external assistant, share a minimal synthetic sample when it is enough to design the method. Remove names, account numbers and confidential details that are unnecessary for the task. CHECK THE RESULT Power Query, Solver, VBA and Office Scripts are Excel tools. AI helps construct or explain their work; these tools perform the transformations or calculations. Copilot in Excel: current workflows Copilot licensing and availability Retired COPILOT worksheet function AI FOR MICROSOFT EXCEL 03 / 25
Prepare the workbook for useful answers A clear dataset prevents many mistakes before the prompt begins A worksheet built for reading is not always ready for analysis. Repeated headers, merged cells, subtotal rows and inconsistent identifiers can make a plausible AI answer wrong. Prepare a clean input area while retaining the original export. A dependable preparation sequence 1. Keep the original file unchanged and make a working copy. 2. Give each column one unique heading and each row one record. 3. Convert the input range to a table with Insert > Table. Confirm the header setting and assign a meaningful table name. 4. Define the record key, date convention, units and missing-value treatment. 5. Record the row count and control totals before making changes. Field Meaning Data rule EmployeeID Stable person identifier Text, including leading zeros WeekEnd Reporting week end Real Excel date Hours Hours for the record Number, not formatted text PayCode Type of recorded hours Approved code list COPY-READY PROMPT Inspect the table TimeData without editing it. List blank keys, duplicate EmployeeID + WeekEnd + PayCode combinations, text stored in Hours and unrecognized PayCode values. Return a count and example record IDs for each issue. CHECK THE RESULT Expected output: an issues list, not silent corrections. Decide whether repeated records are errors or legitimate split entries before combining them. Structured references in Excel tables AI FOR MICROSOFT EXCEL 04 / 25
A prompt that describes the real task Give AI a specification it can turn into a repeatable method The six pieces of a useful request Include Example Goal Create an invoice reconciliation report Input Invoices and Ledger tables with InvoiceID and Amount Rule Exact ID match and a one-cent amount tolerance Output A new Status column and a separate exceptions list Constraints Retain source rows and flag duplicate keys Acceptance test Known matching, missing and duplicate cases COPY-READY PROMPT I use Excel for Microsoft 365 on Windows. Invoices and Ledger each contain InvoiceID and Amount. Return an Excel formula for an Invoices[Status] column. Match exact InvoiceID values, treat an amount difference of at most $0.01 as a match after rounding to cents, and return Missing, Duplicate, Match or Mismatch. Do not assume Ledger keys are unique. Explain the formula and provide five test cases. Do not edit source values. When the answer misses the point Correct one assumption at a time. Say which row produced the wrong result, what you expected and which rule applies. Ask for the smallest change to the method. A request to “make it better” gives the assistant little evidence to work with. A strong follow-up COPY-READY PROMPT The formula returned Match for InvoiceID A103, but Ledger has two A103 records. Revise the uniqueness check. Show the old and new logic and explain whether any other status changes. CHECK THE RESULT For complex work, ask for a proposed plan before any edits. Name the exact sheets or tables that may change and the source areas that must remain intact. AI FOR MICROSOFT EXCEL 05 / 25
Build a formula from a business rule Example: monthly sales for a selected region You know the question: “How much did the South region sell in October?” AI can translate that rule into a formula, explain the references and help adapt it to your table. The answer still depends on valid dates and numeric amounts. Inputs and rule Create a Sales table with Date, Region and Amount. Put the selected region in H2 and the first day of the selected month in H3. The formula includes that day and excludes the first day of the next month. EXCEL / CODE EXAMPLE =SUMIFS(Sales[Amount],Sales[Region],$H$2, Sales[Date],">="&$H$3, Sales[Date],"<"&EDATE($H$3,1)) COPY-READY PROMPT Write a SUMIFS formula for total Sales[Amount] where Sales[Region] matches H2 and Sales[Date] falls within the month beginning in H3. Dates may contain times. Use an inclusive start and exclusive next-month boundary. Explain every argument. Date Region Amount Included? 2026-10-01 South $1,200 Yes 2026-10-31 16:30 South $800 Yes 2026-11-01 South $500 No 2026-10-15 North $700 No CHECK THE RESULT Expected total for South and H3 = October 1, 2026: $2,000. Also test an empty month and a misspelled region. A zero total may be legitimate, but it may also expose inconsistent region labels. If your workbook uses an older Excel version, ask the assistant to avoid unavailable functions. Do not accept a visually correct formula until Excel recalculates it and the known cases pass. SUMIFS function AI FOR MICROSOFT EXCEL 06 / 25
Debug formulas without hiding the problem Find the cause before adding IFERROR AI can read a long formula and help isolate a wrong reference, a mismatch in data types or an incorrect boundary. Broad IFERROR wrappers can make a broken calculation look clean. Ask for a diagnosis before an error-handling change. Symptom First investigation #N/A Missing lookup key, spaces or text/number mismatch #VALUE! Text where numbers are expected or incompatible ranges #REF! Deleted or invalid reference #SPILL! Blocked output range or unsuitable formula location Wrong number Incorrect business rule, criteria or source range A focused debugging sequence 1. Copy the exact formula and one failing input. 2. Provide the expected result and how you calculated it. 3. Ask AI to explain each part of the formula. 4. Inspect intermediate values with helper cells or Excel’s formula-auditing tools. 5. Retest the corrected formula on normal, blank and boundary cases. COPY-READY PROMPT This formula returns 0, but the expected amount is 125. Here are the exact formula, table headings and three synthetic input rows. Identify likely causes in order. Do not add IFERROR. Suggest diagnostic formulas before rewriting the calculation. CHECK THE RESULT A good response identifies the faulty assumption and explains the correction. If a lookup is missing, keep a visible Missing status instead of converting every error to zero. Check copied formulas down the column. A formula that works in the first row can still fail later because a reference was not anchored or one record contains unexpected text. XLOOKUP function Excel functions by category AI FOR MICROSOFT EXCEL 07 / 25
Create dynamic summaries and exception lists Reduce the need to copy formulas into hundreds of rows Dynamic arrays can return a list or a summary that grows with your data. AI is useful for combining functions into a readable formula. Put spilling formulas in a clear worksheet area outside an Excel table. Example: customer sales ranking The Sales table contains Customer and Amount. Use a compatible Microsoft 365 or Excel version with the required functions. Each row below is a sales record, and all amounts must be numeric. EXCEL / CODE EXAMPLE =LET( ids,UNIQUE(Sales[Customer]), totals,SUMIF(Sales[Customer],ids,Sales[Amount]), SORTBY(HSTACK(ids,totals),totals,-1)) COPY-READY PROMPT Create a dynamic two-column summary outside the Sales table. Return each unique Customer and the sum of Amount, sorted from highest to lowest. Use LET to name intermediate results. Explain what happens with blank customer names and tied totals. Input customer Input amount Expected summary A $100 A: $150 B $200 B: $200 A $50 Order: B, then A An exception-list variation EXCEL / CODE EXAMPLE =FILTER(Orders,Orders[Status]="Late","No late orders") CHECK THE RESULT Leave the spill area empty. Add one new source row, verify that the summary expands and confirm that its total equals the source total. Decide how to handle blank customers before reporting. If HSTACK is unavailable, ask for a PivotTable or a compatible helper-column method. State your Excel version instead of letting AI assume every new function exists. LET function Excel functions by category AI FOR MICROSOFT EXCEL 08 / 25
Reconcile two files with explicit match rules Example: invoice amounts against a ledger export Reconciliation is easier when you define both the key and the tolerance. An exact match on a non-unique key can hide a duplicate. Flag blank IDs first. This example uses nonblank text IDs and case-insensitive matches; one ledger record per invoice should exist. EXCEL / CODE EXAMPLE =LET( n,SUMPRODUCT(--(Ledger[InvoiceID]=[@InvoiceID])), IF(n=0,"Missing", IF(n>1,"Duplicate", IF(ABS(ROUND([@Amount]-XLOOKUP([@InvoiceID],Ledger[InvoiceID], Ledger[Amount]),2))<=0.01, "Match","Mismatch")))) Workflow 1. Preserve both exports and align key data types. 2. Identify duplicate keys on both sides before matching. 3. Add the Status formula to Invoices and a difference calculation for unique matches. 4. Create a reverse unmatched check for ledger-only records. 5. Reconcile counts and totals, including missing and duplicate records. Invoice case Expected status One ledger match; equal amounts Match One ledger match; $0.01 difference Match One ledger match; $2.00 difference Mismatch No ledger match Missing Two ledger rows with the same key Duplicate COPY-READY PROMPT Build a two-sided reconciliation plan for Invoices and Ledger. Show counts, amounts, missing keys, duplicate keys and unique matches. Do not allocate partial payments automatically. Explain whether the files have the same reporting cutoff. CHECK THE RESULT If one invoice legitimately has several ledger lines, aggregate those lines by an approved rule first. Do not treat the first XLOOKUP result as the complete invoice amount. XLOOKUP function AI FOR MICROSOFT EXCEL 09 / 25
Find duplicates and possible duplicates Separate repeated keys from records that only look similar Two identical names can represent different people. Two differently written supplier names can represent one company. AI can help design checks and suggest candidate matches, but your record key and review process determine whether a record should be removed. Exact duplicate check For an Employees table, a repeated EmployeeID is a key exception. The formula marks every occurrence of a duplicated ID. Blank keys should have their own status. EXCEL / CODE EXAMPLE =IF([@EmployeeID]="","Missing ID", IF(COUNTIF(Employees[EmployeeID], [@EmployeeID])>1,"Duplicate ID","Unique")) For transaction records Choose the full business key, such as EmployeeID + WeekEnd + PayCode. A repeated EmployeeID alone is normal in weekly payroll data. Decide whether repeated business keys are corrections, split lines or accidental duplicates. COPY-READY PROMPT Identify exact duplicate business keys in TimeData using EmployeeID, WeekEnd and PayCode. Separately list possible duplicates with identical employee and week but slightly different Hours. Return record IDs, match reasons and a proposed review action. Do not delete records. Possible-match review Use normalized text or Power Query fuzzy matching to suggest candidates. Retain original values, show the similarity evidence and confirm with stronger identifiers. Similarity is not proof of identity. CHECK THE RESULT Removing duplicates deletes records. Work on a copy and reconcile the count and amount of removed rows. Preserve an exceptions sheet showing the reviewer, decision and original record IDs. Find and remove duplicates Fuzzy matching in Power Query AI FOR MICROSOFT EXCEL 10 / 25
Clean inconsistent exports Turn a one-time fix into rules that survive the next refresh Typical exports contain extra spaces, inconsistent capitalization, invisible characters and mixed labels. Ask AI to propose explicit transformations. For repeatable work, implement those transformations in formulas or Power Query rather than manually editing each value. EXCEL / CODE EXAMPLE =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) This helper replaces nonbreaking spaces, removes certain nonprinting characters and trims ordinary spaces. It is useful for many exports, but it is not a universal Unicode cleanup routine. Input problem Recommended handling " South " and "SOUTH" Normalize spacing; use a controlled category mapping ID "00123" Keep text type; do not strip leading zeros "$1,250.00" stored as text Convert using the known numeric locale Blank amount Preserve blank unless the business rule defines it as zero COPY-READY PROMPT Create a cleaning plan for SupplierExport. Preserve every original column. Add cleaned columns for SupplierName, SupplierID and Amount. Use a mapping table for approved supplier aliases. Flag invalid amounts and missing IDs instead of guessing. Report before-and-after row counts and total amounts. Apply and check Test the rules on a sample with each known defect. Apply the same rules to the full dataset and retain rejected values in an exceptions table. Compare row counts, distinct key counts and totals with the raw export. CHECK THE RESULT Standardizing a category is different from changing the fact. Do not let AI “correct” an unfamiliar name, invent a missing number or replace a suspicious amount with a plausible one. Cleaning data in Excel Data types and locale in Power Query AI FOR MICROSOFT EXCEL 11 / 25
Handle dates and extract structured fields Example: ambiguous dates and inconsistent note text Changing a cell’s number format does not turn every text value into a valid date. AI can help identify format patterns and propose parsing rules. An entry such as 03/04/2026 is ambiguous without a source convention. Date conversion workflow 1. Keep a RawDate column. 2. Identify the export’s locale and whether it uses day/month or month/day. 3. In Power Query, select the appropriate data type and use a locale-aware conversion when needed. 4. Keep conversion failures in an exceptions query. 5. Inspect known dates and month boundaries before using date-based formulas. Raw value Required interpretation 03/04/2026 Confirm source convention before converting 2026-04-03 Parse as year-month-day with an explicit rule 2026-04-03 23:45 Decide whether the time is relevant 04/31/2026 Flag as invalid rather than silently substituting Extracting from inconsistent notes COPY-READY PROMPT Extract TicketID, requested date and equipment code from these synthetic notes. Return one row per source record with the original RecordID, extracted fields and the exact supporting text. Use Unknown when a field is absent. Do not infer a date from context. For predictable text, request a formula or Power Query parser. For varied natural language, review AI-extracted fields against the source and convert approved outputs into a structured table. CHECK THE RESULT A timezone or overnight-shift rule can change the reporting date. Define that rule before grouping records by day or week. Data types and locale in Power Query AI FOR MICROSOFT EXCEL 12 / 25
Classify comments and support tickets Use a clear category guide and keep an evidence trail Free-text comments are difficult to count consistently. AI can draft categories or label comments, but you need a stable definition for each label. This workflow uses the Copilot pane or an approved external assistant, not the retired COPILOT worksheet function. Category Include Example Scheduling Shift notice or assignment issues "The schedule arrived too late" Equipment Tools, machines or access failures "Scanner stopped working" Training Missing instructions or skills "No one explained the process" Other / unclear Insufficient evidence or outside scope "This needs to improve" COPY-READY PROMPT Classify each row in Feedback using Scheduling, Equipment, Training or Other / unclear. Preserve RecordID and Comment. Add Category, supporting words from the comment and NeedsReview. Use Other / unclear for ambiguous comments. If multiple themes appear, flag the row instead of forcing one category. Review and improve the labels 1. Label a small sample yourself using the definitions. 2. Compare AI labels with that sample and review disagreements. 3. Revise overlapping category definitions. 4. Review every unclear case and spot-check each category. 5. Freeze the category guide and save the prompt version before processing a larger batch. CHECK THE RESULT An AI confidence score is not automatically a calibrated probability. Use actual review results to judge label quality. Keep original comments and human corrections. For employee comments, report themes at an appropriate group level. Text labels should support investigation, not serve as an automatic basis for employment decisions. Retired COPILOT worksheet function AI FOR MICROSOFT EXCEL 13 / 25
Combine monthly files with Power Query A refreshable alternative to repeatedly copying and pasting A folder of similar CSV or Excel exports is a good candidate for Power Query. AI can help define the import process, explain generated steps and write M code when needed. Consistent file structure makes the refresh predictable. Folder import workflow 1. Place only the intended input exports in a dedicated folder. 2. Use Data > Get Data > From File > From Folder where available. 3. Choose Combine & Transform Data and inspect the sample-file transformation. 4. Standardize headings and types, retain source filename and filter unwanted rows. 5. Load the result to a table and refresh after adding the next export. COPY-READY PROMPT Design a Power Query folder import for monthly shipment exports with ShipmentID, ShipDate, Plant, Units and FreightCost. Keep source filename. Exclude temporary files and the output workbook. Flag files with missing required columns. Create row-count and FreightCost controls by source file. Provide UI steps first and M code only if required. What to test Add a new file, refresh and confirm that its records appear once. Then test a file with a renamed column, an invalid date and an extra header row. Decide whether to reject the whole file or isolate its invalid rows. CHECK THE RESULT Save the combined output outside the input folder. Otherwise a later refresh can re-import the output. Do not silently skip failed files and present the resulting total as complete. Append stacks records with aligned columns. Merge adds information using matching keys. Ask AI for the correct operation before combining datasets. Combining files from a folder Merge queries in Power Query AI FOR MICROSOFT EXCEL 14 / 25
Join files without multiplying records A join that runs successfully can still produce the wrong totals Joining orders to a customer list looks simple until the customer list has repeated IDs. A one-to-many match can multiply the order rows and inflate sales totals. Define the relationship before building the merge. Input Record grain Key expectation Orders One row per order line OrderLineID is unique Customers One row per customer CustomerID is unique Merged output One row per order line Same row count as Orders Power Query merge sequence 1. Check CustomerID uniqueness in Customers. 2. Align key data types in both queries. 3. Merge using a left outer join from Orders to Customers. 4. Expand only the required customer fields. 5. Check unmatched keys and compare row counts and order totals. COPY-READY PROMPT Join Orders to Customers on exact CustomerID. Keep every Orders row. Before merging, report duplicate CustomerID values in Customers. After merging, report unmatched IDs, row-count changes and the Orders[Amount] control total. Stop for review if the customer key is not unique. When the names do not match Use a reviewed alias table for known variations. Fuzzy matching can generate candidates for unfamiliar variations, but do not let the similarity threshold decide the final identity automatically. Review candidates with business identifiers. CHECK THE RESULT An intentional many-to-many model needs different controls. Do not force it into a single-record lookup or keep whichever matching row happens to appear first. Merge queries in Power Query Fuzzy matching in Power Query AI FOR MICROSOFT EXCEL 15 / 25
Build PivotTables from business questions Example: output and scrap by plant and month AI can translate a question into rows, columns, values and filters. A PivotTable keeps the summary tied to its source data. The most important choice is often the aggregation: a count can look reasonable when the required measure is a sum. Business question PivotTable setup Output by plant and month Rows: Plant; columns: Month; values: SUM of Units Scrap quantity by plant Rows: Plant; values: SUM of ScrapUnits Production records by shift Rows: Shift; values: COUNT of RecordID COPY-READY PROMPT Create a PivotTable from Production showing total Units and total ScrapUnits by Plant and Month. Use sums, not counts. Separately calculate overall scrap rate as total ScrapUnits divided by total Units. Explain why averaging row-level scrap percentages may give the wrong result. Worked check Record Units Scrap Row rate A 100 10 10% B 900 9 1% Total 1,000 19 1.9% overall The simple average of 10% and 1% is 5.5%. The quantity-weighted result is 19 / 1,000 = 1.9%. Define the intended rate before asking AI to build a chart. CHECK THE RESULT Refresh after updating the source. Confirm the grand total against the source table, inspect one plant-month manually and verify any filter or slicer that changes the report. Creating a PivotTable AI FOR MICROSOFT EXCEL 16 / 25
Create a dashboard and explain a variance Make the numbers traceable before writing the narrative AI can draft a report layout and summarize patterns. A useful dashboard answers a defined operational question and lets the reader trace each measure to its calculation. Avoid explanations that invent a cause for a numerical change. Example: supplier delivery performance Define OnTimeRate as on-time deliveries divided by deliveries due in the period. Define LateCount, total FreightCost and median days late separately. State how cancellations, partial deliveries and missing due dates affect the denominator. COPY-READY PROMPT Build a dashboard specification from Deliveries with Supplier, DueDate, ActualDate, Status and FreightCost. Define every metric and denominator before suggesting charts. Include an overdue exceptions table with ShipmentID. After calculation, write a short summary that separates observed changes from possible explanations requiring investigation. A defensible narrative “On-time delivery fell from 92% to 86% across the compared periods. Supplier B contributed 18 of the 30 additional late deliveries. The data does not show why those deliveries were late.” This is more useful than declaring that the supplier’s staffing caused the decline. View Purpose Line chart by month Show the trend over a consistent time interval Sorted bar chart by supplier Compare counts or rates with denominators shown Exception table Give reviewers the records needing action CHECK THE RESULT Verify period lengths and cutoff dates, ensure charts use the same approved measures, and show the refresh date. Keep the narrative linked to evidence rather than treating it as another source of data. Copilot in Excel: current workflows Creating a PivotTable AI FOR MICROSOFT EXCEL 17 / 25
Forecast with a baseline and a backtest A future estimate needs evidence about past prediction error AI can help prepare time-series data and propose forecasting methods. Begin with a simple baseline before trying a more complex model. The example is monthly demand planning, not a guaranteed prediction. A practical forecasting workflow 1. Aggregate demand into consistent monthly periods. 2. Investigate missing months, returns and unusual events. 3. Reserve the last few observed months as a holdout. 4. Compare a simple baseline with a more advanced method on that holdout. 5. Select a method based on error and practical usefulness, then refit using available history. COPY-READY PROMPT Using monthly Units, compare a last-period baseline, a same-month-last-year baseline where history exists and an ETS forecast. Hold out the final three months. Report actuals, predictions and mean absolute error. Explain missing-data treatment and whether the history supports annual seasonality. Do not use holdout actuals while fitting. EXCEL / CODE EXAMPLE =FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25) This example assumes A2:A25 contains a consistent monthly timeline, B2:B25 contains historical units and A26 is a future date. Review seasonality and data-completion settings. FORECAST.ETS is not available in Excel for the web, iOS or Android according to current Microsoft documentation. CHECK THE RESULT Withhold future actuals from model fitting. Review forecast errors, not only how closely the fitted line follows past data. Use scenarios for known future changes that historical demand cannot explain. FORECAST.ETS function AI FOR MICROSOFT EXCEL 18 / 25
Model scenarios and constrained decisions Example: staffing capacity under different assumptions Use AI to structure a model and identify missing assumptions. Excel formulas compute scenarios; Solver can search for a solution under stated constraints. A generated recommendation is only as good as the model you specified. Scenario People Hours/person Units/hour Capacity Conservative 10 32 8 2,560 Base 10 36 9 3,240 Higher output 10 40 10 4,000 Illustrative capacity = people x productive hours per person x units per hour. These fictional assumptions exclude downtime and other constraints unless you add them. They are planning inputs, not staffing or pay-policy requirements. COPY-READY PROMPT Create a capacity model with named input cells for headcount, productive hours, units per hour and downtime factor. Show conservative, base and higher-output assumptions. Explain each formula and keep assumptions separate from actual results. Identify the constraints that a staffing allocation model would need. For a Solver model Define an objective cell, decision cells and explicit limits. A shift allocation might minimize a modeled cost subject to coverage needs, capacity limits and approved working rules. Use integer or binary constraints when fractional people or assignments are not meaningful. CHECK THE RESULT Check feasibility first. Ask AI to explain the selected Solver method and all constraints. Compare the proposed solution with a manually understood case and identify assumptions that could make it impractical. What-if analysis with Solver AI FOR MICROSOFT EXCEL 19 / 25
Use Python for deeper analysis Example: summarize delays and inspect extreme values When your Excel license and platform support Python in Excel, Python calculations run in Microsoft’s cloud environment. AI can draft analysis code, but it must use the Excel-supported data access method rather than assume unrestricted local files or network access. Small analysis example Create a Deliveries table with Supplier and DaysLate. In a Python cell, the following code reads the table and returns a grouped summary. Check that DaysLate is numeric and that missing values follow your reporting rule. EXCEL / CODE EXAMPLE df = xl("Deliveries[#All]", headers=True) summary = df.groupby("Supplier")["DaysLate"].agg( Records="count", Median="median", Max="max" ) summary COPY-READY PROMPT Draft Python in Excel code to summarize Deliveries by Supplier using count of nonmissing DaysLate, median and maximum delay. Identify missing values separately. Flag extreme delays for investigation without deleting them. Explain every transformation and the denominator of each measure. How to apply it Confirm Python availability, create a Python formula through Excel’s Python workflow, enter the code and choose the appropriate return type. Review the result against a small manual sample. The last expression above returns the summary object. CHECK THE RESULT Count excludes missing DaysLate in this example. Add a separate total-row count if you need all deliveries in the denominator. An extreme value may be an error or a real event, so retain the source record. If Python is unavailable, ask for a PivotTable or worksheet-formula alternative. Advanced analysis does not justify skipping the simpler calculation you can verify. Getting started with Python in Excel Python in Excel availability AI FOR MICROSOFT EXCEL 20 / 25
Draft reliable VBA automation Example: assemble a weekly exceptions report on desktop Excel VBA is suited to desktop Excel workflows. AI can draft a macro, explain an existing macro and help fix an error. Treat generated code as a draft. Specify exactly which workbook and ranges it may write to. COPY-READY PROMPT Write VBA for this workbook only. Read table TimeData and copy rows whose ReviewStatus is not OK into an Exceptions sheet. Include headers and preserve source values. If Exceptions exists, replace only its report table, not unrelated content. Handle a missing source table with a clear message. Do not save, email, delete files, access the internet or change macro security. Explain the code and provide tests. Testing sequence 1. Save a separate macro-enabled working copy when you need to retain VBA. 2. Review workbook, worksheet and range references. Avoid ambiguous ActiveWorkbook assumptions. 3. Run on a small sample with a known number of exceptions. 4. Run again to confirm it does not duplicate the report. 5. Test a missing table, an empty table and an error halfway through. 6. Verify that source values, formulas and formatting remain intact. A useful design requirement Ask the macro to restore application settings it changes, including events or calculation mode, even when it fails. Request a clear result message showing rows read and rows written. CHECK THE RESULT Do not enable unfamiliar macros simply because AI generated them. Follow the organization’s code review and macro policy. VBA does not run in Excel for the web. Office Scripts and VBA differences AI FOR MICROSOFT EXCEL 21 / 25
Automate with Office Scripts and Power Automate Example: update formulas in a named table Office Scripts can automate Excel actions in supported environments and can be called from Power Automate. AI can draft TypeScript code or refine a recorded script. Availability and flow permissions depend on your account and organization settings. EXCEL / CODE EXAMPLE function main(workbook: ExcelScript.Workbook) { const table = workbook.getTable("Invoices"); if (!table) throw new Error("Invoices table missing"); if (table.getRowCount() === 0) return; table.getColumnByName("Difference") .getRangeBetweenHeaderAndTotal() .setFormula("=[@Amount]-[@ExpectedAmount]"); } Prerequisite: Invoices already has Amount, ExpectedAmount and Difference columns. This example writes only the Difference data column. It is an instructional snippet that must be tested in your Excel environment. COPY-READY PROMPT Draft an Office Script that reads Invoices, verifies required columns and updates only Difference with Amount minus ExpectedAmount. Return the number of processed rows and a list of missing columns. Include empty-table handling. Explain what is overwritten. Do not modify the source amount columns. For a scheduled workflow Use a separate automation copy first. Define the trigger, exact workbook and table, access rights, success log and failure notification. Decide how to prevent overlapping runs and duplicate processing. A workbook script and a scheduled flow are different parts of the solution. CHECK THE RESULT Do not assume a scheduled script refreshes every external connection exactly like desktop Excel. Test the full flow, including the data arrival and refresh steps, before relying on its output. Office Scripts and VBA differences ExcelScript Table API AI FOR MICROSOFT EXCEL 22 / 25
HR and payroll: create an exception review Find records to investigate without making employee decisions AI can help connect time exports, employee records and approved rule tables. The goal is an evidence-based review list. Use synthetic examples to develop the logic and approved environments for actual employee data. Input Example fields Purpose TimeData EmployeeID, WeekEnd, PayCode, Hours Recorded time EmployeeMaster EmployeeID, Status, Department Authorized reference data PayCodeRules PayCode, Allowed, ReviewThreshold Approved checking rules A repeatable review 1. Check missing and duplicate employee keys. 2. Validate pay codes against the approved rule table. 3. Flag negative hours, unexplained duplicate entries and unmatched employees. 4. Join department information using a unique key. 5. Create an exceptions sheet with source record IDs and reasons. 6. Have the responsible team resolve exceptions before using the result operationally. COPY-READY PROMPT Design a payroll data-quality review for TimeData, EmployeeMaster and PayCodeRules. Use only the approved thresholds provided. Flag missing IDs, duplicate business keys, invalid pay codes, negative hours and records without a unique employee match. Keep original records. Return issue counts, affected hours and source record IDs. Do not infer misconduct or calculate legal pay entitlements. CHECK THE RESULT An unusual record is a review item, not proof of an employee error. Check timing differences, corrections and legitimate split entries. Retain the reviewer’s resolution and avoid using a generated explanation as a personnel fact. Acceptance test: seed one known instance of each issue in a synthetic sample. Confirm that each appears once in the intended category and that valid records remain unflagged. Structured references in Excel tables Merge queries in Power Query AI FOR MICROSOFT EXCEL 23 / 25
Month-end: build a repeatable close review Connect supporting schedules without losing the original evidence A month-end workbook often contains exports, supporting schedules and formula-heavy summaries. AI can help design a reconciliation process and explain differences. Your organization’s approved accounting treatment remains the rule source. Illustrative close workflow 1. Retain source exports with reporting dates and versions. 2. Confirm account keys and reporting cutoff. 3. Reconcile supporting schedules to the ledger by the approved account mapping. 4. Flag unmapped accounts, duplicated mappings, missing records and differences. 5. Review formula patterns for hardcoded values or inconsistent references. 6. Produce an exceptions report with owner, evidence and resolution status. Control Expected evidence Completeness Input file list, row counts and control totals Mapping quality Unmapped keys and duplicate mappings Reconciliation Support total, ledger total and explained difference Review completion Owner, decision, date and supporting record COPY-READY PROMPT Create a month-end review plan for Ledger and SupportSchedules using the approved AccountMapping table. Identify duplicate or missing mappings before joining. Summarize differences by account and retain supporting source IDs. Explain formulas used and flag assumptions. Do not create adjusting entries or invent explanations for differences. CHECK THE RESULT A zero overall difference can hide offsetting errors. Inspect individual account differences and both unmatched sides. Treat generated commentary as a draft for the reviewer to confirm. First pilot Choose one reconciliation with known rules. Measure preparation time, review time and issues missed. Expand only after the method handles the known exceptions reliably. Merge queries in Power Query XLOOKUP function AI FOR MICROSOFT EXCEL 24 / 25
Your first task and reference library A reviewable result is the goal Before you rely on an AI-assisted workbook Confirm the input version and business rules. Recalculate or refresh the workbook. Match source and output counts and totals. Test normal, blank, duplicate and boundary cases. Trace exceptions to original records. Save the approved formulas or script and the prompt version. Assign an owner for the next refresh. A reusable final-review prompt COPY-READY PROMPT Review this method against the stated rules. Identify unsupported assumptions, changes to source data, missing cases and version-dependent functions. Propose tests with expected results. Separate verified results from suggestions. Do not declare the workbook correct solely because it contains no visible errors. Microsoft documentation Copilot in Excel: current workflows Copilot licensing and availability Retired COPILOT worksheet function Excel functions by category Structured references in Excel tables Combining files from a folder Merge queries in Power Query Creating a PivotTable FORECAST.ETS function What-if analysis with Solver Getting started with Python in Excel Office Scripts and VBA differences Every technical page includes clickable source links for its topic. Documentation and feature availability can change. Verified October 3, 2026 (U.S. Central time). Original prompts, workflows and fictional examples in this guide illustrate methods; they do not guarantee a specific AI response. CHECK THE RESULT Pick one difficult recurring task. Specify its inputs and rules, ask AI for the method, and use the checks above to decide whether the result is ready for your workflow. AI FOR MICROSOFT EXCEL 25 / 25
Flipbook IQ Make your own flipbook. Turn a PDF, Word file or PowerPoint into a flipbook that opens from one link. Free to start. Scan, or go to flipbookiq.com/app Beautiful Flipbooks. Bigger Impact.