The Ultimate Expense Splitting Spreadsheet

I love spreadsheets for all facets of life and this next spreadsheet is for budgeting, and to help you get a grasp of your finances. This spreadsheet is for those looking for a way to split expenses between people. This spreadsheet works for splitting with two people or even up ten people and is the ultimate tool to help keep finances together during a big group trip like my ski trip to the Dolomites in Italy. Nothing is worse than not knowing how much someone owes you at the end of an expensive trip!

Here is a list of my other spreadsheets that may help you in your path to financial success!

• The ultimate spreadsheet to track all your credit cards, sign on bonuses, and annual fees.
• Also, check out my travel itinerary planning spreadsheet which is perfect for organizing and planning your trip!
• As well as my Restaurant List Tracker spreadsheet for keeping track of all the restaurants you have been to in your hometown or abroad.
• For those looking to track their stock portfolio, this is the spreadsheet I use to track my stock investments. In addition, I’ve been trading and selling options for the past few years to generate extra income during early retirement and I’ve also created an options tracking spreadsheet for this purpose.

Why use a spreadsheet to split expenses?

---

You’re probably wondering why you should use a spreadsheet to split expenses. If you’re asking this, then you probably don’t need it because it’s just a few expenses. However, let’s say you travel in a big group or with your significant other for long periods of time. You can easily rack up crazy amounts of transactions between restaurants, bars, taxis, etc. and it will be a headache to sort through in the end.

Conversely, if you’re traveling with multiple people and everyone is paying something else, then it’s almost impossible to properly sort out who owes what. This spreadsheet will help you rectify that situation! In fact, I used this spreadsheet to split expenses with multiple people on an epic trip through Bali.

What about using an app like Splitwise?

I absolutely love using apps to help split expenses. Splitwise available in the Play Store and Appstore is my favorite app of all. The interface is very intuitive and the calculations are spot on. It is also great for splitting between multiple people and is free to use.

However, what Splitwise doesn’t do well is if you don’t add transactions regularly. I found myself in numerous occasions forgetting to add expenses on a timely basis. By the end of the trip, I had hundreds of credit card transactions to add between myself and others that it was incredibly time consuming to add all at once.

Using a spreadsheet is great because I could simply export the transactions in my online banking for my credit cards and easily aggregate them using my expense split spreadsheet. When you have hundreds of expenses, this will make it easier to see everything all at once and have a better understanding of your expenses.

Finally, Splitwise saves a lot of features for its pro package which you must pay for. One of the main things that I always felt was missing was the ability to add expenses in different currencies. Often times, the need to split expenses with people arose from traveling to a country where we had different currencies. This was especially complicated when another person’s finances were done in a different currencies. Figuring out the exchange rates and such was always annoying. My spreadsheet uses exchange rates from Google Finance so you can add transactions in different currencies.

Settling up numerous times with someone

Another benefit of using this spreadsheet is if you are traveling with someone for long periods of time and want to settle up on occasion, but at the same time keep a list of all your transactions. You might want to do this if your credit card bill is due and you need the cash sooner than later to pay off the credit card.

This spreadsheet is not for tracking expenses and budgets

If you’re looking for a spreadsheet to help you plan your personal finances better as far as budgeting goes, this is not the spreadsheet for you. You’ll want to use my budgeting and expenses spreadsheet which will let you track your expenses and income.

Expense Split Spreadsheet

---

The spreadsheet I created focuses on splitting expenses between two people or even a group of people.

You can add as many transactions as possible and split it however you want. The final amount will be listed that shows how much money someone owes to the other person.

The spreadsheet is simple, yet effective. Just make sure you update it on a regular basis. I would recommend spending a few minutes to do this when you have time and to not let it accumulate. It’s still better than Splitwise if you do let it accumulate for a long time but it will just make things easier for you!.  I don’t always do the exact amount for different expenses because it takes a lot of time but that is also up to you. You can use this spreadsheet if you’re a single person as well as if you’re a couple (just add everything up!).

Keep in mind that this spreadsheet is also what I use to track my own expenses. I will be making updates live and will add additional features to this spreadsheet as time goes on so make sure to check in for updates!

Download Expense Splitting Spreadsheet

