What is Pivot Table in Excel?
MS Excel, also known as Microsoft Excel or Excel, is an extremely powerful spreadsheet software program that gives the fastest and easiest way to analyze desired data effectively. The Pivot Table is a very useful tool or feature in an Excel program and plays an important role while working on or analyzing large data sets. Although pivot table seems difficult for beginners in Excel, it is essential to learn it to become a professional or expert in Excel.
In this tutorial, we briefly explain an introduction of what is a Pivot Table in Excel, its requirements, and step-by-step methods to create or insert it into our worksheet with relevant examples.
Introduction to Pivot Table in Excel
A pivot table summarizes the given data set bundled within a grid-like matrix that helps explore or create reports based on useful information. In particular, it enables users to extract the data in a customized format (such as reports or dashboards) from the large, detailed data sets recorded within the Excel sheet.
Unlike the regular Excel reports, the Pivot Tables represent our essential data sets in an interactive view, allowing us to view our data from a different perspective with little tricks. We can easily sort & filter data, group data into desired categories, create charts, break down the data month-wise or year-wise, and perform complex calculations using various functions or formulas.
The Pivot Table helps us view our data effectively and saves crucial time by summarizing the data into essential categories. It is a kind of reporting tool and contains mainly the following four fields:
Why do we use Pivot Tables in Excel?
Following are some of the scenarios when using the Pivot Tables in Excel can be an effective solution for us:
How to create/ insert a pivot table in Excel?
Excel offers multiple ways to insert/ create a Pivot Table within an Excel worksheet. We discuss the two most common methods and corresponding step-by-step procedures to build the pivot tables for sample data. Let us understand each method one by one:
Method 1: By using the tool on the ribbon
The ribbon is the primary area where we can access all the existing tools/ commands of Excel. The Pivot Table is also present on the ribbon area, which we can find under the Insert tab. We use this tool to create a Pivot Table for our sample data where we have 17 records and five fields of information, such as Date, Color, Region, Units, and Sales.
Our data is formatted as a proper Excel table and named 'Table1'. The tables in Excel are the more effective way to create a pivot table, and they automatically adjust whenever new data is inserted or deleted.
We must perform the below steps to create a pivot table for our example data set:
Adding/ Dragging Fields in Pivot Table
Let us now understand how to add the desired fields within the Pivot Table. Suppose we want to know the sum of all sales in our sample data set. So, we must drag the Sales field in the right side pane to the Values box, and it will calculate the total sales, i.e., 929.
Alternatively, we can also click on the checkbox associated with the Sales in the side pane. We can also add more than one field to our Pivot Table simultaneously.
Now, suppose we want to break out the sales data based on the colors. We can drag the Color field to the Rows area. When divided, it is easy to know which color has the highest and lowest sales.
The above image shows that the total sales (Grand Total) remain the same as in the previous image. It makes sense as we have categorized the data for the full data set.
Number Formatting in Pivot Table
With the Pivot Table, we can also format the data as required, maintaining the number formatting to numeric fields as the source data. As we see that the sales values have the currency sign ($) in the source area, we can include it in our Pivot Table values accordingly. Adjusting the number formatting in Pivot Tables can be a crucial step and save our crucial time when data changes frequently.
We can adjust the number formatting in our Pivot Table by following the below steps:
Sorting by Value in Pivot Table
Like typical data sets in an Excel worksheet, we can also sort the data from 'smallest to largest' or 'largest to smallest' in our Pivot Table.
Suppose we want to put the highest sales at the top and the lowest sales at the bottom. So, we can right-click on any sales values to open the options menu. Next, we must go to Sort > Sort Largest to Smallest. This will arrange the list and put the top-selling colors on the top as they have the highest (largest) sales.
After we have sorted the data in the Pivot Table, Excel maintains this order even after we change the data or reconfigure the Pivot Table.
Refreshing Data in Pivot Table
The Pivot Table must be refreshed to update/ reflect its data after changing the data in the source table or range. It is an essential task to bring new updates to our Pivot Table
Suppose we edit cell E4 in our source table and change the value from $96.00 to $11.00. In that case, we don't see any changes in our Pivot Table. Therefore, we refresh the data using the below steps:
Percent of Total in Pivot Table
Since the Pivot Table in Excel helps us view the data differently, we can also display values as a percent of the total. For example, we can show sales values in the portions of percent, making, or completing as a whole. In our sample Pivot Table, we can display the data as a percentage of the total by following the below steps:
Grouping by Date in Pivot Table
Excel's pivot table has some awesome features, and grouping the different data specifically into categories is one of them. It allows us to group dates in our Pivot Table into various units such as months, quarters, or years. Also, we can customize the grouping accordingly.
Let us now delete the additional Sales column and perform the following steps to group dates in our Pivot Table:
Method 2: By using the Keyboard Shortcut
Excel is well-known spreadsheet software that allows us to perform most of its tasks using the keyboard shortcut. It has a wide range of predefined keyboard shortcuts. Moreover, we can also create our custom shortcut keys for any specific task using the Macros feature. So, we can create a Pivot Table a bit faster using the keyboard shortcut 'Alt + D + P'.
Suppose we have the following data set showing how many sales are completed in year quarters for different items.
We need to perform the below steps to use the keyboard shortcut and create a Pivot Table for our sample data in our worksheet:
Two-dimensional Pivot Table
An essential advantage of Pivot Tables is the two-dimensional or two-way arrangements referred to as two-dimensional Pivot Tables. In particular, it represents data in various combined aspects after we drag the different fields into different areas/ boxes accordingly.
For example, suppose we want to break down sales by color and region for our sample data used in Method 1. We can create a two-way Pivot Table by making the following arrangements:
Important Points to Remember