pivot table: value filter on specific items of a field

[bmn111]

Hello I have a pivot table that looks like this:

What I'm trying to do is to create a Value Filter on departments to show only departments that had $0 in sales in Month 1. The result of this filter would of course be that only "Dept D" would be visible.

When I try to create a value filter for the Department field I can only filter on the *SUM* of Sales, and not on the value of sales in month 1.

Am I missing something really basic here?

Thanks for your help!

 

[Jerry Sullivan]

Hello and Welcome,

in xl2007, here are the steps to filter Month 1 values:

Select the Cell that reads "Dept A" > Right Click > Filter > Value Filters

From the Value Filter dialog box that pops up with 3 data entry fields,

You should be able to chose "Sum of Month 1" from the dropdown list on the left.

From the middle list: "equals"
From the right textbox: 0

 

[bmn111]

Hi JS411 -- thanks so much for responding!

You should be able to chose "Sum of Month 1" from the dropdown list on the left.

From the middle list: "equals"
From the right textbox: 0

So when I do as you say I get this:

As you can see it does not let me filter on the different items -- I can just select the "Sales" field which is the sum of the sales by department. I can't seem to be able to filter departments based on MONTHLY sales (month being in the column field of my pivot)

Is there something weird going on with my spreadsheet? Did you create a similar table and were you able to filter on individual monthly items as you say?

 

[Jerry Sullivan]

Is there something weird going on with my spreadsheet? Did you create a similar table and were you able to filter on individual monthly items as you say?

Yes, I was able to create a similar table and filter each month column separately.

Perhaps something about the organization of your report is different than my example.

If you post your file to a host site, or send to me by exchanging email addresses through a PM, I'd be glad to look at your file.

 

[bmn111]

If you post your file to a host site, or send to me by exchanging email addresses through a PM, I'd be glad to look at your file.

Thanks again for responding JS411!

I've uploaded a sample file to here. Using Excel 2007 I'm only able to value filter on the SUM of sales by department. I cannot filter by sales by month (for example to say value filter for only departments of sales > $100 for month 1).

Would love your help here. A screenshot would be hugely appreciated. I'll also follow up with a PM. Thank you!!

 

[Jerry Sullivan]

Thanks again for responding JS411!

I've uploaded a sample file to here. Using Excel 2007 I'm only able to value filter on the SUM of sales by department. I cannot filter by sales by month (for example to say value filter for only departments of sales > $100 for month 1).

Would love your help here. A screenshot would be hugely appreciated. I'll also follow up with a PM. Thank you!!

Ahhhh.... The problem is our two source data were organized differently.
Your data looks like this:

Excel Workbook
	C	D	E
4	depatment	month	sales
5	Dept A	Month 1	500
6	Dept B	Month 1	75
7	Dept C	Month 1	84
8	Dept D	Month 1	154
9	Dept A	Month 2	79
10	Dept B	Month 2	311
11	Dept C	Month 2	12
12	Dept D	Month 2	70
Sheet1

#VALUE!

Excel 2007



Bad assumption on my part since your source data is more typical.

So, that clears up why I was able to filter by column and you weren't, but doesn't help you to get yours to work the same unless you have the option to reorganize your source data.

Hope this helped a little.

 

[bmn111]

Hope this helped a little.

It was definitely an interesting exercise if nothing else

I do appreciate your time and effort!