Excel tutorial for Splitting bills and sharing expenses among friends

In this post, i will share an Excel tutorial where you can create tables and calculate the total amount spent by each person and find out who owes or need to give to whom.

At the end of this post you will be able to make something similar to the following table below.

Table1 = List of expenses including name and amount as well as the grand total spent by all.

Table2 = Amount spent by each person.

Table3 = Amount that each should spend to make expense equal among all.

Table4 = Calculates the settlement amount, people with negative amount should pay to the people with positive value.

In the table above, Shristi should give $107.48 in total to Sudeep ($62) and Kapil(45.48).

Let’s break down to the individual steps to create sheet for splitting bills in Excel.

Step 1: Create expense table

Create expense table similar to the Table1 above. You can include other information such as date, amount and items spent on. But the most important ones are Name and Amount.

Step 2: Create table with name and amount spent by each

Create the Table2 and use the following formula to calculate the amount spent by each.

=SUMIF(Table1[Name], [@Person], Table1[Amount])

This fomula (used in Table2, cell B16, B17 and B18) will calculate the total amount spent by a person from Table1 by matching Name. [@Person] is the reference to the name of person in Table2 itself.

Step 3: Calculate the amount that was expected to spend by each

To calculate this just divide the grand total in the cell C11 by total number of people i.e 3 in the above case.

=Table1[[#Totals],[Amount]]/COUNTA(Table2[Person])

Here, the formula (used in cell B22) “COUNTA(Table2[Person]” will return total number of people entered in Table2. The value that it returns in the above case is 3 since there are three people.

Step 4: Calculate the settlement amount

Create a table and write the name of each person for calculating the settlement amount. The settlement amount for each can be obtained by subtracting the amount that each should spend from the amount that they actually spent.

For example: to calculate for Sudeep, subtract B22 from B16.

If this amount is positive then they should receive that much amount and if it is negative then they should give that amount to the person with positive value.

I hope this tutorial is helpful.