Most grant tracking spreadsheets start the same way. A funder is found, a tab is opened, four columns get typed, and the file does its job for about five months. What ends it is never running out of room. It is the Tuesday somebody discovers that a report was due the previous Friday, and the file says nothing about who was supposed to file it.
That failure is structural rather than a matter of discipline, and knowing which parts of the structure can be fixed inside a spreadsheet and which cannot is what separates a file that runs for years from one that quietly stops being true.
The columns that carry weight
Templates for this circulate widely and most of them track the application and stop. The application is the short part. Here is the set that survives contact with a real grant cycle, grouped by what it is for.
Identifying the opportunity. Funder name, programme name, and a direct link to the guidelines. The link matters more than it sounds, because guidelines move and the version that was read when the decision to apply was made is the one that governs the submission.
The money. Amount requested, amount awarded, and whether the award is restricted to a specific programme or unrestricted. Keeping requested and awarded in separate columns rather than overwriting one is what makes a success rate calculable at the end of the year.
The dates, all of them. Letter of intent deadline, full application deadline, expected decision date, period of performance start and end, and every reporting date the award carries. These are not one column. They are five or more, and the reporting dates are the ones that get omitted from templates and cause the trouble.
The people. Who is writing it, who approves it before submission, and who is responsible for the reports afterwards. One name per column, not a shared cell that says the programme team.
The mechanics. Portal name, the account the submission was made under, and where the submitted documents are stored. Grant portals almost always tie submissions to a single login, and the person who created that login eventually leaves.
The state. A status column with a fixed vocabulary chosen in advance: researching, drafting, submitted, awarded, declined, withdrawn, closed. Free text here defeats every filter built on top of it within a month.
Use two sheets, not one
The single most useful structural change is to stop trying to hold two different objects in one table.
A pipeline row and an award row have almost nothing in common. A pipeline row is about a decision that has not been made yet: is this worth pursuing, who is writing it, when is it due. An award row is about an obligation that already exists: this money arrived, it is restricted to this purpose, these reports are due on these dates, and these records have to be kept.
Combined into one sheet, the columns for one are empty in the rows of the other, filters become awkward, and the awards get sorted out of sight underneath a pile of declined applications. Split into two sheets linked by a shared identifier, each one has a short column set and a single clear purpose. The pipeline sheet gets cleaned out annually. The awards sheet never does, because of what comes next.
The deadlines that are not the application deadline
The reporting calendar is where grant administration actually lives, and it is where spreadsheets most often fall short, because a date in a cell does nothing on its own.
Recipients of United States federal awards are required to retain all federal award records for three years from the date of submission of the final financial report, and where an award is renewed quarterly or annually, three years from the submission of that quarterly or annual report. The retention period extends if litigation, a claim, or an audit begins before it expires, and continues until final action is taken. Records for property and equipment acquired with federal support must be kept for three years after final disposition, and records for program income earned after the period of performance for three years from the end of the fiscal year in which it was earned.
Read that as a design requirement rather than a legal footnote. It means the awards sheet is a record that has to remain accurate and available for years after the programme it funded has ended, and that the documents it points to have to still be there. A tracking file in somebody's personal drive does not meet that requirement, and neither does a link to a folder that was reorganised.
Two practical consequences. Storage location for submitted documents and reports belongs in the awards sheet as an explicit column, pointing at a shared location rather than an individual account. And a retention column, holding the date on which records for that award may be disposed of, is worth calculating once at award close rather than reconstructing under audit pressure.
Where spreadsheets are genuinely fine
It is worth being clear that capacity is never the reason to move.
Google Sheets supports up to twenty million cells or one hundred megabytes for a spreadsheet created in or converted to Sheets. Excel supports 1,048,576 rows by 16,384 columns on a worksheet, with up to 32,767 characters in a single cell. No grant operation reaches any of those numbers. An organisation applying to sixty opportunities a year would take three centuries to fill a Sheets file.
Spreadsheets are also excellent at the things they are excellent at. Sorting by deadline, filtering to one programme officer, summing requested amounts by quarter, and producing a board-ready view in ten minutes are all faster in a spreadsheet than in most purpose-built software. For a single person tracking a modest set of applications, a well-built file with the columns above is the right tool and will stay the right tool.
One conversion detail is worth knowing for teams moving files between formats: when a document is converted from Excel to Google Sheets, any cell containing more than fifty thousand characters is removed. Narrative text pasted into a cell can reach that length, and the removal is silent.
Making the file work harder before replacing it
Most grant tracking spreadsheets are underbuilt rather than outgrown, and five changes take about an hour between them.
Lock the status column to a list. Data validation turning status into a dropdown removes the variants that break every filter: submitted, Submitted, sent, SUBMITTED. This single change is the difference between a status column that can be counted and one that can only be read.
Compute the days remaining rather than storing them. A formula subtracting today's date from the next deadline, with conditional formatting that colours anything inside thirty days, converts a static list into something that reports on itself when opened. Storing a days-remaining number by hand guarantees it is wrong by the following week.
Freeze the header row and protect the formula columns. Both spreadsheet platforms allow specific ranges to be protected while leaving the rest editable. Protecting the computed columns prevents the most common corruption, which is a formula overwritten by a typed value that then never updates again.
One row per award, with reports on their own sheet. The temptation is to add columns for report one, report two, report three. A grant with six reporting dates then needs six sets of columns, and a grant with two leaves four empty. A separate reports sheet, one row per report, linked to the award identifier, sorts by due date across the whole portfolio and answers the only question that matters on a Monday morning: what is due this month.
Put a review in the calendar, not in the file. No spreadsheet notifies anybody. A recurring fifteen minute appointment, owned by a named person, to open the reports sheet and check the next thirty days, is the mechanism that makes the file function. Without it the file is a record of good intentions.
If all five are in place and something still goes wrong, the problem is no longer the template.
Where they break, and why it is not fixable
Three failures, none of which a better template solves.
A row has no owner that survives. A name typed in an owner column is text. Nothing in the file notifies that person, nothing prevents a row being added without one, and nothing records that the name changed. When two staff both believe the other is handling a renewal, the file is equally consistent with both beliefs.
Nothing records who changed what. Version history exists in both major spreadsheet tools and answers the question badly under pressure, because it shows edits to cells rather than a history of the thing being tracked. Reconstructing when a status moved from submitted to awarded, and on whose authority, is an archaeology exercise.
Simultaneous editing degrades the data quietly. Two people sorting, filtering, and adding rows in the same file is where columns drift out of alignment and a paste lands one row off. The damage is invisible at the moment it happens and discovered weeks later, and undo history is finite, with Excel holding one hundred undo levels.
Underneath all three is the same thing: a spreadsheet row is a position in a grid, not a record. It has no identity, no state that belongs to it, and no owner that the system understands. Every workaround built on top is an attempt to give a row properties that rows do not have.
If the applications are coming towards you
The advice above assumes an organisation pursuing grants. A meaningful share of people searching for a grant tracking spreadsheet are on the other side: running a fund, a bursary, a small grants programme, or an open call, and trying to track submissions received.
That situation is different in kind, not degree. Applications arrive from outside, so the fields have to be required at submission rather than typed in afterwards, and a spreadsheet cannot require anything of somebody who is emailing an attachment. Several reviewers score the same application, which needs each submission to carry its own state rather than a shared row. Applicants ask where their application stands, which needs a status that is actually attached to their submission. And the correspondence trail belongs with the application rather than in one staff member's mailbox.
For that shape of work, the submission needs to be a record from the moment it arrives, with an owner, a status, and its files held together. A form tool that keeps every response as a record with its own owner and status is the direct fit, and the use cases page covers the same pattern for open calls and programme applications.
What to change first
Split the existing file into a pipeline sheet and an awards sheet today, and add the reporting dates and the record retention date to the awards sheet, since those are the columns whose absence causes the expensive mistakes. If the file is tracking applications arriving from other people rather than applications being sent out, the spreadsheet is the wrong shape entirely, and the fix is structured intake with an owner and a status on each submission. Halict shows that running end to end.
Q1. What columns should a grant tracking spreadsheet have?
At minimum: funder, programme, a link to the guidelines, amount requested, amount awarded, whether the award is restricted, letter of intent and application deadlines, expected decision date, period of performance, every reporting deadline, the writer, the approver, the person responsible for reports, the portal and account used, where documents are stored, and a status drawn from a fixed list.
Q2. How long do grant records need to be kept?
For United States federal awards, records must be retained for three years from the date the final financial report was submitted, or from each quarterly or annual report for awards renewed on that basis. The period extends if litigation, a claim, or an audit starts before it ends. Property and equipment records run three years after final disposition, and program income earned after the period of performance runs three years from the end of that fiscal year.
Q3. Can a spreadsheet handle a growing grants portfolio?
On capacity, comfortably. Google Sheets allows up to twenty million cells and Excel 1,048,576 rows per worksheet, which no grants operation approaches. The limits that matter are practical: no reliable owner per row, no usable audit trail, and data quality that degrades when several people edit at once.
Q4. Should the pipeline and the awards live in the same file?
They can live in the same file but should not live in the same sheet. Pipeline rows track a decision that has not been made and get cleaned out each year. Award rows track an obligation with reporting dates and retention requirements and stay for years. Keeping them separate, linked by a shared identifier, keeps both column sets short and stops awards being buried under declined applications.
Q5. What is the right tool for tracking grant applications that are received rather than submitted?
A spreadsheet cannot require fields of an outside applicant or give a submission its own reviewer and status, which are the two things that matter when applications arrive from elsewhere. A form with response management handles both: the required fields and files are enforced at submission, and each application becomes a record carrying its own owner, status, and correspondence.