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:

• Rows: This refers to data taken as a specifier.
• Values: This represents the count of the data.
• Filters: This helps us hide or highlight specific data.
• Columns: This refers to values under various conditions.

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:

• Comparing sales totals of various listed products
• Highlighting the product sales as a percentage portion of the total sales
• Combining duplicate data sets
• Adding or inserting the default values to the empty cells
• Counting or showing rows that have some common data

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:

• First, we must select any cell in the table (data set) within our worksheet. After that, we must go to the Insert tab and click on the ‘PivotTable’ button.
  
  This will launch the ‘Create PivotTable’ window where our data range (table) will already be written. However, we can select/ change the input range accordingly.
• In the ‘Create PivotTable’ window, the default location to create a Pivot Table is set as ‘New Worksheet’. It is better to create a pivot table in a new worksheet so that we can differentiate the source data and pivot table. However, we can also create a pivot table on the right side or bottom of our source data in the same worksheet. We must change the default option to ‘Existing Worksheet’ and enter any desired cell in the ‘Location’ box to start the pivot table on the current worksheet.
  
  We select cell G3 in the same worksheet (Sheet1) to create a Pivot Table for our sample data.
• After selecting the source range and the destination location, we must click the OK button, and an empty Pivot Table appears in the selected worksheet. However, Excel displays all the corresponding fields of our example data in the side pane. All the fields are listed but are unused until we manually add or drag fields into the Columns, Rows, or Values area accordingly.
  

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:

• First, we need to press a right-click on any sales cell and click on the ‘Number Format’ option in the list.
  
• Next, we must choose the desired format or create our custom one accordingly. Since we want to include a currency sign, we apply the Currency formatting with zero decimal points and select a Dollar symbol.
  
• Lastly, we must click the OK button, and the dollar sign ($) appears in all cells with the sales data.
  
  Once we have applied the number formatting, it will continue to serve, even after the Pivot Table is reconfigured or new data is inserted.

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:

• First, we press the right-click button anywhere within the Pivot Table area.
• Next, we click the ‘Refresh’ button in the list.
  
  The new or refreshed data appears instantly after clicking the ‘Refresh’.
  
  We can undo the changes by pressing the ‘Ctrl + Z’ to get our original source data and Pivot Table back.

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:

• First, we insert or add the Sales field again inside the Values box. This creates another column in our Pivot Table for the sales.
  
• Next, we must press the right-click on the second or newly created sales column and go to Show Values As > % of Grand Total.
  
  By completing the previous step, we break down the sales as a percent of the total, as shown below:
  

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:

• First, we need to drag the Date field into the Column box. This will list the sales by their separate dates in our Pivot Table.
  
• Next, we must press the right-click button on the header area in our Pivot Table and select the ‘Group’ option in the list.
  
• In the next window, we must deselect the Months and Quarters but keep the Years option selected, as shown below:
  
• Lastly, we must click the OK button, and all the corresponding sales will be categorized based on the color and year.
  
  Since we have sales data only for one year (i.e., 2018), we usually see a single column 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:

• First, we need to select the entire data to create a Pivot Table. We can use the keyboard shortcut ‘Ctrl + A’ to select all the data of the current worksheet.
  
• Next, we must use the ‘Alt + D + P’ keyboard shortcut. We must press each key one after another in a sequence. This will launch the ‘PivotTable and PivotChart Wizard’ window.
  
• In the next window, we must select the ‘Microsoft Excel list or database’ option under the first section. It is because we have our source data already in an Excel worksheet. Also, we must select the ‘PivotTable’ option in the second section. After that, we must press the ‘Next’ button. It will look like this:
  
• In the next screen, we have to enter the range of the input data. Since we have selected the data in the first step, the range will be prefilled. We must press the ‘Next’ button again.
  
• In the last window, we must select whether we want to create a Pivot Table in the same (existing) worksheet or a new worksheet. Like the previous method, we select the ‘Existing worksheet’ option and enter the cell (i.e., “Sheet2!$F$2” because our sample data is in Sheet2) to start the Pivot Table in the same sheet. Lastly, we must click the Finish button.
  
  This will immediately create an empty Pivot Table starting from the entered/given cell reference.
  
  Like the previous method, we can add fields, sort or filter data, adjust the number formatting, group data, and perform other desired tasks in our Pivot Table using the side pane and other options.

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:

• Drag the Color field into the Row area.
• Drag the Sales field into the Values area.
• Drag the Region field into the Columns area.
  
  The above sheet shows a two-way Pivot Table that breaks down sales by color and region.
  Suppose we swap the boxes/ areas for the Color and Region fields, and then Excel creates another two-dimensional Pivot Table. It is only the different view of the same data; thereby, the total sales remain the same.
  

Important Points to Remember

• Excel enables us to choose from a predefined set of different pivot tables, making it easier to create or insert pivot tables in Excel. We need to select the data to create the pivot table and go to ‘Insert tab > Recommended Pivot Tables’. After that, we can choose between the multiple recommendations given by Excel.
• It is essential to note that the Pivot Table and Pivot Charts are two different things. The Pivot Table is the summarized view of data in a grid-like matrix, allowing us to use the desired fields in the table’s rows and columns. In contrast, the Pivot Chart is the graphical representation of the Pivot Table data.