How to use Pivot Table in Excel? Step by Step Tutorial

Pivot table in Excel is one of the most intimidating and powerful functions. When you have large data sets and have to find specific data from the lot, a pivot chart helps summarize the data. Excel experts are fond of this particular Excel function. Many people think that pivot tables are complex. But if you follow a good pivot table example, you will understand the basics of the function. 

In this article, you will learn how to create a pivot table in Excel and the use of pivot charts. 

What is a Pivot Table – A Brief Introduction

A pivot table in Excel is nothing but a summary of your data, represented in the form of a chart through which your report is displayed. Based on your information, you can also explore trends with the help of a pivot chart. In an excel spreadsheet, when you have long columns and rows with values, a pivot table helps summarize the data. To be precise, pivot tables extract meaning from the vast volumes of data in the excel spreadsheet. Pivot also lets you group the data in different ways so that you can draw appropriate conclusions. 

Excel experts opine that a pivot table in Excel can be considered a report. With a pivot table, you will get an interactive view of your data. No formulas are required for creating a pivot table, and you can have different perspectives of the same data through the table. As said earlier, pivot helps in grouping data into categories, filtering data to exclude or include categories, breaking down data into months, years, days, etc., and building charts. 

Learn online data science training from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Explore our Popular Data Science Courses

What is the use of a pivot table in Excel? 

Newbies to MS Excel are often confused about the use and function of the pivot table. But once they come to know how this Excel feature functions, many concepts become clear. As mentioned previously, the main purpose of a pivot chart and pivot table is to summarize huge volumes of data quickly. Studying a pivot table example will help you understand how this function works. 

Mentioned below are some practical situations and scenarios where a pivot table acts as an excellent solution:

1. Combining duplicate data 

Suppose you have to update quite a few numbers of URLs after the completion of a blog redesign. But the blog reporting software couldn’t do the work well, splitting the ‘view’ metrics for a single post between two separate URLs. In the spreadsheet, you have two different instances of each blog post. For accurate data, you must collate the view totals for each duplicate value. 

This is when the pivot table in Excel comes in handy. You don’t have to search and combine the metrics from the duplicate values manually. Use a pivot table and summarize your data by blog post title. The view metrics from the duplicate posts will be automatically aggregated. 

2. Comparing sales totals of various products

A monthly sales worksheet usually has thousands of rows containing monthly sales data for different products. So, if you want to know which product(s) brought maximum money, you cannot do it manually. Sorting data manually from such a massive data volume is impossible. Instead, you can use a pivot chart to aggregate sales figures for each product. 

3. Adding default values to empty cells

Every dataset you enter into MS Excel will not populate each cell. Often, when you are waiting to enter new data in MS Excel, it may get delayed. And since there might be numerous empty cells, it will only increase confusion.

In such situations, a pivot table in Excel is beneficial. You can customize the pivot table and fill the empty cells with default values, like TBD (to be determined) or $0. For large data tables, you can tag the cells using this feature, particularly when multiple people are working on the same sheet. 

Obtaining Employee Headcount for Different Departments

You can automatically calculate things found in an MS Excel table with pivot tables. Among the many things, counting rows with common data is often essential. 

Suppose an Excel sheet lists the employees of an organization – the employee names and their respective departments. When you create a pivot table in Excel from this data, you can show the name of the department and the numbers of employees in each department. You don’t have to count each row manually for sorting the department name. 

How to Create a Pivot Table in Excel?

Here’s how to create a pivot table in Excel:

Step 1: Entering data into a range of columns and rows

For every pivot table, you will need a basic Excel table containing the complete data. Enter the values in specific sets of columns and rows. You can categorize your value in the topmost row or topmost column based on what they represent. 

Step 2: Sorting data by a specific attribute

Once you have entered all the data into the Excel sheet, you have to sort this data for easy management and conversion into a pivot table. For data sorting, you must click on the ‘Data’ tab in the top navigation bar of the Excel spreadsheet and select the ‘Sort’ icon just below it. A window appears, and you can sort your data by any column and in any order. 

Step 3: Highlighting cells for creating a pivot table in Excel

Once you’ve entered data in the Excel worksheet and sorted it according to your preference, you must highlight the cells you’d like to summarize in a pivot chart or table. In the top navigation bar, click ‘Insert’ and then select the ‘PivotTable’ icon. You can do another thing – in the worksheet, click anywhere and select ‘Pivot Table’. You can manually enter the range of cells you wish to include in the pivot table. 

You will get an option box where you can set your cell range. You can also transfer the pivot table to a new worksheet or keep it in the existing worksheet. After making your choices, click OK. 

Step 4: Drag and drop a field in the ‘Row Labels’ area

On completing Step 3, you will get a blank pivot table in Excel. You have to drag and drop a field – as labeled as per column names in the spreadsheet – into the ‘Row Labels’ area. A unique identifier is determined based on which the pivot table will organize your data. 

Step 5: Drag and drop a field into the ‘Values’ area

Now that you know how to organize your data, your next step will be to add in some values. For this, you have to drag a field into the ‘Values’ area. 

Step 6: Fine-tuning the calculations

By default, you can calculate the sum of a particular value. But at the same time, you can change this to maximum, minimum, or average, depending on what you want to calculate. 

Your data is now categorized per your preference and liking. You can save it for future reference. 

Read our popular Data Science Articles

Conclusion

Now you have developed a basic idea about pivot tables in Excel. So, you can use a pivot table for your real-world use cases. Moreover, if you are proficient with MS Excel formulas, you can quickly set up the pivot tables with minimum effort. 

Joining upGrad’s Data Analytics Certificate Program 

The field of data analytics offers extremely lucrative job opportunities to candidates. Data analysts use the colossal volumes of data created every day to extract meaningful patterns for data-driven decision-making. It is a highly lucrative and well-paying career. 

Making a career in data analytics is a lucrative opportunity in the current industry. Data analysts work with huge volumes of data for extracting crucial information. To hone your skills in data analytics, you must join upGrad’s Data Analytics Certificate Program. Powered by Fullstack Academy, you will get a Caltech data analytics certificate upon completing the course. You will be able to learn data visualization skills, Python, and SQL through the course, which will pave your way for making a great data analyst.