Posted on November 23rd, 2009 in excel apps - 19 comments

At PHD household, we believe in using money wisely. Both Mrs. PHD and I come from very modest backgrounds. Our upbringing has taught us value of money in the most effective way – by exposing us to not having any. So when we got our first job (did I tell you that both Jo and I started working in same company and sat in same floor? Oh, it was such a lovely time), we were very prudent and decided not to waste money on anything trivial (we still are, just that over last 6 years our earning capacity increased a bit and we became parents).

Anyways, I am not here to bore you about my household. But I am here to tell you fun ways to track expenses and household budgets using spreadsheets.

Sovan, one of our blog readers from Chennai, India, sent me 2 excel budgeting spreadsheets. Naturally I got excited to see such prudent use of excel. I immediately asked him if it is ok to share these files with our community and he is more than happy. So here they are,

Using this spreadsheet, you can track your expenses. As you enter the expense, the time stamp is automatically generated (read automatic timestamps in excel). Sovan uses the circular reference formula technique we described in that post.

You can also highlight expenses above certain pre-defined amount. For this, Sovan uses Conditional Formatting.

There is an “analysis” worksheet where you can see some graphs based on your expenses. In this, the expenses are sorted from highest to lowest and grouped by category. Again, another technique from Sort and Display chart data automatically post.

Sharing Expenses among Friends using Excel

In tracking and sharing expenses using excel post, we have described how you can use MS Excel to findout how to share expenses among your friends or colleagues. Sovan built on this concepts and developed an expense sharing worksheet that you can use.

Thank you Sovan

It is always inspiring to read mail from our members and learn about various interesting ways they are using Excel. I thank Sovan for sharing these workbooks with us. If you have enjoyed these templates, please drop a thank you note using comments.

PS: I have edited original files Sovan e-mailed me, to make them compatible with Excel 2003.

 10 Supercool UI Improvements in Excel 2010 Why do you visit PHD?
 Written by Chandoo Tags: budgeting, circular formulas, downloads, excel apps, expense tracker, guest posts, Learn Excel, microsoft, personal finance, Sovan, spreadsheets, templates Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

1. Alex Kerin says:

Like it, that spreadsheet is crying out for some sparklines..

2. Jair says:

Hi Chandoo. I don't know what happen, but I can't suscribe to your blog. Could you help me?

3. Chandoo says:

@Alex: Good idea. We can easily add them in Excel 2007 or 2010.

@Jair: It says your e-mail ID is already subscribed. Can you check your spam / bulk mail folder and white-list my e-mail id. The emails come from my gmail ID - chandoo.d @ gmail.com

Let me know if you still face a problem.

4. Jair says:

Chandoo, in VBA we use listbox to make a list with columns. There are a property called Columnwith that fixed column width. I need to let column width automatic, so that user can fix to its need. Could you help me?

5. Jair says:

I'm from Cali - Colombia south America. To me is bigger know you and your people. In my country I'm trying to construct a industry around of Microsoft Excel and VBA, there are a good potencial. Thank you for you help.

6. Martin says:

Chandoo,

I have made a tracking expenses spreadsheet myself, around the concept of forecasting. With that in mind, I've thought about the ways you can spend your money (cash, card, direct debit), the alternatives for each one(local currency, USD; MasterCard,Visa, Amex, etc.)and the date and amount of that expense, and also a category for that expense. My biiiig problem arises when I try to calculate the total for each month for the credit card, based on the date that the credit card closes the month (excuse my English, as I don't recall a better way to say that...).

Any suggestions?

Martin.

7. Sovan says:

Hi Chandoo,

Thank you for posting the excel on PHD...Hello to other members...Feels great to be a part of PHD...I have straight hairs,now a couple of them are pointy....

8. Kanti Chiba says:

Hi Chandoo,

With almost all financial institutions now offering download of bank and credit card statements.
I have automated my spending analysis after downloading from the bank.
If you wish I can send you an extract of the spreadsheet.

9. chrisham says:

I too have been making a Home Budget Spreadsheet, but I like the one posted here. Great Tools and never thought of incorporating a Chart in it. Some great ideas too with the dynamic listing of Category Names in Table.
Just a suggestion to make it more robust, may be Sovan could incorporate features of Monthly Analysis for a select period??

10. Chandoo says:

@Jair: Can you ask any questions not related to this post in our forums, that way more ppl can see your question and help you. Go here: http://chandoo.org/forums/

@Martin: That should be a bit tricky, as each credit card will have its own billing cycle. But not impossible. Assuming the creditcard name, billing start date and billing end date (just the date, not month or year) are in cells A1,B1,C1 ... A5,B5,C5, we can use vlookup to fetch the actual period then use that period in SUMIFS or SUMPRODUCT to find the actual amount that is due.

I am sorry for not writing the formulas here, but you can figure them out after going thru below tutorials:

@Kanti...
Please send it to me, if it is something generic, I can share it with our members here.

@Sovan... this is an awesome tool, thank you once again.

@Chrisham...
that is a good idea, we can attempt it in another post.

11. Srivatsa K R says:

These two were simply awesome...although I had created excel sheets for both the purposes (household and sharing), that was done in a crude way...here, things looks much more simple

12. [...] Household Budget Templates – Free Download [...]

13. Ficho says:

You can track your expenses and manage your budget with web application at this link http://www.troskovnik.podzone.net/

14. Arnav says:

Here is mine... Thanks Chandooo for all your tips... I will keep working on it... 🙂

http://www.bookrum.com/blog/2010/analyze-this/personal-budget-in-excel

15. Imran says:

Nice , Thanks

16. calibra says:

I want to write a spreadsheet for a friend. It will be a single christmas club, where there are several people, each with several accounts requiring continually displayed totals and transactions and standing orders between accounts and to/from external account; this external account does not require a live connection it is in NAME only.
Wondered if your free spreadsheets could be adapted for this purpose
Thanks

17. sarah says:

I Just started my Scentsy Wickless business a couple months ago, I am looking for an excel spreed sheet I can use to keep track of stock in and stock out.  Any help would help

18. chandan says:

Thanks alot, useful spreadsheet.

19. Eric says:

Nice templates! Here is a simple Household Budget Template I created in Excel: http://www.hitdocs.com/household-budget-template-xlsx/

 10 Supercool UI Improvements in Excel 2010 Why do you visit PHD?