Articles

How do you ask AI for an Excel formula that is right the first time?

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 →

The five parts

The brief for one formula.
PartWhat to sayExample
LayoutSheet, column of each field, first and last row, what one row isSheet Data, rows 5 to 24, one row per lease; property in C, rent in G (EUR thousands)
TaskWhat to compute, in wordsTotal rent of one property
OutputWhere the formula goes, how it is copiedSummary!D6, copied right; the property name is in D4
Edge casesBlanks, zeros, missing or repeated keysVacant units have a rent of 0
EnvironmentExcel version, argument separatorExcel 2019, commas; bounded ranges, no whole columns

The same request, twice

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 result on a worked case

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.

Brackenridge portfolio (fictional), 31 December 2026.
LineAlder HouseBirch CourtCedar YardDunmore ParkPortfolio
Lettable area (sqm)3,5503,9003,45015,20026,100
Passing rent (EUR k)991.0946.0716.01,142.53,795.5
Number of units545620

Three things people forget to say

Describe, do not paste

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.

Then test it

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.

Sources

  1. Julian R. Sterling, AI in Excel for Analysts (video course, 2026): running case Brackenridge portfolio
  2. Microsoft Support, function reference for SUMIFS, SUMPRODUCT, INDEX, MATCH and IFERROR
  3. ICAEW, Twenty principles for good spreadsheet practice; R. Panko, What We Know About Spreadsheet Errors

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.

Formulas you can prove, step by step

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).

Watch module 1 free →

Questions readers ask

What should a prompt for an Excel formula contain?

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.

Why does a formula from an AI assistant fail when pasted into Excel?

Often because it uses a function your version does not have, or commas where your regional settings expect semicolons. State both in the request.

Should I paste my data into the assistant?

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.

Read the whole case

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

Also on this site

All 2164 articles →