Group Shared Expense Calculator 2024 – Free Travel Expense Sharing Template
This Group Shared Expense Calculator template can be used to calculate each person’s share of expenses in a group. For example, when you go on a trip with your friends where multiple people pay for shared expenses. At the end of the trip, you would need to calculate how much each person owes and to whom. Another example is if you participate in a group event which needs multiple purchases.
We were on a family trip last week for 3 days with a friend’s family. We shared expenses for car rental, hotel reservation, fuel, etc. and both families paid for expenses at different times. When we returned from the trip, we had to calculate our share of the expenses. I built this template to help with that.
FEATURES
- Expenses can be split equally among all friends in a single step
- Expenses can be split unequal among any number of friends
- Expenses can be split unequal by % or Amount
- Report shows all balances among friends
- Calculates expense amounts for each friend
FREE DOWNLOAD
VIDEO DEMO
HOW TO USE
Step 1: Enter friends’ names. The template allows up to 12 friends.
Step 2: Enter the expense transactions
- Date: Date of expense transaction for your reference (Not used in any calculation)
- Transaction: Description of the transaction for your reference (Not used in any calculation)
- Amount: Expense Amount
- Category: Category of expense (Not used in any calculation)
- Paid By: Person who paid for the expense
- Split Type: There are three ways expense can be shared/split.
Let’s take expense for Coffee to illustrate the three split types.
Split Equal: When the expense is shared equally among all the friends.
This transaction does not need any further data entry. In this case, the $12 will be shared among all 8 friends equally. $.1.5 each.
Split Unequal – %: When expense is shared unequally among friends and we know the share in %, we can use this split type. One example is when only 2 of the 8 friends had Coffee and their shares are 40% & 60%. We enter the shares in appropriate columns named after the friends.
We will also use this split type when we have all the 8 friends sharing in unequal % share.
Note: when entering %, please enter in decimals. For example, enter 50% as 0.5.
Split Unequal – Amt: When expense is shared unequally among friends and we know the share in actual amounts, we can use this split type. Following the same coffee example, when only 2 of the 8 friends had Coffee and their shares are $4 and $8.
Data Validation
There are some validation rules built in the template to ensure accurate data entry.
- Name entered in Paid By column should be present in the Friends list in the Home sheet
- When using ‘Split Unequal – %’, the % entered for all the friends should add up to 100%
- When using ‘Split Unequal – Amt’, the amounts entered for all the friends should add up to expense amount
If there are any errors, you will see it in the last column Validation.
You will also see visual indicators on the expense transactions.
Purple indicates transactions where the shares (% or Amount) are under the expenses and the Red indicates transactions where shares (% or Amount) are over the expenses.
Please ensure that there are no errors in order for the report to provide accurate balances.
Enter each transaction in a new row. If you are new to Excel tables, please read this article on Introduction to data entry in Excel.
Step 3: View Report
Once we complete entering all the expense transactions, we can move to the report sheet.
The sheet shows all the individual balances on who owes whom how much. We can also see the total amount a person owes and a person is owed to.
We can also select one friend to highlight that friend’s details and summary.
The summary at the top shows Amount Owed To, Amount Owed From and Net Balance. The details show the Amounts Owed To, in Green and Amounts Owed From, in Red.
If there are any errors in data entry in the Expenses sheet, you will see a warning message in the Report sheet.
We can also see the amounts paid by each person, total expenses by each person and the net balance.
If the Net Balance is negative, that means the person will have to pay to others more than he will receive from them. Since we are distributing expense amounts equally, sometimes we end up with a rounding difference of a few cents.
Changing Currency
You can change the currency format displayed to suit your currency. Just select the currency cells and press Ctrl +1.
Choose the currency from the list and press OK.
If you find this shared expenses spreadsheet template useful, please share the template with your friends. If you have any suggestions to improve, please post them in the comments.
89 Comments
Hello, thank you for the template – AWESOME!
Is there a possibility to alter the split type “Split Unequal – Amt”? I need one where the expense is shared between the people equally in addition to the amount each person is paying on their own. Do you know what i mean?
Thanks in advance!
Best wishes.
Thank you for your kind words and for sharing your feedback – we’re glad you’re enjoying the template!
Regarding your query on the “Split Unequal – Amt” option: currently, the template does not have a built-in feature to automatically split an expense equally and account for individual contributions in the same transaction. To achieve this, you would need to enter two separate transactions:
1. Enter the total expense as a “Split Equal” transaction to divide it equally among participants.
2. Enter each person’s individual payment as a “Split Unequal – Amt” transaction to reflect amounts paid separately.
This approach will give you both the equal share and the individual contributions correctly.
We appreciate your suggestion – it’s a valuable idea for future enhancements!
Best wishes.
hello
Your excel is super. I need one small help . I have added 9 friends here. As soon as one Friend paid I have updated the Expense tracker which is reflecting in the report sheet correctly.
But when other 8 paid to the above one friend who paid initially , where I will update 8 friends payment ??
Thanks Avijit
Thank you for using our template and for your kind words.
To record the payments from the other eight friends, please enter them in the same Transactions sheet, one below the other, for each individual payback. While doing so, use the Split Unequal – % option and assign 100% to the friend who initially paid. This will ensure the repayments are reflected correctly in the report.
Best wishes.
That’s correct and it worked.
Thanks for all your support and guidance. I may need additional help , will come back to you.
Avijit
You’re very welcome! We’re glad to hear that the solution worked for you.
Please don’t hesitate to reach out for any further assistance—we’ll be happy to help. You can submit any queries or requests via the following link:
https://support.indzara.com/support/tickets/new
Best wishes,
Can I add more rows for expenses?
Thank you for showing interest in our template.
Yes, you can add more rows to track the expenses and there is no limit on it.
Best wishes.
Hello, Is there any way to add more people to increase the count from 12 to 15?
Thank you!
Thank you for showing interest in our template.
It involves some formula level changes. We can take it as a customization project for a fee. Please reach out to us at the below link for estimation:
https://support.indzara.com/support/tickets/new
Best wishes.
Hi, is there a way to have a drop-down by the transaction just like by the name ?
Thanks
Thank you for showing interest in our template.
By default the template does not have the requested feature. We can take it as a customization project for a fee. Please write to us at the below link for estimation:
https://support.indzara.com/support/tickets/new
Best wishes.
do you have this file in google sheets ?
Thank you for showing interest in our template.
Currently we do not have the template in Google Sheets. But you can upload the template to Microsoft OneDrive and use the template online on browser. It is similar to using the Google Sheets from the Google Drive.
Best wishes.
hi, when i downloaded the file, I already noticed that in the “report” tab, there is already 1 flag for error. Why is it so? Thanks.
Thank you for showing interest in our template.
It is flagging the blank row in Expense table as error. On filling the same the error will be removed.
Best wishes.
Thanks Indzara! I have tried to fill in the rows from row 7 onwards in the epxenses tab but the error flag in the report tab is still there 🙁
We regret the inconvenience caused.
I am unable to replicate the same in my end. On entering valid data in Row 7, the flag in the report is removed. You can check the reason for the flag on Column S in the Expenses sheet.
If you are still facing issue, please share your copy of the template to us at the below link to check further:
https://support.indzara.com/support/tickets/new
Best wishes.
Is there a google sheet version?
Thank you for showing interest in our template.
Currently, we do not have a Google Sheets version of the template. But our Excel template can be uploaded to Microsoft OneDrive and use Excel Online to use the template. It is similar to using Google Sheets from Google Drive.
Best wishes.
Hi, the excel is great , but there is a problem , if you do with 11/12 friends and we split the cost equally to all , you will see one of the friend cost is less than the others , i dont know if its formula error or something , but you may try with different split type and ensure the accuracy before sharing with friends
Thanks
Thank you for using our template and sharing your valuable feedback.
I would like to assure you that the template formula is correct and the template is available in our website from 2016. I just tested the template with 11 and 12 friends name and the split equal feature. But I am unable to replicate the highlighted issue. Hence requesting to share your copy of the template with sample data having the highlighted issue at the below link to check further:
https://support.indzara.com/support/tickets/new
Best wishes.
This is a great tool. However, have found it difficult to upload to Google Sheets – I get lot of messages saying “Formula Parse Error” and I’m not sure how to correct them.
Thank you for showing interest in our template and sharing your valuable feedback.
Our Excel template is not supported on Google Sheets and vice versa. but you can upload the Excel template to Microsoft OneDrive and use Excel online to use the template. It is similar to using Google Sheets from Google Drive.
Best wishes.
This is a great expense calculator. But rather than wait until the end of a trip or project to settle up, I’m curious on how to enter amounts paid to other members of the group, and still keep the expense log open for future ongoing expenses. I have an earlier release of this expense calculator that provided another option called ‘Settle’ from the ‘Split Type/Settlement’ drop down menu.
Thank you for sharing your valuable feedback.
You can enter a settlement in the middle of your trip by using the split unequal amount in split type on column F and enter the settlement amount to the respective friend.
Best wishes.
Hi, in the expense sheet, how many lines we can use? On line A21, it shows friend not recognized. I found the names will keep decreasing for every extra line added.
Thank you for showing interest in our template.
There is no limit on Expense entry. The name will not be decreased, when you select a drop-down on an empty cell and the drop-down range has some empty cell as well (Friend List), it will show the blank space which will be after friends name. You will need to scroll up the drop-down to view the friends name you can also press up arrow key in the keyboard after opening the drop down.
If you are still facing issue, please share your copy of the template at the below link to assist further:
https://support.indzara.com/support/tickets/new
Best wishes.
Hi!
Excellente template. Quick question, Is there a way to mark as “paid” or enter the amount paid for a person that pays the money owe to that person?
Example: If Nakul owes Bheem $4.62, is there a way to consolidate that on the spreadsheet? like zero out the balances between the group?
Hopefully i explain myself.
Thanks
Thank you for showing interest in our template and sharing your valuable feedback.
Yes, you can enter it as a expense and select un-equal amount and select the paid by as Nakul and paid amount under Bheem.
Best wishes.
This is an excellent template and thank you very much for this. I just need a help if possible. I would like to split the expenses with only 2 people. So how do I display the reports of both the people instead of having to select from the dropdown in B3.
Thank you for sharing your valuable feedback. You are welcome.
The from and to table will show the balance amount to be paid to and from for all friends without selecting the friend. Only the value in row 3 is impacted according to the friend selection.
If you want to look the row 3 for both the friends, you need to add a row below row 3 and duplicate the formulas present in row 3 and edit the formula references. The password to unprotect the sheet is indzara.
Best wishes.
Great spreadsheet, thank you very much for this.
I’m looking for a little clarification on the report page. What exactly does paid mean vs expenses?
I’m looking to use this for shared credit card transactions so my wife and I can keep track of who pays for what, and who owes what.
Thank you
Thank you for sharing your valuable feedback and you are welcome.
Regarding paid and expenses on report: If you purchase an item for 1000 using your credit card with a deal of 50 50 between you and your wife. Then the amount of 1000 is paid by you and both made an expense of 500 each. If you buy an item for 1000 on behalf of your wife, then you paid 1000 and your wife has spent(Expense) an amount of 1000.
Best wishes.
Hi thank-you for this excellent template. I was wondering if you could give me instructions on how to add a filter based on category to the report sheet? Example: I would like to see net balance / owed to / owed from related to friend Bheem but only for the category food.
Many thanks
Kind regards
Anna
Hi Anna,
Thank you for showing interest in our template.
This We take customization projects for a fee. Please write to us at the below link for estimation.
https://support.indzara.com/support/tickets/new
Best wishes.
Hi! This is a fantastic template. Completely accurate compared to other ones. Is there a Google Sheets version? When I try and import it into Google Sheets, it said that there are some formulas that it can’t recognize. Specifically things like “C_NUMFRIENDS”.
Thanks!
Thank you for showing interest in our template.
Currently, we do not have a Google Sheet version of the template but you can upload our template to Microsoft OneDrive and use Excel online to use the template. Our Excel templates cannot be directly used as a Google Sheet templates since some features used in the template is directly not supported in Google Sheets and vice versa.
Best wishes.
Thank you for sharing this template!
This is excellent, thank you. How would I modify so that the split type is always equal but I select which friends pay for each item. I.e. One transaction is split by 4 friends, but another transaction is only split by 3 people. I want to be able to check/uncheck which friends instead of doing (split by percent). Do you follow? Thank you
Thanks for your message.
Can you please raise a ticket with our support desk at support@indzara.com – we will be happy to help you in this regard.
Best wishes.
Hi if money is owed to a person and that money is paid how do you update this on the spreadsheet?
The money paid is updated against that particular transaction he/she is owed to the person who paid it. If you have any more questions please reach us at support@indzara.com
Hello! We are travelling to different countries with different currencies. How can I add the expenses in different currencies into one table and convert the expenses into one base currency? USD, EUR, RUB, GBP
We would have to have the currency as an input column and then have a table for currency conversion rates. then, we have to write a formula to convert to one base curency.
Best wishes.
Hi It would be super easy to distribute this excel if it was a google sheet. Have you thought about duplicating this amazing tool in to google sheet?
Thank you
Thanks for using our template and sharing positive feedback. The template is designed to work on MS Excel. Some of the features would be compromised on Google Sheets.
However, you may distribute the file through OneDrive. But only one person can update it at a time.
Best wishes
Hi, If a friend pays back during the trip how do I enter that into this template? There doesn’t seem to be an option for settled amounts. Please let me know if this is possible and if not, please let me know if you are able to do this for me and I will compensate.
Thanks for using our template.
This template is designed to distribute the amount owed on each member. We have noted your feedback and might add this feature in the next release.
Best wishes
Hi,
I want to divide Utility bill among a group of room mates.
I got an electricity bill for the amount of Rs. 1300 for a month of 30 days.
A- Stayed for 30 days, B- Stayed for 30 days, C- Stayed for 12 days & D- Stayed for 7 days.
How to split Rs. 1300 among we 4 peoples.
Hello
You may all the days and then divide it in proportion.
In this case, the total number of days is 30+30+12+7= 79
Total Bill: 1300
A’s contribution is (30/79)*1300= 493.67
B’s contribution is (30/79)*1300= 493.67
C’s contribution is (12/79)*1300= 197.46
D’s contribution is (7/79)*1300= 115.18
Total is 1300
Best wishes
There appears to be a calculation error in the the amounts owed. The coloured boxes at the top of the results page don’t populate with any data either. Have I done something wrong with setting this up?
Thanks for using the template.
Please ensure that none of the formulas or links are broken. Please download a fresh copy and check. If it still does not work, please email your issues along with your file to contact@indzara.com
Best wishes
hello, I am using this template to track expenses on shared lodging for a trip in different cities, but I am having difficulties in adding as many columns in the expense page as many friends I add in the Home page. Iy might seem stupid but could you help me?
Lisa
Thanks for using our template.
Please follow the video demo and the text in the template page. In case, it still does not work, please email the template along with the list of issues to contact@indzara.com
Best wishes
I currently use a “Group Shared Expense” App called Settle Up and while it does work most of the time and has reasonably decent support, It will miss an entry if the device (iPhone) that was used to enter the amount is not currently connected to the internet, it will cause a discrepancy in the account balance of other synced devices.
I don’t think that this will happen using MS Excel through the MS cloud based “OneDrive”.
Any comments or suggestions for a cloud based use of your template?
Please try posting the file on OneDrive and use. I have personally not used in mobile.
Please let us know.
Best wishes.
This does seem to work on Apache Open Office. Any one has tried?
I meant it doesnt work
Hello
Thanks for using the template in a different environment. We have created and tested our templates on MS Excel. We cannot assure the productivity of templates on other platforms.
Best wishes
Hello Sir/Mam,
Aweosome Template. Thanks a lot for sharing the Sheet and saving people like me who are not good with Excel. It really helps in reducing lot of work and makes expense calculation and settlement neater.
I just had one request Sir, is it possible to increase number of Friends from 12 to 24? If you can guide me on doing the same that will be really a great help to me.
Please kindly advice. Thank you very much for your help. Appreciate your help Sir!
You are welcome. Thanks for the feedback.
Insert columns after column R in the Expenses sheet to add more friends.
In the Report sheet, we have to insert more columns (after column N) and rows after row 19. Use column N as reference for formulas and row 19 as reference.
Update formulas in column P for total.
Insert rows after row 37 for summary. Use row 37 formulas as reference.
Update formulas in row 39.
Best wishes,
Hi,
Thanks for this beautiful work. I also want to add a few more people to the list, but the Report sheet is protected and needs a password. What is the password?
You are welcome.
Please use indzara as password to unprotect.
Best wishes.
Awesome template thanks for sharing.
Thank you!!!
how can I adjust the payment if some friend give some amount in advance or more amount then It owed to pay.
Hi, could you tell me the necessary steps to add a 13th friend to the calculation? Thanks!
Nevermind, I found a way.
Awesome template, thank you very much, you saved me a lot of trouble!
Could you let me know how you added the additional person?
Hello
You can add up to 12 names on the home sheet among whom the expenses can be divided.
Best wishes
Hello FSJ,
Could you please guide me on the process on adding additional friends to the excel. I need to extend the list for 22 people. Please advice. Thank you
I Have downloaded the sheet nd uploaded it in google drive.When i have added the friends list it is not pulling the expenses sheet.Please help
Hello
Thank you for using this template.
Some features of this product does not work properly on Google Drive. You can use this on One Drive to share the details with others.
Best wishes.
What to do when balances are settled?!
The balance will display 0 when they are settled. We just keep entering new transactions as usual.
Best wishes.
How do you input a payment made by a person and where will this reflect?
Hello
Any payment made will reflect in the Expenses sheet.
Best wishes and regards
How could I make this into something I can track month to month?
I’d like to track expenses throughout the year. and how can I put the report to the bottom of the expense tab?
This template allows tracking expenses throughout the year. However, the report shows the current status of the balances owed. It does not break down by month.
Personal Finance Manager https://indzara.com/2013/05/personal-finance-manager-excel-template/ allows tracking expenses by month, but that does not support shared expenses balance calculations.
Best wishes.
Neat work! You saved my day after vacation with friends. Thank you so much!!
Thanks for feedback. Glad that it was useful.
Best wishes.
Thanks for the great sheet! Can you please tell me how to unprotect it? It have a password!
You are welcome. Please see this article for how to unprotect: https://indzara.com/faq-items/unprotectingprotectedsheets/
indzara is the password.
Best wishes.
Hello,
Great spreadsheet, i have some suggestions. On the last tab, you can minimize payments between people. You can see who is owed to the most and who owes the most and have that person pay. You can start there and move your way down until everyone who is owed money gets paid. I have this in excel if you want to see.
Thank you. I agree with the idea. Thanks for the suggestion.
Best wishes.
Hi there – on the second tab (Expenses) I keep getting an error for Data Validation saying “Friend Not Recognized.” Also no names are populated on row 5 of that tab either, in white font it just says Friend 1, Friend 2, etc. even though I added names to the “Home’ Tab.
Thanks!
Thanks for using the template. It appears that the friend name entered in Expenses sheet is not available in the Friends table in the first step. Please check that.
If there are further questions, please email the file to indzara at gmail. Thanks. Best wishes.