AI Pair Programming for Spreadsheets: A Practical Guide

AI pair programming for spreadsheet work means one thing: you describe what you need, your AI tool writes the formula. You check it, adjust the range references, paste it in. Done. The idea comes from software development -- two coders, one keyboard, one writes while the other reviews -- but it applies directly to Excel and Sheets. This guide covers which tools actually handle formulas well, what prompting structure works, and where each one trips up.

The pair programming idea, applied to your formulas

Traditional pair programming puts a driver and a navigator at the same keyboard. One writes, the other checks in real time. The claim is that code quality goes up and bugs go down because two sets of eyes catch what one misses.

When you open a chat with ChatGPT and describe a spreadsheet problem, you are the navigator. The AI is the driver. It writes the formula based on what you told it; you review, test, and decide whether to use it.

According to Stack Overflow's 2025 developer survey, 84% of developers now use AI tools regularly. The mental model is exactly the same for you writing an INDEX-MATCH or a nested SUMIFS.

The difference is that developer tools are built for code. Spreadsheet formulas have their own syntax, their own quirks (named ranges, relative vs. absolute references, Excel 365 vs. Excel 2016), and their own failure modes. Not every AI pair programmer handles those equally well.

Three tools that write formulas alongside you

There are essentially three categories here.

Generalist coding assistants (GitHub Copilot, Cursor, Continue.dev): built for developers, support dozens of languages including Python and JavaScript, but can also produce Excel and Sheets formulas if you ask correctly.

General-purpose chat AI (ChatGPT, Claude): no IDE integration, but strong at explaining context and iterating on formula logic through conversation.

Spreadsheet-specific AI (Formula Dog): built for exactly this use case. Describe your problem in plain language, get a formula -- no prompt engineering required.

The choice between categories depends on your starting point. If you are already using Copilot for other work, use it for formulas too -- the overhead is zero. If you spend most of your day in a chat interface, ChatGPT or Claude will feel natural. If formulas are your specific bottleneck and you want the fastest turnaround without any setup, a spreadsheet-dedicated tool removes the translation layer entirely.

One thing that holds for all three: the AI does not see your spreadsheet. You are always describing it in words. The clearer that description, the better the result.

Each category works differently, costs differently, and fails differently. Here is how they break down in practice.

GitHub Copilot for formulas: what it does and what it misses

GitHub Copilot helps developers complete tasks about 55% faster (GitHub internal study, 2024). That number applies to code. For spreadsheet formulas, the picture is more nuanced.

Copilot works best when you are already in an environment it understands -- VS Code, for instance, writing a Python script that outputs data to Excel. If you ask it to help with a formula in a text file or comment, it can produce:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found", 0)

That is correct. It handles XLOOKUP well, understands the exact-match flag (0), and includes the missing-value fallback. Good boy.

Where it struggles: Excel 2016 compatibility. Copilot defaults to the newest functions (XLOOKUP, FILTER, LET, LAMBDA). If your company is still on Excel 2016, you need to ask explicitly: "Write this for Excel 2016 with no dynamic array functions." It will give you the INDEX-MATCH equivalent -- but only if you specify.

The other gap: it does not know your data structure. You have to tell it exactly how your sheet is laid out, which column contains what, and whether ranges are named or letter-referenced.

For teams already paying for GitHub Copilot Business or Enterprise, there is no additional cost to use it for formula work. The per-seat pricing ($19-39 per user per month) is already baked in. For individual users, the $10/month individual plan covers the same formula generation capability.

ChatGPT as your formula pair programmer

ChatGPT handles formulas well in a conversational context. The real advantage is iteration: you can say "that formula returned #N/A on rows where the lookup value is empty -- fix it" and it knows exactly what you mean from the previous message.

A typical exchange:

You: I have a column of sales dates in column A and sales amounts in column B. I want to sum all sales from the last 30 days.

ChatGPT:

=SUMIFS(B:B, A:A, ">="&TODAY()-30, A:A, "<="&TODAY())

Replace B:B with your actual range if you want to avoid including the header.

That is what you want: formula first, brief note after. No lecture.

