An Introduction To Pivot Table in Excel

Pivot tables are among the most useful and powerful features in Excel. We use them in summarizing the data stored in a table. They organize and rearrange statistics (or "pivot") to draw attention to the valuable facts. You can take an extremely large data set and see the relevant information you need in a clean, concise, manageable way.

In this article, we will discuss the following topics. 

  • Sample Data
  • Insert Pivot Tables
  • Drag Fields
  • Value Field Settings
  • Sorting by Value
  • Two-dimensional Pivot Table
  • Applying Filters to a Pivot Table
  • Grouping Data in a Pivot Table
  • Percentage Contribution in a Pivot Table

Sample Data

The sample data that we are going to use contains 448 records with 8 fields of information on the sale of products across different regions between 2013-2015. This data is perfect to understand the pivot table.

sales data pivottable

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Insert Pivot Tables

To insert a pivot table in your sheet, follow these steps:

  • Click on any cell in a data set.
  • On the Insert tab, in the Tables group, click PivotTable.

insert pivotTables

A dialog box will appear. Excel will auto-select your dataset. It will also create a new worksheet for your pivot table.

step 2

  • Click Ok. Then, it will create a pivot table worksheet.

pivot table new sheet

Drag Fields

To get the total sales of each salesperson, drag the following fields to the following areas.

  • Salesperson field to Rows area.
  • Sales field to Values area.

drag fields

Value Field Settings

By default, Excel gives the summation of the values that are put into the Values section. You can change that from the Value Field Settings. 

  • Click on the Sum of Sales in the Values field.

value field

  • Choose the type of calculation you want to use.

value field setting

  • Click OK.

vfs

Sorting By Value

  • Right-click any Sales value and choose Sort > Sort Largest to Smallest.

sort1

Result:

sort 2

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

Two-Dimensional Pivot Table

We can create a pivot table in various two-dimensional arrangements. Drag the following fields to the different areas

  • Salesperson to Rows area.
  • Region to Columns area.
  • Sales to Values area.

2d pivot table

Applying Filters to a Pivot table

Let’s see how we can add a filter to our pivot table. We will continue with the previous example and add the Year field to the Filters area.

filter pivot

You can see that it adds a filter on the top of the worksheet.

Grouping Data in a Pivot Table

Excel allows you to group pivot table items. To create the groups, execute the following steps:

  • In the pivot table, select the data you want to group.
  • Right-click and click on Group.

group 1

Now, your data is grouped.

https://www.simplilearn.com/ice9/free_resources_article_thumb/group2.JPG

Percentage Contribution in a Pivot Table

There are various ways to display the values in a table. One way is to show the value as a percentage of the total. 

  • Add the sales field again to the values section.
  • Right-click on the second instance and select % of Grand Total.

percent 1

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

In this article, you’ve learned the basics of pivot table creation in Excel. You can see how simple it is to get started creating one and visualizing your data in many different ways. 

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you! 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.