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 ...

Pivot Table in Microsoft Excel - Pivot Table Field List Report Functions of Filter Column Labels Row Labels Values

Friends,

So far we have discussed about various Excel functions for example VLOOKUP, HLOOKUP, IF, SUMIF, COUNTIF etc, we discussed about conditional formatting, how to find duplicate or unique values, we used tools like filter & sort etc., and  now in this article we will discuss about PIVOT TABLE - which is a very easy and excellent tool provided by Microsoft Excel to summarize a very large data. 

There is lot of things in Pivot Table which can help you in many ways to prepare your data for analysis. This tool in Excel has many customization options to facilitate the data analysis. Proper use of Pivot Table in Excel can reduce the time taken to prepare a report to view or to analyze.

Pivot table is a tool which can summarize large data. We use Pivot Table in Excel to get customized summary from a large or bulky data. Pivot table summaries data by combining the duplicate figures into one unique figure or data. Pivot Table appears to be a very complicated tool in Excel for some people but it is not that complicated if you understand the functions of its various parts. Creating a Pivot Table is very simple, understand your requirement and plan accordingly before inserting Pivot Table. There are few rules which you have to follow to insert a pivot table, these are listed below.

    1.   You need an organized data, here 'organized' means, there should not be any blank column or rows in a Table of data

    2.   Each Column must have a column header or column name.

    3.   No merged column header should present in the column header or within the data

How to Create or Insert a Pivot Table

1.   Select a cell within your data.

2.   Go to Insert Tab, in Tables sub-menu click PivotTable

Create PivotTable Window

Insert PivotTable Table Option

3.   A small window will appear "Create PivotTable" as shown above, you can notice that there is four radio buttons, of which the first one is 'Select a Table or Range' and this option is automatically detects the data range. Check this data range, if it is not OK select it.

4.   In the next block you can notice there is another two radio buttons below the first one, New Worksheet and Existing Worksheet. Click OK if you want to insert PivotTable in new Excel Sheet in the same Workbook or you may select the next radio button and provide the destination cell in the same Excel Sheet. Click OK, I always use New Worksheet.

5.   Now you will  see a Blank format in the Excel sheet and at the extreme right one small tool window named PivotTable Field List - this is the column selection window and your PivotTable design will depend upon this arrangement and therefore, it is very important. Now please watch carefully the below snapshot.

PivotTable Field List Window

6.   This PivotTable filed chooser window has few parts (bordered by red), four small boxes, you need to understand the function of these boxes. We will start from the data part for the ease of understanding.

            a) Values : In the extreme right and in the bottom of PivotTable Field List window you will find a little box named as Values, here you need to put the data part i.e., data column e.g., Sales Target, or Sales value etc., you can put one or more than one fields or columns by either ticking column headers displayed in 'Choose fields to add to report' or you can Drag & Drop the columns in this box.

            b) Row Labels : In left of the Values box there is another small box named as Row Labels, you need to put that column for which you need the data, e.g., Zone, Employee Name, Employee code etc., remember this is not your data part. You will get summarized data respective to this field, for example, North Zone -> 50 (Target), as shown in the bellow picture.

Basic PivotTable

We have just created a basic Pivot Table in Excel. Now let us understand the functions of another two boxes namely Report Filter & Column Labels

           

            c) Report Filter : Now let suppose in my data I need to derive employee wise performance or Target but one by one Employee Code. Here we can use Report Filter, the box located above the Row Labels box. Just Drag & Drop Employee Code in this box and you are done. You will now see a new entry in the top of the Pivot Table as Employee Code (All), with a drop down list. You can select any item in this drop down list and the related data will be displayed in the Pivot Table hiding all other data.

Please refer to the below image.

Use of Report Filter in Pivot Table

            d) Column Labels : Now let suppose I need the data date wise, date will be column header and the Target value will be date wise & day wise. Here we need to put the date column in Column Labels, as shown in the below picture.

Use of Column Labels in Pivot Table

This is the basic methods to use PivotTable in Microsoft Excel. Hope you have fully understand the functions of four magical boxes in Pivot Table. If you have any problem in this part please post in comment box and in my next article I will show you some amazing techniques in Pivot Table in advanced level, till then keep reading...

Thank you...

How to Find Trace Copy or Count Duplicates in Microsoft Excel Using Formula

Hi Friends,

The beauty of Microsoft Excel is that if you think logically then it is possible in Excel. Today we will discuss about such a topic which will make you realize the beauty of Excel.

In my last article I have discussed about how to identify Duplicate or Unique values in Microsoft Excel using Excel's inbuilt tools like conditional formatting. Hope you have got enough knowledge about Conditional Formatting and about Duplicates in Excel.

Today we will continue discussing about Duplicates in Excel but using Formula. We will discuss about

1.   How to Count Duplicates or Unique values Using Formula and then

2.   We will copy or pick or collect the Duplicate or Unique value using Formula

So lets' start. For easy understanding we'll list it step by step.

Find Duplicate or Unique Using Excel Formula

Please have a look at the above picture where light yellow highlighted colored cells contains Duplicate values which I highlighted using Conditional Formatting. Now we need to fill the next four columns using formulas.

The beauty of Excel is that you can do any logical things using proper functions and nesting them with each other.

How to Count Duplicates or Unique values Using Formula

1.   We know COUNTIF function counts specific value or character within a specified range. So we can use this function to count how many times one specific number is appearing in a given range.

