Customize Pivot Table - Step by Step

Friends,

In the last article we have learned about how to create or insert a Pivot Table in Microsoft Excel but that was the basics of Pivot Table, now in this article we will discuss more about the Pivot Table customization.


Pivot Table summarize large data, but for analytical purpose it needs few modifications to make pivot table report more attractive and beautiful. One thing I would like to share with you which I have experienced in my carrier that any report you prepare (summary is also a report) in Microsoft Excel or other Spreadsheet application or any other application, one of the important things are the beautification of the report and the presentation of the report, should be easily understandable and the data provided in the report should be accurate in an organized way.


The tool, Pivot Table is equipped with all these properties and more. You will find this tool as very easy and useful tool and you will be surprised how important this tool is !!!

So, lets' move to the discussion.

Basic Excel Pivot Table

The Pivot Table we have created in the last article (above image), having 

     1. 'Total' column wise and row wise but row wise total is at the top of the Table which we are not accustomed. Moreover, 

     2. Two column headers in the original data are coming in one column in Pivot Table

     3. The Column Header is replaced by Row Labels

These three things needs to be customized, lets' Customize Excel Pivot Table step by step.

          a) Select in cell in Pivot Table, after clicking two more Tabs' will appear, Options Tab & Design Tab

          b) In Design Tab under Layout menu Click Report  Layout button

          c) Click 'Show in Outline Form', you will notice Row Labels column header has split into two column header which was present in the original back-end data, Zone & Day for our example.
The Pivot Table created by default takes the report layout in Compact form, by clicking 'Show in Outline Form' you just changed the layout to Outline form.

Customize Basic Excel Pivot Table Outline Form

          d) Now we need the Total Column in our Pivot Table from Top position to Bottom Position, to do this select any cell in the Pivot Table, 
                      i. Click in Options Tab
                      ii. In the PivotTable category click on Options button.
                      iii. A new window will appear PivotTable Options as shown in the below image. 

Customize - PivotTable Options Window - Classic PivotTable Layout

                      iv. Click on Display Tab
                      v. Click to give a Tick mark in the Classic PivotTable Layout Checkbox.
                      vi. Click Ok and done.
                      vii. Alternatively, you can right click in the PivotTable and select Pivot Table Options..., Pivot Table Options window will appear then follow the above steps and Done. 

Now you can see Row Totals are showing one after another block and it is below the data.

PivotTable Options On Right Click of Mouse

Formatting Pivot Table

Now we will again customize PivotTable to make it more beautiful by applying color and border step by step i.e., formatting Pivot Table. 

If you try to format Pivot Table Like any other table formatting by color and border, it will not be permanent, whenever your data changes these formatting will be erased, therefore, you need some technique that will make PivotTable formatting dynamic which will not change if the data changes. 

There are various methods to format Pivot Table so that it looks beautiful by implementing Dynamic methods to format Pivot Table.

Method 1

             1. Select any cell in Pivot Table
             2. Click Design Tab
             3. In the Pivot Table Styles Tab you will find various styles, select any one from these styles. If you hover over these styles you can see the preview in the Pivot Table.

Pivot Table Styles to Make Pivot Table Beautiful

Pivot Table Styles to Make Pivot Table Beautiful

Method 2

             1. Move the cursor to any row total, it can be easily identified because this cell contains the word Total. A small black arrow will point to the right of this row, click here, you can notice the whole Row Total is selected with the other row Totals.

             2. Now in the Home Tab in Font group select Font Color and Fill color, these rows reflect the color you choose.

             3. We need border in the Pivot Table, therefore, click the drop down arrow in Border Icon and

             4. Select any Borders as per your choice. The selected Rows will be bordered.

             5. Now hover over the Data part, in my example it is the date. A small black down arrow will appear, click here to select the whole column, if you want to select multiple column you can do it by dragging it to the last column.
             
             6. Select border as per your choice. And you are done.

Pivot Table Font & Fore Color - Your Own Customized Color

Fill Blank cells With Zero in Pivot Table

How to remove blank cells in Pivot Table ? 

Often I get this question from my colleagues and friends and students. This appears to be a difficult task to everybody - to fill the blank cells with Zero in Excel Pivot Table. Difficult, because you will not be able to edit Pivot Table cells. To do this you need to follow the following steps.

             1. Open Pivot Table Options window either by right clicking in the Pivot Table or from Options Tab to Options button.

             2. In the Layout & Format Tab you will find For Empty Cells Show option in checkbox in the Format section. 

             3. Type '0' in the text box provided as shown in the below image. 

             4. Click OK and you are done, all blank cells will now show zero.

Fill Blank Cells With Zero in PivotTable

Dynamically Colored Customized PivotTable

That's all friends for today. In my next article I will show you some advanced setting in Pivot Table including how to use Excel formula and functions within the Pivot Table. Enjoy reading...
Thank you ...