A shared Google Sheet can handle club purchases, event costs, trip spending, reimbursements, and a simple dues log. Use one row per expense, a second tab with one row per participant, and a Summary tab that compares what each person paid with their assigned share.

The math is easier to trust when each tab has one job. A $300 bill paid by one member is not the same record as five $60 obligations. The sheet records the agreement; members still approve expenses and settle payments separately.

Use one ledger, not a pile of monthly tabs

For most small clubs, one Expenses tab is easier to maintain than separate Jan, Feb, and Mar tabs. Filter the Date column for a meeting or month while Summary formulas continue to use the same ranges.

Turns out, a single date filter is often simpler than maintaining several nearly identical worksheets.

Tab What goes there Main purpose
Summary Member names, totals, and balances See who paid, who owes, and who is still due
Expenses One row per purchase or approved charge Keep the source record
Shares One row per participant for each expense Show how each bill was divided
Lists Member names, categories, methods, and statuses Power consistent dropdowns
Dues Optional member contribution records Keep dues separate from expense splits
Settlements Optional completed transfers Record who paid whom after the expense

Receipts can stay in Google Drive and be linked from the Expenses tab. Keep the link access aligned with the people who can view the spreadsheet.

Build the basic Google Sheets structure

Open a blank file in Google Sheets. Then build the workbook in this order.

  1. Create the tabs. Rename Sheet1 to Summary. Add Expenses, Shares, and Lists. Add Dues if the club collects regular contributions, and add Settlements if members will record later transfers.

  2. Add the Expenses headers. Use this row in Expenses:

    Expense ID | Date | Description | Category | Amount | Paid By | Split Method | Status | Receipt Link | Notes

    Give every expense a short ID such as E001 or E002. Enter Amount as a number and apply currency formatting. Keep member names consistent in Paid By.

  3. Create the dropdown lists. In Lists, place categories in column A, split methods in column B, statuses in column C, and member names in column D. A practical starting set is Events, Trips, Supplies, Food, Equipment, Membership fees, and Other. Use these exact split labels: Equal, Percentage, and Fixed.

    Select the matching column in Expenses, open Data > Data validation, and point each rule to its list range. You can also follow these Google Sheets dropdown instructions if the menu looks different in your account.

  4. Build the Shares tab. Add these headers:

    Expense ID | Member | Share % | Fixed Share | Amount Owed | Settlement Status | Note

    Enter the expense ID on every participant row. Do not place Alex, Jordan, and Sam in one cell.

    For an equal split, put this in C2 and fill it down for the participant rows:

    =IFERROR(IF(VLOOKUP($A2,Expenses!$A:$G,7,FALSE)="Equal",1/COUNTIF($A$2:$A,$A2),""),"")

    For a percentage split, type values such as 60% and 40% in column C. For a fixed split, leave column C blank and enter dollar amounts in column D.

    Put this formula in E2 and copy it down:

    =IF(LEN($D2)>0,$D2,IF(LEN($C2)>0,IFERROR(ROUND(VLOOKUP($A2,Expenses!$A:$G,5,FALSE)*$C2,2),""),""))

    The formula uses a fixed amount when one exists. Otherwise, it multiplies the expense amount by the percentage. Each expense's percentages should total 100%, and fixed shares should total the expense amount.

    Small rounding differences need a human decision. Add the extra cent to the final fixed-share row, or adjust one participant's amount after reviewing the total.

  5. Add member balances to Summary. Put member names in A2:A. Add these headers:

    Member | Paid for Expenses | Assigned Share | Gross Position

    Use these formulas in row 2:

    =SUMIF(Expenses!$F:$F,$A2,Expenses!$E:$E)
    =SUMIF(Shares!$B:$B,$A2,Shares!$E:$E)
    =B2-C2

    A positive Gross Position means the member paid more than their assigned share. A negative result means the member owes more than they have covered.

    To total a category, put its exact name in J2 and use:

    =SUMIF(Expenses!$D:$D,J2,Expenses!$E:$E)

  6. Add a dues tab when needed. Use this header row:

    Member | Due Date | Amount Due | Amount Paid | Balance | Payment Date | Note

    In E2, enter:

    =C2-D2

    A negative balance can show a credit or overpayment. Dues are contributions to the club, not automatically shared expenses, so keeping them separate prevents the member balance summary from becoming misleading.

  7. Add an expense check. Put an Expense ID in L2 and use this formula in M2:

    =IF(L2="","",IF(ABS(SUMIF(Shares!$A:$A,L2,Shares!$E:$E)-VLOOKUP(L2,Expenses!$A:$E,5,FALSE))<0.01,"OK","CHECK"))

    Copy it down for each expense ID. CHECK means the participant shares do not match the recorded expense amount.

Choose the split method before entering participants

