A family grocery spreadsheet should do two jobs: show what was shared and show who paid upfront. Add clear percentages, calculated shares, and a small balance summary, and you'll have a workable record without a payment app.

Who owes what can then be answered from the sheet, not from memory. The spreadsheet records the agreement and the math; it doesn't move money between family members.

Start with the household rule

Decide how the family will divide groceries before entering a month of receipts. Keep the rule visible in the Settings tab so a new row doesn't become a fresh debate.

Method How to enter it Works well when Tradeoff
Equal Give each participant the same percentage Everyone uses the shared groceries similarly It ignores different incomes or usage
Income-based Set percentages from an agreed income measure Contributions should reflect ability to pay The family must agree on which income figure to use
Usage-based Give higher shares to heavier users and 0% to nonparticipants Household consumption varies Estimates can become subjective
Item-specific Put shared and personal items on separate rows One receipt serves different people It takes more entry time

Turns out, the spreadsheet is usually easier than the conversation. Write the rule in plain language first.

Parents may also decide that children's groceries are simply household costs rather than individual IOUs. Either choice can work if everyone understands it.

Choose columns that match the decision

Use one row for each shared purchase, or split a receipt into several rows when only part of it belongs to the household. The following layout assumes three participants named Alex, Jamie, and Riley.

Column Purpose
Date The shopping date
Store / item A short description such as Aldi weekly shop
Category Groceries, snacks, household items, or another agreed label
Shared total The amount being divided, not necessarily the full receipt
Paid by The person who paid at checkout
Split method Equal, income-based, usage-based, or custom
Alex % Alex's agreed percentage
Alex share Alex's calculated amount
Jamie % Jamie's agreed percentage
Jamie share Jamie's calculated amount
Riley % Riley's agreed percentage
Riley share Riley's calculated amount
Total % A check that the percentages add to 100%
Receipt link An optional link to a receipt photo
Settled? Whether the row has been fully settled
Notes Details about exclusions, substitutions, or rounding

The Shared total deserves care. If a $126.43 receipt includes $18 of personal items, either enter $108.43 as the shared amount or create separate rows for the shared and personal portions.

For two people, remove the Riley columns. For larger households, repeat the percentage and share pair for each additional person.

Create the workbook in a few passes

Start with three tabs: Groceries, Summary, and Settings. The first holds the transactions, the second shows balances, and the third stores names, income ratios, and the household rule.

  1. Open a blank Google Sheet and rename the first tab Groceries.
  2. Add the columns above in row 1. Freeze that row so the headings remain visible while scrolling.
  3. Format Date as a date, money columns as currency, and percentage columns as percent.
  4. Add dropdowns for Split method and Settled?. Use consistent values such as Equal, Income, Usage, Custom, No, and Yes.
  5. Enter one test row for a $50 purchase before adding real receipts.
  6. Add the formulas in the next section and copy them down the expected entry range.
  7. Protect the formula columns after testing them. Leave the date, description, amount, payer, percentages, and notes available for editing.

Keep the names in Settings consistent with the Paid by entries. Alex, ALEX, and Alex R. can otherwise become separate names in a summary.

Add the share formulas

Assume row 2 uses these columns:

  • D for Shared total
  • G for Alex %
  • H for Alex share
  • I for Jamie %
  • J for Jamie share
  • K for Riley %
  • L for Riley share
  • M for Total %

Enter these formulas in row 2:

H2 =IF($D2="","",$D2*$G2)
J2 =IF($D2="","",$D2*$I2)
L2 =IF($D2="","",$D2*$K2)
M2 =IF($D2="","",SUM(G2,I2,K2))

Format G, I, and K as percentages. A displayed value of 40% is stored as 0.4, so the share formula multiplies the grocery total by 0.4.

For an equal three-person split, use =1/3 in each percentage cell rather than typing rounded percentages. For a $50 row with percentages of 40%, 35%, and 25%, the shares are $20, $17.50, and $12.50.

Add a data validation rule to each percentage column so entries stay between 0 and 1. Then use conditional formatting on Total % to flag any row that is not equal to 1. The row should not be considered ready until the percentages total 100%.

Rounding can create a one-cent difference. If you want Riley to absorb the final cent, replace the Riley formula with this version:

=IF($D2="","",ROUND($D2-SUM($H2,$J2),2))

Use that approach only after checking that all three percentages total 100%.

If the split follows income

Put the agreed income figures in Settings. For example, place Alex's figure in B2, Jamie's in B3, Riley's in B4, and the combined amount in B5 with:

=SUM(B2:B4)

Alex's percentage can then be calculated with:

=IFERROR(Settings!$B$2/Settings!$B$5,0)

Use the same pattern for the other household members. Decide whether the figures represent gross or take-home income, and record that decision in Settings. Ratios should be revisited when the household's circumstances change.

Income is sensitive information. Avoid a broad link-sharing setting if the Settings tab contains amounts that everyone with the link shouldn't see.

