PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
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.
In this article , we would learn to create a pivot table from the scratch and using it in different ways.
we need to analyze large data in a short notice.
we need to total, summarize, category wise, sub category wise, perform different calculations like sum, average, or any custom calculation etc.
A collapsible presentation, which we can twist and present in our own way within a few seconds.
Play with rows and columns and create different reports.
we need to filter, group, sort and conditionally format the data.
Let us understand the steps to create pivot table using an example.
DATA SAMPLE:
The pic below shows the sample data which we will be analyzing through PIVOT TABLES.
It shows a number of students with their marks in different subjects. Lets check what we can analyze through pivot.
The data in the table given can be copied by you to try on Excel.
STUDENT NAME | MATHS | SCIENCE | ENGLISH | ART |
JOHN | 34 | 23 | 43 | 45 |
ABRAHAM | 43 | 25 | 45 | 34 |
ASHUTOSH | 45 | 45 | 33 | 43 |
KESHAV | 43 | 33 | 43 | 45 |
STEVE | 23 | 24 | 45 | 33 |
HAROLD | 45 | 87 | 34 | 34 |
STEPS TO CREATE A PIVOT TABLE:
Select a table or range: Enter the range manually or you can select it. We have already selected so a range will itself show in the box.
Use an external data source: Any external data source can also be used. It offers some online resources from various options. CLICK HERE TO KNOW MORE.
In the next options it asks whether you want the pivottable in a new worksheet or the existing one.
If you want in the existing one. Just give it the starting cell address and rest it’ll take care. Although it is always suggested to use the PIVOT TABLE in the new sheet so that it doesn’t mess up with our data.
After choosing the options, click OK.
After clicking OK, it’ll create a pivot table, [Although not a table yet] , in a new sheet or the same sheet as per our preference. Below is the first screen of the pivot table containing sheet.
By default it started showing the total of all the values.
Lets explore the options present in the values (lower right) field.
Click the first one to choose the operation on the first column.
When clicked the option presents in VALUES the above shown box opens us. You can apply the options shows on the condition e.g. move to row labels or to column labels etc. or you can directly drag and drop also.Lets go to VALUE FIELD SETTINGS.
You can choose the NAME OF THE COLUMN and TYPE OF CALCULATION TO BE DONE. Lets find out the average of the marks in first column. Choose AVERAGE from the given list as shown in the picture above.
THE COLUMN AND ROW NAMES HAS BEEN CHANGED MANUALLY AS THE SYSTEM GIVES ITS OWN NAMES WHICH MAY NOT SUIT OUR REPORT.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE