Using AI to Build Spreadsheets and Write Formulas

The formula you would have spent an hour on

A bookkeeper at a 25-person wholesaler needs a column that shows, for each customer, the total of invoices more than 60 days overdue, excluding credit notes, as of the last day of the previous month. She knows it needs SUMIFS and some date logic. Historically that meant 40 minutes of trial, error and forum threads.

Described properly to an AI assistant, it takes two minutes to get a working formula and three more to test it. This is one of the most consistently useful everyday AI tasks there is, because formulas are small, testable and have right answers. The catch is in the word testable.

How to describe what you want

The model cannot see your sheet unless the tool is built into your spreadsheet software, and even then it benefits from precise instructions. Include:

• Which software: Excel (and roughly which version), Google Sheets or something else. Functions differ.
• The column letters and what each contains, including data types. 'Column C is invoice date, stored as real dates, not text.'
• Three to five example rows, anonymised if needed
• Exactly what result you expect for those rows
• Edge cases: blanks, zeros, negative numbers, text in number columns

Example: In Excel 365, column A is customer name, B is invoice date, C is amount, D is type ('Invoice' or 'Credit'). In F2, for the customer in E2, sum amounts where type is Invoice and the date is more than 60 days before the last day of last month. For the rows below, Brightwell Ltd should total 4,350.

The expected answer is the most important part. It turns the formula from something that looks plausible into something you can verify.

Explaining the spreadsheet you inherited

Many businesses run on a workbook built years ago by someone who has since left. Nested IFs nine levels deep, VLOOKUPs pointing at hidden sheets, a macro nobody dares touch. AI assistants are excellent at explaining these.

1. Paste the formula and ask for a plain-English explanation, step by step
2. Ask what inputs it depends on and what would break it
3. Ask whether there is a simpler modern equivalent, such as XLOOKUP, FILTER or LET
4. Test the rewrite side by side with the original across the whole column before replacing anything

The same works for VBA macros and Google Apps Script. Asking for an explanation of what a macro does before running it is a sensible habit in its own right.

Where AI spreadsheet help goes quietly wrong

Formula errors that produce #VALUE! are harmless, because you notice. The dangerous ones return a number.

Test on at least a handful of rows you have worked out by hand, including one awkward one. It takes minutes and it is the difference between a useful tool and a finance error discovered at year end.

Building whole spreadsheets and models

Beyond single formulas, AI can propose a structure for a cash flow forecast, a stock reorder calculator or a commission tracker: which sheets, which columns, which assumptions belong on their own tab. It is a useful sounding board and quick at producing a starter template.

Be more careful with models that drive decisions. Ask the assistant to separate inputs, calculations and outputs, to label every assumption, and to explain each calculation. Then have someone who understands the business logic review it. A pricing model that is 5 per cent wrong in a way nobody spots is worse than no model.

Built-in assistants in Excel and Google Sheets can now act on your data directly, which is convenient. The same checks apply, and it is worth knowing what data the assistant can see.

When the answer is not a better formula

Sometimes the reason formulas keep getting harder is that the spreadsheet is doing a database's job: several people editing, thousands of rows, lookups across many tabs, data copied between files by hand. AI will happily write increasingly clever formulas for a workbook that should have become a system two years ago.

• More than two or three people edit it regularly and overwrite each other
• Someone spends hours each week copying data in from other systems
• Mistakes have reached customers or the accounts
• Only one person understands it

If several of those ring true, read signs your business has outgrown spreadsheets. At SpiderHunts we often start by keeping the spreadsheet as the front end and automating the data flowing into it, then move to a proper custom system only when the evidence says so. Plenty of businesses never need to.

Frequently asked questions