Using ChatGPT to Write Excel Formulas for Farm Spreadsheets

Most farm spreadsheets break in the same place. The columns are fine and the numbers are typed in faithfully every week, but the one cell that should add it all up is either missing or holds a formula someone built in 2017 that nobody dares touch. ChatGPT is very good at fixing that cell. You describe what you want in plain English, and it hands back a formula you can paste in.

The skill to learn isn’t Excel. You need to describe your sheet clearly enough that the answer fits it, and then check the answer before you trust it with a feed budget or a stock return. This guide covers both, using three examples from real farm jobs.

What it costs and what you need

The free version of ChatGPT handles everything in this article. ChatGPT Plus costs about £20 a month. It’s quicker and lets you upload a whole spreadsheet, but you don’t need it for formulas. You also need Excel. Any version from Excel 2016 onwards will do, though the version matters more than you’d think (more on that below). Google Sheets works too, as long as you tell ChatGPT that’s what you’re using.

Find your version before you start. In Excel, go to File > Account > About Excel. If it says Microsoft 365, you have the newer functions such as XLOOKUP and FILTER. If it says 2016 or 2019, you don’t, and a formula built around them will just show #NAME?.

The prompt that gets a working formula first time

Vague questions get generic formulas. “How do I work out my feed budget in Excel” gets you a lecture. What works is describing the sheet the way you’d describe it to a relief worker over the phone: the sheet name, which column holds what, which row the data starts on, and what answer you want in which cell.

Here’s a template worth saving in your phone notes:

I'm using Excel [365 / 2019 / 2016] on a UK farm.
Sheet name: [name]
Column A = [what], Column B = [what], Column C = [what]
Headings are in row 1, data starts in row 2 and runs to about row [n].
I want cell [ref] to show [the answer, in what units].
Here are three made-up example rows: [paste or type them]
Give me the formula, then explain each part in plain English.

Use made-up example rows. ChatGPT doesn’t need real customer names, CPH numbers or bank details to build a formula, and those shouldn’t go into a chatbot anyway. The pillar guide on ChatGPT and AI for farm admin covers what is and isn’t sensible to paste in.

Example 1: a winter feed budget for a suckler and youngstock unit

Say a beef farm has a sheet called Feed set up like this:

There’s also a Stocks sheet listing each clamp or bale stack, with its fresh weight in tonnes in column B and its dry matter as a decimal in column C. The pit is 620 t fresh at 32% DM. There are 180 haylage bales at roughly 0.65 t each (117 t fresh) at 40% DM.

The prompt was the template above, ending: “I want cell G1 to show the total tonnes of dry matter needed for the winter including 10% wastage, where the wastage percentage is in H1. I want G2 to show tonnes of DM in stock from the Stocks sheet, and G3 to show the surplus or shortfall.”

ChatGPT returned:

G1: =SUMPRODUCT(B2:B4,C2:C4,D2:D4)/1000*(1+H1)
G2: =SUMPRODUCT(Stocks!B2:B3,Stocks!C2:C3)
G3: =G2-G1

SUMPRODUCT multiplies head × days × kg along each row and adds the rows up. Dividing by 1,000 turns kilos into tonnes. The results:

That’s a small shortfall. It’s about the difference between tightening up at the feed face and buying a load of straw and blend in February. Before believing it, check the dry cows by hand: 80 × 150 × 11 = 132,000 kg, which is 132 t. The other two groups give 54 t and 40.8 t, so 226.8 t in total, and 226.8 × 1.1 = 249.48. It matches.

A follow-up question is where this starts to save real time. “Add a cell that converts the shortfall into fresh tonnes of bought-in silage at 28% DM” gives =-G3/0.28, which works out to about 15.3 t fresh. Ask for one change at a time, though. Piling three changes into one question is how formulas go wrong.

Example 2: input costs per hectare on an arable farm

Arable spreadsheets usually have a long log of everything applied. Here the Inputs sheet has the field name in column B, the category (Seed, Fert, Spray) in column D and the line cost in pounds in column H. A separate Fields sheet has field names in column A and hectares in column B.

You want a summary grid with fields down the side and categories across the top, showing £/ha. The prompt said so, and it also said this was Excel 2019. That detail changed the answer:

=SUMIFS(Inputs!$H:$H,Inputs!$B:$B,$A2,Inputs!$D:$D,B$1)/INDEX(Fields!$B:$B,MATCH($A2,Fields!$A:$A,0))

