This is in continuation to my earlier content, so if you have not gone through the Part 1 of the series then you should definitely read that first and then come back to this later on.
So we are going to continue our journey into understanding the basics of Pivot Tables and effectively utilizing its many features to make us better data analysts. Let's go..
Data Used
This dataset contains detailed data related to the FIFA World Cup, including information on players' overall ratings, as well as their individual ratings for specific skill sets.
Exploring Pivot Table Design Options
By now you already know that you can create a pivot table by using the Insert option or just selecting the data and pressing Alt + D + P + F
So lets create a pivot quickly so that we can explore few design elements of Pivot Tables in the Design Tab
Drag Club & Short_Name into the Row section of the pivot and then drag Overall & Potential into the Values section
Custom Pivot Table Design
These are default styles that you can choose to quickly format your pivot tables in a visually
appealing manner. However there is an option to customize your designs as well.
Click on the small arrow icon in the design tab >> PivotTable Styles section
Click on New PivotTable Style
In the next prompt, give it a desired name and then there are multiple options on where you want to apply your formatting. And also choose to set a format as Default for that specific document
So let's say you want to create a custom pivot format where your Main Header Row will be a different color and is Italic & Bold.
So in this case select Header Row option as highlighted above and press Format
Now in the new prompt, you can format the font, Border & add fill colors. Once you are done formatting, press Ok
You will now see the new custom pivot format in the PivotTable styles section. However just remember that this new format will be available on this specific workbook. If you open a new workbook it will not be available.
Understanding Layouts
This where you can choose to add below elements to your table
Grand Totals
Sub Totals
Choose Different Report Layouts
Insert Blank Row between each item
Exploring PivotTable Analyze Tab
Let's now look at different options available to us for analyzing our PivotTable data
Change Data Source
In case you want to change the range of your backend data then click on the Change Data Source button in the Change Data Source tab.
Then in the next prompt, you can choose the new range of your data set.
Splitting Filters into multiple sheets
Did you know that we can split our filtered values into multiple sheets using a single click. So to do this we will first add a filter to our existing pivot table using the “Nationality” column.
Now apply the filter and select only Argentina, Australia & Belgium
Go to PivotTable >> Options and then click on Show Report Filter Pages
Press Ok
This will automatically create 3 different sheets with each of the filters applied seperately. Isn't this cool!
Working with Slicers
Slicers are a way of enabling the user to filter out the data. And this is much more intuitive than normal filters. Just click on “Insert Slicer” and select the column that you want to add as a slicer and then press Ok.
This will create a new slicer within your sheet. Now users can select any of the values within the slicer to filter out the data.
We will go into details of SLICER and it many functionalities in a separate blog
Working with Timeline
If your data contains some kind of date column then you can create an interactive timeline slider within your report. Just click on “Insert Timeline”. And similar to slicer it will ask for the column that you want to use to create the timeline and then press Ok.
Creating PivotTable Charts
This is the simplest way to create an interactive chart which will dynamically change values when your backend dataset is updated. To make more sense of the final chart I’m going to make a few adjustments to the pivot table i.e. remove all fields from the Row section and then add Nationality to the Row section.
Select anywhere on the PivotTable and then click on Pivot Chart. This will give you a prompt to select the type of chart.
Select the desired chart type and press OK. This will create an interactive chart which will be refreshed automatically when the backend pivot is refreshed. It also has a built-in Filtering function to make it more interactive for the user.
Comments