PIVOT TABLE is a well known feature of EXCEL which everybody of us might have heard of. But many times we don’t know how to effectively use the PIVOT TABLE.
PIVOT TABLE is a dynamic table which we can create in Excel. We called it dynamic as we can transform it within seconds. The original data remains the same.
We know that whatever is hinged to a pivot, can rotate here and there, so is the name given to these tables.
PIVOT Table is a very powerful tool to summarize, analyze explore the data in very simple steps. Its very important to learn the use of pivot tables in excel if we want to master excel. Pivot tables give us the facility to put different simple operations on a selected data in seconds.
We have already learnt about creating PIVOT TABLES HERE.
In this article we would focus upon the situation when we need a new column in the PIVOT TABLE. The data of the column will be based upon a calculation from the already present columns.
When we create a PIVOT TABLE from the given data, we have the choice of putting the information into different rows or columns.
What if we want to manipulated the data and create some new information in the PIVOT TABLE itself.
For this problem, we have got the option of inserting the CALCULATED FIELD.
A CALCULATED FIELD IS A COLUMN WHICH CAN BE INSERTED IN A PIVOT TABLE AND ANY CUSTOM CALCULATION CAN BE DONE IN THAT CUSTOM CALCULATION.
We already learnt how to create a PIVOT TABLE . [ CLICK HERE ]
LET US TAKE AN EXAMPLE TO LEARN THE PROCESS OF INSERTING THE CALCULATED FIELD IN PIVOT TABLE.
Let us find out the Maximum and Minimum Rainfall of every cities in the given week.
Convert the above table into PIVOT TABLE using the standard procedure as discussed here.
STEPS TO CREATE PIVOT TABLE FOR THE GIVE INFORMATION
As for our example, we want to find out the maximum rainfall in the week , we’ll put the formula as
=MAX(SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY)
Note the formula style. We used the HEADERS to declare the formula. The individual cell addresses won’t work in the Pivot tables.
As soon as we put the correct formula, the values would fill up our column.
Let us now similarly find out the MINIMUM RAINFALL.
For the minimum rainfall, repeat the process of ADDING A CALCULATED FIELD UNDER THE VALUES and put the formula as
=MIN(SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY)
After we have created both the calculated fields, change the column names to MAXIMUM RAINFALL and MINIMUM RAINFALL.
CLICK TO LEARN HOW TO RENAME THE COLUMN IN GOOGLE SHEETS.
The final pivot table is shown below.
CALCULATED FIELDS ARE THE PART OF PIVOT TABLE NOW WITHOUT AFFECTING THE SOURCE TABLE.
YOU MAY LIKE