AI inside spreadsheets: five practical cases, formulas and video lessons
Use Copilot in Excel and Gemini in Google Sheets for margins, receivables, duplicate-payment review, feedback and budgets—with worked examples, copyable prompts and recorded videos.
Imagine asking your spreadsheet: “Find the overdue invoices, calculate their ageing and prepare a collection summary.” AI can help translate that request into formulas, tables and a draft explanation. The strongest workflow combines plain-language instructions with spreadsheet calculations you can inspect.
The five numerical and text examples below use illustrative data you can reproduce. They are not claims about a particular client or tests performed inside a signed-in spreadsheet account. A separate Google Workspace customer video documents a real business use case. Videos are recorded demonstrations, not live streams.
1. Choose the AI that works inside your spreadsheet
| Route | How to use it | Check first |
|---|---|---|
| Excel: Copilot pane | Open Copilot; choose chat or planning before allowing workbook edits. | Eligible licence, supported app build, admin/privacy settings and Automatic calculation for editing. |
| Google Sheets: Gemini panel | Open Ask Gemini and identify the sheet or range you want to work on. | Eligible Workspace or Google AI plan; convert an .xlsx file to Google Sheets when needed. |
| Google Sheets: AI function | Use =AI(prompt, range) for text generation or classification. | Feature access and generation limits; it is not an arithmetic engine. |
Feature names and access vary by plan and rollout. Use the official links at the end if your screen differs. Preparing a clean rectangle with unique headers and one record per row makes your instructions easier to interpret. In Excel, a table is a useful starting point; in either app, keep totals and commentary outside the source records.
Microsoft says the preview COPILOT() worksheet function stopped being available on 14 September 2026. Old cached results can remain, but recalculation can produce #NAME?. Use the Copilot pane for those tasks. Google Sheets’ AI() is a different feature. Read Microsoft’s current notice ↗
- Duplicate the workbook and confirm you are allowed to process the data with the selected tool.
- Fix headers, dates and numeric formats. Give each record a stable identifier.
- Ask for a plan, specify the exact range and identify fields that must stay unchanged.
- Review the proposed formulas or edits, then test a few known answers.
- Reconcile totals and keep the reviewed version with an explanation of material changes.
2. Five worked cases you can try
Amounts below are in rupees. Formulas use English function names and commas; your regional settings may require semicolons. AI-generated narratives are drafts, while the displayed expected numeric results are calculated from the sample inputs.
Find the sales segment that needs attention
Paste these headers in A1:E1: Segment, Revenue, Cost, Profit, Margin. Enter the first three columns; let AI help create the last two.
| Segment | Revenue | Cost | Expected profit | Expected margin |
|---|---|---|---|---|
| Retail | 3,00,000 | 2,10,000 | 90,000 | 30% |
| Wholesale | 2,50,000 | 2,00,000 | 50,000 | 20% |
| Online | 1,50,000 | 1,05,000 | 45,000 | 30% |
| Total | 7,00,000 | 5,15,000 | 1,85,000 | 26.43% |
In A1:E4, add Profit in D as Revenue minus Cost and Margin in E as Profit divided by Revenue. Return a blank for a zero-revenue row and flag its undefined margin for review. Format E as a percentage. Do not change A:C. Explain the lowest-margin segment using only this data; do not invent reasons.
D2: =B2-C2
E2: =IF(B2=0,"",D2/B2)
Total margin: =SUM(D2:D4)/SUM(B2:B4)What to check: Wholesale has the lowest margin at 20%. Overall margin is 26.43%, not the simple average of the three percentages. A zero-revenue row has an undefined margin; do not report it as 0%. A conclusion such as “discounting caused the problem” would need additional evidence.
Build an ageing view without guessing dates
Use A = Invoice, B = Due date, C = Outstanding, D = Days overdue, E = Bucket. Put the real date 19 October 2026 in H1 as a fixed reference date.
| Invoice | Due date | Outstanding | Expected days overdue | Expected bucket |
|---|---|---|---|---|
| INV-101 | 29 Sep 2026 | 50,000 | 20 | 1–30 |
| INV-102 | 15 Aug 2026 | 75,000 | 65 | 61–90 |
| INV-103 | 25 Oct 2026 | 25,000 | 0 | Current |
For A1:E4, calculate days overdue using the fixed reference date in H1. Do not use TODAY(). A zero outstanding amount is Settled; a negative amount is Credit balance and needs separate review. Otherwise use Current, 1-30, 31-60, 61-90 and Over 90 buckets. Flag blank or invalid due dates separately instead of treating them as old invoices. Leave source fields unchanged.
D2: =IF(C2<=0,0,IF(ISNUMBER(B2),MAX(0,$H$1-B2),"Review date"))
E2: =IF(C2<0,"Credit balance",IF(C2=0,"Settled",IF(NOT(ISNUMBER(D2)),"Review date",IF(D2=0,"Current",IF(D2<=30,"1-30",IF(D2<=60,"31-60",IF(D2<=90,"61-90","Over 90")))))))Expected review: Total outstanding is ₹1,50,000; ₹1,25,000 is overdue. Ask AI to prepare a collection-priority note, then confirm dispute status and receipts before contacting customers. These are internal collection buckets, not statutory classifications.
Flag possible duplicate payments
Put Supplier, Invoice reference, Amount and Payment date in A:D. Two rows reading “ABC Supplies / INV-042 / 12,500 / 5 Oct 2026” should trigger a review. A third row with INV-043 is a different record.
Add a review flag in E. Flag rows where supplier, invoice reference, amount and payment date all repeat. Keep every source row. Do not delete or reverse anything. Explain that matching records are possible duplicates, not proof of an erroneous payment.
=IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2,$D$2:$D$100,D2)>1,"Review duplicate","Unique")What to check: Both matching rows are flagged; the different invoice is not. Standardise supplier names and references in helper columns if necessary. Part-payments, instalments and repeated invoice numbering can be legitimate, while a duplicate with a different date will escape this exact-match test. Inspect bank evidence before action.
Turn customer feedback into reviewable categories
Put feedback text in A2:A20. In B2, enter the AI function below, fill down, select the relevant cells and use Generate and Insert when prompted. Give the range explicitly; the function does not see the whole workbook automatically.
=AI("Using only this feedback, return exactly one category: Delivery, Quality, Service, or Other. If unclear, return Other. Return only the label.",A2)| Illustrative feedback | Sample expected label |
|---|---|
| The parcel arrived three days late. | Delivery |
| The product stopped working after one day. | Quality |
| The support executive solved my issue quickly. | Service |
These labels are illustrative expectations, not guaranteed AI outputs. Review a sample, including comments with mixed issues. Count approved categories in a separate cell with =COUNTIF(B2:B20,"Delivery"). Keep AI text generation separate from deterministic calculations, and refresh AI results when the underlying data changes.
Explain a variance after the spreadsheet calculates it
Use Category in A, Budget in B, Actual in C, Variance in D and Variance % in E. For travel, a budget of ₹20,000 and actual cost of ₹24,000 gives a ₹4,000 adverse expense variance, or 20%.
D2: =C2-B2
E2: =IF(B2=0,"N/A",D2/B2)Calculate Actual minus Budget in D and variance divided by Budget in E; return N/A if Budget is zero. For these expense rows, positive variance means over budget. Then draft a short commentary using only the calculated figures. Identify missing explanations and ask questions rather than inventing causes.
Personal use: repeat this for groceries, travel and subscriptions. Business use: compare actual expenses against the approved budget. Do not apply the same favourable/adverse interpretation blindly to revenue rows, where a positive variance can be favourable.
3. Watch the workflows in action
Recorded videos from Microsoft 365 and Google Workspace complement the worked cases. Their interfaces may reflect older versions; follow the current setup notes above. Loading a player connects to YouTube, and availability depends on the publisher, region and network.
Use this for a visual introduction to formula creation and analysis. The source description places formula creation at 00:23. Apply the margin exercise above to your own sample data; disregard any older feature or licensing guidance that conflicts with current documentation.
Watch on YouTube if the player is unavailable ↗Watch how a selected formula can be explained. Then ask Copilot to walk through the ageing formula, identify edge cases and predict the three expected results before you accept changes.
Watch on YouTube if the player is unavailable ↗A named business example: Google Workspace’s video describes Savoursmiths using Gemini in Sheets to analyse data and surface trends for business decisions. It illustrates the workflow; it does not establish a quantified saving or prove results for another business.
Watch on YouTube if the player is unavailable ↗Watch Google Workspace’s Gemini in Sheets overview ↗. For current AI-function syntax, use Google’s live help page below.
4. A good prompt has a verification step
Work only on [sheet/range]. My objective is [result]. Source columns mean [definitions]. Preserve [fields]. First explain your plan. Use spreadsheet formulas for calculations; show the formula and assumptions. Flag missing or invalid inputs. Reconcile totals to the source, test the listed edge cases and ask before deleting data or changing existing formulas.
Before accepting the output, compare source row counts and totals, inspect formulas at the first and last records, and test zero values, blanks, negative amounts and invalid dates. Check that a chart includes the full intended range. Review generated categories and commentary separately from the numeric calculation. Preserve an original version and record who reviewed the result.
If the feature is missing or an answer looks wrong
- No Copilot or Gemini control: check the plan, app version, account and administrator settings using the official documentation.
- AI function unavailable: check Google’s eligibility, language/admin settings and generation limits; third-party add-ons may use different function names.
- Wrong totals: inspect text-formatted numbers, filtered rows, excluded records, duplicates and calculation settings.
- Invented explanations: ask for a facts-only summary and a separate list of questions requiring human investigation.
AI can help prepare a workbook, but a reviewer remains responsible for financial conclusions. Do not paste client identifiers, bank details or other confidential information into unapproved tools. Use authorised data and current organisational policies.