To apply conditional formatting to a pivot table, create a new conditional formatting rule and pay particular attention to the “apply rule to” settings as described below. In the example shown, there are two rules applied. The green shows the top 5 values using a rule like this:

Pivot table conditional formatting top 5 values rule - 1

Details

Pivot tables are dynamic and change frequently when data is updated. If you created conditional formatting rules based on “selected cells” only, you may find that the conditional formatting is lost or not applied to all data when the pivot table is changed, or when data is refreshed.

The best option is to set up the rule correctly from the start. Select any cell in the data you wish to format and then choose “New rule” from the conditional formatting menu on the Home tab of the ribbon. At the top of the window, you will see a setting for which cells to apply conditional formatting to. For the example shown, we want the last option: All cells showing “Sum of sales” values for “Name” and “Date”.

Pivot table conditional formatting create new rule - 2

Note: The second option, “All cells showing “Sum of Sales values”, will include grand total rows and columns as well, which you ordinarily don’t want.

Editing existing rules to fix broken formatting

If you already have a rule set up that is not correctly formatting all values as needed, edit the rule and change the cell selection option if needed. You can access existing rules at Home > Conditional Formatting > Manage Rules.

In the example shown, the rule manager displays two rules like this:

Pivot table conditional formatting rule manager - 3

To edit a rule, select the rule and click the “Edit Rule” button. Then adjust settings in the “Apply rule to” section.

Note: conditional formatting is lost when you remove the target field from a pivot table.

Pivot tables can group dates by year, month, and quarter. This comes in handy if you want to count birthdays that occur in each month, while ignoring the year. In the example shown, there are 100 names and birthdays in the source data. A pivot table is used to count the birthdays that occur in each month of the year.

Fields

The source data contains two fields: Name , and Birthdate . Both fields are used to create the pivot table:

Field list for birthdays per month pivot table - 4

The Birthdate field has been added as a Row field and grouped by Months only:

Birthdates are grouped by Months only - 5

The Name field has been added as a Value field. Because Name is a text field, the field is summarized by count automatically:

Name is summarized by count by default - 6

Helper column alternative

As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the month . Then add the Month field to the pivot table directly.

Formula alternative

As an alternative to a pivot table, you can use the SUMPRODUCT function to count by month, as seen in this example .

Steps

  1. Create a pivot table
  2. Add Birthdate field to Rows area Group by Months only
  3. Add Name field to Values area

Notes

  1. Any non-empty field in the data can be used in the Values area to get a count.
  2. When a text field is added as a Value field, Excel will display a count automatically.