Pivot tables - filtering and formatting

[Jim Palmer]

Two questions regarding pivot tables.


1
Is there a way to use auto filter to limit the items included in row
fields? That is, instead of checking or unchecking items in the drop
down box use filters to say only include accounts greater than 5000 and
less than 7000. I got around this by filtering the source of the data,
copying it to another sheet and changing the source of the pivot table
to the new sheet, but that is cumbersome.

2
Can conditional formatting be used within pivot tables? I have an
actual field, a budget field and a calculated variance field. I would
like to have variances over say $1,000 highlighted. I know how to use
conditional formatting, but I'd like to restrict it to only the
variance field.

Any suggestions would be appreciated.

Regards
Jim Palmer

 

[Debra Dalgleish]

1. You could add a column to the pivot source table, and check each
total against the minimum and maximum values. For example, with
UnitsSold in column I, Min in K1 and Max in L1, enter the following
formula in row 2, and copy down to the last row of data:

=AND(I2>=$K$1,I2<=$L$1)

Refresh the pivot table, add this field to the Page area, and filter for
TRUE.

2. You can add conditional formatting to the pivot table, but it will
stay with the cells, rather than the pivot fields. So, if you change the
layout, the conditional formatting would have to be reapplied.