The right method depends on what the club agreed to fund. Thing is, no formula can decide whether a member should count as a participant.

Rule How to record it Useful for Main tradeoff
Equal Add each participant and divide the amount evenly Regular club costs and shared events Simple, but it can include people who did not attend
Usage-based Add only the people who used or attended, then use an equal split Optional trips, meals, and activities Fairer for attendance, but someone must confirm attendance
Percentage Enter a percentage for every participant Different contribution rules or income-based arrangements The percentages must total 100%
Fixed Enter the dollar amount each person owes Custom portions or item-based costs The fixed amounts must match the expense total

If contributions vary by income, agree on the rule first and collect only the information the club actually needs. Put the decision in the Notes column so a later treasurer can understand the calculation.

Test the workbook with one known expense

Before inviting the full club, enter a $300 event expense as E001. Alex paid the full amount, and five people shared it equally.

Member Share % Amount owed Paid for E001 Gross position
Alex 20% $60 $300 +$240
Jordan 20% $60 $0 -$60
Sam 20% $60 $0 -$60
Priya 20% $60 $0 -$60
Lee 20% $60 $0 -$60

Alex's position starts at +$240, not +$300, because Alex also owes a $60 share of the expense. The other four members each start at -$60.

That is the gross position before anyone settles. It does not automatically tell the club which payment method to use.

Ask one treasurer and one member to verify the Shares rows and Summary results. Fix the workbook before adding old expenses or sharing the file widely.

Record receipts, reimbursements, and settlements

Add a Drive link in the Receipt Link column for each expense that needs supporting documentation. Include a short note when a receipt is missing, an item was disputed, or the club approved an exception.

A settlement is not a new expense. It is a later payment that reduces an existing balance.

If you want to record completed transfers, use these Settlements headers:

Date | From | To | Amount | Related Expense ID | Status | Proof Link

Enter only completed transfers if you want the balance formulas to stay simple. Then add these optional Summary columns:

Sent in Settlements | Received in Settlements | Remaining Position

Use the following formulas in row 2:

=SUMIF(Settlements!$B:$B,$A2,Settlements!$D:$D)
=SUMIF(Settlements!$C:$C,$A2,Settlements!$D:$D)
=D2+E2-F2

After a settlement, a positive Remaining Position means the member is still owed money. A negative result means the member still owes money.

A bank transfer or payment app can handle the actual payment. The spreadsheet remains the club's record of the expense, split, and settlement.

Share the file without exposing the formulas

Give Editor access only to people who need to add or change rows. The treasurer and a backup administrator usually need that access; other members can have Viewer or Commenter access unless they are entering expenses themselves.

Person Suggested access Protect
Treasurer Editor Keep access to all tabs
Backup administrator Editor Allow formula corrections when needed
Members entering expenses Editor, if necessary Protect Summary and formula columns
Members reviewing records Viewer or Commenter Prevent accidental data changes

Select the Summary tab and formula columns such as Shares!E:E, then choose Data > Protect sheets and ranges. You can warn editors before changes or restrict edits to selected people.

Without protection, an editor can overwrite a formula while fixing an unrelated row. The Tiller sharing and permissions guide covers the main permission choices.

Avoid a broad editor link for a financial record. Invite people directly, and check the access settings on linked receipts.

Set a review rhythm the club can follow

At each monthly meeting, the treasurer can review new expenses, open receipt links, confirm participant rows, compare each share total with the expense amount, and update settlement records. Members should raise corrections in Notes rather than silently deleting an approved row.

Use a short rule such as this:

Add the expense, receipt link, participants, and split method before the next club review. Record disagreements in Notes until the club resolves them.

The rule matters more than the exact schedule. A spreadsheet becomes stale when nobody knows who updates it or when a row is considered approved.

Avoid the mistakes that distort club balances

Inconsistent member names break totals. Alex M. and Alex are different criteria in a formula. Use the member dropdown everywhere.

One participant row can hide a missing share. Every participant needs a separate row for every expense.

Rounded shares can miss a cent. Check the total and assign the adjustment to a documented row.

Dues can pollute reimbursement totals. Keep member contributions on the Dues tab unless the club has deliberately defined them as shared charges.

A monthly worksheet can hide old records. Keep one expense ledger and filter it by date instead.

A receipt link can reveal more than the expense amount. Check the linked file's access before sharing it with the full membership.

Know when a spreadsheet is enough

To be honest, Sheets is usually enough when the club has occasional expenses, a treasurer, and members willing to review the record. It is less convenient when the club needs frequent payment requests, automatic reminders, or a high volume of recurring contributions.

A separate payment or budgeting tool may help with requesting or sending money. Keep the shared ledger anyway, or export the relevant records, so the club can see the original expense, agreed split, and completed settlement in one place.

Create the four core tabs, enter the E001 test expense, and wait for every check to show OK before adding the club's real history.