Limiting Data in a Pivot Table? (Excel XP)

Topic

New Reply

Hey all,

Let me start off by saying I don’t know if this is possible, I’m an Access person, so that’s where I’m coming from…

So I have some data in a worksheet, and I would like to create two separate pivot tables. I’ve summarized the difference between the two proposed pivot tables in one field, which contains an A, B, or C. In Pivot Table 1, I want the results of only records that meet A, and in Pivot table 2, I want the results of records that meet A & B combined. I’m trying to do this with ony one list of data, but is it not possible?

The problem is that I can only figure out how to do this two ways, neither of which is the way I want. If I do it with a Page Field (the row across the top of the page), I can only select A or B or C for the entire pivot table. If I do it with a Row Field, I get a column that I don’t want, and I don’t want A, B, and C to show up in separate rows, I want the combined total for A & B.

Do I need to have two different datasources for these pivot tables (ie. one with A, and the other with A&B combined)? Or is there a way to limit the overall pivot table without showing that field in the table?

Hope this makes sense,

Reply | Quote

Replies

If I understand your description correctly, I’d add a calculated column to the source data with formulas that return TRUE if your summary column contains A or B, FALSE otherwise. You should be able to use this as page field in your second pivot table.

Reply | Quote

Hi Hans,

Always saving me, eh? After I thought about it, I did what you said (before you said it) because I think you may have told me the same thing a long–long time ago. Somehow I never remember how to use Excel lol. But you always save the day

Intuitively, though it would make more sense (at least for us Access people) if you could query within excel

Thanks!!! as always,

Reply | Quote

Hi Hans

Perhaps instead of adding a column to the source data (not always possible) you could do this using the ‘multiple items’ feature for the pivot page field.

A page field typically has either one pivot item or (All).
To get a page field to have multiple items:
1. drag the page field into either the row or column area of the pivot report.
2. after you have moved it there, use the drop-down to check the multiple items you want.
3. after choosing your items, drag the field back to to the page field area of the report.
4. voilla!
The page field will now show (Multiple Items)

Note: unfortunately you need to repeat this process if you want to change the selections.

zeddy

Reply | Quote

I get the impression from Cecilia’s description that she wants fixed selections, so your suggesion would be a good way to do it!

Reply | Quote