2.   The syntax of COUNTIF function is COUNTIF(range, criteria), in our example this formula becomes =COUNTIF($C$3:$C$16,C3) as shown in the below image.

3.   The trick is we provide a range (here it is C3:C16) and put dollar sign to fridge it by pressing F4 button and then we provide the number we are looking for Duplicates (here it is "C3").

4.   If the output of COUNTIF function is more than one then this number has duplicates in the given range else no duplicate value is present.

Find & Count Duplicate or Unique Using Excel Formula

5.   Now if you want to write the word 'Duplicate' against duplicate and 'Unique' against unique value in a data range, we just need to update the formula using IF function as shown in the below picture.

the formula is : =IF(COUNTIF($C$3:$C$16,C3)>1,"Duplicate","Unique")

Identify Duplicate or Unique Using Excel Formula

So this is the trick to find the duplicate values using formula, nesting of IF and COUNTIF function now we will move to the second part of this article.

How to Copy or Pick or Collect the Duplicate or Unique values using Formula

This part is also same as the above method or trick, what we need to change is that we need to make a nested formula using IF & COUNTIF function as above example.

Now the tweak is put a conditional checking part to judge whether the count of number is more than one or not. If the count of the provided number is more than one, then we need to print this number if we need duplicate value by referring the cell (C3 for example in the formula =IF(COUNTIF($C$3:$C$16,C3)>1,C3,"") in the value if true part of IF function and one pair of double quotes to print nothing if this number is unique. Please refer to the below image for further clarification.

Identify and Copy Duplicate or Unique Values Using Excel Formula

The reverse is the case if you want to get the data for Unique value.

I hope you have enjoyed this article and realize the beauty of  Nesting in excel.

Identify and Copy Duplicate or Unique Values Using Excel Formula

Keep reading for new ideas in Excel...

Thank you for reading...

How To Remove Duplicate Values In Excel - Identify and Highlight Duplicate Values

Hi Friends,

In this article  we'll discuss about how to find Duplicates or Unique values in Microsoft Excel. In Microsoft Excel duplicate values may come anywhere in a column or in a row and sometimes it is the situation's demand to highlight or find out these duplicate values or to remove or count these duplicate values.

A duplicate value is one where all values in the row are an exact match of all the values in another row. Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell.


So, our requirements can be listed as follows

1. Filter or Identify or Highlight Duplicate or Unique values

2. Remove or Delete Duplicate values

3. Remove or Delete Duplicate rows or columns

4. Count Duplicates or Unique values

The easiest way to identify and highlight the duplicate values is to click on Conditional Formatting button in the Styles group in Home tab. Then point to the first item in the drop down list named as Highlight Cells Rules and this will expand and at the bottom of this expanded list you will find Duplicate Values option Click on it as shown in the below images. 

Highlight Duplicate Values

Highlight Duplicate Values

A new window will appear as Duplicate Values as shown in the above image, select duplicate or unique as per your requirement from the drop down list. You can also select the cell color or the font color from the drop down list or you can customize the highlight color of this list. 

Now your data will highlight the duplicate or unique values as shown in the below image.

Highlight Duplicate Values

We have successfully identified and highlighted the duplicate values, you also can identify or highlight the unique values by the same techniques, only you need to change the duplicate option to unique in the drop down list.

Remove Duplicate Values or Filter Unique Values

In Excel, you have several ways to filter for unique values or remove duplicate values:

• To filter for unique values, use the Advanced command in the Sort & Filter group on the Data tab.

• To remove duplicate values, use the Remove Duplicates command in the Data Tools group on the Data tab.

• To highlight unique or duplicate values, use the Conditional 
Formatting command in the Style group on the Home tab as stated above.

Filtering Unique Values or Removing Duplicate Values

Filtering unique values and removing duplicate values are two closely related tasks because the displayed results are the same, a list of unique values. The difference, however, is when you filter for unique values, you temporarily hide duplicate values, but when you remove duplicate values, you permanently delete duplicate values.

Therefore, it's a good idea to filter for or conditionally format unique values first to confirm that the results are what you want before removing duplicate values.

Filter for Unique Values

1. Select the range of cells, or make sure the active cell is in a table.

2. On the Data tab, in the Sort & Filter group, click Advanced.

Advanced Option to find Duplicate Values

3. In the Advanced Filter dialog box, do one of the following:

    a. To filter the range of cells or table in place, click Filter the list, in-place.

    b. To copy the results of the filter to another location, do the following:

          i. Click Copy to another location.

          ii. In the Copy to box, enter a cell reference.

Alternatively, click Collapse Dialog to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Dialog.

4. Select the Unique records only check box, and click OK.

The unique values from the selected range are copied to the new location.

Remove duplicate values

When you remove duplicate values, only the values in the range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved.

Because you are permanently deleting data, please make a copy the original range of cells or table to another worksheet or workbook before removing duplicate values so that if you find something wring, you can rollback the data.

1. Select the range of cells, or make sure that the active cell is in a table.

2. On the Data tab, in the Data Tools group, click Remove Duplicates.

Remove Duplicates

3. Follow the below procedures:

• Under Columns, select one or more columns.

• To quickly select all columns, click Select All.

• To quickly clear all columns, click Unselect All.

• If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.

4. Click OK.

A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed as shown in the below picture.

After Removal of Duplicate Values

5. Click OK and you are done.

Please Note: You cannot conditionally format fields in the Values area of a Pivot Table report by unique or duplicate values.

Thank you for reading this article...