Pivot Table Examples in Excel (Table of Contents)
What is Pivot Table Examples?
In this article, we are about to check some of the best examples and tricks of pivot tables. The pivot table is such a powerful and important tool excel has, which can do the work of hours in minutes for analysts. In this article, I am going to cover some of the best features of excel pivot table through some examples.
Examples of Pivot Table
The data which I am going to use throughout this article is shown below:
Example #1 – Automatically Creating a Pivot Table
How good it would have been if you don’t need to worry about the questions like – “Which columns should be ideal for my pivot table?”, “Which columns should go under rows, columns, values, etc?”. Do you feel it’s a fantasy? Well, let me take a moment to make you aware, this fantasy has become reality in excel now. If you have excel 2013 or above installed in your system, it has an option called Recommended PivotTables. Let’s see how it works.
Step 1 – Select any cell in your data and click insert >Recommended PivotTables (You can see this option besides the PivotTable tab).
Step 2 – Click > Recommended PivotTable.
Step 3 – Excel will quickly analyze your data and come up with some of the recommended pivot table layouts.
The recommended pivot table option uses the actual data from your worksheet. Hence, there is a good chance that you’ll get a layout which you were looking for, or at least close to one of your interest.
Step 4 – Select any layout of your interest and click Excel created a pivot table on a new worksheet.
In the above example, we have seen the example of How we automatically create a table. Let us see another example in the Pivot Table.
Example #2 – Modifying Pivot Table
Once you create the pivot table, it is easy to modify the same. You can add some more fields in the layout to display more summary using the PivotTable Fields pane which can be found at the right-hand side of your worksheet in which the pivot is.
Here, the column named Customer is added under Rows and Branch is added under Columns. You can add the columns under Rows or Columns pane by simply dragging them down to the respective field area.
On a similar note, you can also remove the field from the pivot table. Click on the column you wanted to remove and there a pane will open, under which you need to click on Remove Field and the field will be removed from the pivot table. Or you can simply drag the field out of the pivot table pane which yields the same result.
If you add any field under the Filters section, it will appear at the upper part of the pivot table as a drop-down list, which allows you to filter the displayed data by one or more than one item.
The above figure shows the example of the Filter fields. I have added the Date under the filter field and can use this column to filter my pivot data. For instance, I have filtered the data for 27-Nov-2018. It means that my pivot table will now only show the data for 27-Nov-2018.
Example #3 – Customize Columns Under Pivot Table
Suppose we want to check the amount-wise distribution of accounts. We want to check what % of accounts are falling under what amount range. We can do this under a pivot table. So first create a pivot table and then the columns as below.
Under Rows Field, select Amount (as a range). To add it as a range, select any cell from Pivot Table and right-click. Click on Group section,
After that, make grouping as shown in the second image. Starting from 0 to 90000 with a difference of 5000.
- Under Values Field, select Amount (as a count). Also, add Amount under the Values field as a % of the column. Add column Amount two times under Values it will automatically select it as a count.
- For storing Amount as % of Amount in each group, click on the second Amount and select Value Field Settings.
- Under Value Field Settings pane, click on Show values As inside which select option named % of Colum Total. It will change the field as % of the Amount for each Amount group. Moreover, you also can use a custom name for the column, which can be displayed in a pivot. Please see the name give Pct (Which makes sense for Percentage column) and Count which makes sense for the count of Amount.
The final output should look like below:
Let us see another example in the Pivot Table.
Example #4 – Data Analysis
Suppose we want to check, on which day of the week gets more deposits in the account? Let’s see how we can go towards an answer to this question through pivot tables.
- Create a pivot table with Weekday under Rows field and Sum of Amount under Values
- If the Values field by default does not give Sum of Amount, make sure to change it through Summarize Values By under Value Field Settings (Change the type from Count to Sum, which will give the sum of Amount instead of count).
Your pivot should look like below:
Please note that I have updated the visual settings of column Sum of Amount using Cell Formatting. Please see the image below for the cell formatting reference.
Now we are going to use conditional formatting to add the data bars in this pivot for better visualization.
Though this pivot shows you that, Thursday is the day on which more account deposits happen, data bar will give you a clearer and more graphical representation of the same.
- Select all fields except Grand total from your pivot
- Click on Home
- Go to Conditional Formatting dropdown > Data Bars. Under which select a bar with a color of your choice and fill (either gradient or solid).
You should get below output:
It gives a better idea in a single look, Right!. In this way, we can use some graphical analysis techniques as well under our pivot table with the help of Conditional Formatting. Hope this article is helpful. Let’s wrap the things up with some of the points to be remembered.
Things to Remember
- Though it is very flexible, Pivot Table has its limitations. You can’t make a change in the pivot table fields. Can’t add columns or rows under it as well as can’t add formula within the pivot table. If you wanted to make changes in a pivot table in the way not allowed normally, make a copy of your pivot table to some other sheet and then do. Select your Pivot table and hit Ctrl + C. or go to Home and select Copy under Clipboard.
- If you update your source data, make sure you are Refreshing the pivot table to capture the latest updates made in your data. This is because pivot prevents automatic up-gradation once the source data has been updated.
Recommended Articles
This is a guide to the Pivot Table examples in Excel. Here we discuss some of the Different Types of Examples in Pivot Table with the excel template. You can also go through our other suggested articles to learn more –
