Use one row per participant per booking. That structure makes cancellation math auditable and lets SUMIFS total each person's adjusted share. A comma-separated participant list may look tidy, but it is poor input for reliable per-person formulas.

Keep both the original split and the adjusted split. That way, you can see what everyone agreed to first and what changed after a cancellation.

A layout that survives cancellation

A two-tab tracker is easier to maintain than one crowded table. Add a third tab for summaries, then use a payment log only if money actually moves between people.

Tab One row means Main purpose
Bookings One utility booking or shared reservation Records the original amount, refunds, final cost, and cancellation details
Shares One participant's share of one booking Calculates what each person is responsible for
Summary One person's totals Shows adjusted shares, outstanding amounts, and pre-settlement positions
Payments One transfer between people Records reimbursements separately from the agreed split

Turns out, separating the booking from the participant share solves most spreadsheet confusion. The booking amount appears once. Each person's responsibility appears on its own row.

Create the Bookings and Shares tabs

Start a new Google Sheet and create tabs named Bookings, Shares, and Summary. Use Payments if your group needs a record of transfers.

Bookings tab

Enter these headers in row 1:

Column Header What to enter
A Booking ID A unique value such as UTIL-001
B Date The booking or payment date
C Utilities Type Electric, internet, water, gas, or deposit
D Description A short detail such as March apartment internet
E Original Amount The amount before any cancellation refund
F Refund or Credit Money returned or credited by the provider
G Final Group Amount The amount the group will still allocate
H Paid By The person who paid the provider
I Include in Group Totals? Yes or No
J Cancellation Status None, Pending, Cancelled, or Refunded
K Cancelled By The participant who canceled
L Notes Refund details, receipt location, or an agreed exception
M Adjusted Share Check A formula that verifies the percentages

In G2, calculate the final amount:

=IF(E2="","",E2-F2)

Copy the formula down. Enter zero in F when no refund or credit applies.

A full refund makes the final group amount zero. A nonrefundable cancellation usually leaves a positive final amount.

Shares tab

Enter these headers in row 1:

Column Header What to enter
A Booking ID The matching ID from Bookings
B Participant One person only
C Split Type Equal, Percentage, Custom, or Reimbursement
D Original Share % The agreed percentage before cancellation
E Adjusted Share % The percentage used for the final calculation
F Share Amount A formula based on the final group amount
G Reimbursement? Yes or No
H Settlement Status Owed, Requested, Paid, or Not due
I Notes The reason for an adjustment or payment status

For a $200 internet booking shared by four roommates, create four rows with the same booking ID. Each person starts at 25%.

In F2, calculate the participant's amount:

=IF(OR(A2="",E2=""),"",SUMIF(Bookings!$A$2:$A$100,A2,Bookings!$G$2:$G$100)*E2)

Copy it down. Format columns D and E as percentages, and columns E, F, and G on Bookings as currency where appropriate.

In Bookings!M2, check whether the adjusted shares total 100%:

=IF(I2="Yes",SUMIF(Shares!$A$2:$A$100,A2,Shares!$E$2:$E$100),"")

An included booking should normally show 100%. A booking with a zero final cost can be excluded from group totals.

Add dropdowns and protect the formulas

Dropdowns keep names and statuses consistent. Consistent text matters because SUMIFS treats Alex, alex, and Alex R. as different entries.

Use this setup workflow:

  1. Select Bookings!C2:C100, then choose Data > Data validation. Add the utility types your group uses.
  2. Add Yes and No dropdowns to Bookings!I2:I100 and Shares!G2:G100.
  3. Add None, Pending, Cancelled, and Refunded to Bookings!J2:J100.
  4. Add Equal, Percentage, Custom, and Reimbursement to Shares!C2:C100.
  5. Add Owed, Requested, Paid, and Not due to Shares!H2:H100.
  6. Freeze row 1 from View > Freeze so the headers remain visible while scrolling.
  7. Protect formula columns G and M on Bookings, plus column F on Shares, from accidental edits.

Keep participant names in a separate list if you want a name dropdown for Participant and Cancelled By. That is safer than typing names from memory.

Decide what the cancellation actually changed

A cancellation can change the provider bill, the group allocation, or only the payment between two people. Record the right change before editing percentages.

Situation Bookings tab Shares tab
The provider gives a full refund Enter the refund in F; G becomes zero; set I to No and status to Refunded Leave the original split for reference, or set adjusted shares to zero and explain it in notes
The provider gives a partial refund Enter the credit; G shows the remaining cost; set I to Yes if money remains Recalculate adjusted shares against the remaining amount
The group keeps the full cost and reallocates it Leave the refund at zero and keep I as Yes Change Adjusted Share %, mark the affected rows as reimbursement adjustments
The canceled person still owes an agreed fee Keep the final group amount unchanged Keep or customize that person's adjusted share and mark the settlement as Owed
One person accepts the entire final cost Keep the final group amount unchanged Set that person's adjusted share to 100% and the others to 0%

For the four-person, $200 example, suppose Jordan cancels and Alex agrees to absorb Jordan's $50 share. The adjusted percentages become Alex 50%, Bea 25%, Cam 25%, and Jordan 0%. They total 100%.

Use 100% for Alex and 0% for everyone else only when Alex is bearing the entire final amount. It is not the right shortcut for every cancellation.

