Color works best when it answers one question at a glance: who covered the original expense, or whether a reimbursement has arrived. Put payer colors in one column and reimbursement status colors across a separate row. Otherwise, a row can only tell one story.

If Jamie pays a $96 grocery receipt, the store has been paid, but the group may still owe Jamie, which is why the word "paid" causes so much confusion in shared-money spreadsheets.

Do you need to see that a bill was purchased, or that everyone paid their share back? Those are different events.

Use one sheet for expenses and another for repayments. Then let conditional formatting handle the visual reminders.

For a reimbursement log with Due Date in column E and Date Received in column F, place this formula in G2:

=IF(COUNTA(A2:D2)=0,"",IF(ISNUMBER(F2),"Paid",IF(AND(ISNUMBER(E2),TODAY()>E2),"Overdue","Pending")))

It leaves blank rows alone, marks a recorded repayment as Paid, and flags an overdue request once the due date passes.

Give each color one job

Use status colors to show action. Use person colors to show identity.

Payer color: Apply a distinct, light fill to the Paid by cell on the Expenses sheet. The person's name should remain visible.

Paid reimbursement: Use a soft green full-row fill on the Reimbursements sheet, along with the word "Paid" in the Status column.

Pending reimbursement: Use amber or yellow for requests that are still open.

Overdue reimbursement: Use a light red fill and the word "Overdue." Add a due date before relying on this label.

Keep the palette small. Bright fills across every row get tiring fast.

Don't color an entire expense row by payer and status at the same time. A blue row for Jamie and a red row for overdue reimbursement will compete. Color the payer cell only, then reserve full-row color for payment status.

Color also should not be the only signal. Labels such as Paid, Pending, and Overdue still make sense if someone prints the sheet in grayscale or cannot distinguish the fills.

Start with an expense log and a repayment log

A shared-expense tracker becomes much clearer once you separate the original purchase from the money people send afterward. Thing is, a restaurant bill can be paid immediately while the group balance remains unsettled for days.

Set up these tabs before entering transactions.

Sheet What it records Recommended columns
Expenses What the group bought and who paid the vendor Date, Expense ID, Description, Total, Paid by, Alex share, Blair share, Casey share, Receipt or note, Check
Reimbursements Money one person sends to another Request date, From, To, Amount, Due date, Date received, Status, Expense ID or note
People Approved names for dropdowns Person
Summary Each person's current position Person, Paid up front, Own share, Received, Sent, Open balance

Replace Alex, Blair, and Casey with your group members. Add or remove share columns as needed.

In the Expenses sheet, enter the full cost in column D and each person's share in columns F through H. Put this check in J2:

=D2-SUM(F2:H2)

A result of 0 means the recorded shares equal the expense total. Any other result needs a second look before the group starts reimbursing one another.

Use the same Expense ID in the Reimbursements sheet. That small detail matters after a long trip, a move, or a month of utility bills.

Build the tracker without manual color updates

  1. Create the People tab and enter each member once. Keep spelling consistent. "Chris" and "Christopher" will be treated as different people.

  2. Add dropdowns for Paid by, From, and To. In Google Sheets, use Data, then Data validation, and choose a dropdown from the People range. In Excel, create a named range such as People, then use Data Validation with =People as the list source.

  3. Add the status formula to Reimbursements!G2, then fill it down. Enter actual spreadsheet dates in Due Date and Date Received. Text that only looks like a date can break the overdue check.

  4. Freeze the header row and turn on filters. Filtering the Reimbursements sheet to Pending or Overdue is often more useful than scrolling through every settled payment.

  5. Record a reimbursement only after it actually arrives. Until then, leave Date Received blank and let the status remain Pending or Overdue.

For a cash payment, write a short reference in the final column, such as "cash at dinner" or "receipt confirmed." It gives the group something to check later without turning the sheet into a debate.

Apply whole-row conditional formatting for repayment status

Select Reimbursements!A2:H1000 before creating the rules. The Status column is G, so use these formulas.

Status Custom formula Suggested format
Paid =$G2="Paid" Soft green fill
Pending =$G2="Pending" Soft amber fill
Overdue =$G2="Overdue" Light red fill

