Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am working on a very basic "home budget" spreadsheet, the goal of which is to quickly be able to see: A) how much money is left over after the bills are paid and B) what % of withdrawals are going to what categories of expenses.

I have a Pie Chart built off of the following data (which is formatted as a table):

category       withdrawal
-------------------------
Utilities      108.59
Auto           119.13
Loans          500.00
Credit Card    78.00
Credit Card    161.00
Credit Card    16.00
Utilities      220.51

What I am attempting to do is consolidate each of the categories into one slice of the pie that is then displayed with it's Category as the label and the percentage of the pie it takes up. What I end up with is each category, regardless of it's uniqueness, taking a slice, like so:

My messed up graph

Am I going to have to solve this with lots of hidden cells containing functions to add specific categories together? I was hoping I could arbitrarily add categories, simply within my table of data without having to go and change formulas in a dozen other areas.

share|improve this question
    
A bit of background as to why I am tackling this: I loved Quicken Online but when they bought Mint, they went that route. Mint is great, for historic data, but doesn't do anything in regards to forecasting (my biggest need). My current bank purchased a license for Quicken Online (they sell it to them as FinanceWorks) but I am in the process of also switching banks. I'm willing to consider other options if there are good thoughts (until I get around to building a localhost-clone of Quicken Online myself). –  Michael Wales Nov 9 '10 at 4:20
add comment

2 Answers

up vote 2 down vote accepted

Enter: PivotTables and PivotCharts

You can be arbitrary with your categories, and it will just lump the same categories together and spit them out however you want (which in your case is probably a sum). You can also add a "sub category" field and then see what percentage of your utility bill goes to heating, or electricity. Or what percentage is which credit card.

share|improve this answer
    
Thank you - worked like a charm and I even got additional functionality (now I can see totals, not just percentages) since adding a PivotChart automatically creates a corresponding PivotTable. –  Michael Wales Nov 10 '10 at 0:14
    
pivot tables might be a good approach –  datatoo Nov 10 '10 at 1:46
add comment

If you define the range of your categories and values as a name in the workbook. When you insert another row into that range it will automatically add the new category and value to your chart.

This doesn't work if you try to append a new category on the end of the range, but it does if you insert a new row into the range.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.