• Home
  • News
  • Key Concepts
  • How To
  • Windows
  • Apple
  • Android
  • Best-Of
  • Reviews

IT4nextgen

Tech Tutorials and Reviews

IT4nextgen > How To > How to Create a Simple Pivot Table in MS Excel

How to Create a Simple Pivot Table in MS Excel

Last Updated February 13, 2023 By Subhash D Leave a Comment

MS Excel is an essential spreadsheet program that allows you to organize your data in the form of rows and columns and perform various mathematical functions on it. MS Excel provides various functions that give you the facility to organize and understand the data in a result-oriented manner. One such feature of MS Excel that is of great help is the use of Pivot Tables. In this article, I will cover all the aspects related to creating and using Pivot Tables.

What actually is a Pivot Table?

The first thought which would come to your mind is ‘What is a pivot table actually’? So, let me answer this question for you.

A Pivot Table is a specialized tool of MS Excel that allows you to reorganize a large amount of data stored in the worksheet for obtaining useful results, comparisons, or even trends based on summarized data.

The pivot table will enable you to create reports. From these reports, you can keep a close eye on the progress being made or make out the fields that need more attention.

Let us have a look at how to create pivot tables.

Creating Pivot Tables

Creating pivot tables is accompanied by some preparations too. In order to create a pivot table, the first and most important thing you will need is the range of data that you want to analyze.

When preparing data, you have to take note that there must be no blank row or column in between the dataset. This is essential as MS Excel will not consider any rows or columns after the blank row or column is encountered. Delete all blank rows or columns present in your data.

Have a look at the prepared sample database.

screenshot of tutorial on pivot table

Locating the Pivot Table Tool

After the dataset is ready, you can create a pivot table. The Pivot Table tool is present in the Insert tab of the MS Excel ribbon.

Screenshot_2 of pivot table creation in excel

How to Create a Pivot Table

Follow the following steps in order to create a pivot table.

  • Select the cells containing the dataset.
  • Click on the pivot table tool from the Insert Tab. You can skip step 1 and select one of the cells from the dataset and directly click on the pivot table tool. Excel will automatically select the dataset for you.
  • On clicking on the pivot table tool, the following dialog box will appear on the screen.
image of pivot table creation window
  • In this dialog box, the table/range represents the dataset. You can choose an external data source too.
  • The next thing to choose is where you would want to place the pivot table report. In the same worksheet or in a new worksheet? For my example, I have selected New Worksheet.
  • Make your desired choices and click OK
  • On clicking OK, a new worksheet with a blank Pivot Table (on the left) and Field List (on the right) will open as shown below:
image showing pivot table options

NOTE: – If you are unable to view the field list then you will have to click on the Field List button in the show/hide section of the Options Tab.

image showing field list options

This is how the pivot table is created. But, you need to add data into your pivot table in order to analyze it further. So, in order to add data to the pivot table, let me first explain to you the various essential components of a pivot table.

Essential components of a pivot table

A pivot table has four important components. These are the areas listed in the field list column. These are as follows:

  • Row Labels

Data to be analyzed as a group is put in the rows area of the pivot table. For example: if we want to group data with respect to the name of employees then we can put it in the rows area as shown below.

table showing row labels
  • Column Labels

The data that is to be analyzed period-wise is put in the columns area of the pivot table. For example: if you want to analyze data with respect to months then we can put it in the columns area as shown below.

screenshot showing columns
  • Values

This area deals with all the calculations to be made in the pivot table. The column on which calculations are to be made is dragged and dropped in this section.

image showing calculation area
  • Report Filters

This is an optional area. This area is used only if you want to filter data according to any particular constraint. For Example: if you want to further filter the report according to the area where the employees work then you can drag and drop the area field in this column and filter your data accordingly.

screenshot of pivot table creation

Filling and Analyzing Pivot Table Data

Let me take an example here. Have a look at the dataset below.

image showing dataset example

Now if you want to analyze the sale data and that too employee-wise. Then, drag the columns in the Field List to the relevant area as follows:

screenshot showing field list of pivot table

As a result, it will display the total of the sales made by various employees in that dataset. Here is the result of the above arrangement.

Now, if you want to analyze how many sales were done by different employees in different months, then you can further refine your result by simply dragging the sale month field to the column label area i.e.

image showing pivot table field list

Now have a look at the pivot table. It is much more refined than the previous one.

You can make various adjustments according to your requirement and analyze as well as summarize your data in a better manner.

NOTE: – After creating the pivot table if you make any changes to the dataset then the changes are NOT automatically reflected in the pivot table. You will have to manually update the pivot table. For manually updating the pivot table, go to the Options tab. In the Data Section, select Refresh. This will reflect all the changes made in the dataset to the pivot table.

To Sum Up

Pivot Tables are just another simple and proficient way to visualize and analyze large amounts of data. Pivot Tables are widely used and reliable for data interpretation. In addition, it is simple to create and manipulate data in various different ways. I hope that this tutorial would have helped you understand the basics of creating and manipulating pivot tables. Go ahead and create pivot tables depending on what and how you want to analyze your data.

EXPLORE MORE

  • difference between google sheets and excel
    Google Sheets Vs. Microsoft Excel: Full Comparison Guide
  • scroll bar not showing on excel in laptop screen
    Scroll Bar Missing in Excel: Here is DIY How to Guide
  • how to sort in excel
    Sort Methods to Organize Data in MS-Excel [With Screenshots]
  • mail-merge-word-excel
    How to Mail Merge a Document in Word from Excel [Easy Steps]

Filed Under: How To Tagged With: Microsoft Office

About Subhash D

A tech-enthusiast, Subhash is a Graduate Engineer and Microsoft Certified Systems Engineer. Founder of it4nextgen, he has spent more than 20 years in the IT industry.

Share Your Views: Cancel reply

Latest News

Apple SE phone

Upcoming iPhone SE 4: All You Need to Know

Gemini 2.0

Gemini 2.0: A New Era in AI with Flash, Pro, and Flash-Lite Models

apple-vision-pro

What’s so ‘Pro’ About Apple Vision Pro Headset

Tesla phone

Tesla Phone: Release Date, Price, Specs, and Latest Rumors for the Tesla Pi

android 15

Android 15: Top 7 New Features for Pixel Devices You Need to Know

  • About Us
  • Privacy Policy and Disclaimer
  • Contact Us
  • Advertise
  • Newsletter!
  • Facebook
  • LinkedIn
  • Twitter

Enjoy Free Tips & News

Copyright © 2025 IT4Nextgen.com