# Team expense tracker for Excel: claims, receipts and who is owed

Canonical URL: https://www.evenoutapp.com/templates/team-expense-tracker-excel
Updated: 2026-10-06

A team expense tracker is a claims log. Each row is one expense a team member paid for, with the amount and a status of Needs receipt, Submitted, Approved or Paid. What the team owes a person is the total of their Approved rows. In Excel, the SUMIFS function adds those up for every person at once.

Key points:
- Log one row per claim with a status of Needs receipt, Submitted, Approved or Paid. What the team owes a person is their Approved total.
- One SUMIFS formula, filled across and down, builds the whole summary when its column headers match the status names exactly.
- A Days open column, from today's date minus the claim date, shows which unpaid claims have waited longest.
- In the example, 7 claims total $208.65: $74.05 paid, $79.20 approved, $18.90 submitted and $36.50 waiting for receipts.

## How to use this template

This tracker is for a team whose members pay for things first and are repaid later: a volunteer crew, a school or youth team, a club committee. It is one claims log and one summary. The formulas below are for Excel, and the same grids work on paper.

1. **Log every claim in its own row** when the person tells you, with the date, who paid, what for and the amount.
2. **Set the status.** Needs receipt means the claim is in but has no receipt. Submitted means it has one and is waiting for a decision. Approved means someone with authority said yes. Paid means the person has been repaid.
3. **Keep claims without a receipt out of Approved.** A claim stays at Needs receipt until the receipt arrives.
4. **Read the summary each week** and repay what is Approved.
5. **Watch Days open.** It shows how long each unpaid claim has been waiting.

## The template

### Grid 1: claims (columns A to E)

| Date | Claimed by | What | Amount | Status |
|---|---|---|---|---|
| | | | | |
| | | | | |
| | | | | |
| | | | | |

### Grid 2: follow-up (columns F to H, same rows)

The Row column only matches each line to its claim in Grid 1. It is not a column in the sheet, so Days open is column F.

| Row | Days open | Approved by | Paid on |
|---|---|---|---|
| 1 | | | |
| 2 | | | |
| 3 | | | |
| 4 | | | |

### Grid 3: summary (columns J to N)

The column headers are the four status names, spelled exactly as they are in the Status cells.

| Person | Approved | Submitted | Needs receipt | Paid |
|---|---|---|---|---|
| | | | | |
| | | | | |
| | | | | |
| Total | | | | |

## The formulas

Put headers in row 1 and the first claim in row 2. These formulas assume no more than 199 claims, which is row 200.

- **Status drop-down.** Select the Status cells, open the Data tab, choose Data Validation, set Allow to List and type `Needs receipt,Submitted,Approved,Paid` in Source, without quote marks. The cells then offer the four statuses in a drop-down, so nobody has to type them.
- **Days open (F2).** `=IF(A2="","",IF(E2="Paid","",TODAY()-A2))`. Copy it down to row 200, and rows with no claim stay empty. Format column F as Number, because Excel may otherwise show the result as a date.
- **Summary (K2).** `=SUMIFS($D$2:$D$200,$B$2:$B$200,$J2,$E$2:$E$200,K$1)`. Put each person's name in column J, copy the formula across to N and down one row for each person, and fill the Total row with a SUM of the cells above it. The ranges must have the same number of rows, and they do.
- **Claims waiting more than 14 days (any cell).** `=COUNTIFS($F$2:$F$200,">14")`.

## A filled example: seven claims

Say a volunteer team pays for supplies and van fuel, and Rob approves claims. The log below is as of March 20, so Days open counts from that date.

| Date | Claimed by | What | Amount | Status |
|---|---|---|---|---|
| Mar 1 | Lena | Gloves and bags | 42.30 | Paid |
| Mar 3 | Marcus | Van fuel | 38.00 | Approved |
| Mar 3 | Lena | Poster printing | 24.50 | Needs receipt |
| Mar 7 | Sofia | Water and snacks | 31.75 | Paid |
| Mar 9 | Marcus | Van fuel | 41.20 | Approved |
| Mar 12 | Tomas | Cable ties and tape | 18.90 | Submitted |
| Mar 14 | Lena | Parking at the drop-off | 12.00 | Needs receipt |

