Splitting group expenses in Google Sheets is easy. You can use my ready-to-go template or the custom array formula powered by the SPLIT_EXPENSE named function.
If every expense is shared equally among all members, you might not even need a spreadsheet.
For example, if four people go on a trip and split every cost equally, the math is straightforward. But in real-life situations, not everyone benefits from every expense. That’s when my template or formula becomes really helpful.
The good news? My solution works in both cases—whether all expenses are shared among everyone or only among the actual beneficiaries.
To use the split group expenses template, simply enter the expense details in the “Expense” tab: who paid, how much, and who the beneficiaries are. The array formula in the “Split” tab will automatically calculate who owes what and to whom.
You can fill it out as expenses happen—quick and easy. It only takes a few minutes, and your updated summary is always ready.
Input
Output
Usage Notes
1. Enter the beneficiary names in row 3 (D3:3) on the “Expense” tab.
2. Log the expense details in:
- A4:A – Expense name
- B4:B – Who paid
- C4:C – Amount paid
3. Mark the beneficiaries using “Yes” or “No” dropdowns under each name.
The final split group expenses breakdown will be calculated automatically in the “Split” tab.
Important: Every name listed in row 3 (beneficiaries in D3:3) should also appear at least once in column B (payer list), even if they didn’t pay anything. For example, if Person 5 is a beneficiary but didn’t pay for anything, just add their name in column B on a blank row. You can leave the corresponding A and C cells empty. See the range A11:C11 in my template for an example.
How to Use the Array Formula to Split Group Expenses in Google Sheets
One of the coolest parts of this template is the array formula in cell D2 on the “Split” tab:
=SPLIT_EXPENSE(
Expenses!B4:B,
Expenses!C4:C,
FILTER(Expenses!D4:R, Expenses!D3:3<>""),
FILTER(Expenses!D3:3, Expenses!D3:3<>"")
)This formula generates the full split table automatically—no need to drag formulas down or across.
The SPLIT_EXPENSE function is a custom named function designed specifically to split group expenses in Google Sheets.
Syntax
SPLIT_EXPENSE(paid_by, amt_paid, split, split_h)Arguments
- paid_by: A vertical array of names representing who paid. It can include duplicates.
- amt_paid: Corresponding amounts. Must be the same length as
paid_by. - split: A 2D array of “Yes”/”No” dropdowns indicating beneficiaries. Should have the same number of rows as
paid_byand as many columns as there are unique names in the group. - split_h: A one-row header listing those unique names. Should match the column count of
split.
Using SPLIT_EXPENSE with a Closed Range
You can also test this function using a fixed-size range. It’s a great way to understand how it works before using it in dynamic or infinite ranges.
Example:
=SPLIT_EXPENSE(B4:B8, C4:C8, D4:F8, D3:F3)Breakdown:
paid_by: B4:B8amt_paid: C4:C8split: D4:F8split_h: D3:F3
In this example, the final calculation shows that B and C need to pay A a total of 5250. B owes 1250, and C owes 4000.
As before, every name listed in the header row (D3:F3) must also appear in the payer column (B4:B8), even if they didn’t contribute directly.
Conclusion
How can you get the SPLIT_EXPENSE named function to split group expenses in Google Sheets? And does it cost anything?
It’s completely free! Just make a copy of my template to get started. If you’d like to use the SPLIT_EXPENSE function in another file, you’ll need to import it. I’ve explained how to do that in my Named Functions Guide.
This setup is one of the easiest ways to split group expenses in Google Sheets—accurate, transparent, and shareable with your group.
