Data Source in Pivot Tables

[Pam]

I am working in Excel 2003. I have a bumch of expense catergories that I
would like to put into a pivot table. I also have a month and year column.
So I put my year in the column part of the Pivot Table Chart Wizard and my
month is the row part, and then all my expenses in the data portion. This
works to a point. As soon as I go to my Pivot Table and change my dad to
only view 1 or 2 expenses I lose the rest of the data. I have to go back in
and add the rest again. Why is it doing this?? Is there a better way??
What am I doing wrong?

 

[Debra Dalgleish]

When you clear a checkbox in a data field dropdown list, you're removing
the field from the pivot table layout. Just like a row field that was
removed, you have to drag it back to the pivot table layout, in order to
see it again.

 

[Pam]

Is there anyway to keep it there so i don't have to continue to drag it back.
I want to do a presentation and having to keep draggin it back won't look
very good. Will it be any different if i change how my information is set up?
thanks

 

[Debra Dalgleish]

Changing your data layout might help, because it sounds like you have a
column in your source data for each expense category.

Instead, you could create a single column for Category, and a single
column for Amount.

Instead of this layout:

Year Month Salaries Rent Stationery
2008 9 50000 10000 2400

You would have this:

Year Month ExpCat Amt
2008 9 Salaries 50000
2008 9 Rent 10000
2008 9 Stationery 2400

Then, add ExpCat to the Row or Column area, and you can check and
uncheck the expense categories.

To reorganize your data, you can use the 'unpivot' technique described
by John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

 

[Pam]

I rearranged my data, it did help, it was a lot of work, but I figured it
out. I am now trying to have an expense catergory and a revenue category on
the same pivot table. I haven't figured out a way to have both of those on
one pivot table. ANy suggestions?

 

[Debra Dalgleish]

Change the ExpCat heading to Category, and add another column to the
source data:

Year Month Category Amt Type
2008 9 Salaries 50000 Expense
2008 9 Rent 10000 Expense
2008 9 Stationery 2400 Expense
2008 9 Sales 9200 Revenue