Thing is, a Reimbursement? flag is only a label. It does not prove that money changed hands. Use the Payments tab for that.

Build a Summary tab with formulas

Put each participant's name in column A of Summary. Add these headers:

Column Header
A Person
B Adjusted Share
C Outstanding Share
D Paid to Provider
E Confirmed Sent
F Confirmed Received
G Pre-settlement Position

In B2, total the person's adjusted shares:

=SUMIFS(Shares!$F$2:$F$100,Shares!$B$2:$B$100,$A2)

In C2, show only shares still marked Owed:

=SUMIFS(Shares!$F$2:$F$100,Shares!$B$2:$B$100,$A2,Shares!$H$2:$H$100,"Owed")

In D2, total final group amounts paid by that person:

=SUMIFS(Bookings!$G$2:$G$100,Bookings!$H$2:$H$100,$A2,Bookings!$I$2:$I$100,"Yes")

If you add the optional Payments tab, use these formulas in E2 and F2:

=SUMIFS(Payments!$E$2:$E$100,Payments!$B$2:$B$100,$A2,Payments!$F$2:$F$100,"Confirmed")
=SUMIFS(Payments!$E$2:$E$100,Payments!$C$2:$C$100,$A2,Payments!$F$2:$F$100,"Confirmed")

Then calculate the position in G2:

=D2-B2-E2+F2

A positive result means the person has paid more than their adjusted share. A negative result means they still owe money, assuming the payment log contains only transfers between group members.

For a category view, place this formula in an empty Summary cell:

=QUERY(Bookings!A1:M100,"select C, sum(G) where I = 'Yes' group by C label sum(G) 'Final group amount'",1)

This groups included bookings by utility type. To total all recorded provider credits, use:

=SUM(Bookings!$F$2:$F$100)

To total final amounts still attached to bookings marked Cancelled, use:

=SUMIFS(Bookings!$G$2:$G$100,Bookings!$J$2:$J$100,"Cancelled")

The formulas work because every participant has a separate row. They will not reliably identify Alex inside a cell that says Alex, Jordan, Sam.

Add a Payments tab when money moves

The share table answers, "Who should bear the cost?" The payment table answers, "Who sent money to whom?"

Use these columns:

Column Header What to enter
A Payment Date When the transfer was made
B From The person who sent money
C To The person who received money
D Booking ID The related booking
E Amount The transfer amount
F Status Pending or Confirmed
G Note Payment reference or explanation

Do not replace a share row with a payment row. Keep both records. A person can owe $50, send $50, and still have a useful original share record.

Receipt links can go in the Notes columns. Keep the receipt folder limited to the same people who can see the tracker.

Share the file without losing control

Open Share and add the group members by email when possible. Give editing access to the people responsible for updates, commenting access to people who only need to suggest corrections, and viewing access to anyone who needs a read-only record.

Protect the formula cells before granting edit access. A protected range helps prevent someone from replacing a calculation with a typed dollar amount.

Avoid public link sharing for a file containing payment details or personal notes. Review the sharing list after the booking ends, especially when the group includes people who will not share future expenses.

Agree on one update rule. For example, the person who paid enters the booking, the person who canceled adds the cancellation note, and everyone confirms their settlement status.

Mistakes that create bad totals

Do not delete the original percentage after a cancellation. Keep it in Original Share % and make the change in Adjusted Share %.

Do not mark a provider refund as a reimbursement allocation. A refund changes the final group amount. A reimbursement allocation changes who bears the remaining amount.

Do not mark a row Paid just because someone promised to pay. Use Requested or Owed until the transfer is confirmed.

Check the adjusted percentage before settling. If an included booking does not total 100%, the sheet is assigning too little or too much of the final cost.

To be honest, most errors are small entry errors rather than difficult formulas. Use the dropdowns, stable booking IDs, and a short note for every exception.

FAQ

How do I mark a utilities reimbursement after someone cancels?

First decide whether the provider refunded money. Enter that amount in Refund or Credit, then let Final Group Amount calculate the remaining cost. If the group is reallocating the remaining cost, change only the adjusted percentages and mark the affected share rows as reimbursement adjustments.

Should the person covering a cancellation be set to 100%?

Only if that person is accepting the entire final amount. If Alex simply absorbs Jordan's 25% share in a four-person booking, Alex becomes 50%, not 100%. Use 100% only when the other participants' final shares are zero.

Can I keep all participants in one cell?

You can, but it makes per-person totals unreliable. Use one Shares row per participant instead. A comma-separated list can still appear in a note or summary field.

What if our split is not equal?

Enter the agreed percentages in Original Share %. Your rule might be based on usage, room size, nights stayed, income, or another written agreement. Adjust only the affected rows after the cancellation, and confirm the total remains 100% for an included booking.

Is a shared Google Sheet private?

Access depends on the file's sharing settings. Sharing with named people gives you more control than a broadly shared link, but the sheet still contains information that group members can copy or forward. Limit access to people who need the record.

When should I use something other than a sheet?

A sheet is a reasonable fit for a small group with occasional bookings and straightforward settlements. Consider another workflow if you need frequent receipt capture, automated reminders, many currencies, or detailed transfer history across a large group.

Start with one real booking. Enter its original amount, refund or credit, participant shares, and settlement status, then test one cancellation. Confirm that the adjusted share check reads 100% before you share the file.