How to Create a Simple Pivot Table in MS Excel

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.

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.

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.

• 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:

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.

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.

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

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

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

Filling and Analyzing Pivot Table Data

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

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:

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.

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.