Easy Steps to Apply Conditional Formatting in the Pivot Table

Example

We have data for a newly launched canteen, and we have the records for the number of products sold in each of five months -Jan, Feb, March, April, and May.

  • First, we must select the data. Then, in the “Insert” Tab, click on “Pivot Tables.” As a result, a dialog box appears. Next, we must insert the pivot table in a new worksheet by clicking “OK.” Currently, a pivot table is blank. Next, we need to bring in the values. Then, drag down the “Date” in the “Rows” Label, “Name” in the “Column,” and “Sales” in “Values.” As a result, the pivot table will look like the one below. To apply conditional formatting in the pivot table, first, we must select the column to format. In this example, select “Grand Total Column.” Then, in the “Home” Tab in the “Styles” section, click on “Conditional Formatting.” Consequently, a dialog box pops up. Then, we need to click on “New Rule.” As a result, another dialog box will pop up. There are a variety of rules to be applied in the pivot table. For example, we will select “Format only cells that contain,” and we can see another dialog box in the same window. Next, we must insert any value in the “Cell Value” section. In this case, we have mentioned a range from 70000 to 90000. Then, click on the “Format” at the bottom. Next, we must select the type of formatting we want. For example, we have chosen a color (red color) from the “Fill” section. After that, click on “OK” and again “OK” to close the “Formatting Rule” dialog box. And the pivot table is formatted in red with the values between 70000-90000.

And the pivot table is formatted in red with the values between 70000-90000.

First, let us insert a pivot tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more in this data.

  • Step 1: We must select the data. Then, in the “Insert” Tab, click on “Pivot Tables.”

  • Step 2: Then, insert the pivot table in a new worksheet by clicking “OK.”

  • Step 3: Drag down the “Product” in the “Row” label and “Months” in the “Value” fields.

  • The pivot table will look as shown below.

  • Step 4: Our motive is to highlight those cells with the highest number of products sold in each row. For that, we must select any cell in the pivot table. Then, click on “Conditional Formatting” and click on a “New Rule.”

  • Step 5: In the first option, apply a rule to select the third option.

  • Step 6: Now, select the third option of “Format only top and bottom ranked values” in “Rule type.”

  • Step 7: In “Edit the Rule Description,” we must enter 1 in the input box, and from the drop-down menu, select “each Column Group.”

  • Step 8: Then, we must click on the “Format.”

  • Step 9: Select “Green Color” in the “Fill” section. And click on “OK.”

  • Step 10: Again, click on “OK” to end the formatting rule dialog box.

The above formatting compared the value in the column. If we want the formatting in the rows, we can select each row group.

Rules For Conditional Formatting in Pivot Table

In the “Home” tab under the “Styles” section, there is a button for “Conditional Formatting.” We can insert a new rule as per our requirements.

There are various rules for conditional formatting. They are as follows:

  • Format CellsFormat CellsFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user’s preferred format. The formatting of the cell depends upon the nature of the data present.read more based on their values.Format cells that only contain specific data.Format only top and bottom ranked values.Format only values that are above or below average.Use a formula to determine which cells to format.

Things to Remember

  • We must select rules as per requirement.For example, when conditional formatting is applied to a block of cells, formatting is applied to only those specific cells.Any change in data may represent the wrong illustration in conditional formatting in pivot tables.

This article is a guide to Conditional Formatting in the Pivot Table. We discussed applying conditional formatting to the Excel pivot table, practical examples, and a downloadable Excel template. You may learn more about excel from the following articles: –

  • Power BI Conditional FormattingConditional Formatting with FormulasConditional Formatting for Blank CellsConditional Formatting Based On Another Cell ValueExcel Minus Formula