Split Utility Bills by Percentage in Google Sheets

Does an even split make sense when one roommate works from home running two monitors and a window air conditioner all day? Usually not. Splitting utility bills by percentage prevents quiet household resentment before monthly bills turn into arguments.

You enter the total bill once. The spreadsheet handles the math instantly.

Choosing Your Percentage Baseline

Different households need different logic. Equal splits work fine when everyone has identical rooms and similar habits. Percentage splits offer a realistic middle ground when habits diverge.

Income ratios work well for couples or long-term roommates who pool life costs. For example, if Person A brings home $4,000 and Person B brings home $2,800, their combined take-home pay is $6,800. Person A earns roughly 58.8% of the total, while Person B earns 41.2%, as outlined in CheckMyPayment's rent split breakdown. Applying those percentages to a $300 electric bill means Person A pays $176.40 and Person B pays $123.60.

Thing is, room square footage offers another clean baseline, especially when heat or cooling dominates the bill, though to be fair, roommates who pick bedroom size usually apply that ratio strictly to rent while leaving water and trash split straight down the middle. That mixed setup works great. You just keep separate rows for each utility type.

Spreadsheet Columns and Layout

Set up a single tab named Utility Tracker. The easiest layout separates raw billing data on the left from individual dollar calculations on the right.

Format columns F through H as percentages with zero decimal places. That keeps the screen clean.

Core Formulas for Rows and Balances

Build the calculation engine using three straightforward steps.

1. Calculate individual dollar shares. In row 2, multiply the total bill by each person's percentage:

   • Cell J2: =C2 * F2
   • Cell K2: =C2 * G2
   • Cell L2: =C2 * H2

2. Validate percentage totals. Typos happen. In cell I2, check that the row percentages total exactly 100%: =IF(ROUND(SUM(F2:H2), 2) = 1, "OK", "CHECK") If someone enters 40% and 40% without accounting for the remaining 20%, the cell flags CHECK in bright text.

3. Calculate net monthly balances. Tracking what people owe on each bill is only half the job. You also need to track who actually paid the provider. Create a summary block off to the side in columns N and O:

   • Alice Total Paid: =SUMIF(D2:D50, "Alice", C2:C50)
   • Alice Total Share: =SUM(J2:J50)
   • Alice Net Balance: =SUMIF(D2:D50, "Alice", C2:C50) - SUM(J2:J50)

Turns out, that single net balance formula solves most roommate math headaches. A positive number means the household owes that person money. A negative number means that person owes the household. Repeat the formula for Bob and Charlie.

If your percentages never change from month to month, you can store your group's default split in row 1 and reference those cells directly. As detailed in SpreadsheetPoint's guide to cell locking, adding a dollar sign like F$1 freezes the row reference when you drag formulas down.

Handling Reimbursements and Variable Costs

Not every bill follows a fixed percentage. Sometimes one person buys furnace filters or covers a one-off utility connection fee.

In Expensesorted's roommate template guide, individual reimbursements are handled by assigning 100% to the person responsible and 0% to everyone else. If Bob buys an extra router for his personal office, he lists the $80 bill, enters his name in Paid By, and sets his percentage to 100% with Alice and Charlie at 0%. The net balance remains zero for Alice and Charlie, while Bob's personal purchase stays recorded.

Sheet Sharing and Protection

When multiple people edit one document, formulas get overwritten. A few basic safeguards keep your numbers intact.

• Share by email rather than open links: Send invites directly to specific Google accounts with Editor permissions. As SheetsBootcamp's sharing walkthrough points out, public edit links allow anyone with the link to modify or delete data.
• Lock calculated columns: Use the menu path Data > Protect sheets and ranges. Lock columns I through L and your summary cards so housemates can only enter dates, bill totals, and percentage inputs, as demonstrated in SheetsBootcamp on protected ranges.
• Use keyboard shortcuts to audit: Press F2 or Ctrl + E while selecting a formula cell to highlight every referenced cell in color, a handy trick documented in Google Docs Help.

House Rules That Keep the Tracker Working

A spreadsheet only works if people update it consistently. Establish three house rules before logging the first bill:

• Log bills within 48 hours of receipt: The account holder logs the bill as soon as the provider sends an email statement.
• Attach receipt links in the notes column: Upload statement PDFs or screenshots to a shared Google Drive folder and drop the link in Column E.
• Settle net balances on the first of the month: Pick one day each month to review the net balances and clear debts through bank transfers or payment apps.

When a Spreadsheet Is Not Enough

To be honest, spreadsheets suit groups with predictable, recurring bills who want zero monthly app fees. They work well for two to five housemates sharing four or five statements a month.

If your household frequently splits grocery runs, restaurant checks, and daily rides alongside utilities, a spreadsheet will feel clunky. Everyday expenses generate dozens of micro-transactions that require mobile receipt scanning and instant push reminders. For strictly tracking monthly electricity, gas, water, and internet, though, a clean Google Sheet gives you total control without subscription fees.

Create a fresh Google Sheet, set up the header columns, and enter last month's electric bill to test your share formulas. Once your math matches your statements, share edit access with your roommates and set your monthly settlement date.