In Google Sheets, choose Format, then Conditional formatting, and select "Custom formula is" for each rule. In Excel, choose Home, Conditional Formatting, New Rule, then "Use a formula to determine which cells to format."

The $ before G locks the status column. The row number stays relative, so row 20 checks G20 rather than G2. Google's conditional-formatting help shows the same mixed-reference approach for formatting an entire row from one cell.

Do not use =$G$2 here. That would make every row examine G2.

Now apply payer colors separately. Select only Expenses!E2:E1000, then create one rule per person, such as =$E2="Alex" for light blue or =$E2="Blair" for light purple. The rule stays in the payer column, where it won't mask repayment status.

Make the summary answer who is still owed

Color is helpful, but the Summary tab answers the harder question: who is ahead after purchases and confirmed repayments?

For this example, enter Alex in Summary!A2, Blair in A3, and Casey in A4. With the sample Expenses columns above, use these formulas for Alex's row.

Summary cell Formula Purpose
B2 =SUMIF(Expenses!$E$2:$E$1000,$A2,Expenses!$D$2:$D$1000) Total Alex paid up front
C2 =SUM(Expenses!$F$2:$F$1000) Alex's share of all expenses
D2 =SUMIFS(Reimbursements!$D$2:$D$1000,Reimbursements!$C$2:$C$1000,$A2,Reimbursements!$G$2:$G$1000,"Paid") Confirmed money Alex received
E2 =SUMIFS(Reimbursements!$D$2:$D$1000,Reimbursements!$B$2:$B$1000,$A2,Reimbursements!$G$2:$G$1000,"Paid") Confirmed money Alex sent
F2 =B2-C2-D2+E2 Alex's open position

For Blair's Own share formula, use the Blair share column instead: =SUM(Expenses!$G$2:$G$1000). For Casey, use column H.

Turns out, the signs are easier to read than they first appear. A positive Open balance means the group still owes that person overall. A negative balance means that person still owes the group overall.

This summary checks contribution totals. It does not decide the exact transfer route between several people, especially after uneven splits or partial repayments. Use the Reimbursements sheet to record the transfers your group agrees on.

Agree on the split before you color it

A spreadsheet can calculate a rule, but it cannot choose a fair rule after the fact. Write the split method in the expense note or use a separate Split method column.

Situation Practical split rule
Shared groceries or a group dinner Equal shares among people who participated
Utilities with clearly different usage Usage-based shares, with the basis noted
Rent with unequal room sizes Agreed fixed shares for the lease period
Lodging where someone stayed fewer nights Nights-stayed split
One person's personal purchase Enter zero for everyone else and assign the full amount to that person
Deposit return or refund Record it as a new transaction rather than changing the original expense

For a trip, decide whether canceled reservations, rental-car fuel, and shared groceries use the same rule. They often do not. A quick note such as "hotel split by nights stayed" prevents confusion later.

Keep the shared record usable

A group sheet works better with a boring routine.

  • Add the expense and receipt reference soon after the purchase.
  • Leave Date Received blank until the repayment is complete.
  • Decide who can edit formulas and who can add expense rows.
  • Review Pending and Overdue reimbursements after a major event or at a regular household check-in.
  • Add correction rows for mistakes instead of quietly rewriting old amounts.

Keep the receipt link or note with the expense row. Keep the payment reference with the repayment row. That repetition is useful when someone asks about a charge weeks later.

If a roommate, partner, or trip group prefers not to use a shared editable file, one person can maintain the tracker and send a filtered view or exported copy. The important part is agreeing on where the current record lives.

Fix the mistakes that make color rules lie

Problem Likely cause Fix
Every row has the same color The rule uses =$G$2 Change it to =$G2
Empty rows show as Pending The status formula has no blank-row check Use the COUNTA(A2:D2)=0 portion of the formula
An overdue reimbursement stays Pending The due date is stored as text Re-enter it as a real date value
Payer colors hide overdue colors Payer rules apply to the full row Limit payer rules to the Paid by column
The Check column is not zero Shares do not equal the recorded total Correct the total or the individual shares before settling up
A completed transfer is missing from the summary Its Status is not Paid Enter Date Received and confirm the status changed

Start with one recent grocery bill and one real repayment. If the payer cell, status row, and Summary balance all tell the same story, copy that pattern for the rest of your shared expenses.