| Row | Days open | Approved by | Paid on |
|---|---|---|---|
| 1 | blank | Rob | Mar 8 |
| 2 | 17 | Rob | blank |
| 3 | 17 | blank | blank |
| 4 | blank | Rob | Mar 15 |
| 5 | 11 | Rob | blank |
| 6 | 8 | blank | blank |
| 7 | 6 | blank | blank |

The summary adds up each person's rows by status.

| Person | Approved | Submitted | Needs receipt | Paid |
|---|---|---|---|---|
| Lena | 0.00 | 0.00 | 36.50 | 42.30 |
| Marcus | 79.20 | 0.00 | 0.00 | 0.00 |
| Sofia | 0.00 | 0.00 | 0.00 | 31.75 |
| Tomas | 0.00 | 18.90 | 0.00 | 0.00 |
| Total | 79.20 | 18.90 | 36.50 | 74.05 |

The four totals add up to 208.65, the same as the Amount column, so no status was mistyped. The team owes Marcus 79.20 now. Lena has 36.50 held until she brings the two receipts. Two claims, Marcus's first fuel claim and Lena's poster printing, have been open 17 days, so the formula for claims waiting more than 14 days returns 2.

## What to agree on

### Who approves

Name the approver, and the amount above which a second person signs off. Write the name in Approved by when the status changes, so a later question has an answer.

### How fast approved claims are repaid

Pick a number of days and write it above the log. The example uses 14, which makes Marcus's first claim 3 days late. A rule that nobody can see is a rule that gets forgotten, so the days-open column is what makes it work.

### The receipt rule

Decide whether a claim can ever be approved without a receipt, and for what amount. If the answer is never, the Needs receipt status does the enforcing. If there is an exception for small amounts, write the amount down.

### One keeper

One person owns the file, so there is a single copy and nobody works on a stale one. Everyone else sends rows to that person.

### Teams that cover for each other

Some teams have no central pot. Four people share the van fuel one person paid, and the question is only who owes whom. That is a splitting job and not a claim. In [EvenOut](https://www.evenoutapp.com/), add the expense, choose who paid and who shared it, and "Balances" shows you what you owe or are owed with each person. With "Only their own" under "Who can edit", people change only what they added or paid for.

Costs that come out of a fixed allocation belong with the [team budget tracker template](https://www.evenoutapp.com/templates/team-budget-tracker-template), which tracks what is committed, spent and left on each line. For one person's trip claim on a single form, see the [travel expense report form](https://www.evenoutapp.com/templates/travel-expense-report-form), and for a treasurer's monthly routine, the [club treasurer duties checklist](https://www.evenoutapp.com/guides/club-treasurer-duties).

## Frequently asked questions

### Why does the summary come out short of the total?

A status was typed a little differently, such as a trailing space or a second spelling, so SUMIFS skipped that row. Add the four status columns and compare them with the total of the Amount column. A drop-down list on the Status cells keeps the spellings the same.

### How do you stop people editing each other's claims?

Nothing in the layout stops someone editing another person's row, so the rule has to be a habit: each person adds only their own claims at the bottom, and the approver changes only the Status and Approved by cells. One person keeps the file and everyone else sends rows to them.

### Can the same sheet track a team trip?

Yes, for claims such as fuel or tolls that are repaid by a team budget. A trip where the members split costs among themselves needs paid and fair-share totals per person instead, which the group trip expenses spreadsheet lays out.

## Sources

- [Microsoft Support: SUMIFS function](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)
- [Microsoft Support: COUNTIFS function](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)
- [Microsoft Support: TODAY function](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)
- [Microsoft Support: IF function](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)
- [Microsoft Support: Create a drop-down list](https://support.microsoft.com/en-us/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b)