fbpx
Search
Close this search box.

How to: sharing trip expenses using excel

Share

Facebook
Twitter
LinkedIn

Yesterday we were calculating our Washington trip expenses and it occurred to me that if there is an excel template where I could enter the trip expenses and who paid what to find out how much we need to pay up / collect, it would be great. I looked around for few seconds, couldn’t find anything. So I went ahead and built expense sharing worksheet on google docs. Feel free to use it for sharing your trip / party / picnic / apartment expenses.

Here is how I have done it.

Sharing Trip / Party / Picnic / Apartment expenses using Excel

  • First I have created a list of people who need to share. Just for simplicity I have limited the no to 10. You can edit the excel and change it if you want.
  • Then I have created an expense table like the one shown below. The requirements for this are simple, (1) should be able to capture descriptions of each spending (2) should be able to specify who footed the bill (3) should be able to mention the amount (4) should be able to exclude people from sharing a particular expense. Again, for simplicity sake I have limited the number of people who can be excluded from sharing an expense to 4. You can always edit this and change the formula accordingly.

    Sharing Trip / Party / Picnic / Apartment expenses using Excel

  • Now next to the list of people I have added 3 columns to show (1) how much that person has already paid (2) how much is his/her share (3) what is the difference.
  • The formula for (1) how much already paid is straight forward sumif(), the formula for a particular person’s share is something like this,

    If the row has a person
    (total expenses / no.of people) – (total expenses excluded for this person / no. of people)
    else “0”

    The actual formula involved sumif() over the four columns since each expense can be excluded for maximum of 4 people.

  • Once I have entered data the output looked something like this,
    Sharing Trip / Party / Picnic / Apartment expenses using Excel
    Hence both Yerry Jang and Dointy Paired Hilbert need to collect 237 $ and 90 cents from Barren Wuffet, Gill Bates and Beff Jezos put together. Not bad 😉

How to use it?
Just save the expense sharing excel sheet from google doc [or download the expense sharing excel sheet] to your machine / account. Enter the expenses in columns B,C and D. Enter the people names in column L. Incase you need to exclude any one from a particular line item, just enter their number (it will be next to the person’s name in column K]. As you enter the expense details, the Column O displays the actual dues.

Feel free to comment / extend / share / suggest. Your turn…

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

22 Responses to “How to: sharing trip expenses using excel”

  1. Sabs says:

    Hi , Dropped in here through india uncut. Execllent effort to capture the expense statement. Have personally done this many times, but never had the patience to pull out a format like this.

    Just have one small query. As per your example , the total amount spent is 714$ but if I add the shares , it adds upto 686$ only. Shouldn't the amount spent and shares for each person match ?

  2. Chandoo says:

    @Sabs, welcome to PHD, thanks for appreciating my post.

    I am sorry, but when I try to add the shares {139, 137, 142, 145, 151 } it adds up to 714. Are you referring to the image or one of the excel sheets. Let me know if there is some confusion?

    • Sara says:

      Hi Chadoo, great work indeed! I have filled in the sheet but have a problem though. I get the #NAME error throughout all colum I and consequently in N and O. Any tips?
      Thanks!
       

  3. Sabs says:

    Sorry about bombarding you. Your version in the download is different from what you have in google docs. The google doc version and the screen shot in your post doesn't have the problem I mentioned.

  4. Chandoo says:

    @ Sabs, hey thanks for pointing out the mistake. Let me update that faulty excel. You have a good day 🙂

  5. Mahin says:

    Hi Chandoo:

    I personally developed on for myself and made it public. you can find it here http://www.screeperzone.com/expensesharer . It reduces the number of money/check transactions . thought it might be useful. Yours is good too.. thanks!

  6. Mikael Grave says:

    Hi Chandoo,
    Another simple online solution to handle shared expenses:
    http://shortreckonings.com
    The user interface was carefully thought to make expense entry as quick as possible. For example, the tool does auto-complete descriptions so that you never need to enter twice recurring expense descriptions.
    You can enter non-even split, for example fixed amounts, percentages or any kind of sharing weights.
    Enjoy!

  7. Steve says:

    Hi,

    I found yet another free tool for managing shared expenses:
    http://www.expensesharing.com

    This one differs that it allows to invite other colloborants,
    and is easy to use. In addition it is capable to handle currency conversion.

  8. Bourne says:

    Thanks for the post.. this template is very helpful... http://www.screeperzone.com/expensesharer is also a very helpful application.takes an extra step to reduce the number of money transactions between participants.

  9. Sandeep says:

    Great effort!...I also struggled quite a bit to share expenses in a fair manner when I was staying with roomies. Being somewhat of a techie myself, I came up with this :

    http://sandypai1983.wordpress.com/2010/07/21/great-excel-tool-for-sharing-expenses/

    Hope you find this useful. Excel is amazingly handy and useful in all sorts of basic analytical stuff.

  10. vivek says:

    How can we use this for ruppes?

  11. Jeremiah Minifield says:

    In your example, if you exclude 1 person, the amount should increase not decrease. I can't see how the exclusion works. Please explain. Thanks.

  12. Narendra Agrawal says:

    Hi,
    There is a small issue in the formula. It is actually more of an XL issue. In the example if you check the 15th item and see the per head calculation, you will realize that the final value is incorrect.

    Here in the end, in the denominator of formula it comes as "10-5-3". XL instead of returning 2 returns 10-(5-3)=2. Hence u get a 18/2=2.3 instead of 18/2=9

  13. jm says:

    I think the formula in column I should be as follows:

    =D4/(10-COUNTA(WHO_ALL)-COUNT(E4:H4))

    Rather than COUNTIF(WHO_ALL, ""), which is counting blank rows in the WHO_ALL range, when it should be counting actual participants.

  14. Jeff Weir says:

    My god, Chandoo...here I am combing your blog archives in chronological order, and this is the first post on Excel on your blog, despite 3.5 years of blogging before this!

    And the titles of some of those earlier blogposts...oh my!

    I like!

  15. Nika Gerson Lohman says:

    Great excel, thanks. Just note that the fields that use the "WHO_ALL" name in your experiment tab points to the list in the example instead of the list in the experiment tab 😉

  16. Although I have used this Excel for many trips, I have now found another one that even works better. The excel from this site does not like it when you enter expenses that someone paid when the person paying for it should not be included in the calculation. The one on the other website does. Here's the link:
    https://github.com/brsanthu/expense-share-calculator

    Regards,

    Nika.

  17. NEIL says:

    How that "exclude" formula work ?
    " =D4/(10-COUNTIF(WHO_ALL,"")-COUNT(E4:H4)) "

    can you please explain in detail ?

    Thanks.... !!

Leave a Reply