Mastering PivotTables: Part 14 (Conditional Formatting in PivotTables)

Conditional formatting helps you visually explore and analyse data, detect critical issues/problems, and allows you to identify pattern and trends in the data. It is important to understand the differences when using conditional formatting on a PivotTable. There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field and by value field.

Conditional Formatting by Selection in PivotTablepivotp14,1

  1. Open the file provided here or use any other similar Excel file.
  2. Select the visible range of values (B6:M23). Remember to not select the area where it shows the fields, and only select the area with the data values.
  3. Navigate to the Home tab > Conditional Formatting > New Rule
  4. From the “Apply Rule to” section, selected Selected Cells.
  5. Select a rule to format only cells with cell values.
  6. Set the rule description to set a rule where the Cell Value is greater than or equal to 1000.
  7. The cells colour can also be formatted, and to do this, click on the Format button in the Pop-up, and change to a desirable colour.
  8. Collapse the filters showing the detail for each quarter. The cells are hidden, and any cells greater than 1000 are not formatted.
  9. To clear the rule, go to Home Tab > Conditional Formatting > Clear Rules > From Entire Sheet. The other options are also reasonable to use in this case.

Conditional Formatting by Value Field

  1. pivotp14,2Using the same file (unedited version), select the visible range of values (B6:M23). Remember to not select the area where it shows the fields, and only select the area with the data values.
  2. Navigate to Home > Conditional Formatting > New Rule
  3. From the “Apply Rule to” section, select All cells showing “Sum of Amount” values.
  4. Select a rule to format only cells with cell values.
  5. Set the rule description to set a rule where the Cell Value is greater than or equal to 1000.
  6. The cells colour can also be formatted, and to do this, click on the Format button in the Pop-up, and change to a desirable colour.
  7. The rule should be applied to all cells in the range like previously in “By Selection”, but now the Row and Column Total are also formatted.
  8. Adding fields to the PivotTable will also have the rule apply to the data.

Conditional Formatting by Corresponding Field

  1. pivotp14,3Using the same file, select the visible range of values (B6:M23). Keep the QTR labels collapsed.
  2. Navigate to Home > Conditional Formatting > Manage Rules > Edit Rule
  3. Change the Apply rule to All cells showing “Sum of Amount” values for “Department” and “Quarters”.
  4. Click OK again and the total are no longer formatted. When you have the QTR labels collapsed, you can see that the rule will not apply, and will only apply when the QTR labels are expanded.

 

No comments yet.

Leave a Reply

Powered by WordPress. Designed by WooThemes

Free WordPress Themes