Use AI to Fill an Excel Model Without Breaking Formulas

To use AI to fill an existing Excel model without breaking formulas, have the agent write numbers only into a named list of input cells, in a copy of your master workbook, and then prove nothing else moved: compare every formula before and after, and check that the outputs you trust (NOI, DSCR, cash-on-cash, IRR) recalculate to the right numbers. The AI does the reading. Your model keeps doing the math.

That sounds obvious. Many AI spreadsheet tools do something else. They generate a new spreadsheet, write formulas for you, or rebuild your template in their own format. If you have a master model your IC has signed off on, none of that is what you asked for.

Why doesn't pasting into ChatGPT or an Excel add-in just work?#

There are three common approaches, and each fails in a different place.

Chat gives you numbers to retype. You upload a T-12, ask for the operating expenses, and get a tidy table in the chat window. Then you copy 40 numbers into Excel by hand. The AI part took a minute, and the retyping is where the transposed digit gets in.

Formula bots write formulas. Tools like Better Analyst (formerly Formula Bot) take an uploaded .xlsx or CSV, generate formulas from plain English, and can create a new spreadsheet from a prompt. That is useful for analysis. It does not help when the formulas are the part you already trust and want left alone.

Template generators replace the template. Some tools offer to build the model for you. For a new analyst that is fine. For a firm whose model has ten years of conventions in it, a generated model is a second model to audit.

The job people actually have is narrower: read these documents, put the numbers in my cells, don't touch anything else.

What does "without breaking formulas" actually mean?#

It means four specific things. Check each one, because each fails on its own.

1. Input cells only. The agent writes to a fixed list of cells or ranges, such as Inputs!C6:C30 and RentRoll!B8:H31, and nowhere else. A formula cell is never a destination, even when its value looks wrong.
2. Links stay links. Some "input-looking" cells are really links, like =Assumptions!C4. Overwriting one with a pasted value breaks the chain silently, and the model looks fine until the assumption changes. A cell counts as an input only if it currently holds a constant.
3. Every formula string is identical before and after. Not the same result: the same text. This is the check that catches a range write that clipped one formula at the edge of a block.
4. Cached values are recalculated. An .xlsx stores each formula and the last value Excel computed for it. A library that writes inputs does not recalculate. Until Excel does, anything that reads cached values (a preview, a PDF export, another script) shows the old deal's outputs next to the new deal's inputs.

Step 1: Put the source files and the master workbook in one folder#

Make a folder for the deal and put everything in it as it arrived: the T-12 PDF, the rent roll (PDF, scan, or a forwarded email), and a copy of your master workbook. Keep the real master somewhere else.

Point the agent at that folder. A desktop agent like Claude Code or Codex, running on your Mac, reads the PDFs and the workbook straight off the disk. Nothing goes through a web upload form (what the agent reads is still sent to the model provider as part of each turn), and the file it edits is a real file you can open in Excel afterward.

Name the output file before you start, for example Maple-Court_UW_2026-09.xlsx. The master copy is never edited in place.

Step 2: Tell it your rules once#

Extraction errors are rarely about reading. They are about judgment the PDF does not contain. Write the rules down in the first message and keep them for next time:

• Vacancy: "Use 8% economic vacancy on gross potential rent, not the T-12's actual vacancy."
• Management fee: "Replace the seller's management fee with 4% of effective gross income."
• Exclusions: "Move one-time items (roof, parking lot, legal settlements) out of opex and list them separately."
• Reserves: "Add $250 per unit per year."
• Cell map: "T-12 opex lines go to Inputs!C12:C24 in this order. Unit mix goes to RentRoll!B8:H31. Write nothing outside these ranges."
• Stop conditions: "If the rent roll has more units than the template has rows, stop and tell me. Do not insert rows."

The last rule matters more than it looks. Templates sum fixed ranges like SUM(H8:H31). Insert a 25th unit below row 31 with a script and the total ignores it. The model still calculates, just wrong. Row changes are for a person in Excel, where references adjust.

Step 3: Watch the extraction and correct misread lines#

Ask the agent to show its mapping before it writes: each PDF line, the page it came from, the amount, and the destination cell. A table like this takes seconds to scan:

This is where you catch the classic misreads: a subtotal read as a line item, a trailing-month column read instead of the annual total, a credit shown in parentheses read as positive. Say "that's the December column, use the total," and have it redo the table. It only writes once you agree.

Step 4: Check that the outputs tie#

Run two checks, in this order.

