How to split a bill using a spreadsheet?

Jason Chan
August 10, 2022

During a night out with friends, you might offer to cover the bill for convenience and worry about splitting it later. But then when you’re looking at the receipt the next day, things might get a little confusing. Besides everyone’s individual orders, you have to somehow account for the 8-9% tax and 15-20% tip. Now, how do you determine how much everyone owes? Ditch your calculator app (or TI-84 for all the STEM people out there) and learn how you can use a spreadsheet to easily get accurate numbers for how much everyone owes you.

There are also specific apps out there, like Splitwise.com, that can help you do this, but some people (like myself) would rather see the numbers laid out in a spreadsheet to see how the numbers are being calculated from input to output.

Luckily, it’s pretty easy to build a spreadsheet that calculates how much each person owes based on what they ordered and splitting the tax and tip proportionally. You can see an example below:

image

It’s quite easy to create a spreadsheet like this from scratch in Subset. All you have to do is the following:

  1. Create a Receipts Input Table and write each item, the cost, and the name of the person who ordered it
  2. Create a Tip and Tax Table to input the Tip and Tax (meant to be split proportionally amongst the group)
  3. Create a Total Table. This should include the Subtotal (the sum of everything in the Receipts Table) and the Tip and Tax (comes from the Tip and Tax Table)
  4. Create a Table for how much each person owes
    1. This table can be created by grabbing each person from the Receipts Table and removing duplicates. Then apply the SUMIF function to sum the Cost from the Receipts Table IF the item belonged to the respective person. The formula will look something like =SUMIF(Receipt_Person,Person,Receipt_Cost)
    2. To find the total, you can figure out the proportion that each person ordered compared to the Subtotal and then apply that same proportion to the Total. The formula will look something like =Individual_Subtotal/Subtotal*Total
  5. Request the amounts from each person in the party!

If you’d like an easy-to-use, reusable template that requires no formula writing, check out the Receipt Split spreadsheet we built using Subset and clone it for yourself!

Link Here!

We’re building a better spreadsheet
Get your spot on the waitlist

Sign up for email updates