.
This represents Task Pane Options. Click the button
.
The Task Pane Options- Move, Size and Close appear in the dropdown list.A PivotTable is an extremely powerful tool that you can use to slice and dice data. You can track and analyze hundreds of thousands of data points with a compact table that can be changed dynamically to enable you to find the different perspectives of the data. It is a simple tool to use, yet powerful.
The major features of a PivotTable are as follows −
Creating a PivotTable is extremely simple and fast
Enabling churning of data instantly by simple dragging of fields, sorting and filtering and different calculations on the data.
Arriving at the suitable representation for your data as you gain insights into it.
Ability to create reports on the fly.
Producing multiple reports from the same PivotTable in a matter of seconds.
Providing interactive reports to synchronize with the audience.
In this tutorial, you will understand these PivotTable features in detail along with examples. By the time you complete this tutorial, you will have sufficient knowledge on PivotTable features that can get you started with exploring, analyzing, and reporting data based on the requirements.
You can create a PivotTable from a range of data or an Excel table. You can start with an empty PivotTable to fill in the details, if you are aware of what you are looking for. You can also make use of Excel Recommended PivotTables that can give you heads up on the PivotTable layouts that are best suited for summarizing your data.
You will learn how to create a PivotTable from a data range or Excel table in the Chapter - Creating a PivotTable from a Table or Range.
Excel gives you a more powerful way of creating a PivotTable from multiple tables, different data sources, and external data sources. It is named as PowerPivot that works on its database known as Data Model. You will learn these Excel power tools in other tutorials in this Tutorials Library.
You need to first know about the normal PivotTable as explained in this tutorial, before you venture into the power tools.
The PivotTable layout simply depends on what fields you have selected for the report and how you have arranged them in Areas. The selection and arrangement can be done by just dragging the fields. As you drag the fields, the PivotTable layout keeps the changing and it happens in a matter of seconds.
You will learn about PivotTable Fields and Areas in the Chapters – PivotTable Fields and PivotTable Areas.
The primary goal of using a PivotTable normally is to explore the data to extract significant and required information. You have several options to do this that include Sorting, Filtering, Nesting, Collapsing and Expanding, Grouping and Ungrouping, etc.
You will have an overview of these options in the Chapter - Exploring Data with PivotTable.
Once you collate the data required by you by the different exploration techniques, the next step that you would like to take is to summarize the data. Excel provides you with a variety of calculation types that you can apply based on suitability and requirement. You can also switch across different calculation types and view the results in a matter of seconds.
You will learn how to apply the calculation types on a PivotTable in the Chapter - Summarizing Values by Different Calculation Types.
Once you have explored the data and summarized it, you need not repeat the exercise if and when the source data gets updated. You can refresh the PivotTable so that it reflects the changes in the source data.
You will learn the various ways of refreshing data in the Chapter – Updating a PivotTable.
After exploring and summarizing the data with a PivotTable, you would be presenting it as a report. PivotTable reports are interactive in nature, with the specialty that even a person not familiar with Excel can use them intuitively. Because of their inherent dynamic nature, they will enable you to change the perspective quickly of the report to show the required level of detail or to focus on the specific items in which the audience expresses interest.
Further, you can structure a PivotTable report for standalone presentation or as an integral part of a broad report as the case may be. You will learn the several of reporting with PivotTables in the Chapter – PivotTable Reports.
You can create a PivotTable either from a range of data or from an Excel table. In both the cases, the first row of the data should contain the headers for the columns.
If you are sure of the fields to be included in the PivotTable and the layout you want to have, you can start with an empty PivotTable and construct the PivotTable.
In case you are not sure which PivotTable layout is best suitable for your data, you can make use of Recommended PivotTables command of Excel to view the PivotTables customized to your data and choose the one you like.
Consider the following data range that contains the sales data for each Salesperson, in each Region and in the months of January, February and March −
To create a PivotTable from this data range, do the following −
Ensure that the first row has headers. You need headers because they will be the field names in your PivotTable.
Name the data range as SalesData_Range.
Click on the data range – SalesData_Range.
Click the INSERT tab on the Ribbon.
Click PivotTable in the Tables group. The Create PivotTable dialog box appears.
In Create PivotTable dialog box, under Choose the data that you want to analyze, you can either select a Table or Range from the current workbook or use an external data source.
As you are creating a PivotTable from a data range, select the following from the dialog box −
Select Select a table or range.
In the Table/Range box, type the range name – SalesData_Range.
Select New Worksheet under Choose where you want the PivotTable report to be placed and click OK.
You can choose to analyze multiple tables, by adding this data range to Data Model. You can learn how to analyze multiple tables, use of Data Model and how to use an external data source to create a PivotTable in the tutorial Excel PowerPivot.
A new worksheet is inserted into your workbook. The new worksheet contains an empty PivotTable. Name the worksheet – Range-PivotTable.
As you can observe, the PivotTable Fields list appears on the right side of the worksheet, containing the header names of the columns in the data range. Further, on the Ribbon, PivotTable Tools – ANALYZE and DESIGN appear.
You will understand in detail about PivotTable Fields and Areas in the later chapters in this tutorial. For now, observe the steps to add fields to the PivotTable.
Suppose you want to summarize the order amount salesperson-wise for the months January, February, and March. You can do it in few simple steps as follows −
Click on the field Salesperson in the PivotTable Fields list and drag it to the ROWS area.
Click the field Month in the PivotTable Fields list and drag that also to ROWS area.
Click on Order Amount and drag it to ∑ VALUES area.
Your first PivotTable is ready as shown below
Observe that two columns appear in the PivotTable, one containing the Row Labels that you selected, i.e. Salesperson and Month and a second one containing Sum of Order Amount. In addition to Sum of Order Amount month wise for each Salesperson, you will also get subtotals representing the total sales by that person. If you scroll down the worksheet, you will find the last row as Grand Total representing total sales.
You will learn more about producing PivotTables as per the need as you progress through this tutorial.
Consider the following Excel table that contains the same sales data as in the previous section −
An Excel table will inherently have a name and the columns will have headers, which is a requirement to create a PivotTable. Suppose the table name is SalesData_Table.
To create a PivotTable from this Excel table, do the following −
Click on the table – SalesData_Table.
Click the INSERT tab on the Ribbon.
Click PivotTable in the Tables group. The Create PivotTable dialog box appears.
Click Select a table or range.
In the Table/Range box, type the table name – SalesData_Table.
Select New Worksheet under Choose where you want the PivotTable report to be placed. Click OK.
A new worksheet is inserted into your workbook. The new worksheet contains an empty PivotTable. Name the worksheet – Table-PivotTable. The worksheet – Table-PivotTable looks similar to the one you have got in the data range case in the earlier section.
You can add fields to the PivotTable as you have seen in the section – Adding Fields to the PivotTable, earlier in this chapter.
In case you are not familiar with Excel PivotTables or if you do not know which fields would result in a meaningful report, you can use the Recommended PivotTables command in Excel. Recommended PivotTables gives you all the possible reports with your data along with the associated layout. In other words, the options displayed will be the PivotTables that are customized to your data.
To create a PivotTable from the Excel table SalesData-Table using Recommended PivotTables, proceed as follows −
Click on the table SalesData-Table.
Click the INSERT tab.
Click Recommended PivotTables in the Tables group. The Recommended PivotTables Dialog Box appears.
In the Recommended PivotTables dialog box, the possible customized PivotTables that suit your data will be displayed.
Click on each of the PivotTable options to see the preview on the right side.
Click on the PivotTable - Sum of Order Amount by Salesperson and Month and click OK.
You will be get the preview on the right side.
The selected PivotTable appears on a new worksheet in your workbook.
You can see that the PivotTable Fields - Salesperson, Region, Order Amount and Month got selected. Of these, Region and Salesperson are in ROWS area, Month is in COLUMNS area, and Sum of Order Amount is in ∑ VALUES area.
The PivotTable summarized the data Region-wise, Salesperson-wise and Month-wise. The subtotals are displayed for each Region, each Salesperson, and each Month.
PivotTable Fields is a Task Pane associated with a PivotTable. The PivotTable Fields Task Pane comprises of Fields and Areas. By default, the Task Pane appears at the right side of the window with Fields displayed above Areas.
Fields represent the columns in your data – range or Excel table, and will have check boxes. The selected fields are displayed in the report. Areas represent the layout of the report and the calculations included in the report.
At the bottom of the Task Pane, you will find an option – Defer Layout Update with an UPDATE button next to it.
By default, this is not selected and whatever changes you make in the selection of fields or in the layout options are reflected in the PivotTable instantly.
If you select this, the changes in your selections are not updated until you click on the UPDATE button.
In this chapter, you will understand the details about Fields. In the next chapter, you will understand the details about Areas.
You can find the PivotTable Fields Task Pane on the worksheet where you have a PivotTable. To view the PivotTable Fields Task Pane, click the PivotTable. In case the PivotTable Fields Task Pane is not displayed, check the Ribbon for the following −
The PivotTable Fields Task Pane will be displayed on the right side of the window, with the title – PivotTable Fields.
On the right of the title PivotTable Fields of the PivotTable Task Pane, you
will find the button
.
This represents Task Pane Options. Click the button
.
The Task Pane Options- Move, Size and Close appear in the dropdown list.
You can move the PivotTables Task Pane to anywhere you want in the window as follows −
Click Move in the dropdown list. The
button appears on the Task Pane.
Click the
icon and drag the pane to a position where you want to place it. You can
place the Task Pane next to the PivotTable as given below.
You can place the Task Pane on the left side of the window as given below.
You can resize the PivotTables Task Pane – i.e. increase / decrease the Task Pane length and/or width as follows −
Click on Task Pane Options −
that is on the right side of the title - PivotTable Fields.
Click on Size in the dropdown list.
Use the symbol ⇔ to increase / decrease the width of the Task Pane.
Use the symbol ⇕ to increase / decrease the width of the Task Pane.
In the ∑ VALUES area, to make Sum of Order Amount visible completely, you can resize the Task Pane as given below.
The PivotTable Fields list comprises of all the tables that are associated with your workbook and the corresponding fields. It is by selecting the fields in the PivotTable fields list, you will create the PivotTable.
The tables and the corresponding fields with check boxes, reflect your PivotTable data. As you can check / uncheck the fields randomly, you can quickly change the PivotTable, highlighting the summarized data that you want to report or present.
As you can observe, if there is only one table, the table name will not be displayed in the PivotTable Fields list. Only the fields will be displayed with check boxes.
Above the fields list, you will find the action Choose fields to add to
report. To the right, you will find the button −
that represents Tools.
In the dropdown list, you will find the following −
Five different layout options for Fields and Areas.
Two options for Sort order of the fields in the Fields list −
Sort A to Z.
Sort in Data Source Order.
As you can observe in the above Fields list, the Sort order is by default – i.e. in Data Source Order. This means, it is the order in which the columns in your data table appear.
Normally, you can retain the default order. However, at times, you might encounter many fields in a table and might not be acquainted with them. In such a case, you can sort the fields in alphabetical order by clicking on – Sort A to Z in the dropdown list of Tools. Then, the PivotTable Fields list looks as follows −
PivotTable areas are a part of PivotTable Fields Task Pane. By arranging the selected fields in the areas, you can arrive at different PivotTable layouts. As you can simply drag the fields across areas, you can quickly switch across the different layouts, summarizing the data, in a way you want.
You have already learnt about PivotTable Fields Task Pane in the earlier chapter on PivotTable Fields in this tutorial. In this chapter, you will learn about the PivotTable areas.
There are four PivotTable areas available −
The message - Drag fields between areas below appears above the areas.
With PivotTable Areas, you can choose −
You can just drag the fields across these areas and observe how the PivotTable Layout changes.
If you select the fields in the PivotTable Fields lists by just checking the boxes, all the nonnumeric fields will automatically be added to the ROWS area, in the order you select.
You can optionally, drag a field to the ROWS area. The fields that are put in ROWS area appear as rows in the PivotTable, with the Row Labels being the values of the selected fields.
For example, consider the Sales data table.
Your PivotTable appears with one column containing the Row Labels – Salesperson and Month and a last row as Grand Total, as given below.
You can drag fields to the COLUMNS area.
The fields that are put in COLUMNS area appear as columns in the PivotTable, with the Column Labels being the values of the selected fields.
Drag the field Region to COLUMNS area. Your PivotTable appears with the first column containing the Row Labels – Salesperson and Month the next four columns containing the Column Labels – Region and a last column Grand Total as given below.
Drag the field Month from ROWS to COLUMNS.
Drag the field Region from COLUMNS to ROWS. Your PivotTable layout changes as given below.
You can see that there are only five columns now – the first column with Row Labels, three columns with Column Labels and a last column with Grand Total.
The number of Rows and Columns is based on the number of values you have in those fields.
The primary use of a PivotTable is to summarize values. Hence, by placing the fields by which you want to summarize the data in ∑ VALUES area, you arrive at the summary table.
Drag the field Order Amount to ∑ VALUES.
Drag the field Region to above the field Salesperson in ROWS area. This step is to change the nesting order. You will learn nesting in the chapter – Nesting in the PivotTable in this tutorial.
As you can observe, the data is summarized region-wise, salesperson-wise and monthwise. You have subtotals for each region, month wise. You also have grand totals month wise in the Grand Total row grand totals region wise in the Grand Total column.
The Filters area is to place filters in PivotTable. Suppose you want to display results separately for the selected regions only.
Drag the field Region from ROWS area to FILTERS area. The filter Region will be placed above the PivotTable. In case you do not have empty rows above the PivotTable, the PivotTable is pushed down inserting rows above the PivotTable for the filter.
As you can observe, (ALL) appears in the filter by default, and the PivotTable displays data for all the values of the Region.
Check boxes will appear for all the options in the dropdown list. By default, all the boxes are checked.
The PivotTable gets changed to reflect the filtered data.
You can observe that the filter displays (Multiple Items). Therefore, when someone is looking at the PivotTable, it is not immediately obvious of what values are filtered.
Excel provides you another tool called Slicers to handle filtering more efficiently. You will understand Filtering Data in a PivotTable in detail in a later chapter in this tutorial.
Excel PivotTable allows you to explore and extract significant data from an Excel table or a range of data. There are several ways of doing this and you can choose the ones that are best suited to your data. Further, while you are exploring the data, you can view the different combinations instantly as you change your choices to pick the data values.
You can do the following with a PivotTable −
You can sort the data in a PivotTable in ascending or descending order of the field values. You can also sort by subtotals from largest to smallest or smallest to largest values. You can also set sort options. You will learn these in detail in the chapter – Sorting Data in a PivotTable in this tutorial.
You can filter the data in a PivotTable to focus on some specific data. You have several filtering options in PivotTable that you will learn in the chapter – Filtering Data in a PivotTable in this tutorial. You can use Slicers for filtering, which you will learn in the chapter – Filtering using Slicers in this tutorial.
You can nest fields in a PivotTable to show a hierarchy, if relevant to your data. You will learn this in the chapter - Nesting in a PivotTable in this tutorial.
When you have nested fields in your PivotTable, you can expand and collapse the values of those fields. You will learn these in the Chapter – Exploring Data with PivotTable Tools in this tutorial.
You can group and ungroup specific values of a field in a PivotTable. You will learn this in the Chapter – Exploring Data with PivotTable Tools in this tutorial
You can sort the data in a PivotTable so that it will be easy for you to find the items you want to analyze. You can sort the data from lowest to highest values or highest to lowest values or in any other custom order that you choose.
Consider the following PivotTable wherein you have the summarized sales data region-wise, salesperson-wise and month-wise.
You can sort the data in the above PivotTable on Fields that are in Rows or Columns – Region, Salesperson and Month.
To sort the PivotTable with the field Salesperson, proceed as follows −
Click the arrow
in the Row
Labels.
Select Salesperson in the Select Field box from the dropdown list.
The following sorting options are displayed −
Further, the Salesperson field is sorted in ascending order, by default. Click Sort Z to A. The Salesperson field will be sorted in descending order.
In the same way, you can sort
the field in column – Month, by clicking on the arrow
in the column
labels.
Suppose you want to sort the PivotTable based on total order amount – highest to lowest in every Region. That is, you want to sort the PivotTable on subtotals.
You can see
that there is no arrow
for subtotals. You
can still sort the PivotTable on subtotals as follows −
Right-click on the subtotal of any of the Salespersons in the Grand Total column.
Select Sort from the dropdown list.
Another dropdown list appears with the sorting options – Sort Smallest to Largest, Sort Largest to Smallest and More Sort Options. Select Sort Largest to Smallest.
The subtotals in the Grand Total column are sorted from highest to lowest values, in every region.
Likewise, if you want to sort the PivotTable on subtotals region wise, do the following −
Right click on the subtotal of any of the regions in the Grand Total column.
Click Sort in the dropdown list.
Click Sort Largest to Smallest in the second dropdown list. The PivotTable will get sorted on subtotals region-wise.
As you can observe, South has the highest order amount while North has the lowest.
You can also sort the PivotTable based on the total amount month wise as follows −
The PivotTable will be sorted on total amount month wise.
You can observe that February has highest order amount while March has the lowest.
Suppose you want to sort the PivotTable on total amount region wise in the month of January.
Click on the arrow
in Row Labels.
Select More Sort Options from the dropdown list. The Sort (Region) dialog box appears.
As you can observe, under Summary, the current Sort order is given as Sort Region in ascending order. Ascending (A to Z) by is selected under Sort Options. In the box below that, Region is displayed.
Click the More Options button. The More Sort Options (Region) dialog box appears.
As you can observe, under Sort By, Grand Total is selected. Under Summary, the current sort order is given as Sort Region by Sum of Order Amount in ascending order.
Click Values in selected column: under Sort By.
In the box below that, type B5.
As you can observe, under Summary, the current sort order is given as follows −
Sort Region by Sum of Order Amount in ascending order using values in this column: January. Click OK.
The Sort (Region) dialog box appears. Select Descending (Z to A) by: under Sort Options.
Under Summary, the current sort order is given as follows −
Sort Region by Sum of Order Amount in descending order, using values in this column: January. Click OK. The PivotTable will be sorted on region, using values in January.
As you can observe, in the month of January, West has the highest order amount while North has the lowest.
In the PivotTable, the data is sorted automatically by the sorting option that you have chosen. This is termed as AutoSort.
Place the cursor on the arrow
in Row Labels or
Column Labels.
AutoSort appears, showing the current sort order for each of the fields in the PivotTable. Now, suppose you want to sort the field Region in the order – East, West, North and South. You can do this manually, as follows −
Click the arrow
in Row Labels.
Select Region in the Select Field box from the dropdown list.
Click More Sort Options. The Sort (Region) dialog box appears.
Select Manual (you can drag items to rearrange them).
Click OK.
Under Summary, the current sort order is given as Drag items of the Region field to display them in any order.
Click on the East and drag it to the top. While you are dragging East, a horizontal green bar appears across the entire row moves.
Repeat the dragging with other items of the Region field until you get the required arrangement.
You can observe the following −
The items of the nested field – Salesperson also move along with the corresponding Region field item. Further, the values in the other columns also moved accordingly.
If you place the cursor on the arrow
in Row Labels
or Column Labels, AutoSort appears showing the current sort order of the
fields Salesperson and Month only. As you have sorted Region field manually,
it will not show up in AutoSort.
Note − You cannot use this manual dragging of items of the field that is in ∑ VALUES area of the PivotTable Fields list. Therefore, you cannot drag the Sum of Order Amount values in this PivotTable.
In the previous section, you have learnt how to set the sorting option for a field to manual. You have some more sort options that you can set as follows −
Click the arrow
in Row Labels.
Select Region in the Select Field box.
Click More Sort Options. The Sort (Region) dialog box appears.
Click the More Options button.
More Sort Options (Region) dialog box appears. You can set more sort options in this dialog box.
Under AutoSort, you can check or uncheck the box - Sort automatically every time the report is updated, to allow or stop automatic sorting whenever the PivotTable data is updated.
Now, First key sort order option becomes available. You can use this option to select the custom order you want to use.
As you can observe, day-of-the-week and month-of-the year custom lists are provided in the dropdown list. You can use any of these, or you can use your own custom list such as High, Medium, Low or the sizes list S, M, L, XL that are not in alphabetical order.
You can create your custom lists from the FILE tab on the Ribbon. FILE → Options. In the Excel Options dialog box, click on advanced and browse to General. You will find the Edit Custom Lists button next to Create lists for use in sort and fill sequences.
Note that a custom list sort order is not retained when you update (refresh) data in your PivotTable.
Under Sort By, you can click Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.
When you sort data in a PivotTable, remember the following −
Data that has leading spaces will affect the sort results. Remove any leading spaces before you sort the data.
You cannot sort case-sensitive text entries.
You cannot sort data by a specific format such as cell or font color.
You cannot sort data by conditional formatting indicators, such as icon sets
You might have to do in-depth analysis on a subset of your PivotTable data. This might be because you have large data and your focus is required on a smaller portion of the data or irrespective of the size of the data, your focus is required on certain specific data. You can filter the data in the PivotTable based on a subset of the values of one or more fields. There are several ways to do that as follows −
You will learn filtering data using Slicers in the next chapter. You will understand filtering by the other methods mentioned above in this chapter.
Consider the following PivotTable wherein you have the summarized sales data region wise, salesperson wise and month wise.
You can assign a Filter to one of the fields so that you can dynamically change the PivotTable based on the values of that field.
Drag Region from Rows to Filters in the PivotTable Areas.
The Filter with the label as Region appears above the PivotTable (in case you do not have empty rows above your PivotTable, PivotTable gets pushed down to make space for the Filter.
You will observe that
Salesperson values appear in rows.
Month values appear in columns.
Region Filter appears on the top with default selected as ALL.
Summarizing value is Sum of Order Amount.
Sum of Order Amount Salesperson-wise appears in the column Grand Total.
Sum of Order Amount Month-wise appears in the row Grand Total.
Click on the arrow in the box to the right of the Filter Region.
A drop-down list with the values of the field Region appears. Check the box Select Multiple Items.
By default, all the boxes are checked. Uncheck the box (All). All the boxes will be unchecked.
Then check the boxes - South and West and click OK.
The data pertaining to South and West regions only will get summarized.
In the cell next to the Filter Region - (Multiple Items) is displayed, indicating that you have selected more than one item. However, how many items and / or which items is not known from the report that is displayed. In such a case, using Slicers is a better option for filtering.
You can also filter the PivotTable by picking the values of a field manually.
You can do this by clicking on the arrow
in the Row Labels
or Column Labels cell.
Suppose you want to analyze only February data. You need to filter the values by the field Month. As you can observe, Month is part of Column Labels.
Click on the arrow
in the Column
Labels cell.
As you can observe, there is a Search box in the dropdown list and below the box, you have the list of the values of the selected field, i.e. Month. The boxes of all the values are checked, showing that all the values of that field are selected.
Uncheck the (Select All) box at the top of the list of values.
Check the boxes of the values you want to show in your PivotTable, in this case February and click OK.
The PivotTable displays only those
values that are related to the selected Month field value – February. You can
observe that the filtering arrow changes to the icon
to indicate
that a filter is applied. Place the cursor on the
icon.
You can observe that is displayed indicating that the Manual Filter is applied on the field- Month.
If you want to change the filter selection value, do the following −
Click the
icon.
Check / uncheck the boxes of the values.
If all the values of the field are not visible in the list, drag the handle in the bottom-right corner of the dropdown to enlarge it. Alternatively, if you know the value, type it in the Search box.
Suppose you want to apply another filter on the above filtered PivotTable. For example, you want to display the data of that of Walters, Chris for the month February. You need to refine your filtering by adding another filter for the field Salesperson. As you can observe, Salesperson is part of Row Labels.
Click on the arrow
in the Row
Labels cell.
The list of the values of the field – Region is displayed. This is because, Region is at outer level of Salesperson in the nesting order. You also have an additional option – Select Field. Click on the Select Field box.
Click Salesperson from the dropdown list. The list of the values of the field – Salesperson will be displayed.
Uncheck (Select All) and check Walters, Chris.
Click OK.
The PivotTable displays only those values that are related to the selected Month field value – February and Salesperson field value - Walters, Chris.
The filtering arrow for Row Labels also changes to the icon
to indicate
that a filter is applied. Place the cursor on the
icon on
either Row Labels or Column Labels.
A text box is displayed indicating that the Manual Filter is applied on the fields – Month, and Salesperson.
You can thus filter the PivotTable manually based on any number of fields and on any number of values.
If you have fields that contain text, you can filter the PivotTable by Text, provided the corresponding field label is text-based. For example, consider the following Employee data.
The data has the details of the employees – EmployeeID, Title, BirthDate, MaritalStatus, Gender and HireDate. Additionally, the data also has the manager level of the employee (levels 0 – 4).
Suppose you have to do some analysis on the number of employees reporting to a given employee by title. You can create a PivotTable as given below.
You might want to know how many employees with ‘Manager’ in their title have employees reporting to them. As the Label Title is text-based, you can apply the Label Filter on the Title field as follows −
Click on the arrow
in the Row
Labels cell.
Select Title in the Select Field box from the drop down list.
Click on Label Filters.
Click on Contains in the second dropdown list.
Label Filter (Title) dialog box appears. Type Manager in the box next to Contains. Click OK.
The PivotTable will be filtered to the Title values containing ‘Manager’.
Click the
icon.
You can see that
is displayed
indicating the following −
You might want to know the titles of the employees who have more than 25 employees reporting to them. For this, you can apply the Value Filter on the Title field as follows −
Click on the arrow
in the Row
Labels cell.
Select Title in the Select Field box from the drop down list.
Click on Value Filters.
Select Greater than or equal to from the second dropdown list.
The Value Filter (Title) dialog box appears. Type 25 in the right side box.
The PivotTable will be filtered to display the employee titles who have more than 25 employees reporting to them.
You might want to display the data of all the employees who were hired in the fiscal year 2015-15. You can use Data Filters for the same as follows −
Include the HireDate field in the PivotTable. Now, you do not require manager data and so remove ManagerLevel field from the PivotTable.
Now that you have a Date field in the PivotTable, you can use Date Filters.
Click the arrow
in the Row
Labels cell.
Select HireDate in the Select Field box from the drop down list.
Click Date Filters.
Seelct Between from the second dropdown list.
The Date Filter (HireDate) dialog box appears. Type 4/1/2014 and 3/31/2015 in the two Date boxes. Click OK.
The PivotTable will be filtered to display only the data with HireDate between 1st April 2014 and 31st March 2015.
You can group the dates into Quarters as follows −
Right click on any of the dates. The Grouping dialog box appears.
Type 4/1/2014 in the box Starting at. Check the box.
Type 3/31/2015 in the box Ending at. Check the box.
Click Quarters in the box under By.
The dates will be grouped into quarters in the PivotTable. You can make the table look compact by dragging the field HireDate from ROWS area to COLUMNS area.
You will be able to know how many employees were hired during the fiscal year, quarter wise.
You can use the Top 10 Filter to display the top few or bottom few values of a field in the PivotTable.
Click the arrow
in the Row
Labels cell.
Click Value Filters.
Click Top 10 in the second dropdown list.
Top 10 Filter (Title) dialog box appears.
In the first box, click on Top (You can choose Bottom also).
In the second box, enter a number, say, 7.
In the third box, you have three options by which you can filter.
Click on Items to filter by number of items.
Click on Percent to filter by percentage.
Click on Sum to filter by sum.
As you have count of EmployeeID, click Items.
In the fourth box, click on the field Count of EmployeeID.
Click OK.
The top seven values by count of EmployeeID will be displayed in the PivotTable.
As you can observe, the highest number of hires in the fiscal year is that of Production Technicians and a predominant number of these are in Qtr1.
If your PivotTable has a date field, you can filter the PivotTable using Timeline.
Create a PivotTable from the Employee Data that you used earlier and add the data to the Data Model in the Create PivotTable dialog box.
Drag the field Title to ROWS area.
Drag the field EmployeeID to ∑ VALUES area and choose Count for calculation.
Click on the PivotTable.
Click the INSERT tab.
Click Timeline in the Filters group. The Insert Timelines dialog box appears.
As you can observe, All Periods – in Months are displayed on the Timeline.
Click on the arrow next to - MONTHS.
Select QUARTERS from the drop-down list. The The Timeline display changes to All Periods – in Quarters.
Click on 2014 Q1.
Keep the Shift key pressed and drag to 2014 Q4. The Timeline Period is selected to Q1 – Q4 2014.
PivotTable is filtered to this Timeline Period.
You might have to clear the filters you have set from time to time to switch across different combinations and projections of your data. You can do this in several ways as follows −
You can clear all the filters set in a PivotTable at one go as follows −
To clear a Label, Date, or Value Filter do the following −
Click on the icon in the Row Labels or Column Labels.
Click on the
<field
name> from which you want to clear the filter in the Select Field box in the
dropdown list.
Click on Clear Filter From <Filed Name> that appears in the dropdown list.
Click OK. The specific filter will be cleared.