In Excel 365 you’d likely get XLOOKUP in place of INDEX/MATCH. They give the same answer, but XLOOKUP won’t run on 2019. Note the dollar signs, too. $A2 locks the column so the field name stays put as you drag right, and B$1 locks the row so the category heading stays put as you drag down. If you aren’t sure why a dollar sign is where it is, ask. ChatGPT explains this part well.

To check one cell, take Top Barn, 24.6 ha of winter wheat. Its fertiliser lines in the log are 6.0 t of 34.5% AN at £330/t (£1,980) and 2.4 t of an NS product at £360/t (£864). That’s £2,844 in total, and £2,844 ÷ 24.6 = £115.61/ha. If the grid shows £115.61, the formula is reading the right rows.

Example 3: a sheep stock reconciliation

The annual sheep and goat inventory in England is counted at 1 December, and your flock register has to agree with what’s physically in the fields. A movements log makes this easy if a formula does the adding. Column B of the Movements sheet holds the movement type (Birth, Purchase, Sale, Death) and column C holds the number of head. The opening count, 612, is in cell F1.

=F1+SUMIFS(C:C,B:B,"Birth")+SUMIFS(C:C,B:B,"Purchase")-SUMIFS(C:C,B:B,"Sale")-SUMIFS(C:C,B:B,"Death")

With 880 lambs tagged, 40 bought in, 520 sold and 31 deaths, the book figure comes out at 981. The physical count was 978. A second formula flags the gap:

=IF(F3-F2=0,"Agrees","Check: "&F3-F2&" head")

That shows “Check: -3 head”. Three missing sheep can be a real gap or a typing error, and a typing error is the likelier culprit. SUMIFS needs an exact match, so a cell containing “Sale ” with a trailing space, or “Sold”, gets silently ignored. To find strays, ask ChatGPT for a formula that counts entries in column B that aren’t one of the four words. It’ll give you something like:

=COUNTA(B2:B500)-SUMPRODUCT(COUNTIF(B2:B500,{"Birth","Purchase","Sale","Death"}))

If that shows anything other than zero, you’ve found your missing sheep, or at least the cell hiding them. A drop-down list (Data > Data Validation > List) stops it happening again.

How to check the answer every time

ChatGPT writes formulas that look confident and are occasionally wrong. Usually it’s pointing at the wrong column, because your description was slightly off or it guessed. These five checks catch nearly all of it:

1. Work one row out on a calculator. You saw this in every example above. If one row matches, the logic is right. If it doesn’t, the formula is reading something you didn’t intend.
2. Make it explain itself. Paste the formula back and ask “which cells does this read, and what does each part do?” If the explanation mentions column E and your costs are in H, you’ve found the fault.
3. Use Evaluate Formula. Excel has it under Formulas > Evaluate Formula. It steps through the calculation one piece at a time and shows the value at each stage, so you can watch exactly where a number goes wrong.
4. Break it on purpose. Put in a blank cell, a zero, and a word where a number should be. A feed budget that crashes with #VALUE! because one group has no figure yet is better found now than at the January review.
5. Reconcile the totals. In the input cost grid, £/ha × hectares summed across all fields should equal the plain SUM of column H on the Inputs sheet. If they differ, a field name is spelt two ways somewhere.

Units deserve a special mention. ChatGPT can’t know whether your silage tonnage is fresh or dry matter, or whether your DM column holds 32 or 0.32. Say so in the prompt. Getting it wrong doesn’t throw an error, it just makes the answer a hundred times too big, and on a feed budget that can look almost believable.

When it keeps getting it wrong

If two attempts have failed, stop rewording the question and change what you give it. Copy the actual headings and five rows of dummy data straight from the sheet and paste them in. ChatGPT reads pasted table text well. Then tell it exactly what went wrong, for example: “This returns #N/A in row 7, where the field name is Long Meadow.” Specific errors get specific fixes.

Sometimes the problem is regional. A formula from an American source may use US date order, so 03/04 means 4 March rather than 3 April. Some European settings also use semicolons where UK Excel uses commas. Putting “UK” in the prompt prevents most of that.

Next time, pick the formula in your own farm spreadsheet that you trust least. Describe what it should do using the template, and see whether ChatGPT’s version gives the same answer as yours. If they disagree, one of them is wrong, and checking a row by hand will tell you which.