An assistant cannot see your sheet. Everything you leave out, it guesses. Five lines remove the guesses.
To get a correct Excel formula from an AI assistant, describe five things: the layout of the data, the task, where the formula goes and how it is copied, the edge cases, and your version of Excel. The assistant cannot see your sheet and does not test its answer, so a vague request returns a formula that runs and may be wrong. A brief of five lines returns one you can test and keep.
The case comes from the AI in Excel for Analysts course by Julian R. Sterling, author of The Real Estate Asset Manager.Get the DRM-free PDF, $19.99 →
| Part | What to say | Example |
|---|---|---|
| Layout | Sheet, column of each field, first and last row, what one row is | Sheet Data, rows 5 to 24, one row per lease; property in C, rent in G (EUR thousands) |
| Task | What to compute, in words | Total rent of one property |
| Output | Where the formula goes, how it is copied | Summary!D6, copied right; the property name is in D4 |
| Edge cases | Blanks, zeros, missing or repeated keys | Vacant units have a rent of 0 |
| Environment | Excel version, argument separator | Excel 2019, commas; bounded ranges, no whole columns |
Vague: "Give me a formula for total rent by building." A typical answer: =SUMIFS(G:G,C:C,"Alder House"). It works in one cell. It sums anything typed below the table, and the name must be retyped in each column.
Briefed: with the five lines above, the answer is =SUMIFS(Data!$G$5:$G$24,Data!$C$5:$C$24,D$4). The ranges are bounded and locked, the criterion is the header cell, and the dollar before the 4 lets the formula be copied to the right.
The Brackenridge portfolio, fictional, has 20 units in four properties. One formula, copied across, gives the first lines of a summary; the portfolio column is computed directly from the data, so that it can be checked against the four properties.
| Line | Alder House | Birch Court | Cedar Yard | Dunmore Park | Portfolio |
|---|---|---|---|---|---|
| Lettable area (sqm) | 3,550 | 3,900 | 3,450 | 15,200 | 26,100 |
| Passing rent (EUR k) | 991.0 | 946.0 | 716.0 | 1,142.5 | 3,795.5 |
| Number of units | 5 | 4 | 5 | 6 | 20 |
A formula depends on the shape of the data, not on its content. Headers, the layout and two or three invented rows are enough. The real rent roll, with tenant names and rents, does not need to leave your file.
A brief makes the right formula likely. It does not prove it. Apply the formula to four invented rows whose answer you computed by hand, and keep the test in the workbook.
Every figure is computed by script from the course's running case, the Brackenridge portfolio, a fictional rent roll of 20 units, and checked on 19 November 2026. Function availability depends on your version of Excel. For education, not investment advice.
26 short video lessons on using an AI assistant in Excel without being wrong: the five-part brief, SUMIFS, SUMPRODUCT, INDEX and MATCH, a mini sample with a known answer, checks that tie and the audit of a received model. You build a checked summary from a rent roll, with step files, exercises, a prompt kit and a certificate exam ($149).
The layout of the data (sheet, columns, first and last row, what a row is), the task, where the formula goes and how it is copied, the edge cases, and your Excel version and argument separator.
Often because it uses a function your version does not have, or commas where your regional settings expect semicolons. State both in the request.
It is not needed for a formula. Describe the layout and give two or three invented rows; ask your firm before sharing real data with any tool.
This article is one calculation from The Real Estate Asset Manager. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
See the course →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Prefer a PDF? The same book is sold as a DRM-free PDF edition, searchable and printable. Get the PDF edition, $19.99