MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table - Conditional Format Query

Posted by JAF on June 15, 2001 3:20 AM


I have a pivot table that is initially displayed to list employee names in rows, and the "hours type" (Earned or Taken) in columns.

Earned hours are a positive figure and Taken are negative. The Grand Total column displays the sum of Earned and Taken - so if employee John Smith had "Earned" 3.00 hours and "Taken" -2.50 hours, then the Grand Total column would display 0.50.

The Grand Total figure SHOULD never be a negative, as employees must earn their hours before they take them, but in order to clearly highlight any instances where an employee has taken more than they have earned, I'd like to format the Grand Total column to highlight in red any instances where the figure in that column is less than zero.

I can select the range and apply the formatting without difficulty to the pivot table "starting position", but when the fields are re-arranged, the conditional formatting remains in the area specified.

Is there any way to set up the Grand Total field on a Pivot Table so that the conditional formatting remains in the Grand Total column, irrespective of what column number the Grand Total may currently be??

Hope this makes sense....


Posted by Mark W. on June 15, 2001 7:00 AM

JAF, you need to Enable Selection using the PivotTable
toolbar's Select command. Also, if your PivotTable
has any Page fields you'll need to set their
item value to '(All)' before applying the cell
format. You may find that after applying the
desired format to the Grand Total and reorganizing
the PivotTable layout that you'll need to reset
the format of the cell where the Grand Total was
once located. But, once you've done that you can
change the PivotTable at will and the cell formatting
of the Grand Total will behave as desired.