Formulas, pivots and VBA
Excel AI assistant: get the formula, then prove it in your workbook
ZeroTwo sits next to Excel, not inside it. Paste a sample, get a formula, test it on rows where you already know the answer, then copy it into your sheet. Microsoft 365 Copilot is the in-Excel option, and the matrix below shows when each one fits.
- prompts on this page, 8 of them with expected results to check
- 12
- functions used here that need a newer Excel (see the version notes)
- 3
- cells ZeroTwo writes to in your workbook: you paste in, you copy out
- 0
Which kind of Excel assistant fits your data?
Two questions decide it: how sensitive the data is, and how much of the live workbook the AI needs to touch. Find your cell. The two cells where ZeroTwo fits are marked.
- A1Sensitivity Low / Access Low
Paste a sample into ZeroTwo
Public or made-up data and a one-formula question. Paste a few rows, get the formula, run the test row, copy it in.
- B1Sensitivity Low / Access High
An assistant that runs inside Excel
The AI has to see many sheets or write into cells. ZeroTwo cannot do either, so use an in-Excel tool such as Microsoft 365 Copilot.
- A2Sensitivity High / Access Low
ZeroTwo with an anonymized sample
Replace names, IDs and amounts with placeholders that keep the structure, get the formula, then apply it to the real data locally.
- B2Sensitivity High / Access High
Whatever your organization has approved
Ask the data owner or IT before any of this data goes into an AI tool. This page cannot judge your policy, and no assistant is a safe default here.
The test sheet: eight formulas with answers you can check
Each row is a prompt you might send, a reference formula, a small input and the result you should see. The expected values were worked out by hand from the inputs. We have not run these in Excel for this page, so run each one in a scratch sheet before you trust it. They are also the checks to apply to whatever an assistant gives you for the same task.
Sample data for the pivot test
| Row | A: Date | B: Region | C: Amount |
|---|---|---|---|
| 2 | 2026-01-15 | East | 100 |
| 3 | 2026-02-10 | West | 50 |
| 4 | 2026-03-20 | East | 25 |
| 5 | 2026-04-05 | East | 40 |
| 6 | 2026-05-12 | West | 60 |
| 7 | 2026-06-30 | West | 10 |
| Task | Formula to test | Test input | Expected result | Edge case to try | Version |
|---|---|---|---|---|---|
| Domain from an email | =TEXTAFTER(A2,"@") | A2 holds jane@acme.io | acme.io | A blank cell or an address with no @ returns #N/A. That is a loud failure; wrap in IFERROR only if blanks are expected. | Excel for Microsoft 365 and Excel 2024. Older Excel: =MID(A2,FIND("@",A2)+1,255). |
| Total by region and quarter | =PIVOTBY(B2:B7,"Q"&ROUNDUP(MONTH(A2:A7)/3,0),C2:C7,SUM) | The six-row sample above | East: 125 in Q1, 40 in Q2. West: 50 in Q1, 70 in Q2. Grand total 285. The result may also show total rows and columns. | Dates stored as text make MONTH return #VALUE!. Excel has no QUARTER function, so a formula that calls one is invented. | Microsoft 365 only. Elsewhere, build a PivotTable by hand. |
| A SUMIFS that returns 0 | =SUMIFS(C:C,A:A,"East*",B:B,">"&D2) | A holds Eastern, B holds 120, C holds 40, D2 holds 100 | With "East" the result is 0 because the criterion is an exact match. With "East*" it is 40. | Numbers stored as text are skipped. Compare COUNT and COUNTA over the data rows (without the header): a gap means text numbers. | All versions. |
| Clean text pasted from the web | =TRIM(CLEAN(SUBSTITUTE(B2,UNICHAR(160)," "))) | B2 holds Acme, a non-breaking space, two spaces, then Corp | Acme Corp | Zero-width and other Unicode spaces survive. Compare LEN(B2) with what you can see. CHAR follows the system character set; UNICHAR uses Unicode. | All current versions. |
| XLOOKUP in place of VLOOKUP | =XLOOKUP(D2,Pricing!A:A,Pricing!C:C,"NOT FOUND",0) | Key 1002 appears twice in Pricing!A:A; key 1999 does not appear | 1002 returns column C of its first matching row. 1999 returns NOT FOUND, where VLOOKUP returns #N/A. | A key stored as text ("1002") will not match the number 1002 in either function. A blank default hides missing keys, so use a visible marker. | Check Microsoft Support for XLOOKUP in your Excel version. |
| Six-month linear forecast | =FORECAST.LINEAR(SEQUENCE(6,1,13,1),B2:B13,A2:A13) | A2:A13 holds 1 to 12; B2:B13 holds 100, 110, 120 and so on up to 210 | 220, 230, 240, 250, 260, 270 | If column A holds dates, the x-values must be dates too. Mixing month numbers with dates gives a plausible but wrong series. | SEQUENCE is a dynamic-array function; check Microsoft Support. |
| Order numbers from free text | =REGEXEXTRACT(A2,"ORD-\d{5}\b") | A2 holds Re: ORD-12345 delayed. A3 holds ORD-123456 | A2 returns ORD-12345. A3 returns #N/A. | Without the closing \b the pattern silently returns ORD-12345 for the six-digit number, a wrong answer that looks right. | Excel for Microsoft 365. |
| Top customers by growth | =TAKE(SORT(FILTER(HSTACK(A2:A4,(C2:C4-B2:B4)/B2:B4),B2:B4>0),2,-1),2) | Acme 100 to 150, Beta 200 to 210, Gamma 0 to 50 (name, start, end) | Acme 0.5, then Beta 0.05. Gamma is excluded. | A zero starting value makes the growth #DIV/0!. Decide whether to exclude such rows or report them separately, and check where error rows land after a sort. | FILTER, SORT, TAKE and HSTACK are dynamic-array functions; check Microsoft Support. |
Version notes for TEXTAFTER, PIVOTBY and REGEXEXTRACT are from Microsoft Support, read 5 October 2026: TEXTAFTER, PIVOTBY, REGEXEXTRACT.
When one formula is not the question
If you are asking about a whole dataset rather than a single cell, ask for analyze the whole dataset with checked calculations instead, and use this page for the formula work inside the workbook.
If the data lives in a database and not a sheet, the database analysis page covers schema and query work.
Try it yourself
Paste the sample rows into a chat, send one task from the table in plain English, and compare the answer with the expected column.
Four more prompts, and how to check each one
These do not reduce to one expected cell, so the check is a procedure rather than a number.
- VBA
Prompt
Write a VBA macro that, on Sheet1, highlights every row in the used range where column D is less than column E.
How to check
Work on a copy saved as .xlsm. Run it on five rows where you know which qualify and compare with =SUMPRODUCT(--(D2:D6<E2:E6)). The header row and blank cells are the usual surprises, and macros change the sheet.
- Formatting
Prompt
Conditional formatting rule that highlights duplicates in column A, ignoring case.
How to check
COUNTIF already compares text without regard to case, so =COUNTIF($A:$A,A1)>1 is enough. Put Acme and ACME in the sheet and confirm both highlight. COUNTIF also treats * and ? as wildcards.
- Charts
Prompt
I have monthly revenue for 2025 and 2026. Which chart type shows the year-on-year change, and what table should I build it from?
How to check
Build the chart from the table it lists, then read the axes: months in calendar order, both years on one scale, and no truncated axis that exaggerates the gap.
- Modeling
Prompt
From base MRR in B1, monthly churn in B2 and new MRR in B3, project 12 months under best, base and worst cases. State every assumption.
How to check
Rebuild months 1 to 3 by hand in a scratch column and compare. If the formula compounds churn but adds new MRR flat, it should say so. If it cannot state its assumptions, do not use the result.
When a formula counts as accepted
A formula is accepted when it produces the cell values you expected on rows you chose, including the awkward ones. Not when it looks right, and not when two models produce the same text.
- 01
Write the expected results first
Pick three to five rows where you already know the answer, including one awkward one. Write the expected values down before you read the formula.
- 02
Run it on those rows only
Paste the formula into the first row of a scratch column and compare with your expected values. Do not fill down yet.
- 03
Break it on purpose
Try a blank, a number stored as text, an error value and a missing lookup key. Decide whether each result is what you want.
- 04
Check version and locale
Confirm the function exists in your Excel version. Check the list separator (comma or semicolon) and date format in your regional settings, which change how formulas are typed and read.
- 05
Fill down and reconcile
Compare a total from the formula column with one computed another way, and spot-check the last row and any row near a blank.
A second model is still useful as a reviewer: ask it to list the assumptions in the formula and what would break them. Then test. Agreement between two models shows they read the request the same way, not that the formula is right.
Why test at all: what the spreadsheet-error research says
The figures below count whole spreadsheets, not cells. The paper reports cell error rates separately.
- 91%
- of the 54 audited spreadsheets from 1997 onward contained errors
- Whole spreadsheets, from the field audits in the paper.
- 24%
- of 367 spreadsheets across all seven field audits contained errors
- Weighted average. The older audits used methods less likely to catch errors.
- About half
- of errors were caught by one person inspecting cell by cell
- Group inspection caught about 80% in the paper.
Source: Raymond R. Panko, Spreadsheet Errors: What We Know. What We Think We Can Do (EuSpRIG, 2000), arXiv:0802.3457, read in full on 5 October 2026. It studies human-built spreadsheets from before 2000 and does not test AI-written formulas; applying the same cell-by-cell checking to AI output is our inference. An earlier version of this page quoted an 88% figure that we could not find in this paper, so it has been removed.
Microsoft 365 Copilot and ZeroTwo, side by side
They are different shapes of tool. ZeroTwo publishes this page, so the left column is limited to what Microsoft states on its own page. Many people will want both: Copilot where the AI has to work in the sheet, ZeroTwo for quick formula help and model choice.
| Question | Microsoft 365 Copilot in Excel | ZeroTwo (external workspace) |
|---|---|---|
| Where it runs | Inside Excel, in a chat pane opened from the Home tab. | In a separate chat workspace on web, desktop and mobile. |
| What it can see | The open workbook. Microsoft says to start from a spreadsheet stored on OneDrive or SharePoint. | What you paste or attach. It cannot see your live workbook. |
| Getting it into the sheet | Works in the sheet: Microsoft describes adding columns and formulas, formatting tables and creating charts. | You copy the formula back, then test it. |
| How you get it | Requires signing up for Microsoft 365 Copilot. Check Microsoft for current pricing. | Free plan ($0): 100 free credits to start, +20 bonus credits every day you log in, no card. Pro is $29.99/month. |
| Choice of model | Not described on Microsoft's page. | 60+ models. The Free plan has limited model selection. |
| Checking the output | Microsoft's own tip is to review and adjust the output. | The same applies here. See the test sheet above. |
| Sensitive data | See Microsoft's data, privacy and security page for Copilot. | Anonymize before pasting and follow your organization's policy. See the privacy policy. |
Microsoft column: from Microsoft's AI in Excel page, read 5 October 2026. Check that page for current requirements and pricing. ZeroTwo column: see pricing and the privacy policy.
Excel AI assistants, answered
Is there a free AI for Excel formulas?
Yes, in the sense that ZeroTwo has a Free plan ($0) with 100 free credits to start, 20 bonus credits every day you log in, 1 file upload per message and limited model selection, and no card is required. It writes formulas for you to copy back; it does not run inside Excel. Copilot in Excel requires signing up for Microsoft 365 Copilot.
Does ZeroTwo work inside Excel?
No. ZeroTwo is a separate chat workspace. You paste or attach a sample, get a formula, and copy it into your workbook. If the AI needs to see your live sheets or write into cells, you need an assistant that runs inside Excel, such as Microsoft 365 Copilot.
How do I know an AI-written formula is correct?
Test it. Write expected results for a few rows you know, run the formula on those rows, try a blank, a text-formatted number and an error value, then reconcile a total before filling down. A second model can help you question the assumptions, but two models agreeing is not a calculation test.
Which Excel versions support the functions on this page?
Per Microsoft Support, TEXTAFTER is available in Excel for Microsoft 365 and Excel 2024, PIVOTBY in Microsoft 365, and REGEXEXTRACT in Excel for Microsoft 365. For any other function, check its Microsoft Support page for your version before you rely on it.
Can AI build a pivot table in Excel?
It can write a PIVOTBY formula or list the steps to build a PivotTable. It cannot create the PivotTable in your file. PIVOTBY needs Microsoft 365, so on older versions ask for PivotTable steps instead.
Is it safe to paste company data into an AI tool?
Treat it like any outside service. Replace names, IDs and amounts with placeholders that keep the structure, follow your organization's policy, and ask the data owner if unsure. The privacy policy explains how ZeroTwo handles data.
Related ZeroTwo pages
- Chat with spreadsheets
Ask questions of a sheet you attach.
- AI agent for data analysis
Carry a whole dataset through checked calculations.
- Analyse a database
Schema and query work, when the data is not in a sheet.
- AI document analysis
Read contracts, filings and RFPs with sources attached.
- AI PDF summarizer
The same workspace, applied to PDFs.
- AI for accountants
Workflows for finance teams who live in Excel.