Anyone who’s created a Pivot Table with Slicers in Excel 2013 or Excel Office 365 may have come across an apparent issue – loosing your Conditional Formatting when using a Slicer to filter the data in the Pivot Table.

Above is how my Pivot table looked before using the Slicer (looks great with Data Bars and Colour Scales applied)

But after using the Slicer…..hey! where’d my pretty formatting go!?

PivotTableConditionalFormatting_AfterSlicerClick

The GOOD NEWS! is there is a solution.

If you’re like me then perhaps you applied the Conditional Formatting by highlighting the value column (using the column header) and applying the Conditional Formatting that way (using the Conditional Formatting tool on the Excel Home ribbon).

This works fine and looks great initially; but as soon as we use that Slicer our hard work is undone; So what’s the solution to this?

Thankfully! it’s simple! it’s just a case of clicking the mouse in the correct place and selecting the correct options. So here’s what to do.

  1. Left click into the column that contains the values you want to format (so in the above I can click on the cell that shows the Population value for China).
  2. Go to the Excel Home ribbon.
  3. Open the Conditional Formatting drop down list and select New rule.PivotTableConditionalFormatting_NewRule
  4. Select the All cells showing option.PivotTableConditionalFormatting_NewRule2
  5. Set the Format and Appearance settings to meet your requirements; in this case I want a Data Bar with Dark Blue formatting applied. I’ve set the Minimum value to the Lowest Value and the Maximum to the Highest Value (there are other options here including using a Formula or specifying Actual values).
  6. Notice the Negative Value and Axis button; this can be used to set the Appearance for Negative values
  7. Click OK to apply this Conditional Formatting.
  8. I’ll follow the same process to apply the Colour Scale formatting to the Population % cells; selecting different Format and Appearance options of course.
  9. Now my Pivot Table looks like it did before.PivotTableConditionalFormatting_BeforeSlicerClickHowever this time when I use the Slicer the Conditional Formatting is preserved, Hurray!!PivotTableConditionalFormatting_NewRule4
    We hope you found this useful and/or interesting. If so please like, share or comment.
Advertisements