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

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

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.

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?