Pivot Tables in Data Analysis

Pivot tables are useful in data analysis when we need to summarize a large dataset quickly without writing complex formulas. They help us group, count, add, and compare data in different ways, so the data can be understood more easily. In Excel, pivot tables are used to handle sales reports, employee records, customer data, expense sheets, inventory lists, and other table-based data.

In this article, we will learn about pivot tables in data analysis, their uses, and the main features used to create and customize them in Excel.

What Is a Pivot Table in Data Analysis?

A pivot table is an Excel tool that summarizes a large dataset into a compact table. It allows us to choose which fields to show as rows, columns, and values, and it calculates totals, counts, averages, and other results automatically. We can change the layout at any time by dragging fields, without changing the original data.

For example, if a dataset contains sales records of different regions and products, we can use a pivot table to find the total sales of each region. Similarly, we can show the products in columns to compare sales across regions and products in one view.

Sample Dataset Used in This Article

We will use the following dataset in the examples. The headings are in row 1, and the data is in the range A1:E9.

Requirements for Creating a Pivot Table

Before creating a pivot table, the data should be arranged properly.

• Each column should have a unique heading in the first row. 
• There should be no blank rows or blank columns inside the data. 
• Each column should contain the same type of data, such as only dates or only numbers. 
• There should be no merged cells or subtotal rows in the data. 

How to Create a Pivot Table in Excel

Steps

1. Click on any cell inside the data range. 
2. Go to the Insert tab and click on PivotTable. 
3. In the Create PivotTable box, check the table or range. 
4. Choose New Worksheet or Existing Worksheet, and click OK. 
5. Drag the fields into the areas of the PivotTable Fields pane. 

Explanation:
In the above steps, we select a cell in the data and insert a pivot table. After that, Excel opens an empty pivot table with a field list. Finally, we drag fields into the layout to build the summary.

Parts of the PivotTable Fields Pane

Creating a Summary Using Rows and Values

Steps

1. Drag the region to the Rows area. 
2. Drag Sales to the Values area. 

Output:

Explanation:
In the above example, we place Region in the Rows area. After that, we place Sales in the Values area, and Excel adds the sales of each region. Finally, it shows the total of all regions in the Grand Total row.

Using Rows, Columns, and Values Together

Steps

1. Drag Region to the Rows area. 
2. Drag Product to the Columns area. 
3. Drag Sales to the Values area. 

Output:

Explanation:
In the above example, we place Region in rows and Product in columns. After that, Excel adds the sales for each combination of region and product. Finally, we can compare the products across all regions in one table.

Changing the Summary Function

By default, Excel uses Sum for numbers and Count for text. We can change this to find averages, minimum, maximum, and other results.

Steps

1. Click on any value in the Values area, or click the field in the Values box. 
2. Choose Value Field Settings. 
3. Select a function, such as Average, Count, Max, or Min. 
4. Click OK. 

Output:
If we choose Average for Sales by Region, the result for North is 52000, because the three North sales add up to 156000, and 156000 divided by 3 is 52000.

Explanation:
In the above example, we change the summary function from Sum to Average. After that, Excel recalculates every value in the pivot table. Finally, it shows the average sales instead of the total sales.

Show Values As

The Show Values As an option, it is used to display the results as percentages or comparisons instead of plain numbers.

Steps

1. Open Value Field Settings for the field in the Values area. 
2. Go to the Show Values As tab. 
3. Choose an option, such as % of Grand Total, % of Column Total, or % of Row Total. 
4. Click OK. 

Output:
With % of Grand Total, the North region shows about 38.24%, because 156000 divided by 408000 is 0.3824.

Explanation:
In the above example, we choose % of Grand Total. After that, Excel divides each value by the total of all values. Finally, it displays the share of each region in the overall sales.

Grouping Data in a Pivot Table

Grouping is used to combine items into larger sets. It is commonly used with dates and numbers.

Grouping Dates

Steps

1. Drag the Date field to the Rows area. 
2. Right-click on any date in the pivot table and choose Group. 
3. Select Months, Quarters, or Years, and click OK. 

Output:

Explanation:
In the above example, we place the Date field in rows and group it by months. After that, Excel combines all the dates that fall in the same month. Finally, it shows the monthly sales instead of daily rows.

Grouping Numbers

If a numeric field is placed in rows, we can right-click on it, choose Group, and enter the starting value, ending value, and interval, such as 10000. This is useful for creating ranges like 0-10000 and 10000-20000.

Sorting and Filtering in a Pivot Table

We can sort a pivot table by clicking the drop-down arrow beside the row or column labels and choosing a sort option, or by right-clicking a value and selecting Sort > Sort Largest to Smallest. We can also use label filters and value filters, such as Top 10, to display only the best or worst items. The Filters area can be used to filter the whole report by a field such as Product.

Slicers and Timelines

Slicers are visual filter buttons that make it easy to filter a pivot table. Timelines work in the same way, but they are used for dates.

Steps

1. Click inside the pivot table. 
2. Go to the PivotTable Analyze tab and click on Insert Slicer. 
3. Select the field, such as Region, and click OK. 
4. Click on a button in the slicer to filter the data. 

Explanation:
In the above steps, we insert a slicer for the Region field. After that, the slicer shows a button for each region. Finally, clicking a button filters the pivot table, and holding the Ctrl key lets us select more than one button. For dates, we can use Insert Timeline instead.

Calculated Field

A calculated field is used to create a new field using a formula based on the other fields in the pivot table. It is useful when the required column does not exist in the original data.

Steps

1. Click inside the pivot table. 
2. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field. 
3. Enter a name, such as Average Price. 
4. Enter the formula = Sales / Quantity. 
5. Click Add and then OK. 

Output:
The pivot table shows a new field, Average Price, for each region. For North, the result is 156000 divided by 7, which is about 22286.

Explanation:
In the above example, we create a calculated field by dividing Sales by Quantity. After that, Excel applies the formula to the summed values of each group. Finally, it shows the average price in the pivot table. Note that the formula works on the totals, not on each original row.

Refreshing a Pivot Table

A pivot table does not update automatically when the source data changes. To update it, right-click inside the pivot table and choose Refresh, or go to PivotTable Analyze > Refresh. If new rows are added outside the original range, use Change Data Source or convert the source data into an Excel table so the range expands automatically.

Pivot Charts

A pivot chart is a chart linked to a pivot table. When we change the pivot table, the chart changes too.

Steps

1. Click inside the pivot table. 
2. Go to PivotTable Analyze and click on PivotChart. 
3. Choose a chart type, such as a column chart or a pie chart, and click OK. 

Explanation:
In the above steps, we create a chart from the pivot table. After that, Excel links the chart to the same fields. Finally, any filter, slicer, or layout change in the pivot table updates the chart automatically.

Formatting and Design

We can change the look of a pivot table from the Design tab. It allows us to choose a style, show or hide grand totals and subtotals, and change the report layout to Compact, Outline, or Tabular. We can also format numbers by opening Value Field Settings and clicking on Number Format, so that the values show as currency or with thousand separators.

Conclusion

Pivot tables are an important part of data analysis because they turn large datasets into clear summaries with only a few clicks. They help us calculate totals, compare categories, group dates, and filter results without writing formulas. By learning features like rows, columns, values, grouping, slicers, calculated fields, and pivot charts, we can analyze and report data more easily in Excel.