Pivot Table Calculated Field - Step by Step

Friends,

In the last two articles we have learned about how to create or insert a PivotTable and few Customization in Microsoft Excel. In this article we will discuss about Calculated Fields in Pivot Table.

But before that let us first know ...

Why should we use Calculated Fields and in which circumstances we should use this technique.

Let me ask you a question ?

Have you ever try to sum two columns in a Pivot Table ?

Let me clear it, if you want to sum first two columns using SUM function or using plus sign like sales value of product 1 & product 2 in the last blank column what will happen ?

You will find a long formula starting GETPIVOTDATA and six parameters (!!!), total value will be calculated for one cell. Okay, now if you drag it to sum the entire data range (that we normally do), you will get zeros all the cells except the first cell. This is because when you try to sum two cells from Pivot Table by default Excel uses the GETPIVOTDATA function with static cell ranges. This is the reason not to get the sum in all cells.

Now in this situation we have one option left i.e., sum by reference (i.e., mentioning that 'sum C2 cell with D2 cell' for example), but here is also we might have problems because in future if you want to change the Pivot Table structure there will be either a blank column between the Pivot Table and the newly created column outside the Pivot Table or Pivot Table will ask you to overlap the column which you just created.

But think if we have some technique to sum these two columns within the Pivot Table as individual column then these problems will vanish and additionally you will get an opportunity to add the new column whenever you need in the Pivot Table.

Yes... there is a process to do this in Microsoft Excel PivotTable, which is known as Calculated Field.

Calculated because you put your own formula to calculate from the Pivot Table column(s).

Field because your newly created column will be listed in Pivot Table as a new Column or Field.

How to Create Calculated Field in Pivot Table

       1.   Select any cell within Pivot Table, you will see two additional Tabs Options and Design

       2.   Click on Option Tab

       3.   Under Tools submenu, click Formulas beside PivotChart

       4.   Click Calculated Field

Calculated Field Options Tab Then Formulas

       5.   A new small window will appear, "Insert Calculated Field"

a)  In the first input box labeled as Name type a name for your Field, for example, 'Sum Of Three Products'

b)  Go to next input box either by clicking in the box or you can hit TAB button

c)   Here you will put formula to be calculated, e.g., I need sum of three columns so I typed SUM after equal sign and selected the fields separated by commas, you can use plus sign without Typing SUM.

d)  To select the fields to be added you need to select it, and either you can double click or can click on the Insert Field Button, notice a single opening and closing quote is automatically printed in the formula box for each item.

e)   Click OK and you are done, you can now see a new field in the Pivot Table and you can also find it within the PivotTable Field List Box.

Insert Calculated Field Window

Create Calculated Field Window - Using Formula

New Field Added as a Column within Pivot Table

New Field Added as a Column within Pivot Table

This is how to create Calculated Field in Pivot Table. Now I will show you...

How to Use Logical Function IF in Calculated Field in Pivot Table

To use logical IF function within Pivot Table as Calculated Field the steps are same as mentioned earlier, remember if you use IF function in Calculated Field it only displays the numbers after calculation, no Texts are allowed, if you try to evaluate Text based upon the result of IF it will give you #VALUE error.

Please watch carefully the following picture you will understand how to use IF in Calculated Fields.

Using IF function in Pivot Table Calculated Field

Using IF function in Pivot Table Calculated Field

Remove or Delete a Calculated Field from PivotTable

It is very simple.

From Options tab click Formulas to open the Insert Calculated Field window.

From Name drop down list select the Field you want to Delete, click Delete button below Modify button, click OK and done.

Remove or Delete a Calculated Field from PivotTable

Modify a Calculated Field from PivotTable

If you need to rectify formula or the field name, its' better to do it through Modify button.

From Options tab click Formulas to open the Insert Calculated Field window.

From Name drop down list select the Field you want to Modify. Make necessary changes in the Name or Formula and then click Modify button and click OK and you are done.

Modify a Calculated Field from PivotTable

Calculated Field in Pivot Table

Few Things About Calculated Fields in PivotTable

Limitations

       1.   Calculated field formulas cannot refer to the pivot table totals or subtotals

       2.   Calculated field formulas cannot refer to worksheet cells by address or by name.

       3.   Sum is the only function available for a calculated field.

       4.   Calculated fields are not available in an OLAP-based pivot table. (OLAP - Online Analytical Processing, we'll discuss in another article).

Features

       1.   For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.

       2.   Calculated fields are automatically available in all pivot tables that are based on the same pivot cache

That's all friends for today. Hope the topic discussed today is easily understandable. Keep reading & keep asking & commenting...

Thank you ...

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