Why bother building a shared expense spreadsheet when dozens of bill-splitting apps already exist? Apps hike their subscription rates, lock basic export tools behind paywalls, and force everyone in your house to register yet another account. A spreadsheet sidesteps the whole mess. Your group controls the ledger directly. You log what someone spent, mark who owes a cut, and let cell formulas run the numbers. Nobody has to argue over totals when rent comes due.
Structuring the Workbook
Two tabs are all you need. One tab logs individual receipts. The other tab shows running balances.
Jamming raw receipts and summary math onto a single sheet turns messy fast, especially when you log dozens of expenses a month.
Setting Up the Transactions Table
Give your first tab clean headers so your formulas stay reliable.
| Date | Description | Payer | Category | Total Amount | Alex Share | Jordan Share | Taylor Share | Receipt Link |
|---|---|---|---|---|---|---|---|---|
| 2026-06-01 | Groceries | Alex | Food | $120.00 | $40.00 | $40.00 | $40.00 | Receipt |
| 2026-06-03 | Internet | Jordan | Utilities | $75.00 | $25.00 | $25.00 | $25.00 | Receipt |
| 2026-06-05 | Dinner Out | Taylor | Food | $90.00 | $45.00 | $45.00 | $0.00 | Receipt |
Assigning each person their own column makes uneven splits trivial to manage. Nobody gets confused.
Converting to an Official Excel Table
Select your headers and data rows, then press Ctrl+T on Windows or Command+T on Mac. Check the box for "My table has headers" and click OK. Head to Table Design on the ribbon and rename Table1 to Expenses.
Thing is, loose cell ranges like B2:B50 snap the minute someone inserts a new purchase row at the bottom. A proper Excel Table expands on its own. It also swaps fragile cell coordinates for clear labels, which Microsoft's structured reference documentation notes will keep formulas from breaking when data moves. Reading Expenses[Total Amount] beats tracing grid letters every time.
Formulas for Logging Individual Splits
Your split math depends on how your group handles shared purchases. Equal splits need a straightforward division. If Alex, Jordan, and Taylor split an expense three ways, put this formula in Alex's share column:
=[@[Total Amount]] / 3
The @ symbol grabs the value strictly from that active row. Drag or copy that formula into the columns for Jordan and Taylor.
Uneven splits take manual numbers or percentages. If Taylor skips a group meal, drop zero in Taylor's column and divide the remaining total between Alex and Jordan. For rent weighted toward a larger bedroom, multiply the total directly:
=ROUND([@[Total Amount]] * 0.60, 2)
Always wrap percentages in ROUND. Skipping it lets hidden fractions of a cent skew your final settlement figures. It saves headaches later.
Calculating Balances on the Summary Tab
Open your second worksheet and label it Balances. Create four columns across the top: Person, Total Paid, Total Share, and Net Balance. List each person in column A. Make sure the spelling matches your transaction log, or the lookup will fail.
To calculate what Alex paid up front for everyone, put this formula in B2:
=SUMIFS(Expenses[Total Amount], Expenses[Payer], A2)
SUMIFS searches the Payer column, finds every purchase Alex covered, and adds the total. Next, sum Alex's personal share across all logged lines in cell C2:
=SUM(Expenses[Alex Share])
Cell D2 subtracts what Alex owes from what Alex paid:
=B2 - C2
A positive result means the group owes that person cash. A negative balance means that person needs to pay out.
Turns out you do not need complex nested logic to reconcile group expenses. I used to build enormous IF and VLOOKUP chains that tried to match who owed whom down to the individual penny, but it only slowed the workbook down and confused roommates who just wanted a single settlement number. Net balances keep things simple.
Collaboration and Formula Protection
A shared workbook only helps if everyone can open it without overwriting formulas. Storing the file on OneDrive or SharePoint enables live coauthoring for Microsoft 365 users. In fact, Microsoft's guide on coauthoring in Excel notes that collaborators can update the same workbook simultaneously across desktop and web versions.
People make typing errors when multiple editors share a single sheet. Lock your formulas in three quick steps:
- Select your input cells (Date, Description, Payer, Total Amount, and individual shares), hit Ctrl+1, click the Protection tab, and uncheck "Locked".
- Go to the Review tab and choose "Protect Sheet".
- Add an optional password and select OK. Your data cells remain open for edits while the underlying math stays secure.
Recordkeeping and Monthly Settlement
To be honest, the spreadsheet math rarely causes problems. Shared trackers stall when nobody agrees on habits.
A clear monthly routine prevents missing receipts:
- Enter purchases within 48 hours. Paste a link to your cloud receipt folder right in the row.
- Check balances on a set schedule. The first of each month fits roommates, while the last day of travel suits trips.
- Pay out through standard payment apps. Anyone carrying a negative balance sends funds directly to someone in the green until all rows hit zero.
- Archive the period. Copy your clean tab for the upcoming month, or clear out the settled rows so old transactions stop cluttering your view.
Format your two tabs today and convert the transaction grid into an Excel Table. Enter your last three receipts to test the balance math. Once those figures line up, share the link.