The spreadsheet is in Google Sheets because if I update it in the future, it will be easy to access from anywhere with an internet connection. I prefer this to Microsoft Excel. It’s easy to download it and use with Microsoft Excel as well.

To download offline, click the red button above, then click file > download as > Microsoft Excel

To use it with your own Google profile, just click file > make a copy

Using my expense splitting spreadsheet

---

As someone that works with spreadsheets regularly, I’ve included some functionality like conditional formatting and formulas that an Excel novice might not understand. I will explain everything necessary for those wanting to get the full use out of the spreadsheet.

For the most part, anyone with any excel experience should just follow my spreadsheet and populate accordingly. Do not touch cells with formulas because everything is intricately linked. All cells that you should update have been color coded with gray cells.

Expenses Sheet

This is where you will do most of your work. I think the sheet is quite simple but make sure to read the following things just to have a better understanding of the sheet.

Most of manual inputs will be on the light green cells. This is where you can add your transactions. The columns I included in this section are what I find the most useful for my purpose. If you need more granular details, you can definitely insert your own column. I mostly just export my transactions in my online banking account and then copy it accordingly to this spreadsheet.

• Item Name: Self Explanatory
• Date: Date that the transaction was incurred
• Currency: The currency that the transaction is in. I find this useful if say your friend’s finances are in Euros but yours are in USD. He/She pays for something in Euros but you want to be compensated in USD since you paid for the majority of the expenses.
• Amount: The nominal amount of the transaction
• Amount (USD): Amount converted to USD if the currency was something besides USD. Since my finances are in USD, I keep this spreadsheet in USD. If your finances are in Euro for example, feel free to change it accordingly.
• Paid By: This is a dropdown menu with the names of the people you’re splitting expenses with.

Now we move on to the areas with the orange cell shading:

This is pretty self explanatory as well. Add the names of the people you’re splitting expenses with. In the example I have 4 people that I’m splitting expenses with.

There is then a check mark box for who the expense is split with. There is the option to have an expense but it’s not split in 4 ways as per the above screenshot. For example, Johnny could pay for one transaction but Jay is not part of that transaction. Therefore, you would just check the boxes Johnny, James, Jacob.

To mass check the boxes, simply copy a checked box and paste it over all the other boxes you want.

Balances Sheet

The balances sheet is where you go to settle up all of your expenses. This sheet will sum up the total expenses incurred by the group, as well as who is owed money (who needs to pay money). Using the same example as the picture from above, we have these results:

Here is a rundown of all the columns:

• Name: Self explanatory
• Paid: How much this person paid out of their pocket
• Owed: How much this person owes to the group based on the expenses listed in the expense sheet
• Difference: The difference between Column C and D. If this is Green, it means this person is owed money. If it’s red, then this person owes money

The spreadsheet is not smart enough to say who owes who money unfortunately. In this example above, you can see that Johnny and Jacob are owed money and James/Jay owe money. However, the amounts are not clear who should pay what to who. In these examples, you’ll just have to calculate who owes what.

The easiest way to do this is have James pay Johnny the full amount he owes to the group ($1,412). Johnny is then owed $569. Something like this:

Afterwards, Jay can then pay $1,066 to Jacob, and $569 to Johnny. Then the whole group will be settled!

If you are splitting between two people, this will be a lot easier and you won’t have to do any extra steps as like above.

FX Rates

---

FX Rates is the final sheet in this spreadsheet and probably the most complicated. I use Google Finance’s Currency history to display a historical table of the exchange rates. Google Finance is an incredibly powerful function and I’ve explained in detail about this function in my Google Finance spreadsheet post.

This means, you will have a different exchange rate every day based on the closing value of the day. This value will then be matched to the date your expense was added and the calculation will be made accordingly. I’ve added in a few currencies that are popular like EUR, GBP, CAD, and MXN but of course depending on the trip you’re taking you might have different currency needs.

Adding a new FX Rate

To add a new currency pair, simply copy one of the existing formulas that I’ve used like the below:

=GoogleFinance(“CURRENCY:USDZAR”,“close”,“1/1/2022”,DATEVALUE(today()), “DAILY”)

You’ll want to change the “CURRENCY:USDZAR” text to whatever currency you’re looking for. If you want the Turkish Lira for example for a trip to beautiful Istanbul, then type in “CURRENCY:USDTRY”