The grant tracking spreadsheet that does not break

Most organisations track grants in a spreadsheet, and most of those spreadsheets stop working at around the same point — not because spreadsheets are the wrong tool, but because of one structural choice made on day one that is painful to undo later.

This is that choice, the four things a working sheet needs, and a free workbook that is built the right way round. No signup, no email.

One row per obligation, not one row per grant

The instinct is one row per grant: funder, amount, start date, end date, report due date. It reads well and it survives about four grants.

It breaks because a grant is not one deadline. A single award routinely carries an interim financial report, a final financial report, one or more performance reports, a budget-revision window, sometimes a match or cost-share certification, and a closeout package — five to fifteen separate things you owe, each with its own date and its own owner.

Model that one-row-per-grant and you end up widening the sheet: a column for each report, then a column for each date, then colour-coding to say which ones are done. By grant eight the sheet is thirty columns wide, and the question everyone actually asks — “what is due in March?” — has no answer, because the dates are spread across columns rather than stacked in one.

One row per obligation inverts it. Each row is a single thing owed, tagged with which grant it belongs to. “What is due in March” becomes a filter. So does “what does Maria own”, and “what is overdue”, and “what does this funder expect next”. The sheet stops growing sideways and starts growing downward, which is what spreadsheets are good at.

Four things a working sheet needs

A warning ladder, not a due date. A date that turns red on the day it is due has told you too late. Funder reports usually need review and sign-off before submission, so the useful alarm is 30 days out, with 14 and 7 as escalations. Drive it off TODAY() so it maintains itself.

An owner on every row. An obligation without a named person is not tracked, it is merely recorded. This is the single most common gap, and it is why things are missed in organisations that had the date written down all along.

Status that suppresses noise. Once something is submitted it must stop glowing. A sheet that cries wolf on completed items trains people to ignore the colour, which removes the only thing the colour was for.

Budget tracked by line, not in total. An award can be 70% spent overall and exactly on track, while its equipment line is at 98%. Grant budgets are enforced by line, so that is the level worth watching.

A workbook built this way, free

Rather than describe it, here is one. It has a Grants sheet, an Obligations sheet built one row per obligation, a Budget sheet with a per-line overspend flag, and a dashboard that answers “what is due this month”. The formulas people usually get stuck on are documented on their own sheet, in plain English, so you can change them rather than work around them.

Download the grant deadline tracker (.xlsx, 82 KB)

It is seeded with example rows so the structure is obvious; delete them and start typing. It works in Excel, LibreOffice and Google Sheets — the core sheets use only TODAY, IF, INDEX/MATCH and COUNTIF, which have worked since Excel 2010. Only the dashboard’s two list views need Excel 2021 or newer, and they degrade to a readable note rather than an error.

Free to use, copy, modify and share. No attribution required, no email required.

Where a spreadsheet genuinely stops

It would be convenient for us to claim the spreadsheet fails early. It does not. For a small portfolio, a well-shaped sheet is the right answer, and a tool bought too early is just an expense with a login.

There are three honest limits, and they are all structural rather than cosmetic.

A spreadsheet cannot chase you. Every formula in it recalculates when somebody opens the file. If the person who would have opened it is the person who forgot, the conditional formatting never fires. This is the real mechanism behind most missed reporting deadlines, and no amount of formula work fixes it — the reminder has to leave the file.

It has no audit trail. A cell that changed does not record who changed it or when. That is tolerable until an auditor asks how a budget figure moved, at which point the honest answer is that nobody knows.

It decays with staff turnover. Sheets encode their author’s assumptions. When that person leaves, the next person inherits something they are afraid to change, which is how organisations end up with a compliance function nobody fully understands.

If none of those three is biting yet, use the workbook and spend the money elsewhere. If two of them are, the sheet is no longer the cheap option — it is just the one nobody has costed.

This guide is general information about how grant compliance work is commonly organised, not legal, audit or accounting advice. Grant rules change, and terms vary by funder and award — always check your own award documents and the current regulation, and talk to your auditor about your specific circumstances.

This guide is general information about how grant compliance work is commonly organised, not legal, audit or accounting advice. Grant rules change, and terms vary by funder and award — always check your own award documents and the current regulation, and talk to your auditor about your specific circumstances.