Pivot table in excel is one of the powerful tool to analyze data or creating a summary from a data set. Excel is considered as basic tool for data analysis and pivot table gives an extra power to it. Here we’ll discuss the pivot table in excel tutorial in terms of steps to create pivot table, advantages of pivot table, formatting the design of pivot table, insert slicer in pivot table and pivot chart.
Table of Contents
What is Pivot Table
Pivot table is an excel tool to create a summary report in terms of calculations like summation, average, minimum, maximum etc. Let’s say, we have a data set where variables are like product name, category of the product, sales, and quantity sales. From this data set we would like to create a summary of product wise average sales, so pivot table is the quickest way to create such summary report.
5 Advantages of Pivot Table
Create quick summary report from data
No chance of error
Easy and simple to use
No need to use any excel function
Dashboarding made easy with pivot table
Few cases are mentioned below where pivot table is useful.
Compare sales of different products
Deduplication or unique product sales. In other words, combine duplicate sales data
Show a predefined value of empty cells
Pivot Table in Excel Tutorial (Step by step guide)
Before creating a pivot table, you should follow the thumb rule. First, need to visualize or imagine the output or final product as pivot table. It is not only applicable for pivot table but also for data visualization. When we say visualize or imagine the output, basically you need to decide where you would like to place variables, in rows or column or value or filter.
The steps to crate pivot table is mentioned below.
Select the data set by using “Ctrl + A” or the entire data set can be selected manually.
Go to “Insert” tab and select “Pivot Table”. In the insert tab, another option is available as “Recommended Pivot Tables”. It is showing various pivot table output options and you can select from those as per the requirement.
3. Pop-up window will open to choose the output location. By default, new worksheet is selected. So, click on ok and a new worksheet will open with pivot table.
4. New worksheet will be containing the pivot table field window on the right. All the column names will be visible in the pivot table field. When we say data, it is basically selected data set.
5. To create a pivot table, first step is to visualize or decide the output and alignment. In this case, we would like to see product wise sales and profit. So, product should be placed in either rows or column. Let’s put product column in the Rows. On the other hand, sales and profit in the value field.
The output will look like below.
6. By default, the value field is showing sum value of all products. In some cases, it can take count calculation by default. If you want to change the calculation, click on the dropdown visible at right side. Here, we would like to see sales as SUM and profit as average. So, to do that, click on the dropdown placed on the right side and select “value field setting” option.
After that, select the calculation type and hit on ok. In this case, we have selected “Average”.
These are simple steps to create pivot table in excel.
Change the design of pivot table
Pivot table is showing default design, which can be changed. To change the default design of pivot table, follow the below steps.
Place the cursor on the pivot table to show “Pivot table fields” or right click on the pivot and click on “Show Field List”.
When field list is activated, “Design Tab” option will also be activated. Within “Layout”, various options are available like “Subtotal”, “Grand Total”, “Report Layout” & “Blank Rows”. To deactivate subtotal, or grand total, these options can be used by clicking on the down arrow.
If you want to change the colour and format of the table, choose the design from Pivot table style.
Change data source of pivot table
Sometimes, we may have to update the source data and at the same time pivot table also. If any data is being changed within the selected data table, pivot table can be updated by using refresh which is available with right click on pivot table. But if more column needs to add in the data table, refresh option will not work.
So instead of creating a new pivot table, only the source data can be updated, and pivot table will update accordingly.
To change the data source, click on the pivot table which will activate “Pivot Table Analyze” tab. Under “Data”, “Change Data Source” option is available. Click on it and change the table/range and click on Ok. The data will be updated as per new range or table.
Insert slicer in pivot table
Pivot table is also used to create dashboard and analyzing data. During the analysis of any data set, we need to slice and dice it to get insight out of that. Slicer options give the extra power in pivot table to slice the data. In pivot table, data can be sliced by using filter option and both slicer and filter will give the same output. Though, filter option is taking time to apply repeatedly and get the output. In case of slicer, it can be applied quickly and get output or sliced data. Moreover, if you are using multiple slicer fields, it will work like a dependent option and can be linked with another slicer. Let’s see how slicer option can be added.
Before adding slicer option, pivot table needs to create. In other words, slicer option is only available with pivot table. Follow the below steps.
Create pivot table from the data set.
Place the cursor on the pivot table
Click on “Insert Slicer” option which is there in “Filter”. Pivot Table analyze tab will be activated when place cursor on pivot table.
All the column/ variable names will be visible in a pop-up window from where required columns can be selected.
Pivot chart
Pivot chart is another powerful tool exist in the pivot table section to create chart from pivot table. As mentioned earlier that pivot table is helpful to create summary report from a data where pivot chart is a value addition to visualize the data or table in the form of chart or graph.
To create or add pivot chart, follow the below steps.
Pivot table to pivot chart:
Place the cursor on the pivot table which will activate “Pivot table analysis” tab.
In the pivot table analysis tab, you can find “Pivot chart” within tools.
Select the required chart and click on “Ok”.
Data to pivot chart (without pivot table):
Select the data set.
Go to “insert” tab.
You’ll find “pivot chart” option under charts.
If you want to insert only pivot chart, click on “Pivot chart”. Else if you want to add pivot table along with pivot chart, click on the second option, “Pivot Chart & Pivot table”.
After that, you need to select the required column names to create pivot chart the same way pivot table was created.
So, create & design pivot table as per your requirement and play with data.
4 thoughts on “Pivot Table in Excel Tutorial | Create Pivot Table in 5 steps”