Show balances on a Summary tab

A useful summary separates the amount a person was assigned from the amount they paid at checkout.

Person Assigned share Paid upfront Net due
Alex calculated total calculated total assigned share minus paid
Jamie calculated total calculated total assigned share minus paid
Riley calculated total calculated total assigned share minus paid

If A2 contains Alex's name, use the following formulas for open rows marked No in the Settled? column:

B2 =SUMIFS(Groceries!$H$2:$H$1000,Groceries!$O$2:$O$1000,"No")
C2 =SUMIFS(Groceries!$D$2:$D$1000,Groceries!$E$2:$E$1000,A2,Groceries!$O$2:$O$1000,"No")
D2 =B2-C2

For Jamie, change the assigned-share range from column H to J. For Riley, use column L. Copy the paid and net formulas down for each name.

A positive net due means that person still owes money. A negative result means that person paid more than their assigned share and may be due a reimbursement.

The Paid by value means who paid the store, not who later reimbursed someone. If reimbursements are partial or involve several people, create a separate Payments tab with Date, From, To, Amount, Related row, and Notes. That preserves the original grocery record.

Add simple reports when they help

A category summary can show where the household's grocery money is going. With Category in column C and Shared total in column D, place this formula on a report tab:

=QUERY(Groceries!A1:P1000,"select C, sum(D) where C is not null group by C label sum(D) 'Total spent'",1)

To review purchases above $50, use:

=FILTER(Groceries!A2:P1000,Groceries!D2:D1000>50)

Change the amount or range to suit the household. These reports help with review; they don't replace the split rule.

Share the sheet without giving away more access than needed

Thing is, a link is convenient but broad. A specific-person share setting is usually easier to control when the sheet includes income figures, receipt photos, or private notes.

Use the Share button and choose the narrowest access that fits. Viewer access works for someone who only needs to review the record, Commenter access allows questions without changing cells, and Editor access is appropriate for people who will enter receipts or maintain formulas.

If you use general link access, choose the role deliberately. An edit link should go only to people the household trusts with the entire file. On a phone, sharing is typically under the three-dot menu and Share & Export.

Protect the formula columns and the Settings tab through Data > Protect sheets and ranges. Protection helps prevent accidental edits, but it isn't a substitute for careful sharing permissions.

Test the file from a phone before sending the link to the family. Check that the amount fields are editable, the formulas still calculate, and the receipt links open for the people who need them.

Use a short weekly review

A weekly check-in keeps the ledger from becoming a month-end archaeology project. Give one person responsibility, or rotate that task if several adults shop regularly.

  • Add each receipt and enter the shared amount.
  • Confirm the payer and the split percentages.
  • Check that Total % equals 100%.
  • Add a receipt link or a note for unusual purchases.
  • Review the Summary tab and record reimbursements separately.
  • Mark a row Yes only after the related balance is settled.

To be honest, the review is where most of the value appears. The formulas only make the agreed rule visible.

Common problem Fix
A personal item is included in the shared total Use a separate row or reduce the shared amount
Percentages do not total 100% Check the Total % cell before settling
Someone overwrites a share formula Protect formula columns and copy from a clean row
The payer's name varies between rows Use one spelling and a dropdown
A partial reimbursement is mistaken for a settled row Track the payment separately and leave the grocery row open
A public edit link exposes private details Use restricted sharing or separate sensitive information

Know when a spreadsheet is enough

Sheets is a sensible fit when the family can enter receipts manually and wants a transparent record. It works especially well when the main need is tracking, calculating, and reviewing rather than automatic payment requests.

Consider another tool when receipt capture, reminders, or payment requests have become the main burden. Compare those tools by function: tracking, requesting, paying, exporting, and recordkeeping are separate jobs.

You don't need to move money through the spreadsheet. A reimbursement can happen through whatever method the household already uses, while the sheet keeps the supporting record.

FAQ

How do I split groceries unevenly by income?

Agree on the income measure first, then turn each person's figure into a percentage of the household total. A 60/40 arrangement would give one person 60% of each eligible shared purchase and the other person 40%.

What if a purchase is only for some family members?

Set nonparticipants to 0% for that row, or split the receipt into separate rows. Use the Notes column to explain the choice.

Should children have grocery balances?

There is no single household rule. Some parents include children in usage calculations, while others treat children's groceries as a parental or household cost. Write the choice down before using the summary for reimbursements.

What happens if one person forgets to log a purchase?

Keep the original receipt or a photo, then add it during the next review. The Paid by field matters because an unrecorded purchase can make the balance look fair when it isn't.

Can Google Sheets send the reimbursement?

The sheet can calculate and document the amount, but it doesn't send money. Record the payment separately and mark the related row settled when the household's agreed process is finished.

Create the three tabs, enter last week's receipts, test one $50 row, and agree on how to mark a purchase settled before sharing the file.