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!
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
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…
same here lol
If you put the initial cash payment in a first line stating: Cash payment Hassan $500 with all participants x’d, then “Cash payments Carl; $400 – all participants x’d, it will account for those initial payment contributions
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
This was greatly helpful for expense split for our trips. Great tool!!
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)
This is my issue too – we go as a group of 6 families- and some have 2 members some have 4 some have 5 .. so when we split we split by heads – a family is referred to by a name who has x number of individuals in the family -so the totals should get split accordingly
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.
Click into the drop-down cell > select data validation or right click with the mouse to find that option > in the criteria selection section update the range by highlighting the column headers with the updated names of your party. This will fix the error you receiving
This excel sheet is very useful for me, just i want to know how to add extra rows after using all 50 rows which come with original expense sheet ??, please explain me in detail, I tried all ways to add extra rows but failed due to some formula or cell locks. your reply will be valuable for me. Thank you.
I am looking for a solution with excel to share expenses for 5 families each with different number of members in each family.
also, can it track expenses over a number of days and transactions paid between the members
How to change the currency & the column width?
This is so good for keeping track of who has paid for coffees each day and who for but it’s starting to play up when i add new lines everyday?? Like i right click on row 12 to insert and it inserts a new line above row 12 so it puts all my dates out. How can we get it to add new lines after it’s used up the 12 that you put in your template??
Great Spreadsheet and works perfectly for me. Just one thing though, how do I get the columns to adjust width so that the number shows and the “####” are not rendered on screen.
How should you include an individual’s gift contribution to the group of $1000 in order to make the entire group’s payments lower?
For example, Johnny decided to cover $1000 of the total trip so that everyone can afford the trip. How would you include this information in the chart?
I am trying to copy this spreadsheet into google sheets. I noticed that the ‘Settlement Transactions’ sheet is key for the final output but I can’t seem to find that sheet. Not sure what to do to fix this.
This is very helpful!
How can we make it so tax and tip are figured according to their own personal total, and not splitting everyone’s grand total?
If someone has a $30 meal it isn’t fair for them to have to split the tax and tip for someone else’s $50 meal.
If after, for example, 12 entries, the money owed to each other is settled, is there a way of entering this (with the new 0 balance recognised) and then being able to carry on with new entries?
Or is the table only good for one ending balance and settlement?
Take the opportunity to thank the originator for making this great sheet available.
Greetings from Portugal
I have two people to be added to the spreadsheet – instead of a 50/50 split of all amounts added to the sheet i need to split the total amount payable by each person in a ratio of 4:2 Example igf the total added to the sheet was £100 then the settlement figure should show Name 1 owning 66.66 and Name 2 owing 33.33. How cold edit the code to achieve this please?
Thank you for this, worked great for our ski trip
Hey, just wondering after all this, is there a way I can use it to find what was the total of my expenses? I paid for most but not all, I’d like to know what I alone had spent.
1. Dont see an option to extend the column width and hence the numbers on the column / cell is coming as “XXX” and it does not allow me to modify the column width.
Hi Andrew – this is a great tool and I’d much rather keep our shared expenses in Excel than an online tool….but. The allocations of who owes whom was fine for the first few entries which were all paid by the same person, but after entering payment made by a second member, the totals are incorrect. Is there a way I can send you the file to have a look at??