PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

HOW TO USE PIVOT TABLE IN EXCEL?

PIVOT TABLE IN EXCEL
OUR FIRST PIVOT TABLE

INTRODUCTION

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.

BUTTON LOCATION OF PIVOT TABLE IN EXCEL

We can find the option to insert PIVOT TABLES under the INSERT TAB under TABLES subsection.

BUTTON LOCATION PIVOT TABLES EXCEL
PIVOT TABLES BUTTON LOCATION

BENEFITS OF USING PIVOT TABLES IN EXCEL

Let us discuss the benefits of using pivot tables and its uses in Excel.
Pivot tables are used when
  • 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.

STEPS TO CREATE PIVOT TABLE IN EXCEL

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 NAMEMATHSSCIENCEENGLISHART
JOHN34234345
ABRAHAM43254534
ASHUTOSH45453343
KESHAV43334345
STEVE23244533
HAROLD45873434
example of pivot table
DATA SAMPLE FOR PIVOT TABLE

STEPS TO CREATE A PIVOT TABLE:

  • Select the data including  headers ,go to insert tab and click PIVOT TABLE BUTTON.
  • Click PIVOT TABLE BUTTON. A dialog box will open as shown in the following pic.
PIVOT TABLE DIALOG BOX EXCEL
PIVOT TABLE DIALOG BOX
  • As we have already selected the table, the range would show up automatically, otherwise we can put it manually or by selecting the table.
  • Choose the location for the pivot tables. It can be on a NEW WORKSHEET or EXISTING WORKSHEET. If existing worksheet, we need to tell the starting cell location.
  • CLICK OK.
  • Our PIVOT TABLE is ready.
* We have discussed all the options in detail in the next portion of the article.



PIVOT TABLE IN EXCEL
OUR FIRST PIVOT TABLE

UNDERSTAND DIFFERENT OPTIONS AND SCREENS WHILE CREATING A PIVOT TABLE

CREATE PIVOT TABLE DIALOG BOX OPTIONS

PIVOT TABLE DIALOG BOX EXCEL
PIVOT TABLE DIALOG BOX

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.

pivot first screen excel
PIVOT WELCOME SCREEN
Click all the fields available on the right box named PIVOT TABLE FIELD LIST.
Now let us understand the different areas of the PIVOT TABLE SHEET.

PIVOT TABLE SHEET DESCRIPTION IN EXCEL

DETAILS

Pivot table screen has the following area. LOOK AT THE PICTURE BELOW FOR BETTER UNDERSTANDING.
Let us discuss all the areas one by one.
CHOOSE FIELDS TO ADD TO REPORT: This field consist of all the columns [fields] in the checkbox format. Click the checkbox and that field will be used to create a table. When we click it, Excel will automatically guess about its use and place it in any of the box. [Of course, we can change it as per requirement].
 
REPORT FILTER: It is an optional area. We can create filters there to keep the data we want. We can create a filter of any of the field and choose the data according to that.
 
COLUMN LABELS: This field will contain the columns of our pivot tables. For examples, if we need the values, we need to select values from the dropdown list there.

ROW LABELS: The fields which we need as rows.
 
VALUES: The output values area. We need to specify the operation which must be put on our data so that we get the output. We can select many operations in this field which we will see later in this article.
We put the fields in this area which we want to calculate.
PIVOT TABLE SCREEN DETAILS
PIVOT TABLE SCREEN DETAILS

CHANGE THE CALCULATION FORMULA IN PIVOT TABLE IN EXCEL

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.

OPERATION FIELD PIVOT TABLES EXCEL
PIVOT TABLE OPEARTION FIELD

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.

VALUE FIELD SETTINGS DIALOG BOX
VALUE FIELD SETTINGS DIALOG BOX

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.

AVERAGE OF MARKS EXAMPLE PIVOT TABLE EXCEL
AVERAGE OF MARKS

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

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: