You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s Author: Joe Chirilov, a program manager on the who has done a lot of work on both the Excel and Excel Services teams. Joe is going to discuss a spreadsheet he recently built using Excel 2007.
Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. We had a blast, but that’s a conversation for another day. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. This gave rise to an interesting problem, one that I’m sure you all hit once in a while: how do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time?
As you might guess, I built a spreadsheet to figure it all out for me. This isn’t the first time I’ve built such a spreadsheet, but it occurred to me that others might find such a spreadsheet useful so I decided to share it with you. As spreadsheets go, there’s nothing really complicated here, though it’s always nice to have that ready-made template when you need it. You can download the spreadsheet here: http://officeblogs.net/excel/Split_Costs.zip. You’ll notice the spreadsheet has three versions in it – more on this below.
So how do you use this thing? As I previously mentioned, it’s made for situations where multiple people in a group temporarily cover the costs of things for others in the group. You setup the spreadsheet by inputting:
Then for each cost you enter the following:
Using my file as an example, it has sample data in it (also shown in the image below), Katherine paid for the train tickets, the tickets cost $1000, and the tickets were for Joe, Beth, Rob, and Katherine, so each owes 25% of the total cost.
This design allows for some flexibility in how the costs are split. Notice in the example above that James and Nancy did not get a train ticket so they aren’t assigned any percentage of the cost. The spreadsheet doesn’t enforce that everyone pays the same percentage. All it cares about is that the percentages add up to 100% (more on this later). In the image below, James booked two hotel rooms for five people, one room for two and one for three. The room for two costs more because it is split up among fewer people.
Once all the data has been filled in, what you typically see is that some people owe money, even if they paid for some costs, and some people owe a negative amount, which means they need to get paid back! It may look something like this:
At this point the easiest thing to do is to assign someone as the money collector. Once that person collects all the money, they pay back those that are owed money. This way, money exchanges hands the least amount of times as possible and the process is pretty clean. That’s all there is to it.
As you can see from the image above, the spreadsheet also provides a column for entering any amounts you have collected so far, so you can track how much you have and how much is left to collect.
On Spreadsheet Design
As I was cleaning up my spreadsheet to share with the world, I got to thinking about how others certainly have built similar spreadsheets before, and this got me wondering about other ways in which people may have approached the problem. So I built three different versions of the spreadsheet – they all do exactly the same thing but the way the data is laid out is different in each version.
So along with sharing this spreadsheet out, I’d like to take an informal poll, intended to feed my curiosity more than anything else: which of the three versions do you prefer most and why? I have my guesses but I’d like to hear from you first. I think it could be an interesting experiment, though I’m not sure yet what it will teach us. :)
How It Works
I’d like to talk a bit about a couple of the design points in the spreadsheet that hopefully you can apply to spreadsheets that you build.
Style1 uses the table feature a lot. We’ve talked about tables in the past, and if you haven’t used them, they are a great tool for working with tabular data. Read our articles on tables to learn more. In this spreadsheet, the structured references used in the formulas makes them much easier to read. Just look at how I compute “Remaining” in tables (=[Total Owes]-[Paid]) versus normal cells (=D3-E3).
Here’s a trick to using VLOOKUP with tables. VLOOKUP isn’t table-aware, so the third argument, col_index_num, is still a number and not, say, a column name when used with Tables. Next time you use VLOOKUP on a table, use the following formula for the third argument instead of an index number:
MATCH( TableName[[#Headers], [ColumnName]], TableName[#Headers], 0)
Substitute ‘TableName’ with the name of the table you are looking up, and ‘ColumnName’ for the name of the table column you want to index into. This, in effect, gives you a tight pointer to the column that does not break if the column name changes or if the column is re-arranged in the table, thereby providing a more robust alternative to using a numerical index.
You can see this technique in use in the spreadsheet in cells B23:F28 and B33:B38 on the Style1 sheet.
I typically take a paranoid approach to my spreadsheet building, and I sprinkle lots of checks into the spreadsheet to make sure things are working properly. Conditional formatting is a great way to highlight error conditions that pop up in your spreadsheet. Here are a couple examples of how I used checks and conditional formatting to keep my spreadsheet in tip-top shape:
Percentages: make sure the distribution of cost always equals 100%. Create a formula that adds up all the percentages and apply a conditional formatting rule that highlights the cell if it equals anything other than 100%, as in the image below:
Payer: make sure that for any given cost there is only one person paying upfront. Create a formula that counts the number of values in the “Who Paid” column and apply a conditional formatting rule that highlights the cell if the count ever goes above 1, as in the image below:
If you play around with the spreadsheet you will find a few other examples like this. To learn more about creating conditional formats, check out this Office Online help article.
Good luck tracking costs for your next group event/outing. And don’t forget to let me know which of the three spreadsheet versions you like most.
Style 3 is better because it is easier to extend the list for more expenditure items. Style 1 will be a nightmare because you need to add column to each individual table. When you have 30 or 100 items, style 3 prints better (long long report with width fixed by number of person). Style 2 will be a very wide print out, which is less easy to read.
I think it is overly restrictive to assume that only one person pays. When dealing with cash transactions, it is not unfrequent for us to settle a bill by two or more people (because of availability of notes, of small changes, etc). Or you ride in two taxis and decided to share the total cost (instead of each taxi passenger sharing the cost of that particular taxi).
There are always cents problems because numbers don't divide. If you change the plane cost from 3000 to 2000, you get a $0 highlighted. Because of the way the sum is divided, I think the payer will consistently receive a fraction of a cent less than what he paid. A fair system should carry forward any cents that cannot be evenly divided.
I think it is a tiny bit better to record "number of shares" instead of "percent owes", because it is more intuitive. Although errors are well guarded against, at least it will save a mental computation to determine the denominator.
I should say you did a very good spreadsheet. Very neat. I ended by writing an Access application myself.
I think all 3 suffer from not being easily scalable. To add another item means adding 3 headings.
Better is to put the names in one column each, containing the % split, with one column at the beginning which shows who paid (using data validation from a dynamic list (taken from the names in the first row), so there is only one row per item, and one column per person, making it easy to scale and expand.
Nice article. I'll test as soon as possible!
There are actually numerous applications on the internet that do just this (Buxfer is one). I have also created my own application in PHP (and working on one in RoR) which is a little easier to use for my friends and I. We actually just used it for a trip we booked to Europe!
Thanks for the excel version of this. The problem is an interesting one to solve (minimize the number of transactions...especially when A owes B and B owes C).
I prefer style 3, which is very similiar to a spreadsheet that I have customary used for cost-splitting. The major difference is that I typically don't track individual payments within a category. The total incoming versus outgoing is the key data (i.e. I don't really care if Susy paid $100 for trains or hotels, she paid $100 is all that really matters). It's reduces the number of rows in a large complex split scenario.
Given the same scenario nowadays, tracking expenses for a large group of people, I would be inclined to use a service such as BillMonk (https://www.billmonk.com). The older I get, the more I like other folks to handle the math. :)
I must appologise as i do not use these feeds often and currently have a request and dont know where to post.
I need to create a live duplication of one excel sheet to another inside the same workbook. For example if data is entered into sheet 1 it appears in sheet 2 in the same cell!
If anyone has an idea on how to do this please email me on email@example.com
Can also someone let me know which forum to post questions like this on.
Love the VLOOKUP/MATCH tip - worth it just for that alone.
Please help me to download this file. After I unzip the file had extension Xlxs. I've never seen this extension before. It should be "Split Costs.xls (?). Thanks.
Mai - that is a new file format introduced in Excel 2007. You can download a compatibility pack to allow earlier versions of Office to read and write that file format here:
Thank you very much for your fast response and kindness... I did try to download the link, however, I get the message like this: “This file is not in a recognizable format... 1) If you know the file is from another program which is incompatible with MSExcel, save it in a format that is compatible, such as text format. 2) If you suspect the file is damaged, click Help for more information about solving the problem.3) If you still want to see what text is contained in the file, click OK. Then click Finish in the Text Import Wizard.
When I click OK I get a weird text. I have Excel 2003. So when you have a chance, if you don’t mind, please teach me. I really want to learn. Thank you very much. Merry Christmas and Happy New Year!