Regression check first. Before touching the new deal, have the agent write the previous deal's inputs into a fresh copy of the template. Open it in Excel, force a full recalculation (Ctrl+Alt+F9 on Windows, or Formulas > Calculate Now on a Mac), and compare the outputs to the version you saved last time. Cash-on-cash, IRR, MOIC and DSCR should match to the cent. If they don't, the write process is changing something, and you've found out on a deal whose answers you already knew.

Then the new deal. After the agent writes the new inputs:

• Ask for a diff of every cell whose value changed. Every one should be inside your approved ranges.
• Ask for a count and comparison of formula strings before and after. The answer should be "identical."
• Recalculate in Excel, save, and hand-check two outputs you can compute on a napkin, like NOI and DSCR.

Gotchas: #NAME?, merged headers and lost styles#

#NAME? on XLOOKUP, IFS and friends. In the file format, Excel stores newer functions with a prefix: _xlfn.XLOOKUP, _xlfn.IFS, _xlfn.MAXIFS, _xlfn.TEXTJOIN, _xlfn.LET, and _xlfn._xlws.FILTER. Microsoft's own file-format spec lists them this way. A tool that writes these functions without the prefix produces a file where the formula shows #NAME? until someone retypes it. You'll also see _xlfn. appear literally when an older Excel opens a newer model. The simplest protection is the rule from Step 2: the AI writes inputs, never formulas.

Saving a formula-less copy by accident. If a script opens the workbook with openpyxl's data_only=True, it reads "the value stored the last time Excel read the sheet" instead of the formula (that's openpyxl's own documentation). Save that workbook, and those cells now hold numbers instead of formulas. Any script that reads values and writes the file must open it twice, or not at all.

Shapes, charts and extensions. openpyxl's docs say shapes "will be lost from existing files if they are opened and saved." Check your charts, images and form controls in the output file too. When openpyxl warns that a data validation extension "is not supported and will be removed", take it literally and check your dropdowns.

Merged headers. In a merged range only the top-left cell holds a value. A write aimed at any other cell in the merge either errors or disappears. Put the cell map on unmerged cells.

Styles through the wrong library. SheetJS lists styling as a Pro feature, so a file saved through its free Community Edition can lose fills, fonts and borders. Your blue-input and black-formula colour coding is how a reviewer audits the model, so check it survived.

Manual calculation mode. If the master was saved with calculation set to Manual, nothing recalculates on open. The outputs will look like they tie because they're still the old numbers.

Worked example: a T-12 into a multifamily underwriting model#

A hypothetical 24-unit property. The folder holds the T-12 PDF, a rent roll PDF, and a copy of the firm's underwriting template. The rules are the ones from Step 2.

The agent writes the GPR inputs, the vacancy rate, other income and each opex line into the template's input cells. The template computes EGI, NOI and everything downstream itself.

Tie-out: with annual debt service of $165,000 in the loan tab, DSCR should read 1.33. With $1,200,000 of equity, Y1 cash-on-cash should read 4.50%. If the model's own cells show those numbers after recalculation, and the formula comparison says nothing changed, the fill is good. IRR is the model's to compute, which is exactly why the regression check on last deal's inputs comes first.

Which tool should you use: Copilot in Excel, Shortcut, or a desktop agent?#

As of September 2026:

When Copilot is the better choice: your inputs are already in Excel, you're on Microsoft 365, and you want the edit inside the file you have open. That is fewer moving parts than any separate tool.

When Shortcut is the better choice: you want a finance-specific agent that lives in Excel as an add-in and your firm is comfortable with credit-based pricing.

Where Universe fits#

Universe is a Mac app that runs agents like Claude Code and Codex on your own computer, through the Claude or ChatGPT plan you already pay for. It fits this job when the hard part is the pile: a T-12 PDF, a scanned rent roll, a forwarded broker email, and a master workbook, all in one folder.

What it does here, specifically:

• The agent works in the folders you give it, reading the source documents and writing the output file next to them. Every step shows in a session you can read back later.
• Your rules (vacancy, fees, exclusions, the cell map) live in the session. You can save them as a skill so every agent that does this job applies them the same way.
• Files the agent makes are kept with earlier versions on your Mac, so a bad fill doesn't cost you the good one before it.
• The same job can be scheduled for when the month's T-12 lands. Schedules run on a Mac that's switched on.
• Universe charges for the software and never resells model usage. See pricing.

What it isn't: a spreadsheet app. An .xlsx opens read-only in Universe's panel, and the edit is made to a copy. You open that copy in Excel to recalculate, review and save. For the rent roll side of this job in more detail, see putting a T-12 and rent roll into the model you already use. The real estate and IB page is at /for/real-estate-ib.

If that's the job on your desk, download Universe. It runs on Apple silicon or Intel Macs with macOS 13 or later.