Pivot Table Conditional Formatting

Pivot table helps the user to expand, isolate, sum, and group the particular data in real time. It can show you the differences in very big data sets and helps in organization of large amounts of data in Excel

Steps to create a Pivot Table:

• Select any Cell from the table you want to create the Pivot Table

We have the above data set with us for this example.

• Go to Insert Tab -> Click on Pivot Table

• A dialog box, Create Pivot Table appears:

Here Select the table/range by first clicking inside the Table/Range field and dragging and selecting the required range.

Then choose where you want to display the pivot table. Here we have chosen to place the table in existing worksheet to help us analyze more effectively.

And then Click on OK.

• Now the option to choose Pivot Table Field Comes:

We will choose Date, City and Quantity fields for now.

Now that your pivot table has been created it will look something like this:

Here, you can see how effectively we can analyse important information such as on a particular date, we can see the number of quantity of products sold in stores of each city.

This task would be daunting to analyse from the plain tabular database we had.

So, you have the features understood now, let us dive into making pivot tables more fun by defining conditional formatting in pivot tables.

Conditional Formatting in Pivot Tables

Now that we have made our pivot table, let us understand how to apply conditional formatting to it.

First let’s reduce some fields from our pivot table using the PivotTable field options and unchecking the date field.

We get,

Now we want to highlight the top two cities which have the most quantities sold. This can save time and prove beneficial when you have a long list of cities or any data set which you may find very difficult in analyzing these situations.

Steps:

1. Select the values of the field which you want to apply conditional formatting

2. Click on Conditional Formatting button found inside the Home tab of Excel

3. Then Click on Top/Bottom Rules -> Top 10 Items
4. Now, you’ll get a dialog box

Here, we replace the default value 10 to 2, so that only top 2 cells are displayed and we are highlighting it with Green Color with Dark Green Text

Then Click on OK

5. Now we have got our formatted table

In the above pivot table, notice that only the top two cities by quantities are highlighted. In our case Mumbai and Bangalore.

It is easy to determine the top cities by your own visualization only in our example set. But this method can help you to get valuable insights from large data sets as well.

Also, we can use a plethora of other options found inside the Conditional Formatting Options.

My favorite is the Data Bars formatting:

Which immediately gives some insights about the data being presented.

Hope this article was helpful to you.

Keep Reading!

Peace!