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.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

Sorting By Value

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

sort1

Result:

sort 2

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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.

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

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 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! 

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Post Graduate Program in Data Analytics

Cohort Starts: 27 May, 2024

8 Months$ 3,749
Post Graduate Program in Data Science

Cohort Starts: 28 May, 2024

11 Months$ 4,199
Caltech Post Graduate Program in Data Science

Cohort Starts: 29 May, 2024

11 Months$ 4,500
Post Graduate Program in Data Engineering

Cohort Starts: 4 Jun, 2024

8 Months$ 3,850
Data Analytics Bootcamp

Cohort Starts: 11 Jun, 2024

6 Months$ 8,500
Applied AI & Data Science

Cohort Starts: 18 Jun, 2024

3 Months$ 2,624
Data Scientist11 Months$ 1,449
Data Analyst11 Months$ 1,449