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. You can read more of his writing on his personal blog at NapkinMath.io.
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!