INTRODUCTION TO PIVOT TABLES
When you have a large amount of data in your worksheet and you can’t analyze or sort the data then Pivot Table helps you to carry out the task easily and more conveniently. Pivot Tables are powerful functions in excel. We are going to start with what is Pivot Table and explain its uses, types, importance, creation and much more.
What is a Pivot table?
A Pivot Table helps to get an overview of the data represented in the Excel spreadsheet. Pivot Tables allows you to analyze, summarize the data for easy analysis, quickly extract the data from any part of the Pivot Table and show only relevant data in our reports. Bill Jelen and Mike Alexander are referred to as “Father of Pivot Tables.” They have defined Pivot Table as “a tool that would help users to recognize that these patterns will be helpful to build advanced data more quickly”
In just a few clicks, you can represent your data in charts, tabular and more presentable form using Pivot Table. It can sort all your relevant data into respective columns and rows, into charts. It can even highlight keywords or important points within a few clicks and can save your valuable time.
How to Quickly Create a Pivot Table?
Before you create a Pivot Table you need to keep the following things in mind:
- Your data should be in tabular form
- The data in the column should be the same. Text and number should not be in the same column
- No column or row should be left empty. The same was while we do AutoSum
- The first row should contain unique, short heading
- It should provide details of similar records. For example, if sales are the head then it should include data relating to sales and not purchase.
- After you do this and have your data ready in the form prescribed above you can now create Pivot Table in less than a minute.
A huge data table of 300 rows is shown below. You can easily create a Pivot Table as explained here.
Execute the below-given steps to create a Pivot Table:
- Select any cell from your data table
- From the Insert Tab in the ribbon click on the Pivot Table option
- A dialogue box gets open that enables you to select the data. It by default chooses a new worksheet to store the Pivot Table
- Click on OK and right click on Pivot Table and select Pivot Table options
- Click on the display button and enable classic Pivot Table layout option
- Now just drag the Rating column from the Pivot Table fields window into the Row field and Salary p.a. (US$) column into the Value field
The resulted Recommended Pivot Table shows the sum of Salary of the first 50 employees against the rating from 1 to 5.
The same you can achieve it manually without using the Recommended Pivot Table option.
Recommended Blog on Pivot Table
- Pivot Table Slicers In Excel
- How to Group Numbers, Dates & Text in Pivot table in Excel
- How to sort in Pivot Table | Custom Sorting
- Create Pivot Table in Excel
How to Create a Pivot Table Manually?
- Select the range of cells
- Go to the Insert tab and select the Pivot Table option
- A dialogue box will open that will ask you to select the data you want to analyze
- After analyzing the data, select the location of the data in your worksheet
- Click on OK and Excel will create a blank Pivot Table
- Insert the Rating column into the Rows field and Salary p.a. (US$) into the columns field
You will get the exact same result as you got using “Recommended Pivot Table” option as shown below
How To Refresh Data In Pivot Table? (div, name)
Whenever you want to change data or add data into your Pivot table you simply don’t have to create full Pivot Table again. If you add any new data or change any value in the data and you have to refresh those data in your Pivot Table, then you can carry out using three types:
TYPE 1: If you want to make a single change in your data then go to –
Ribbon > PIVOTTABLE TOOLS > Options > Refresh
If there is more than one change you want to refresh then go to-
Ribbon > PIVOTTABLE TOOLS > ANALYZE > Data > Refresh All
(Shortcut key Refresh All is CTRL + ALT + F5)
TYPE 2: Press ALT + F5
TYPE 3: Right click on the Pivot Table and select Refresh option.
The Pivot Table shown in below picture is created for 300 employees and suddenly a new employee joins the firm. In such case, no need to create the table again.
Just add the details of the new employee in your data table and Refresh the Pivot Table as explained above. This will update the Pivot Table automatically.
NOTE: By default, Pivot Tables cannot be refreshed automatically. You need to refresh it manually by simply clicking on the Pivot Table and clicking on Refresh.
If you save your data in the form of Table in your data sheet, then your Pivot Table will automatically get refresh when some data is added.
- How to use Pivot Table in Excel
- Create a report in excel for sales data analysis
- Create report in excel using Pivot Table
How To Drill Down Into A Pivot Table
When you create a Pivot table, it reorganizes and summarizes the selected columns and rows of data. This lets you see a total displayed in a Pivot Table. You can easily Drill Down to visualize and extract the data that makes up the total in the Pivot Table. To Drill Down, simply double-click on the number and Excel will add a new sheet to your workbook that contains the data used for calculation.
Here, we have a Pivot Table showing the Division and number of Employees working in that Division. To view the details of employees for each Division, use the Drill Down method.
As shown in the picture below, 28 employees work in Division AD. If we want to view the details of those 28 Employees who work in Division AD, then just double click on the cell containing number 28 and a new sheet will get open with all the details of those 28 employees.
Likewise, you can view the details of different employees also working in other divisions.
Pivot Table Terminology
This is a tool where your data is summarized to sort and analyze the data automatically. It pulls information from one table and results are displayed on another table. It makes us easy to retrieve specific information easily from a large source. The terms used in the Pivot Table are as follows:
COLUMN FIELD: A field that is assigned a column orientation in the Pivot Table report
DATA AREAS: A cell in the Pivot Table that contains a detailed summary of the data
GRAND TOTALS: Total of all rows and columns in the Pivot Tables
GROUPS: Collection of items that were treated as single items. You can group manually or automatically
ITEM: An element that appears as a row or column header in the Pivot Table
PAGE FIELD: You will see a list of all the fields that were in your source data. It is at the upper left of the Pivot Table report
REFRESH: To update the data in the Pivot Table after alteration in the summary data
ROW FIELD: A field that is assigned a row orientation in the Pivot Table report
SOURCE DATA: The data which is used to create Pivot Tables
SUBTOTALS: Separate column total and row total can also be done in the Pivot Table report
- How to use AVERAGEIFS FUNCTION in Excel
- How to use AVERAGEIF FUNCTION
- How to use ABS Function in Excel
- How to use AND Function in Excel
Why Table For A Pivot Table?
Reasons to use tables to create Pivot Tables are as follow:
Handle a large amount of data for reporting: Pivots Tables are capable of handling a large amount of data but in an easier way to understand the data
Pivot field across and down your report: You can view your report in the three-dimensional form rather than in a flat traditional form
Display complex data graphically: Graphical representation helps to understand the report more easily through charts. It usually saves time for analyzing and decision making
Sort and filter data: You can easily sort and filter your data in the Pivot Table
Drill-down your data: You can drill down your data very easily at a time.
Use Slicer and Timelines: You can use additional features of Pivot Tables like the Slicer and Timelines. Slicer is used for graphical representation whereas timelines are used to sort data according to the dates
Refresh data: As explained earlier, we can refresh data into the Pivot Table when you alter data at the source data
How To Add Fields To Your Pivot Table?
Sometimes, it may happen that you want to add a new field into the Pivot Table after you have created a Pivot Table. In such cases, you can add fields in the Pivot Table very easily.
- Find the Pivot Table in the picture below. It contains the information about the total salary drawn by the employees of each division. Now, if you want to make your Pivot Table more efficient by adding more details, you can do it by following steps:
- Click anywhere in the Pivot Table you created, it will show the field list
- Check the boxes on the field you want to add or drag directly the fields into the Pivot Table. Here, we have added the following data-
- Rating data to the Report Filter field so that we can filter the Pivot Table w.r.t Rating of employees
- We have grouped the Age data and added to columns filed. (Grouping is explained in detail below)
- Click on OK and fields will be added to your Pivot Table as shown in the picture below
This Pivot Table gives you the information about total salary drawn by the employees from each division between the age group of 19-28, 29-38 and so on. You can also filter the Rating column for deep details.
Points to Remember:
- Normally field with numeric information is added to the value area and field with text are added to row label area
- The field in the row label area appear as heading on the left of the table, field added to column label area are added at the top of the table
- Field added to the value area appears inside the table
- You don’t need to create a new Pivot Table to make changes into your current Pivot Table
- Another way to add a field into the Pivot Table is to drag it from the field list to the location you want to add
How To Remove Field From Your Pivot Table?
After a Pivot Table is created you may see that some of the data is not required in the Pivot Table. You can remove the field in the same way of adding the field.
We have a Pivot Table which contains information about the number of employees working in each division whose salary is between 10,000-509999 & 510000-1009999. If you are not interested in knowing the salary range of the employees, you can simply remove it from your Pivot Table.
Steps to Remove Field from Pivot Table:
- Clicking anywhere in the Pivot Table will show a field list
- Uncheck the boxes on the field you want to remove. In this case, we are unchecking the Salary data
- Click on OK and the field will be removed from your Pivot Table as shown in the picture below
Another way to remove a field from your Pivot Table is to drag the field out from the PivotTable Fields window. You can also click on the drop-down menu of the column you want to remove and click on Remove field option.
How To Rearrange Fields Into The Pivot Table?
Moving the field in the Pivot Table will show you different looks of how your Pivot Table will appear i.e. same data with the different orientation. We can move the field in the Pivot Table at any time. The easiest way to move a field from one place to another place is to drag the field.
The area section in the field list will help us to arrange the field in the Pivot Table. We can drag the field we want to arrange between the areas.
You can also arrange your field in alphabetical order or in the order of the source data.
Let’s take an example where you have a Pivot Table that shows the total no of employees working in each division. Also, you can filter the data rating wise i.e. you can also get to know that how many employees have the rating 1,2,3,4 or 5 from each division.
But to view this you need to add a filter for each number every time. To solve this problem, you need to rearrange your Pivot Table.
Steps to Rearrange Pivot Table:
- Click anywhere on your Pivot Table
- Drag the rating column from the Report Filter field and place it to the columns field
You will get to see the rating wise data of employees for each division as shown below
How To Use Field Settings In A Pivot Table?
Field added to a pivot table has attributes that you can set in a dialog box called Field Settings. These attributes include the name of the field, the type of calculation used, layout options etc.
You can observe and change attributes of the field with the help of Field Settings dialog box.
Let’s take an example where you have a Pivot Table on which you have applied a filter of rating from 1 to 5. For each rating, we can view the no of employees working into each division and total salary of the employees from each division.
Now, if you want to view the average salary drawn by the employees of each division, you just have to follow the below steps:
Steps to Change the Field Settings:
- Right click on any one of the cells from the Sum of Salary field
- Click on Value Field Settings option
- Select the Average option from the list and click OK
This will change the data from the sum of salary of each division into the average salary drawn by employees of each division. This is how you can use the Value Field settings to change your Pivot Table.
How To Format Numbers In The Pivot Tables?
Numbers which are added in the Pivot Table needs formatting. You can select the number and do formatting manually but that will consume a lot of time. By default, general formatting is used in the Pivot Table. There are two methods in which you can format numbers in Pivot Table.
METHOD 1: Select the cells and do manual formatting. This way becomes too long as the Pivot Tables get long. Excel will be confused and will apply the same formatting.
METHOD 2: This is the most simple and preferred way to carry out the formatting in Excel. The three different ways to do this are:
Let’s take the below example which shows the no of employees in division AD and sum of their salary w.r.t rating in the Pivot Table. The numbers in the salary column are not in proper format. To convert it into the correct format, use the below steps.
Steps to carry out Number Formatting:
- Right click on the value in the Pivot Table and click on the Number Format option
- Choose the correct format from the category list. In this case, it will be currency format
- Click on OK button and you will get the currency format for salary field as shown in below picture
- By directly double clicking on the Field name you can change the format of the Pivot Table
- Also by clicking on the drop-down menu and selecting value setting, you can select the format you want in the field
NOTE: This can be carried out only when Pivot Table does not contain column label
- OFFSET Function
- INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
- ISFORMULA Function and FORMULATEXT Function
- SUBSTITUTE Function
How To Control Grand Totals In The Pivot Table?
By default, the grand total will appear at the bottom of the Pivot Table and to the right of the table. Columns grand total appears at the bottom of the table and Rows grand total appears to the right of the table.
In this example, we have a Pivot Table containing the division, count of employees and their age as shown in below picture. We also get to see the grand total for the sum of age in the Pivot Table, which is not required. To remove the grand total from the Pivot Table, perform the following steps:
Steps to Hide Grand Total in the Pivot Table:
- Click anywhere in the Pivot Table
- Pivot Table tools will be displayed. Click on Design > Grand Total
- Click on the Checkbox from various options given below and click OK
- Off for Rows and Columns
- On for Rows and Columns
- On for Rows only
- On for Columns only
Here, we will select the ‘Off for Rows and Columns’ option. Now, you can see as shown in below picture, that the grand total from the Pivot Table is removed
You can also use the other option Pivot Table tool > Options Menu > Total & Filter tab > Set up Grand Total as you like.
The other way to open Pivot Table tool is by right-clicking on the Pivot Table.
How To Control Subtotal In A Pivot Table?
If you have only one field in the row you won’t have subtotal. When you add more fields in the row then subtotal is automatically generated.
Let’s take an example where you have a Pivot Table showing the rating wise employee details. Here, we have rating and age data in the rows field and salary & count of name in the values field as shown below. The subtotals are generated automatically when you add more than one column in rows field which is unnecessarily occupying the space and making the Pivot Table look unorganized. To get rid of this, follow the below steps.
Steps to Hide Subtotals in a Pivot Table:
- Select a cell in the Pivot Table
- Under Pivot Tools, on the Excel ribbon click on the Design option
- Click on the Subtotal option in the Layout
- It shows four options. From these options, click on ‘Do Not Show Subtotals’ option
This will remove all the subtotals from the entire Pivot Table and you will find a clear and tidy looking Pivot Table as shown in the picture below.
How To Rename Field In The Pivot Table?
You might need to change a column or a row heading in the Source Table. If you try to change the name of the column directly, it might not get accepted. Also, a dialogue box will open showing the name already exists in the table.
Let’s take an example where you have a Pivot Table showing Employee name in rows field and their salary in column field. As you can see in below picture, the name of salary column is ‘Sum of Salary p.a. (US$)’. If you want to change this default name to name of your choice, then use the below trick.
Simply alter the name by adding space, it will be accepted. And hence you can rename your field.
This is used when we want to change a single name in the Pivot Table. Field setting dialogue box helps you to change the field name. Instead of making changes in the source data and then in the Pivot Table, you can directly change in the Pivot Table of Excel.
Steps to Rename the Field in Pivot Table:
- Click on the cell name or click on ‘Sum of Salary p.a. (US$)’
- Type the name ‘ Salary p.a. (US$)’
- Click OK
Tip: When you have n number of field names to be changed, you need to use MACROS. Macros help to make our job easier.
How To Add A Field Multiple Times To A Pivot Table?
We cannot actually add more than one field at once in the Column or Row area of the Pivot Table. If we add product field in the row area and if the product already appears in the field row, nothing changes. If we add the product field to the column then the product field will disappear from the row.
If you want to see the average salary of the employees vs total salary of the employees by division, then add the same field more than once and change the function of that field. Two fields cannot carry out a similar function. Like both cannot carry out sum of salary. We need to change one field function from sum of salary to Average of salary.
Steps to Add a Field to a Pivot Table more than once:
- Click anywhere on the Pivot Table
- From the Pivot Table, Fields window drag the salary column to the values field, where sum of salary column already exists
- Right click on values field and go to Value Field settings
- Change the type of calculation and click on OK
Here, we have selected the Average option. This will show the total salary and average salary of employees division wise as shown below.
NOTE: The name should not be repeated from the Pivot Table
How To Use Pivot Table Layout?
After you have created a Pivot Table and added fields to your table, you will now want to enhance your layout to make it easy to read. You can use the layout to change the Pivot Table format like the field, columns, rows, lines displayed. There are three choices for you to change the layout of the report.
COMPACT LAYOUT: This layout has been added new in Excel. By default, the compact layout is selected for all Pivot Tables. This layout is useful when you want to reduce the width of the Pivot Table. Expand and Collapse buttons are displayed so that you can hide or display details. It saves space and data readable. One of the disadvantages of this feature is that if you copy the data into another worksheet further analysis would be harder.
TABULAR LAYOUT: This layout shows data in a table form and makes it easier to copy cells from one worksheet to another. Subtotal never appear at the top of the group. It is a traditional format used by most of the people.
OUTLINE LAYOUT: It is similar to the tabular layout. But it displays subtotal at the top of every group. Here, the width of the Pivot Table is not taken into consideration. This layout helps us when we want to show field name as headings. Because of the outlines, each field is filtered separately. It uses too much of horizontal space. The advantage of this layout is that it does not allow the data of the Pivot Table to the new page for further analysis.
Let’s take an example where we have a Pivot Table in a tabular form. It contains the information of the no of employees working in different divisions and also information regarding their salary. As shown in below picture, the table is quite lengthy. To compress the table without losing any information, use the compact form of Pivot Table.
Steps to Change the Layout of the Pivot Table:
- Click anywhere in the Pivot Table and activate the PivotTable tool
- Select the Design tab on the ribbon
- Click the report layout icon and choose the Compact layout
Grouping Pivot Table
John Walkenbach in Excel 2016 Bible explained: “One of the most important features of a Pivot Table is the ability to combine items in the group.” Grouping will allow you to simplify your analysis and focus on selected items. You can group dates, number, and field text. You can quickly group items in the field by highlighting the items and the pressing keyboard shortcut key ALT + SHIFT + RIGHT.
Generally, there are two ways in which you can group in the Pivot Table.
Excel automatically detects the field across the Pivot Table. It adds calculated column and rows according to the data. The new version of Microsoft Excel 2016 helps you to automatically group date or time field.
NOTE: Grouping is applicable only for the row fields and column fields. Data in the value field and report filter filed cannot be grouped together.
How To Group A Pivot Table Manually?
Grouping means putting the numbers into range. Manual grouping of Pivot Table allows you to group the data according to your wish. The range of group need not be consistent in the manual grouping.
Here, you have a Pivot Table where you want to view how many employees of different age group work in each division. To group the age of employees as per your requirement, use the manual grouping technique.
Steps to Group Pivot Table Manually:
- First, hold the control key on the keyboard (CTRL) and select each item you want to include. Here, we have selected 10 items in the first group
- Go to the Analyze pane and click on the “group selection” from the options table of the Pivot Table tool
- Excel will put the selected items into that group and the remaining item into another group
- Keep on doing this until you are done with all the items in the Pivot Table
In this way, we have created 4 age groups manually and compressed our Pivot Table. Subtotal for manual grouping can be enabled or disabled.
How To Group Pivot Table By Numbers?
Group Pivot Table means creating a frequency distribution. Frequency distribution will put numbers into range.
Let’s take the same example as we have taken in a manual grouping. Here, we will group the age field using an automatic technique.
Steps to Group Numbers automatically in Pivot Table:
- Right click on the column field where you want to group the numbers
- Select the option group
- Now, in the grouping window, select the starting range and ending range. Also, select the number by which you want to make the group
- It by default takes the starting value as the first value and ending value as the last value in your column field
- You can change the value or use the same value and click on OK
In this example, we have used the same default value generated. As shown in the below picture, the automatic grouping is done successfully.
How To Group Pivot Table By Dates?
Grouping dates in the Pivot Table help us to group data into week, months and quarter of the year. Grouping makes your data flexible to read. We need to see that we have grouped all the dates in the Pivot Table. If any of the dates is not grouped then Excel won’t group the date field. We need to take care while grouping the data with dates.
When you want to view the no of employees and their total salary w.r.t their date of joining it becomes a very huge data table as every employee’s joining date is unique. Therefore, you can group the date to view how many employees had joined the organization in a particular year.
Steps to Group Dates in Pivot Table:
- Right click on the column field where you want to group the dates
- Select the option group from the list
- In the grouping window, select the years’ option. Also, check the starting date and ending date
- You can change the date or use the same date and click on OK
This will group the Pivot Table by years as shown below and will make it easy to view.
How To Collapse And Expand Pivot Table Group?
To expand the Pivot Table, it is necessary that your Pivot Table must be grouped. Pivot Table can be collapsed after it is expanded.
Steps to Expand/Collapse Pivot Table:
- Right click on any one cell or select all the cells from the grouped field and select the option Expand/Collapse
- In the Expand/Collapse option click on the Expand option
- This will show the details of the employees that had joined in that year in the same sheet
- If you want to collapse the data then click on collapse option and will get back you the original grouped data
SORTING PIVOT TABLE
Sorting of data in Pivot Table can take place horizontally or vertically. You can sort data in Pivot Table that you want to analyze. You can sort data from the lowest to the highest values or from the highest to the lowest values or any order of your choice. You can also sort data alphabetically. Sorting can be done for row fields, subtotal etc. You cannot sort data using specific formats, conditional formatting indicators etc.
HOW TO SORT PIVOT TABLE ALPHABETICALLY?
When we create a Pivot Table, the fields are placed in the order as it was present in the source data. Sorting the Pivot Table alphabetically will help us to find a specific name or item easily.
As shown in the picture below, we have a Pivot Table that shows the number of employees and their total no of salary by each division. But this data is not in a proper alphabetical order.
If we want to represent the number of employees working in each division in an increasing/decreasing order, we have to sort the Pivot Table. This can be done with the below steps.
Steps to Sort the Pivot Table Alphabetically:
- Right click on the cell in the Pivot Table. Here, we will click on any one of the cells from the Row field
- Click on the Sort option from the list
- Select the ‘Sort A to Z’ option and your data will get sorted accordingly
How To Sort Pivot Table By Value?
In many cases, you’ll want to sort pivot table items by values instead of names. To sort a pivot table by value, just select a value in the column and sort as you would sort an Excel Table.
Let’s take an example where you have a Pivot Table with the data of employees. We want to view the no of employees working in all the divisions in a highest to lowest order. To do this, we will sort the values in Pivot Table.
Steps to Sort the Values in Pivot Table:
- Right click on the cell in the Pivot Table. Here, we will click on any one of the cells from the Values field
- Click on the Sort option from the list. Since it is a number value, sorting options here are different than those of text values
- Select the ‘Sort Largest to Smallest’ option and your data will get sorted accordingly
In this way, you can compare which division has the highest no of employees and which one has the smallest.
How To Sort Pivot Table Manually?
Along with sorting pivot tables by alphabets and numbers, you can sort a pivot table manually, by just by dragging the items you want to move.
Let’s take the same example as above. If you want to view only the details of the RDD division first in your Pivot Table, then in such case you cannot sort the data alphabetically or numerically. In such cases, you can go for manual sorting.
Steps to sort a Pivot Table Manually:
- Select the cell you want to move. In this case, we will select RDD
- Hover the mouse on the cell to view a four-headed arrow
- Now, drag the cell into the new location. Here, we are dragging it to the top position
This is how you get to see the only one value sorted. Likewise, you can sort as many cells you want.
Filtering Pivot Table
One of the best features of a Pivot Table is filtering, which allows you to view specific results in your data. Here we are going to see which types of filters are available and learn how you can apply more than one filter on a Pivot Table field at the same time.
How To Filter Pivot Table Globally?
Though there are several ways to filter information in a pivot table, the report filter is considered as the basic tool to limit the information in a pivot table. It gives you a way to limit the information faster by filtering one or more fields.
Let’s take an example where we have a Pivot Table showing no of employees and their salary division wise as shown in below picture. If we want to get the more details out of it, we have to add more fields to our Pivot Table. Here we have added Rating data to the Report Filter field to filter the Pivot Table globally by rating.
Steps to Filter the Pivot Table Globally:
- Click anywhere on the Pivot Table
- Drag the field from the Pivot Table Fields and place it to the Report Filter field. Here we have added the Rating data to the Report Filter field
- It is always that when you add a new report filter, it shows all the items. To use the filter, click on the drop-down arrow on the report filter
- Select any one value and click on OK. We have selected the rating 5 which shows that total 35 employees from overall divisions have the rating 5 and so on
You can also click on the checkbox of ‘Select Multiple Items’ option and select multiple items from the filter.
How To Filter Pivot Table By Rows Or Columns?
After adding a field as a row or column label in a pivot table, you get to filter the results in the table by items automatically. A drop-down arrow appears in the header area of the rows and columns in the Pivot Table. And this is how you can filter the Pivot Table using rows and columns.
Here we have a data table which consists of the Divisions that we have placed in the row field and we have no of employees in the values field. We can also view the rating of the employees as the rating column is placed in the columns field as shown in below picture.
Now, if you want to view the details of only the first four divisions and the ratings only up to first three, then in such a case use the rows and columns filter as explained below.
Steps to Filter the Pivot Table using Rows and Columns:
- Click on the drop-down arrow of the rows field
- Uncheck the row items you do not want to display and click on OK
- It will show only those row items that you have not unchecked
- Now, click on the drop-down arrow of the columns field
- Uncheck the column items you do not want to display and click on OK
- It will show only those column items that you have not unchecked
To clear the filter click on the option of ‘Clear Filter from Division’. This is how you can filter the Pivot Table using Rows and Columns.
How To Filter Pivot Table By Date?
Excel Pivot Table has special options for filtering the date fields that have been added as a row or column label. We can filter the dates very easily using these options. The options available for filtering Date in Excel are Tomorrow, Today, Yesterday, This Week, Last Week, Next Month, Last Quarter, This Year etc.
Let’s take an example where we have a Pivot Table that includes a date (grouped by Year) in the columns field and division in the rows field. To filter the date values, follow the below steps.
Steps to Filter the Pivot Table by Date:
- Click on the drop-down arrow of the columns field where you have your date values
- Hover your mouse on the Date Filters option from the list
- It will show a long list of Date filter options. Select any one option that you want to use for filtering dates. Here, we are using the between option
- When you click on the between option, it will open a new window where you have to give the range of dates that you want to display and click on OK
Here, we want to show the data only for one decade so we have chosen the date from 2001 to 2010. Hence, the date filter will display only the data from 2001 to 2010. You can also use other date filter options if you want.
How To Filter Pivot Table By Value?
In addition to the ways of filtering discussed above, you can also filter the values that appear inside the Pivot Table. Value filters are located in the same drop-down menu options where label filters are located. Value filters work on the values having a label field rather than filtering the label itself. The options available for filtering values are Greater Than, Less Than, Between, Equals etc. These options are all related to filtering numeric values.
In the Pivot Table shown below, we want to view the details of only those divisions that have more than 10 employees. To do so, we have to apply the value filter on the values and select the range we want to display.
Steps to Filter Pivot Table by Values:
- Click on the drop-down arrow of the rows field and go to the Values Filter option
- In Values Filter option, choose the Greater Than option
- This will pop up a window where you have to specify the field name and number for applying the command and click OK
Here, we have selected the number 10 for the count of employees’ name. This will display details of only those divisions that has greater than 10 employees in your Pivot Table as shown below.
How To Filter Pivot Table To Show Top Values?
To show top or bottom values, we have a special kind of value filtering for a Pivot Table. It is an enhanced feature of the value filter option where you can filter the top or bottom values from the Pivot Table.
Here is an example of filtering top/bottom values in Pivot Table. We have a Pivot Table that shows how many employees have joined the organization each year from 1995-2018. Now, if we want to view only those 5 years that has highest no of employees joining the organization from the Pivot Table, then we can use the Top 10 option for filtering.
Steps to Filter Pivot Table by Top 10 Values:
- Click on the drop-down arrow of the rows field and go to the Values Filter option
- Select the Top 10 option
- This will pop up a window where you have to specify the field name, number and whether you want to filter top or bottom values and click OK
In this example, we have filtered the top 5 counts of employees as shown in below picture. You can also filter the bottom values using this option. Also, though the option has the name Top 10, you can exceed the limit more than 10.
How To Filter Pivot Tables With Multiple Filters?
Do you know you can add multiple filters to your Pivot Table at a time? Normally, a Pivot Table does not allow you to apply multiple filters to the same field. However, you can change the settings to enable this option. Here is how we are going to discuss the technique to add multiple filters at a time.
Let’s take an example where we have a Pivot Table that shows how many employees have joined the organization in each year from 1995-2018. Now, if we apply a date filter to view the dates between 2008- 2018 and we want to apply a value filter on this filtered Pivot Table, it is not possible directly. To apply multiple filters at a time, follow the below steps.
Steps to Filter Pivot Table by Top 10 Values:
- Click on the dropdown list on rows field and select date filters
- Select the option between and enter the dates for which you want to display the details and click OK. Here, we have selected the dates between 2008- 2018
- Now, add a value filter. For that, go to the dropdown list and select the between option
- Give the range from 10 to 20 for the count of employees and click OK
- It will filter the entire Pivot Table and generate the unexpected result
- To apply both the filters together, right-click in the Pivot Table and select the PivotTable options from the list
- Select the Totals & Filters tab
- Activate the “Allow multiple filters per field” option and click OK
- Now clear the filter and apply both the filters again as explained above
As shown in the picture below, you can find multiple filters applied on the Pivot Table
Pivot Table Slicer
Along with the filter in the Excel table, you can also add a slicer to a table. Slicers have the same function as filters. They let you selectively display rows based on field values. But, instead of drop-down menus like that in filters, slicers provide large, friendly buttons that are always visible in your table.
To clear the slicers, you can use the clear icon on top of the slicer. You can also use the Clear button on the ribbon in the Analyze pane of Pivot Table Tools.
To remove a slicer from your Pivot Table, just select the slicer and press delete button.
How To Add Slicer To A Pivot Table?
Slicer buttons function like filters. In other words, filters and slicers work together and stay in sync. As we click on various buttons in a slicer, Pivot Table filters the table. To enable more than one button at a time from your slicer, hold down the control key as you click.
Let’s see how to add a slicer in Pivot Table using the below example.
Here we have a Pivot Table that holds the basic employee information. We have the Division data in the Filters field from which we can select the items and apply the filter to the Pivot Table. But if you want to do it more quickly and easily use the slicer for filtering.
Steps to Add a Slicer to the Pivot Table:
- Click on the Pivot Table and go to the Analyze pane
- Click on the Insert Slicer option to select a column on which you want to apply the slicer and click OK
- Here, we have applied the slicer on Division field
- Click on any one of the divisions from the slicer and view the result as shown in the picture below
It shows how many employees have joined from the AD division in different years and have different salaries. Likewise, you can view the data for other divisions also.
How To Use Pivot Table Slicer Styles?
Like we have Pivot Table styles, Pivot Table slicers also have their own structure for creating and applying the visual styles.
Use the Slicer styles available on the ribbon of Slicer Tools. They hold the mechanism of the color and style that is used to display the slicers. Slicer styles have the five main areas of formatting to control: the slicer background, the header, selected items, unselected items, and hovered items. When you hover a mouse on the buttons, the slicer shows the preview of the style. Along with the Pivot Table styles, there are a wide variety of styles available, each with a different look.
How To Use A Slicer With Many Pivot Tables?
One of the advantages of using slicers is that they can control multiple Pivot Tables or charts. Therefore they are very useful for building Pivot Table dashboards. Now we are going to see how to use a slicer with multiple Pivot Tables.
We have two Pivot Tables showing different data about employees. One is showing the date of joining of the employees and other is showing the divisions of employees. If we add a slicer using one Pivot Table, it will control only that Pivot Table and the data of the other Pivot Table will not be filtered. In order to control multiple Pivot Tables using one slicer, follow the below steps.
Steps to Use a Slicer with Multiple Pivot Tables:
- Click on the slicer and go to the options pane
- Click on the Report Connections button. You can also directly right click on the slicer and select Report Connections option
- In the report connections dialogue box, you will find the list of Pivot Tables. Here, we have two Pivot Tables but you can also have more than that.
- Click on the checkbox of the Pivot Tables and click on OK
Now use the slicer to filter and you will find that the slicer effect is seen on both the Pivot Tables and Grand Total of both the Pivot Table now matches to each other as shown below.
Pivot Table Calculation
You can create calculated fields using the formulas in a Pivot Table. Even though there are limitations to the functioning of the calculated field, they let you control your Pivot Tables. For calculated fields, the distinct amounts in the other fields are summed, and then the calculation is carried out on the total amount. Calculated fields are automatically available in all Pivot Tables as Pivot Tables are based on the pivot cache
How To Insert Calculated Field To A Pivot Table?
A calculated field can only be displayed in the value field. You cannot move a calculated field into the Row or Column label area. Also, calculated fields can only summarize data using the Sum function. You’ll observe that the Summarize Values by menu is deactivated on the ribbon. You can change the Summarize Values by calculation using the Field Settings.
Here we have a Pivot Table where we have the salary of all the employees’ division wise and we want to calculate the bonus salary given to the employees which are 10% of their total salary. To do this, follow the below steps.
Steps to Add a Calculated Field to a Pivot Table:
- Click on the Pivot Table and go to the Analyze option
- Select the Fields, Item & Sets option
- Click on the Calculated Field option
- Name the Calculated field. Here we want to add a bonus column so we have named it Bonus Salary
- Add a formula to calculate the bonus using the Salary column and clicking on Insert Field
- Click on OK and you will find the new column in the values field as shown below
How To Transform A Calculated Field In A Pivot Table?
After you create a calculated field, you can easily update the formula and make changes to it. But you can’t undo the changes made using the Calculated Field dialog box, so be careful.
Steps to Transform a Calculated Field:
- Go to the Insert Calculated Field dialog box
- Select any cell in the pivot table and on the Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, then choose Calculated Field
- Select the calculated field from the drop-down list
- You can now update the formula as per your requirement
- Click on the Modify button to update the formula leaving the dialog box open
- Click OK close the dialog box
You can see that your calculated field gets transformed or modified.
Pivot Table Styles
Pivot table styles are available on the Design tab under the PivotTable Tools option. To use a style you can select any cell in the Pivot Table and go to the Design tab. All the styles are listed there. When you hover over any style, it will give a preview of that style.
How To Apply A Pivot Table Style?
Pivot Table has a number of pre-built styles. With the help of them, you can format the complete Pivot Table using a single click.
Here, you have a Pivot Table, where you want to make some formatting changes.
Steps to Apply a Pivot Table Style:
- Click on the Pivot Table and go to the Design option
- Click on the drop-down of Pivot Table Styles
It has three levels- Light, Medium and Dark. Select any style of your choice and apply to your Pivot Table
How To Create A New Pivot Table Style?
You can create your own Pivot Table style easily and also apply them to multiple Pivot Tables.
Let’s take the same example as above. We have to create a new style here.
To create a new style follow the below steps:
- Duplicate the existing style by right-clicking, and choosing Duplicate from the menu
- Name the duplicated style
- After you create a new Pivot Table style and apply it, you can easily customize the style as you wish
- Right click on the Pivot Table style and choose the Modify option from the list
- Click on OK for the new setting to take effect
When we perform the above process and apply the custom style, we can view all the formatting we defined.
Pivot Charts are an extension of Pivot Tables. They are always connected to a Pivot Table, and you cannot transform one without transforming the other. You can find Pivot Chart Tools on the ribbon, with the Analyze tab. You can access several useful commands by right-clicking a Pivot Chart.
Pivot Charts don’t have the Chart Filter menu that a normal chart has. To filter data in a Pivot Chart, you can work directly on field buttons or filter the Pivot Table itself.
How To Create A Pivot Charts?
Pivot Charts are based on Pivot Tables. So, to create a Pivot Chart you must have a Pivot Table. If you want to create a Pivot Chart and Pivot Table from scratch, here is the procedure explained with an example.
Let’s take an example where you want to represent the employee data division wise in the form of Pivot Chart. But the data is in the form of Excel Table. So, to create a Pivot Table and Pivot Chart use the below steps.
Steps to Create a Pivot Chart:
- Go to the excel data and click on the Insert option
- In Insert option, go to the Charts section and click on the drop-down arrow of Pivot Charts option
- Select the PivotChart & PivotTable option from the list
- Select the table range and click OK
This will take you to the new sheet where you have to create a Pivot Table. Here we have selected the Division in rows field and count of employees in values field
You will get to see that as you create a Pivot Table, your Pivot Chart will automatically get created as shown in below picture. This will save your time in creating the chart. When you make changes to the Pivot Table, Pivot Chart will also change.
How To Change The Pivot Chart Types?
After you create a Pivot Chart, it’s simple to change the Chart type if the default chart type is not relevant for your data. You can change the Chart Type very easily with the help of Change Chart Type option.
Here we have a Pivot Table showing the total no of salary given to the employees from all the divisions every year. We have represented this data in the form of Pivot Chart as shown below. But this chart data is not much self-explanatory. Therefore we need to change the type of Pivot Chart.
Steps to Change the Pivot Chart Types:
- Click on the chart you have created and in the Pivot Chart Tools, go to the Design tab
- Click on the Change Chart Type option
- You will get to see the different types of chart options. Select any one type of your choice and click OK. We have selected the line chart in this example
- There is also Chart Styles pane in the ribbon. You can choose any style that will suit your data.
In this way, you can change the type of your chart as shown below.
How To Work With Pivot Chart Options?
When you select a chart type, you get to see that there are many options available to increase readability.
Let’s take the same example as taken above. If we want to make our Pivot Chart more attractive and improve the readability, use the Pivot Chart options.
Steps to Change the Pivot Chart options:
- Click on the Pivot Chart and go to the Pivot Chart Tools option.
- Select the Format option and go to the WordArt styles to make all the text in the chart a dark gray
- Change the number formatting of the Salary field in the Pivot Table to currency. This will also show its effect in Pivot Chart
- Also sort the numbers in Pivot Table from lowest to highest so that the series in the Pivot Chart changes
- Change the outline color and size of the chart from the Shapes style option in the Format tab
- Right click on the Plot area of the chart and change the color
- Click on the gridlines of the chart and modify them
There are many other formatting options you can work with to enhance your Pivot Chart.
How To Filter A Pivot Chart?
After you create a Pivot Chart, you may need to add or remove certain data from your data set. For that, you need to add a filter to your Pivot Chart. You can also filter your Pivot Table to filter your Pivot Chart. Here, we have explained how to filter data in Pivot Chart.
Here, we have a Pivot Table showing total no of employees working in each Division and their ratings. Now, we will filter this data. Note that we can filter the rows field, columns field and filter field but we cannot filter the values field in Pivot Chart.
Steps to Filter the Pivot Chart:
- Click on the dropdown list of the division field button on the Pivot Chart and select the divisions you want to show and click OK
- Here we have selected the AD, CDFD, HFD, PEMD and RAD divisions as shown in the picture below
- You can also filter the filter field by clicking on the filter field button on top of the Pivot Chart as we have done here
This is how you can filter your Pivot Chart in no time.
How To Create A Second Pivot Chart?
When you want to create a second Pivot Chart using the same data source, you just have to copy the chart and paste it on the same sheet. You can also change the chart type without affecting the first chart. But if you make any changes to the field both the charts will change. Therefore, if you want to create two independent charts on the same sheet use the steps given in the below example.
Here is the Pivot Table showing no of employees per division. We have also created a column Pivot Chart as shown in below picture. To create two independent Pivot Charts on the same sheet showing the same data, use the below steps.
Steps to Create a Second Pivot Chart:
- Go to the Insert tab and click on the Pivot Chart option
- Click on the drop-down and select the PivotChart & Pivot Table option
- Select the range and choose the existing worksheet option to create a new Pivot Table and Pivot Chart on the same sheet
- Add the same fields present in the first Pivot Table to the new Pivot Table so that both the Pivot Tables reveal the same information but independent of each other
- Add one more field to the second chart in the Filter Field like we have added rating field here
- Filter the second chart w.r.t rating and you will observe that changes have occurred only in the second chart
Also, change the Chart Type and style of the second chart as shown in the picture below
How To Make A Self-Contained Pivot Tables?
When you create a Pivot Table, Excel creates a duplicate of the data and stores it in a Pivot Cache. Pivot Cache makes exploring data with a Pivot Table. Since a Pivot Cache is a true copy of the source data, you can remove the source data from your workbook if you like.
We have created a Pivot Table from the source data for employees. Now, if the source data is removed for some of the other reason, you can extract the original data with the help of Pivot Table as the duplicate of the data is stored in Pivot Cache.
Steps to make a Self-Contained Pivot Table:
- Create a new Pivot Table
- Update the source data and check your Pivot Table. You will observe that your Pivot Table is not updated along with the source data. This is because the data is stored in Pivot Cache
- Refresh the Pivot Table and you will get the updated Pivot Table
- Now, delete the source data and go to the Pivot Table. Again you will notice that the Pivot Table is not hampered due to the Pivot Cache.
- Save this file and you will observe that removing source data reduces the file size significantly
- Again if you want to get back the source data, simply go to the Pivot Table and double click on the Grand Total. This is how you can get back your source data.
How To Group A Pivot Table By Weekdays?
As discussed above, you can group the fields by year, by month, by quarter, and even by day and hour. But if you want to group by something like the day of week, you’ll need to refer to the below example.
We have a source data showing joining date and details for all the employees. Since, we want to convert this data into a compact Pivot Table and split the data by weekdays and month, we can group the dates easily into months, but to group into weeks, follow the below steps.
Steps to Group a Pivot Table into Week Days:
- Go to the source data and convert it into the table by clicking on the Insert tab and selecting the Table option
- After you source data gets converted to the Table format, click on the Summarize with Pivot Table option from the Design tab of Table Tools
- Select the Table range and location and click OK
- This will take you to the new sheet where you have to add the fields in the Pivot Table
- We have grouped the date of joining and added to the rows field and count of employees to the values field
- Now, go to the source data and add a new column. Write the formula =TEXT([@DoJ], “dddd”) add the weekdays to source data
- Go to the Pivot Table and right click to refresh
- After refresh, add the new column i.e. Week column to the columns field
Picture below shows how we can easily group a Pivot Table by day of week.
HOW TO CLONE A PIVOT TABLE?
When you have one Pivot Table and you want to create another Pivot Table, you have to show a different view of the same data. This can be achieved easily by cloning or duplicating the Pivot Table.
Here, we have a Pivot Table for divisions by salary. But if we want to look for ratings by salary, then the solution is to rearrange the Pivot Table to show ratings by salary, instead of divisions by salary. You can easily do this once or twice.
But if this is a report that you update weekly or monthly, and you want to always see ratings by salary, divisions by salary, and other information, then you cannot make the changes manually each time. The simplest way is to create additional Pivot Tables, where each Pivot Table shows a different view of the same data.
Steps to Clone a Pivot Table:
- Name the worksheet of your first Pivot Table. In this case, we have named it ‘Divisions by Salary’
- Duplicate the worksheet and name it. Here, we have named it as ‘Ratings by Salary’
- Adjust the Pivot Table to show the breakdown you want
- Now we have two Pivot Tables, both linked to the same data. If we refresh one Pivot Table, the other Pivot Table also gets refreshed
You can use this approach to create as many Pivot Tables as you want, all linked to the same source data.
HOW TO COPY A PIVOT TABLE WITHOUT COPYING DATA?
When you create a Pivot Table with source data in a worksheet, Excel creates a data cache that comes along with the Pivot Table. If you have some sensitive and confidential information that you do not want to share, then this technique is of no use.
To copy and paste the Pivot Table without sharing the source data, we have to use the paste spatial option.
Here we have a Pivot Table showing the Division by Salary data. Since it a salary-related data, we cannot share the source data. Also, the data increases the size of the worksheet. To overcome this issue follow the below steps.
Steps to Copy a Pivot Table without the Data:
- Copy the Pivot Table and open a new worksheet
- Right click on the new worksheet and go to the Paste Special options
- First, select the value option from Paste Special window and click on OK. The value option will copy only those values that are displayed in the Pivot Table and not the entire source data
- Again go to the Paste Special options and click on the Formats option and click OK. Formats option will copy the formats from the Pivot Table
- Once again go to the Paste Special option to select the Columns width option to adjust the widths
Now, you will observe that the Pivot Table you copied looks exactly the same as the original one as shown in the picture below. Also, it does not carry the source data.
HOW TO GROUP A PIVOT TABLE BY CREATING RANGE OF AGE?
One of the most powerful features of Pivot Tables is their ability to group the data. Any field that is added as a row or column label, gets automatically grouped by the values that appear in that field.
We have a Pivot Table showing how many employees of each age fall in the ranting of 1-5. But this Pivot Table is too lengthy and we need to group the age field to get the compact Pivot Table.
Steps to Group the Age Data:
- Right-click anywhere in the age field
- Select the Group option
- Select the starting and ending range and click OK
This is how you can easily group the age data as shown below.
WHY PIVOT TABLES?
A Pivot Table is a table which is used to store the summary of the source data. It consists of rows, columns, data fields, and pages as we have discussed above. Now, we are going to see the advantages of using Pivot Table.
Advantages of using Pivot Table:
- Smooth Working – The data can be summarized in various ways including frequencies and averages. You can add n number of fields to the Pivot Table without disturbing the data
- Ease of Use- Anyone can easily create and use the Pivot Table within a fraction of second. It is very easy to use.
- Simple Data Analysis- Pivot Table allows you to compress a large amount of data in such a way that you need to view only a few data fields and can easily drag into it.
- Easy Summary of Data- The data can be summarized in a simple format that is easy to understand. Users can arrange rows and columns of the data and they can label it and sort it in any way they want to.
- Quick Report Creation- One of the important features of Pivot Tables is that it helps to create reports in an efficient way. This saves your time for creating reports manually.
10 PIVOT TABLE PROBLEMS AND THEIR SOLUTIONS
- New data not included when you refresh-
When you add new data to your data source, it is not included in your Pivot Table even after refresh. To fix this problem go to-
PivotTable Tools > Analyze > Change Data Source
When you select the proper range of data, it will update your Pivot Table
- You want totals and percent of the total-
When you want to show the total and percent of the same total in your values field, do the following-
- Add the same data twice to your values field
- Right click on second data and select the option ‘Show Values As’
- Select the option ‘% of Grand Total’ to add Percent of Total to your Pivot Table
- You don’t want a datasheet-
When you don’t need the data source after creating a Pivot Table, you can simply delete that. Your Pivot Table will not be harmed. This is because the data is stored in the Pivot Cache as explained above. If you want to get the source data back simply double-click on the Grand Total in Pivot Table.
- The automatic field names are not of your choice-
The automatic value filed names generated by Pivot Tables are not much liked by anyone. If you want to rename it, just go to the header and edit the name. If you want to keep the original name as shown in the Field list, then Pivot Table will not allow you to do so. But you can still manage to keep the same name by adding one extra space in the end.
- Pivot Table loses table formatting-
When you format the numbers in your Pivot Table using shortcut-Ctrl+ Shift+ $ and if you change the row fields, then number formatting will be completely lost. To avoid this, right click on values field and go to
Value Field Settings > Number Format > OK
- You can’t see the data behind a subtotal-
When you want to see a data behind the subtotal, just double-click on that subtotal and all the data related to that subtotal will be displayed in a new sheet.
- Changing one Pivot Table affects another-
When you copy a Pivot Table and paste it on the same sheet and make changes to one Pivot Table, it will affect another. To avoid this, do the following-
- Cut paste one Pivot Table to a new datasheet
- Refresh the Pivot Table and copy it back to the original datasheet
- Now, make some changes like ungrouping the data and it will not affect the first Pivot Table
- Refreshing a Pivot Table changes the column widths-
If you refresh the Pivot Table it sometimes changes the column width. If you do not want to bother the column width, then just right click on Pivot Table and go to-
Pivot Table Options > Layout & Format > Autofit column widths on update
Uncheck the box and click Ok
- Field headings make no sense-
Most of the times, the Row headers and column headers make no sense in Pivot Table and just take up space. If you want to remove them then go to –Pivot Table Tools > Analyze > Disable Field Headers option
- Blanks in your Pivot Table-
When you have blank cells in your Pivot Table, you can easily fix it by adding a value in all the blank cells of your Pivot Table. This can be done as follows-
- Right Click on Pivot Table
- Go to Pivot Table options
- In the Layout & Format section, check the box of ‘For Empty Cells Show’ option
- Add a value to the text box in it e.g. ‘0′ and click OK
This value will be displayed in the Pivot Table for all blank cells
Here, we have learned about the Pivot Table in Excel. We have seen its uses and functioning with examples. Hope your concept regarding the Pivot Table is clear and you can now carry this out easily.