Spreadsheets

Shared Google Sheet for a couple's expenses with protected ranges

Set up a shared Google Sheet for a couple's expenses with an input tab each, a protected summary, a who-paid column and a settle-up cell.

Editorial illustration for Shared Google Sheet for a couple's expenses with protected ranges

A shared Google Sheet for a couple works best with one input tab per person, a summary tab that only the owner can edit, a column recording who paid, and one settle-up cell that shows who owes whom. Protect the summary with Data, Protect sheets and ranges so formulas cannot be overwritten, and give each person edit access to their own tab.

How should the tabs be laid out?

  • Partner A: Date, Amount, Category, Paid for, Notes, rows from 2.
  • Partner B: the same columns, in the same order.
  • Summary: totals, the settle-up cell and a split percentage.
  • Settings: the category list, kept in one place so both tabs use the same spelling.

The Paid for column holds one of two words: Shared or Mine. Anything marked Mine is personal and stays out of the settle-up. Add a dropdown with Data, Data validation, Dropdown, and use the same list in both input tabs. The tab a row sits on already records who paid. If you would rather keep one combined log, add a Paid by column and run SUMIFS on it instead.

What formulas do the totals need?

In Summary!B2, shared spending paid by Partner A, and in B3, shared spending paid by Partner B:

=SUMIFS('Partner A'!$B$2:$B$500, 'Partner A'!$D$2:$D$500, "Shared")

Amount is column B and Paid for is column D, matching the layout above. For Partner B, repeat the formula with the Partner B tab name. Then in B4 add the two for the household shared total: =B2+B3.

How does the settle-up cell work?

Decide on a split. For an equal split, each person should bear half of the shared total. The difference between what A paid and half of the total is what B owes A. Put this in B6:

=B2 - B4/2

A positive answer means Partner B owes Partner A. A negative one means the reverse. Use an IF to make it readable:

=IF(B6>0, "B owes A "&TEXT(B6,"#,##0"), IF(B6<0, "A owes B "&TEXT(-B6,"#,##0"), "All settled"))

Say A paid ₹42,000 of shared bills in a month and B paid ₹30,000. The shared total is ₹72,000, so each share is ₹36,000. A paid ₹6,000 more than their share, and 42,000 minus 36,000 is ₹6,000, so B owes A ₹6,000. Check it from B's side: 30,000 is ₹6,000 short of 36,000.

ItemCalculationAmount
A paid for shared itemsFrom the Partner A tab₹42,000
B paid for shared itemsFrom the Partner B tab₹30,000
Shared total42,000 plus 30,000₹72,000
Equal share each72,000 divided by 2₹36,000
Settle-up42,000 minus 36,000B owes A ₹6,000

If you split in proportion to income, say 60:40, put B's share percentage in a cell, such as B5 = 0.4, and compute what B owes as B5*B4 - B3. With the same numbers, 0.4 times 72,000 is 28,800, minus the 30,000 B paid, is minus 1,200, which means A owes B ₹1,200 under that split.

How do you protect the summary?

  1. Open the Summary tab and select the cells with formulas.
  2. Choose Data, Protect sheets and ranges, then Add a sheet or range.
  3. Click Set permissions, choose Restrict who can edit this range, and keep only yourself.
  4. Leave the split percentage cell unprotected if you want both of you to adjust it, or protect it as well and agree changes together.

Do the same for the Settings tab so the category list does not change unnoticed.

What do the permissions actually allow?

Share the file from the Share button using each person's email address, with the Editor role. Editors can change anything not protected, so protection is what stops accidents. Viewer and Commenter roles are useful for a third person, such as an accountant, who should look but not change. Protection is a guard against mistakes, and the owner can always change it, so keep the owner role with whoever manages the file.

Where does a shared sheet stop being the right tool?

This setup suits two people splitting a household. SpendixAI is built around one personal workspace and is not a bill-splitting tool, so for the settle-up itself the sheet is the right choice. For your own spending, though, importing statements and categorising them there can run alongside it.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports