Quick Navigation

You know the problem well: You are sharing a household, going to dinner with a group, or going on a trip with friends… Now, you have to keep track of who paid for what, who owes who, and how to settle all your IOUs with as few payments as possible… Some folks will pay with cash, some use credit cards, and when the bill arrives, it’s a huge hassle to split it up. Fortunately, Excel here to save the day – here, you can download a completely free and easy spreadsheet that will track expenses, who paid, who owes whom, and how much. Tally it all up, and settle up your debts all at once!

## The Problem with Bill Splitting Websites

The problem of sharing expenses is so common, there are a million websites set up to help you solve it. But then you have to get everyone to agree to sign up for the service, diligently log your expenses, and hope you can figure out the splitting features. Sometimes it’s just easier to write it all down and sort it out later. Fortunately, Excel has all the features necessary to solve these issues in moments!

I designed a Shared Expense Calculator inside a normal Excel spreadsheet to demonstrate some of the powerful functions and formulas in Excel and show an example of the kinds of complex problems that can be solved with it. The Shared Expense Calculator is just a normal Excel worksheet and uses basic Excel functions with no plug-ins, no VBA, and none of the usual headaches that come with using private software. Best of all, it’s available to download here, for free!

The spreadsheet is embedded above. You can experiment with it by adding or removing names, adding new expenses, and splitting them according to who bought what. You can download the Shared Expense Calculator spreadsheet by clicking on the Download button () in the bottom right of the embedded file above.

Or, you can download the Shared Expense Calculator by clicking here:

If you’re confused about how to split bills, share expenses, or figure out who owes who, it’s probably easier than you think.

- To add people to the list of participants, just change a name in the file or add one in the yellow box next to the other names. Need to delete one? Just select the name and press delete!
- To add expenses to the list, just add a line to the description column in the yellow box. Need to delete an expense? Just select its description and press delete!
- Once you have created an expense, enter the amount in the “Amount” column and select the name of the person who paid from the drop-down in the “Who Paid?” column.
- To include people in the split for an expense, just put an “X” in the cell under their name. A person can pay for an expense without participating – just don’t put an “X” under his/her name!
- When everything is entered, you will see who underpaid and who overpaid in the row labeled “Total Debt/Income”. If a person is owed money, their number will be green. If a person owes other people money, it will be red.
- To find out the easiest way to settle up all the debts, look in the section called “Settlement” to see a suggestion of how to settle up with the smallest number of payments!

Unfortunately the spreadsheet doesn’t work for me (Excel 2011 for Mac). The settlements are not shown (Value Error). Any chance to fix that?

Oddly enough that’s the version I’m using and it seems to be working fine. Perhaps the original template got muddled? Maybe try and download it again. I was actually concerned that it wouldn’t work in Excel 2011 as it’s getting kind of old now and this template will not work in Google Sheets either. It seems to be working just fine for me in Excel 2011 for Mac though. Good Luck.

I have a more complex level of calculations to account for. I received payments upfront from each member of the group for a large expense, Some overpaid, some underpaid. There was also an additional Collective CASH pot that everyone contributed to which I used to pauy out expenses but now posess and need to distribute out.

How can I account for the funds that were paid already to ME personally, funds that were paid into the POT, Funds that are now remaining to be paid out to all…

Hi Casper, I have the exact same requirement, please do share if you find out a solution, I will equally share with you if I find anything.

I take it no one has found this one… this is exactly what I need as well…

I tried to download the spreadsheet several times (both basic and extended versions). The same result – Settlement section has the Value error just right after opening the file. Probably because I’m using the Russian version of Excel what is strange anyway

Decimal point in the formulae is wrong. Use a , – not the .

I think this will work for me., thanks!!!

Great spreadsheet. what happens when you get past 54 entries, and how to you enter payments – like person A paid person B to settle the amount due?

Hi Sheila! You can use negative amounts to calculate payments and get remaining balances… Good luck!

Hi Andrew, when I use large numbers the dont show anymore due to limited column width, the numbers end up showing like ####.

Is there a way that you can fix that? its fully protected on column format.

I have the same problem, is there a fix for large amount entries?

Unprotect the sheet then u can change the width of the column

How do I get 54 entries on a sheet? I can only get 11 on a sheet. Thank you.

Ha! I answered my own question. I had downloaded the basic expense calculator. I checked this website again and downloaded the extended version which has room for 54 entries. :-0

would plz guid me how to download the extended version.

I tried to download the spreadsheet several times (both basic and extended versions). The same result – Settlement section has the Value error just right after opening the file. Probably because I’m using the Russian version of Excel what is strange anyway

Regards

Akshay

Hi,

Just wondering if there is an easy way to change the currency from $ to £?

Its just a display thing – but i dont want to confuse my friends by asking for dollars!

Thanks

Ricky

Click on the cell and you’ll notice a $ in the formula. Just repalce it with a £ and hey presto! It at the end of the formula, here’s where I did it…..

‘….Settlement Transactions’!B4)&” £”&TEXT(‘Settlement Transactions’!D4,”#.00″)&”.”,””)

For figures exceeding 3 digits, the cells don’t show the entire value. Instead it is marked with a star. Because the sheet is locked, I am not able to extend the column length. Is there a way around this?

Sometimes, a person pays or is responsible for more people … that are not reflega here. The file must be editable, otherwise it is not useful. Thank you.

just unprotect the file and then you can change few things

Sometimes, a person pays or is responsible for more people … that are not reflega here.

The file must be editable, otherwise it is not useful.

Thank you.

How would you cater for if one person pays for multiple people (for example partners)

The settlements are largely wrong unfortunately. In testing different scenarios, many times the wrong settlement amounts are listed.

Sorry to hear you’re not getting good results! Can you share your examples?

I would like to use this for recurring expenses in the team. How do I reflect the amount being settled between two of the team members. I tried negative value and trying putting x on the party paying (also tried putting x on both payee and receiver) but doesn’t seem to reflect on the settlement

Brilliant spreadsheet that works a treat for me and saved me a ton of time. Would be great if there was the option to change the currency (I’m in the UK and work in £).

what changes need to be made if used on google sheets?

nvm. just add ArrayFormula(your formula above)

hi ,

this is really helpful

you are a genius …..

Hi,

Instead of splitting equally between the people by putting an ‘x’ is it possible to define % between the participants. For example is one person has to pay 50% and the others split equally the rest of the amount.

Hi Siva!

This is actually designed to split equally in order to keep things simple. If you want to split by different ratios, you can break your expense into multiple rows and assign the correct amounts to each person that way.

I have copied the worksheet within the workbook in order to track multiple categories. However when I do this the formula for the Settlement is the same on every worksheet (the original one) instead of reflecting what the settlement for that specific worksheet should be. Example: I am using this to track an investment property purchased by 3 people so I have copied the sheets to have one for the mortgage, one for utilities, and one for all other expenses and purchases. When I enter items on the expenses and purchases worksheet the settlement is still showing the settlement from the Mortgage worksheet. What part of the formula do I need to update to correct this.

There is a hidden worksheet where the calculations are done. Unhide it and you can decode the formulas. Good luck!

This file is great, would be even better if it had the possibility to give specific amount per users. Example, if I have a family of 6, i should pay more than a single person.

Therefore, instead of a cross, we would have a stake (like 1 for a single, 0.5 per kid etc.)

This way, Mickael with a family of 6 would have a stake of 4, Ben who is single a stake of 1)

How do I go past 50 line items? I tried to simply insert more rows in the middle, but that results in strange errors.

I’m currently splitting costs between three people for a trip. Two of the three people have paid for several transactions. It’s showing that only the person who hasn’t paid anything owes the other two, but the other two have not spent the same amount so there is some owed between them as well, but for some reason it won’t calculate to show that. Is there a way to fix this error so that it accurately calculates who owes who?

Hi Heather!

Will you please provide a screenshot or details of how you have the cells marked? It is difficult to help unless we can replicate the problem.

Thanks!

Andrew

HOW TO DELETE COLUMNS

Great tool, but can not use it as we do not have equal splits. Is there anyway to modify the sheet to fix this?

Using it for a dinner club, sometimes we have guests for which only one of us pays. Any way we can work with percentages?

I can see users have asked this previously but have not seen the answer.

Many thanks

Isn’t working for me, i have difference of many euros, but only shows cents to pay.

Can you help me?

Amazing spreadsheet. How can I extend this beyond the ~50 lines available to put in expenses?

Trying to get it to work on Google Sheets! Any tips?

I got this to work on Google Sheets by changing the validation on the “Who Paid?” column. Select all of the cells under “Who Paid?”, select Data > Data validation…; then instead of using the named range “Participants”, change the criteria to the list of names at the top of the columns. (In the Basic sheet, this is ‘Shared Expenses’!J2:N2 )

Then you’ll need to fix each of the entries in that column so it validates correctly; but of course you’ll need to do that anyway, since you’ll be changing the names at the top of the columns.

For some reason I couldn’t get it to work by defining the named range “Participants” to be the same set of cells. Not sure why.

Thanks Dan. Mystery solved.

What do you mean, “fix each of the entries in that column so it validates correctly;”

Changing to the correct names is done, and the pull-down is working with new names, but the spreadsheet is only partially functional. Most of Settlement Transactions is #ref!

Thanks!

works like a charm! just what I was looking for and needed. Able to split costs evenly between both 2 and 3 people on the same trip, depending on how many participated in the activity. Thank you for making this easy!

I have been using this to calculate monthly rent among 3 roommates. I used to just save a different file each month, but would prefer to have the whole year together with different sheets for each month. When I tried to do this, the current month settlement just copied the value from the previous month. I’m not super excel literate, so your advice to Jesee about unhiding the settlement worksheet and decoding the formulas didn’t exactly help me. Do you have more specific instructions? Thanks!

is there any way to calculate the person paid total means how much I have paid in the total of the purchasing individual

My question is going horizontal, there is room for 30 people. I have 33 people in my group and it did not work for remaining 3 people. Works fine up to 30 people

How can I unprotect the sheets?

Can you change the split ammount Like one person pays 82% the other 18% if so how ?

I HAVE AMOUNTS THAT ARE LARGER THAN THE FIELDS. CAN YOU PLEASE SEND ME HOW TO CORRECT THIS SO THAT I DO NOT GET THE “####” ERROR?

HI Andrew Roberts,

thanks for posting and sharing this. Found it very useful in calculating shared costs with my family after a big get together. I adapted your method to allow each line to be split arbitrarily – i.e. each participant pays a set proportion of the total. Also added a web data-service to get a currency table from XE.com. That way, the amounts owed in Sterling could be translated into Euro. This was very useful for us !

I don’t have the time or skills to make this into a robust tool, so if you wish just DM me and I’ll send you through what I’ve done so far. Maybe it could be the basis of a ‘v2’ model – since changing the ‘splits’ and adapting currency seem to be 2 common user requirements.

ES

Hello! I am unable to add more than 5 people. Am I doing something wrong?

Thanks!

Hi Andrew. After adding 27 people, the rest of the people (39 total) doesn’t show, down in the settlement. The amount is split by 39 but the names don’t show, down in the settlement section.

how do i add more rows in description column..

Did you find the solution? People are saying extended version but from where to download, please share the link.

Thanks

Jaswant

How can I add more expenses? I’m at the last line and when I try to insert a new row to add additional expenses, it’s grayed out. I’m on a Mac Excel. Doesn’t seem to make sense why this isn’t working. Please help!

Having trouble with this in Google Sheets. As suggested in this thread, since I could not find a range named “Participants” (only Header, Marks, and Payer) I created the pulldown value range in column F from “Workshop Expenses Shared’!I4:P4”, which is the row with the name of everyone.

In Settlement Transactions I have the initial values showing up: the names (as headers) and, on the Start row (row 3) the calculations in blue for the person who paid, and split cost between the remaining people in red. The rest of Settlement Transactions is #ref! errors.

Is the problem because of the missing “Participants” range. In the downloaded (and imported to G-Sheets) version, the “Who Paid?” pulldown says, “Invalid:

Input must fall within specified range”

Where should that range be? If I make it J4:Z4 (aka Header, aka names of everybody), it only gets me partway to having a useful spreadsheet.

I might just have to install Excel to see what I’m missing….

Hi John,

I think Dan’s comment above covers what needs to be done – The Who Paid column, the drop down cells need to have their Data validation range criteria as the row of names in the ‘Shared Expenses’ sheet. Or, alternatively you can change the validation to be a List of Items and set up the names separated by a comma

Hi there,

This worksheet has been very useful. I use it to track expenses for me and my partner.If we received a monetary gift from someone and want to apply that to our balances, how do you suggest I go about applying this gift?

I cannot for the life of me find where the extended version of this template is. Can someone please point me in the right direction?

downloaded this for me & my friend as we are always buying things for each other, so wonderful thank you so much

Hi, hope that someone can help on this thread!

I’m having issues on the Settlement Section of the sheet – this doesn’t seem to work both in a Gsheets version nor the downloaded Excel (with name changes and transactions copied) from the link in this post. Screenshot is visible here https://drive.google.com/file/d/1KyoWU9vEuwtjMg7eSfLYLEZwdQiLDI5f/view?usp=sharing

The Settlement says ‘Libbie pays Lingy $2603.22’ yet there are no owings between these two people. The formulas are a bit too complex for me to try digest. Hope someone can help!

The math for the settlements section is incorrect.

hi there, what about if PERSON A paid fully for PERSON B, therefore it is not a shared cost, but what PERSON B owes PERSON A?

Many thanks

How do I unprotect a sheet and delete rows?

Is there a way to add a column next to Settlement, with how much each person has paid so far. Example if person A owes person B $500 and pays $200, where can I log this so I can see their new total and show that they have already paid for either a specific item in the Description column or just in general.

Maybe if I can change the X to a check, that would indicate they paid or a x in the next to the settlement column, indicating they have paid in full.

Thanks!

What about shared taxes and tips that are proportionate to each of their expense.

This is awesome, however the who paid column wont let me change names on drop down list and says:

There was a problem

The data you entered in cell F5 violates the data validation rules set on this cell.