QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Create a Pivot Table in Excel?

By Neha T Leave a Comment

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.

Create Pivot Table in Excel 1

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

Create Pivot Table in Excel 2

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.

Create Pivot Table in Excel 3

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.

Create a Pivot Table in Excel 3-1

 

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.

Create Pivot Table in Excel 4

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.

Create Pivot Table in Excel 5

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.

Create Pivot Table in Excel 6

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’.

Create Pivot Table in Excel 7

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.

Create a Pivot Table in Excel 8

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

Create Pivot Table in Excel 9

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.

Create Pivot Table in Excel 10

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.

Create Pivot Table in Excel 11

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.

Create Pivot Table in Excel 12

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.

You Might Also Like:

delete blank row feature imageHow to Delete Blank Rows in Excel 2016? how-to-sort-data-in-ms-excel-2016-featured-imageHow to Sort Data in Ms Excel 2016? Merge Cell in Excel feature imageHow to Merge Cells, Columns & Rows in Excel? Graph in Excel - featured ImageHow to Create a Graph in Excel? how-to-create-macro-in-ms-excel-2016-featured-imageHow to Create Macros in MS Excel 2016? how-to-create-links-in-ms-excel-2016-featured-imageHow to create Links in MS Excel 2016? how-to-create-a-drop-down-list-in-ms-excel-2016-featured-imageHow to Create a Drop-Down List in MS Excel 2016? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to Reset Password in Gmail Through an Automated Call
  • How to Change Start Pages in Google Chrome?
  • How to Disable Control Panel in Windows 7
  • How to Personalize Your System Appearance in Windows 10
  • How to enable picture in gmail chat window

Copyright © 2023 · QueHow · Contact Us