How to Create a Pivot Table in Excel?

The pivot table in Excel which allows you to arrange and summarize the complex data easily. You can create a pivot table within a few seconds without worrying about any formula. There are many advantages of using the pivot table as it fast and flexible. Below are the benefits of using the pivot table.

Benefits of Pivot Table:

• A pivot table allows you to summarize and calculate the data according to your requirement.
• A pivot table allows you to apply filter the pivot table values.
• You can change the type of calculation to summarize the data in the pivot table.
• You can also change the number format of the values in your pivot table.
• You can select the column header and row header of your choice.
• Pivot table once build allows you to change the filter field column header, row header, value filed as you need.
• It gives you accuracy and flexibility.
• Pivot table updates its data when you do any changes in the table on which your pivot table is based. You just need to click the ‘Refresh‘ button in Analyze tab.
• Pivot table provides the summarized result faster than the other tool in Excel.

In this section, we will discuss the steps of creating the pivot table. We will also see some short hands-on the pivot table.

Steps to Create a Pivot Table in Excel:

Step 1: Select any random cell in a normal table of which you want to create a pivot table. Click on the Insert tab of the same sheet.

Step 2: Under the Insert tab click on the Pivot Tables in the Tables group.

Step 3: A dialog box ‘Create Pivot Table’ will get open which will prompt two requests. First is to ‘choose the data of which you want to create Pivot Table’ which is already fulfilled as in step 1 as we have selected a random cell in the data of which we have to create a pivot table.

Second is where you have to create the pivot table; in the New Worksheet or in the Existing Worksheet. You have the Location box below where you have to select the location for your pivot table.

I have created the Pivot table beginning from the cell H5 in the Existing sheet only.

Click OK.

 

Step 4: A ‘Pivot Table Fields’ dialog box will appear. Here, you can decide the arrangement of your pivot table. You have a scroll box below the search box where you have fields from your selected table.

At the bottom, you have four areas Filters, Columns, Rows and Values. To build the pivot table you have to drag the fields from the scroll box to the desired area below.

Like, in the image below you can see that I have dragged Date field from scroll box to Filters area, Sections to Columns, Staff to Rows and Lectures to Value which by default shows the sum of all the values in the field.

Look at the left side of the image above, you have all the sections in the column header and name of all the staffs in the row header. The values for this pivot table are the values from the Lecture field. Above the table, you have one filter, Date as we have dragged Dates field in the Filters area.

Filter

In the image below, the  Filter area of the pivot table has the Date Field. Using the filter down arrow you can select the date values for which you want to view the data in Pivot table. Like I have selected the date 04-01-2020. So, the pivot table will show the data for the selected date only.

Adjusting the Setting of Values in Field

Step 1: As by default the values in the pivot table are summed. To change the setting click on the down arrow beside the field in the Value area. Among the options click on ‘Value Field Settings’.

Step 2: Value Field Settings dialog box will open where you can choose the type of calculations to summarize the data from the selected field.

You can also change the Number Format of the values. For that click on Number Format.

Detail Information for the Values in Pivot Table

Step 1: Like, I want to see how the grand total for the number of lectures taken by Jhonny on 04-1-2020 is 7. So, I double click the cell containing the Grand total value for the number of lectures taken by Jhonny.

Step 2: A new sheet will be generated just before the sheet in which you have the pivot table, to display the details of the cell you double-clicked in the pivot table.

In the image below, you can see that on 04-01-2020 Jhonny has taken 4 lectures on Cryptography in section A and 3 lectures on Computer in section C. So, the total number of lectures taken by the Jhonny is 7.

Pivot Table Tools

Excel provides you with the two tabs, Analyze & Design which have the tools to Analyze and Design the pivot table. You can explore the tools according to your requirements.

So, this is how one can create a pivot table and manipulate it to obtain the summarized result as you want. You can explore to get more.