How to overlay different totals figures with filters?

[wes228]

This may be a complicated question: I am doing a budget sheet in Excel for the different expenses accrued by the different classes we offer in our university department (honoraria for guest lectures, tickets to shows or museums, etc.). The sheet is formatted as a table and I have the Total Row enabled at the bottom.

However, we have a reserved budget for different things. For instance, Fall 2013 museum tickets has, say, a budget of $1,000. Fall 2013 honoraria might have a budget of $2,000. I want to be able to filter to see only Fall 2013 honoraria: the totals row will reflect this, but I also want it to automatically tell me how much money is left in that budget (i.e. I want it to know to subtract $2,000 from the totals figure because that is the budget for Fall 2013 honoraria).

It gets a little more complicated because we also have a budget for each class too. One class might have both honoraria for guest lectures and field trips to a museum, and we might say "We don't want this class to spend more than $500." Again, I want to filter to see just this class, and have the Totals row also show me how much money is remaining for this class.

Thank you!

[lts_ram]

Hi,

I attach a sample. Try this if it helps you.

[wes228]

Thank you but is there any way to do this by using filters and the Totals Row? Maybe a PivotChart is what is called for? The way that you have it set up would quickly become unruly, and we don't necessarily want to group expenses by class.

[lts_ram]

Welcome,
As suggested Pivot Table is one such method.
I am not an expert in excel but, I have done it up to my comprehension in understandingexcel functions.
If you please attach a sample and your requirement, experts, members of the forum can
give better focus to your problem and solve.

[wes228]

How do I attach an Excel worksheet to my message?