How to Create Pivot Tables in Excel: Detailed walkthrough

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.

How to Create Pivot Tables in Excel?

1. Insert Pivot Table

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.

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

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

2. Drag Field

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.

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

• Choose the type of calculation you want to use.

• Click OK.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

4. Sorting By Value

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

Result:

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

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

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

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

Now, your data is grouped.

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

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

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore 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 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