The main friction: you have to copy-paste between the chat window and your spreadsheet. There is no live sync. If your formula needs five rounds of iteration, that is five copy-paste cycles.

The prompt structure that gets the right formula faster

Bad prompt: "Formula to look up a value in Excel"

Good prompt: "I have sales data in Excel 365. Column A has product IDs (text), column B has prices (numbers). I want to look up the price for a product ID I type in cell E1. If not found, show 0."

The difference is context. AI pair programmers work with what you give them. The more precise your description of the data structure, the closer the first output will be to what you actually need.

A useful template:

Tool: [Excel 365 / Excel 2016 / Google Sheets]
What column A contains: [describe]
What column B contains: [describe]
What I want to calculate: [describe]
Edge case to handle: [blank cells / errors / version limits]

Fill that in, paste it once, and the formula you get back is usually production-ready -- or close enough that one correction finishes it.

Formula Dog: built for the spreadsheet job

Formula Dog was designed specifically for Excel and Sheets formula generation. You describe a formula problem in plain English, it outputs the formula. No IDE, no system prompt, no version flag required -- it handles the Excel/Sheets distinction automatically.

The interface skips the back-and-forth. If you need an INDEX-MATCH that handles duplicates, or a SUMPRODUCT with multiple criteria across non-contiguous ranges, it handles that without you having to explain what SUMPRODUCT is.

The limitation: less iterative than a chat interface. If the formula needs refinement, you re-describe and re-generate rather than continuing a thread.

That said, for the specific use case -- "I need the formula, I know what I want, I do not want to explain it to a generalist AI three times" -- it is the fastest path from problem to clipboard.

When your AI returns a formula that almost works

Research shows 66% of developers say they spend more time fixing AI-generated code than expected. The formula equivalent is a formula that runs without an error but returns the wrong answer.

Three situations where this shows up:

Relative vs. absolute references. AI tools often return =VLOOKUP(A2, B:C, 2, 0) when you will be copying that formula down a column. That is fine for A2 -- it adjusts automatically. But if the lookup range should stay fixed (say, it references a table on a separate sheet), you needed $B:$C. The formula runs, gives wrong results after row 2, and you spend ten minutes figuring out why.

Check: after pasting, copy the formula to the last row of your data and verify the result. If the lookup range shifted, add the $ signs.

Version mismatch. Copilot or ChatGPT returns a FILTER or UNIQUE formula; you are on Excel 2016. It will show #NAME? because those functions do not exist there. Fix: always specify your Excel version in the prompt. Better yet, ask for a fallback: "Also show me the pre-365 equivalent."

Off-by-one in date ranges. SUMIFS date logic is finicky. ">="&TODAY()-30 includes today; ">"&TODAY()-30 does not. AI tools usually get this right on the first pass, but verify the boundary condition on a known date before trusting the result in a report.

The setup that actually saves time on formula-heavy work

Pair programming, human or AI, works best when the reviewer has clear criteria for accepting or rejecting the suggestion. For formulas, that means:

1. Test on a small slice. Run the formula on 5-10 rows before applying it to the full dataset.

2. Verify edge cases. What happens when the lookup value is blank? When the date is in the future? When the value is zero vs. empty?

3. Check version compatibility. If the spreadsheet gets shared, does the recipient have the same Excel version?

The AI handles the syntax. You handle the logic check. That split -- syntax to the machine, logic to you -- is what makes the pair model work.

For routine formula work (a SUMIFS, a COUNTIF, a basic INDEX-MATCH), an AI pair programmer shaves a few minutes off each task. That adds up across a week. For complex formulas -- multi-criteria SUMPRODUCT, dynamic named ranges, LAMBDA functions -- it is the difference between spending 45 minutes debugging a formula someone wrote five years ago and getting a clean version in 90 seconds.

The best way to start: pick one type of formula you reach for at least twice a week and route it through an AI tool for the next five days. Compare the time. If it is faster and the output is reliable, expand from there. If the overhead of describing the problem cancels out the time saved, try a different tool or a more specific prompt template. The pair model only works if both parties pull their weight.

Biscuit has already fetched the formula. The range references are yours to check.