How to Split Bills and Share Expenses Using a Free Excel Spreadsheet

Settle Bill Small

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

Split Bills Graphic Logo

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!

Download the Shared Expense Calculator for Excel

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 (download) in the bottom right of the embedded file above.

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

GetItNow

How to Share Expenses and Settle Up IOUs

How to Use It

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 ThumbnailAndrew 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!

91 thoughts on “How to Split Bills and Share Expenses Using a Free Excel Spreadsheet

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

    1. 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.

  2. 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…

    1. 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.

      1. 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

  3. 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

  4. 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?

    1. 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.

      1. 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

  5. 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

  6. 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

    1. 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″)&”.”,””)

  7. 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?

  8. 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.

  9. 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.

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

  11. 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

  12. 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 £).

  13. 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.

    1. 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.

  14. 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.

  15. 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)

    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

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

  17. 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?

    1. 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

  18. 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

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

    1. 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.

      1. 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!

  20. 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!

  21. 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!

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

  23. 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

  24. 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?

  25. 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

  26. 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.

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

      Thanks
      Jaswant

  27. 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!

  28. 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….

    1. 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

  29. 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?

  30. 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?

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

  32. 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!

  33. 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

  34. 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!

  35. 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.

    1. 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

  36. 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.

  37. 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

  38. 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??

  39. 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.

  40. 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?

  41. 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.

  42. 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.

  43. 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

  44. 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?

  45. 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.

  46. 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.

  47. 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??

Leave a Reply

Your email address will not be published. Required fields are marked *