Use one row per receipt, store the purchase date as a real date value, and keep each person's share separate from the receipt total. That setup lets you sort a shared ledger, total a date range, and see who is still owed without retyping the same numbers.
It works for roommate utilities, trip meals, groceries, club purchases, and other small-group costs. You can start with a blank sheet or adapt a template.
Start with one transaction table
Keep it boring. One row, one receipt.
A single Transactions tab is usually easier to filter and summarize than separate tabs for January, February, and March. Add a month column or use date filters later if the group needs a monthly view.
| Column | What to enter | Example |
|---|---|---|
| Date | The date the purchase happened | 01/15/2026 |
| Description | A short, recognizable receipt name | Group dinner |
| Category | A controlled label for summaries | Food |
| Amount | The full receipt total | 120.00 |
| Paid By | The person who paid the merchant | Alex |
| Split Method | Equal, custom, usage, or another agreed rule | Equal |
| Participant share columns | One column per person showing that person's share | Alex Share: 30.00 |
| Receipt Link | Optional link to a scan or photo | Drive link |
| Reimbursed? | No, Partial, or Yes | No |
| Notes | Context, exceptions, or a correction note | Airport transfer |
The Amount column should contain the total receipt amount. Do not put Alex's portion there. Put each person's portion in the share columns instead.
A plain Splits cell containing 2/4 can be useful as a note, but it is not enough for dependable settlement math. Separate share amounts are much easier to check.
Make the date column sortable
Sheets can sort reliably only when the entries are actual date values, not text that merely looks like a date.
Select column A, then use Format > Number > Date. If you need a specific display, apply a custom date format such as MM/DD/YYYY. The display can change without changing the underlying date used by formulas.
Date first. Then verify.
If a date was pasted as text, try =DATEVALUE(A2) in a helper column. Format the result as a date, then paste the converted values back into column A. For a known date, =DATE(2026,1,15) avoids ambiguity.
Select the full table before sorting. Use Data > Sort range and sort by the Date column from A to Z. Sorting only column A can separate dates from descriptions, amounts, and payer names. SpreadsheetPoint's guide to sorting dates in Google Sheets shows the same principle with date ranges.
Set up the Google Sheets receipt tracker
Follow these steps in a blank file or a copied template.
- Create a blank Google Sheet, or open SpreadsheetPoint's Google Sheets expense tracker template if you want a starting layout. If you use an external template, choose
File > Make a copyso your group works in its own file. - Rename the main tab
Transactions. - Add the headers from the table above in row 1. Put the date in column A, category in column C, amount in column D, and payer in column E so the example formulas below match.
- Add one share column per participant. For example, columns G through J could be
Alex Share,Jordan Share,Sam Share, andPriya Share. - Format the amount and share columns as currency. Format the date column as a date.
- Add a category dropdown with data validation. A list such as Food, Groceries, Travel, Rent, Utilities, Gifts, and Other keeps summary formulas consistent.
- Enter one real receipt and check that the participant shares add up to the receipt total before sharing the file.
For an equal expense, you can copy this formula across four participant share columns:
=IF($F2="Equal",$D2/COUNTA($G$1:$J$1),"")
Here, D2 is the receipt total, F2 contains the split method, and G1:J1 contains participant names. Replace the range if your group has a different number of people.
For a custom split, replace the formulas with the agreed dollar amounts. A lodging bill might use nights stayed, while a grocery bill might use actual items consumed. The sheet should reflect the rule the group agreed to, not force every receipt into an equal split.
Add a Split Check column if the group uses uneven shares. In row 2, use:
=IF(ABS(SUM(G2:J2)-D2)<0.01,"OK","Check")
The result should be OK for every completed row.
Choose the split method before entering receipts
Different expenses call for different rules. Agree on the method before the first disputed receipt appears.
| Method | Works better when | How to record it |
|---|---|---|
| Equal split | Everyone receives roughly the same benefit | Divide the total equally |
| Usage-based split | People consumed different amounts | Enter each person's actual share |
| Fixed amount | One person's portion is already known | Enter the agreed amount for each person |
| Percentage split | A household or group uses agreed contribution percentages | Record the dollar result for each person |
| Nights stayed | Lodging costs vary by time at the property | Allocate the total by nights or another written rule |
For couples or households, an income-based contribution can work if both people agree to it in advance. It is a household policy, not a default spreadsheet formula.
Whatever method you choose, the participant shares on a row should add up to the Amount. That is the number worth checking.
Useful formulas for dated expense totals
The formulas below assume this layout:
A: DateC: CategoryD: AmountE: Paid ByG:J: Participant shares
Use the same starting and ending row in every range. Mixed ranges are a common reason SUMIFS fails.
Total one category for a date range
This example totals January groceries while including January 1 and excluding February 1:
=SUMIFS($D$2:$D$1000,$A$2:$A$1000,">="&DATE(2026,1,1),$A$2:$A$1000,"<"&DATE(2026,2,1),$C$2:$C$1000,"Groceries")
Using the first day of the next month as an exclusive end date is less error-prone than trying to remember the last day of every month. You can replace the dates and category with cell references for a reusable summary.
For more SUMIFS examples, see SpreadsheetPoint's SUMIFS guide.
Create a category summary
On a Summary tab, this query groups the transaction table by category:
=QUERY(Transactions!A1:D1000,"select C, sum(D) where C is not null group by C label sum(D) 'Total'",1)
It returns one row per category and the total amount recorded for that category. The final 1 tells Sheets that row 1 contains headers.
Compare a budget with actual spending
Create a small budget table with these columns:
| Category | Budget | Actual | Remaining | Percent Used |
|---|---|---|---|---|
| Groceries | 200.00 | formula | formula | formula |
| Utilities | 150.00 | formula | formula | formula |
If the category is in A2 and the budget is in B2, enter these formulas:
C2:=SUMIF(Transactions!$C$2:$C$1000,A2,Transactions!$D$2:$D$1000)D2:=B2-C2E2:=IFERROR(C2/B2,0)
Format column E as a percentage. For a monthly budget, use the date-range SUMIFS formula instead of the all-time SUMIF.
Show who paid and who has a share
On the Summary tab, use a table with Person, Paid, Share Owed, and Net Before Payments.
If the person's name is in A2, the paid formula is:
=SUMIF(Transactions!$E$2:$E$1000,A2,Transactions!$D$2:$D$1000)
For Alex, whose shares are in column G, use:
=SUM(Transactions!$G$2:$G$1000)
Then calculate net:
=B2-C2
A positive result means the group owes that person before repayments. A negative result means that person owes the group.
Keep reimbursements in a separate tab
The purchase ledger records what happened at the store, restaurant, hotel, or utility company. A repayment is a different transaction.
Create a Payments tab with:
| Column | Example |
|---|---|
| Date | 01/20/2026 |
| From | Jordan |
| To | Alex |
| Amount | 30.00 |
| Related receipt or period | January groceries |
| Notes | Bank transfer |
Do not change the original receipt amount when someone pays Alex back. Do not add the repayment to the original purchase either. Recording it separately prevents double-counting and preserves the history.
If D2 on the Summary tab contains a person's net before payments, this formula calculates the adjusted result:
=D2+SUMIF(Payments!$B$2:$B$1000,A2,Payments!$D$2:$D$1000)-SUMIF(Payments!$C$2:$C$1000,A2,Payments!$D$2:$D$1000)
Money sent increases that person's position. Money received reduces it.
Record cash repayments too. The sheet is a group record, so it should show settlements regardless of how the money moved.
Share the sheet without losing control
Use the Share button to add the specific people who need access. Give Editor access to people who enter or correct receipts, and Viewer access to people who only need to review totals.
| Access choice | Practical use |
|---|---|
| Editor | Roommates or trip organizers entering receipts |
| Viewer | Someone checking the totals without changing rows |
| Protected range | Formula cells, headers, and summary areas |
Protecting a formula range can prevent accidental edits. It does not replace careful sharing permissions, so avoid a public link for records that contain personal financial details.
Keep formula cells on the Summary tab and protect them after testing. Leave the transaction-entry cells editable. Version history can help identify an accidental change or restore an earlier version.
Set a simple group routine
Decide when a receipt gets entered. Some groups update after each purchase; others review the sheet weekly. Either can work if everyone follows the same timing.
Write down the split rule, the treatment of missing receipts, and who can approve a correction. A short note in the Notes column is useful, but a shared rule is better.
To be honest, the sheet is only as fair as the entry rule. Keep receipts available, use the same category names, and ask the group to review unusual rows before reimbursement.
Common mistakes to catch early
- Dates stored as text: If sorting or
SUMIFSignores a date, convert it withDATEVALUEor enter it withDATE. - Inconsistent categories:
Food,food, andGroceriesare different criteria. Use a dropdown. - Duplicated totals: When one receipt covers several categories, split the dollar amount across rows instead of repeating the full total.
- Unclear share cells: A note such as
2/4does not tell Sheets who owes what. Enter dollar shares or use a separate Shares tab. - Reimbursements mixed with purchases: Keep payments on their own tab.
- Partial-range sorting: Sort the complete table so each row stays together.
- Formula ranges with different lengths: Keep every
SUMIFandSUMIFSrange aligned from row 2 through the same final row.
Questions that come up
Should I use a separate tab for every month?
Usually, start with one Transactions tab. A single dated table makes annual totals, date filters, and category summaries easier. If your group prefers monthly tabs, keep identical headers and create a separate summary that combines them.
What if one receipt covers multiple categories?
Add one row for each category allocation and include the same receipt reference in a Receipt ID or Notes column. The split amounts across those rows should add up to the original receipt total. Do not repeat the full receipt amount on every row.
What if the group has uneven splits?
Use the participant share columns and enter exact amounts, percentages converted to dollars, or usage-based values. Check that the shares equal the receipt total before marking the row complete.
How often should we update the tracker?
After each purchase is the cleanest option. A weekly review also works for a less active group. Agree on a routine, then use the Date column to spot missing or delayed entries.
Create the Transactions tab, enter one real receipt, and verify the date, category, payer, and share check before inviting the group as editors.