Table Of Contents

arrow

Pivot Table in Excel

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Ramya Puttaparthi Tirumala

Reviewed by :

Dheeraj Vaidya, CFA, FRM

How to Create a Pivot Table in Excel?

The Pivot Table is created by using the following methods:

Method #1

Pivot Table in excel can be created using the following steps

  1. Click a cell in the data worksheet.

  2. In the “Tables” section of the “Insert” tab, click “Pivot Table.”


    pivot table method 1.1

  3. A “Create Pivot Table” window appears (as shown below).


    excel pivot table method 1.2

  4. Now under the option “Choose the data that you want to analyze,” Excel automatically selects the data range.

  5. The data range is displayed in the “Table/Range” box under the “Select a table or range.”

  6. In the “Table/Range” option, we can verify the selected cell range (shown in the below image).


    excel pivot table method 1.3

  7. The next step is to choose the location of the Pivot Table report. The worksheet can either be the existing or a new one.

  8. In this example of Excel Pivot Table, cell D1 is selected. Under the option “Choose where you want the Pivot Table report to be placed,” select “Existing Worksheet” (shown in the below image).


    excel pivot table method 1.4

    Note: Select “New Worksheet” if we prefer to insert a Pivot Table on the new worksheet (as shown in the below image).

    excel pivot table method 1.6

  9. To analyze multiple tables, go to the option “Choose whether you want to analyze multiple tables” and click the below checkbox “Add this data to the Data Model.”


    excel pivot table method 1.5

  10. Click “OK” to insert the Pivot Table in the worksheet, depending on the location selected.

    If we select “New Worksheet,” the “Pivot Table 1” is placed on the new empty worksheet.

    The succeeding image shows a column named “Pivot Table Fields” on the right-hand side. This shows a list of fields or columns to be added to the Pivot Table report.

    Now, the Pivot Table is created on the Column range A (“Maths”) and Column B (“Science”), respectively. The same is displayed in the “Fields” list (shown in the below image).

    excel pivot table method 1.7

Method #2

  • At first, select the data range. It is an input to the Pivot Table.
excel pivot table method 2.1
  • Go to the “Insert” tab and select “Recommended Pivot Tables.” This option provides the recommended ways of creating Pivot Tables. The user can select and choose one among the given recommendations.

excel pivot table method 2.2
  • The below image shows the two recommendations given by Excel. One is the “Sum of Maths by Science,” and the other is “Sum of Science by Maths.”
excel pivot table method 2.3
  • When you click one of the options, the actual Pivot Table along with the values, opens in the right-hand-side panel.
excel pivot table method 2.4
  • Here, the “Group by” option provides the following ways of grouping:
  1. “Add Science column marks group by Maths column marks” 
  2. “Add Maths column marks group by Science column marks”
  • The user can select either of the two ways of grouping. The grouped data is displayed in ascending order (for both the ways of grouping).
  • The other option is “Blank Pivot Table.” To create a new Pivot Table, click “Blank Pivot Table” box. It is displayed at the bottom (left-hand side) of the “Recommended Pivot Tables” window as shown in the succeeding image.
  • This step will follow the “Method 1” (mentioned in the previous section) of creating a new Pivot Table.
excel pivot table method 2.5

Pivot Table Uses

Let us understand the uses of the Pivot Table with the help of the below-mentioned case studies:

#1 - “Max” of Science marks by Maths marks

The table below provides the marks of the subjects Maths and Science in Column A and Column B, respectively. The given data is selected to create the Pivot Table in excel.

Now generate the Pivot Table report to find the maximum number which is present in the “Science marks column” by “Maths marks column” values.

excel pivot table method 2.1

Let us follow the steps shown in previous sections - “Method 1” or “Method 2” to generate the Pivot Table.

  • Now, drag “Maths” marks to the “Rows” field and “Science” marks to the “Values” field.
excel pivot table example 1.4
  • Right-click on the Pivot Table and select “Value Field Settings.”
excel pivot table example 1.1
  • Now select the “Max” option from the “Summarize value field by” option in the window.
excel pivot table example 1.2
  • The “Max” option returns the maximum number present in the Science marks (represented in Column B of the table below).
excel pivot table example 1.3

#2 - Average of Maths Marks Column

A list of Maths and Science marks is provided in Column A and Column B of the table below. Generate the Pivot Table report on the average number of the Maths marks (Column A).

EX 2.1

Select the data in Column A (Maths marks) to create the Pivot Table.

Let us follow the below steps to find the “Average” of the Maths marks in Column A.

  • Create a Pivot Table and drag “Maths” in the “Rows” field.
Pivot Table in Excel - Average - Maths
  • Right-click on the Pivot Table and select “Field Settings.”
EX 2.2
  • In the “Field Settings” window, select the “Custom” button under the tab “Subtotals & Filters.” 
  • Below this tab, choose the “Average” function.
EX 2.3
  • Now drag “Sum of Maths” in the “Values” field.
Pivot Table in Excel - Average - Sum of Maths
  • Right-click on the Pivot Table and select “Value Field Settings.”
EX 2.8
  • In the “Value Field Settings” window, go to the “Summarize value field by” tab. Then select the “Average” option.
EX 2.9
  • The Value Field is selected as “Average,” which returns the average value of 77.4 as a result in the Pivot Table Report.
EX 2.10

Similarly, other numeric operations can be performed on the given dataset. The dataset can also be filtered to fit the ranges as per the requirement.

Frequently Asked Questions (FAQs)

1. What are Pivot Tables used for?

A Pivot Table is used to summarise, sort, group, reorganize, and count the data in a Worksheet. It calculates the total and average of the data provided in a table. It allows the transformation of data from columns into rows and rows into columns, respectively. It also permits the grouping of data by any field or column. It also performs many advanced calculations on the data.

2. What is a Pivot Chart in Excel?

A Pivot Table is a built-in feature of Excel. It helps organize, visualize, and summarize the selected columns and rows in a spreadsheet. It helps to obtain a desired report on the given data.

3. What is the difference between Pivot Table and Pivot Chart?

Pivot Tables allow us to view the data summarized in a grid of horizontal and vertical columns. It is also used to extract information from a large dataset.

Pivot Charts are the visual representation of the Pivot Table data. It helps to summarize and analyze the datasets and patterns.

  • Pivot Table is a data processing technique in Excel.
  • Pivot Table is used to summarize data and extract information from a large dataset.
  • Pivot Table assists in making dashboards and reports based on a data source.
  • To create Pivot Table, click the “Tables” section under the “Insert” tab. The keyboard shortcut is - Press “ALT+D+P.”
  • Pivot Chart is a visual representation of Pivot Table, which allows us to summarize and analyze the datasets and patterns.