Excel Pivot Tables Tutorial by Aha Tutorial

Excel Pivot Tables Tutorial PivotTable is an extremely powerful tool that you can use to slice and dice data. In this tutorial, you will learn 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. This guide targets professional who have to track and analyze hundreds of thousands of data points with a compact table. This guide targets people who want to use tables or charts in presentations and help people understand data quickly. Whether you want to make a comparison, show a relationship, or highlight a trend, these tables help your audience “see” what you are talking about. Before you proceed with this tutorial, we are assuming that you are already aware of the basics of Microsoft Excel

basics. If you are not well aware of these concepts, then we will suggest you to go through our short tutorials on Excel.

SEE MORE FREE EBOOKS: CLICK HERE

Donate link: https://paypal.me/sutranxt

Thank you!

TABLE OF CONTENTS

1. Excel Pivot Tables - Overview 2. Excel Pivot Tables - Creation 3. Excel Pivot Tables - Fields 4. Excel Pivot Tables - Areas 5. Excel Pivot Tables - Exploring Data 6. Excel Pivot Tables - Sorting Data 7. Excel Pivot Tables - Filtering Data 8. Filtering data using Slicers 9. Excel Pivot Tables - Nesting 10. Excel Pivot Tables - Tools 11. Excel Pivot Tables - Summarizing Values 12. Excel Pivot Tables - Updating Data 13. Excel Pivot Tables - Reports

Do you want to learn the really advanced content that we couldn’t include in this eBook? 1. Excel-based Production Scheduling System CLICK HERE 2. Dose For Excel Add-in CLICK HERE 3. Aplica Excel Contable (view mobile) CLICK HERE

Excel Pivot Tables Overview 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.

CREATING A PIVOTTABLE

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.

PIVOTTABLE LAYOUT - FIELDS AND AREAS 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.

EXPLORING DATA WITH PIVOTTABLE 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.

SUMMARIZING VALUES 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.

UPDATING A PIVOTTABLE 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.

PIVOTTABLE REPORTS 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.

Excel Pivot Tables Creation 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.

CREATING A PIVOTTABLE FROM A DATA RANGE 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.

ADDING FIELDS TO THE PIVOTTABLE 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.

CREATING A PIVOTTABLE FROM A TABLE 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 – TablePivotTable 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.

CREATING A PIVOTTABLE WITH RECOMMENDED PIVOTTABLES

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.

Excel Pivot Tables - Fields 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.

PIVOTTABLE FIELDS TASK PANE 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 −

• • •

Click the ANALYZE tab under PIVOTTABLE TOOLS on the Ribbon. Check if Fields List is selected (i.e. highlighted) in the Show group. If Fields List is not selected, then click it.

The PivotTable Fields Task Pane will be displayed on the right side of the window, with the title – PivotTable Fields.

MOVING PIVOTTABLE FIELDS TASK PANE 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 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.

button

You can place the Task Pane on the left side of the window as given below.

RESIZING PIVOTTABLE FIELDS TASK PANE 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.

PIVOTTABLE FIELDS 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. •

Click on the Tools button.

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 −

o o

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 −

Excel Pivot Tables - Areas 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 − • • • •

ROWS. COLUMNS. FILTERS. ∑ VALUES (Read as Summarizing Values).

The message - Drag fields between areas below appears above the areas. With PivotTable Areas, you can choose − • • • •

What fields to display as rows (ROWS area). What fields to display as columns (COLUMNS area). How to summarize your data (∑ VALUES area). Filters for any of the fields (FILTERS area).

You can just drag the fields across these areas and observe how the PivotTable Layout changes.

ROWS 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. • •

Drag the field Salesperson to ROWS area. Drag the field Month to ROWS area.

Your PivotTable appears with one column containing the Row Labels – Salesperson and Month and a last row as Grand Total, as given below.

COLUMNS 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.

∑ VALUES 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.

FILTERS 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. • •

Click on the arrow to the right of filter. Check the box – Select Multiple Items.

Check boxes will appear for all the options in the dropdown list. By default, all the boxes are checked. • •

Check the boxes – North and South. Clear the other boxes. Click OK.

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 Pivot Tables Exploring Data

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 − • • • • •

Sort the data. Filter the data. Nest the PivotTable fields. Expand and Collapse the fields. Group and ungroup field values.

SORTING AND FILTERING DATA 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.

NESTING, EXPANDING AND COLLAPSING FIELDS

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.

GROUPING AND UNGROUPING FIELD VALUES 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.

Excel Pivot Tables - Sorting Data 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.

SORTING ON FIELDS 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 − • • •

Sort A to Z. Sort Z to A. More Sort Options.

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.

SORTING ON SUBTOTALS 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 − • •

Right click on any of the Subtotals in the Grand Total row. Select Sort from the dropdown list.

•

Select Sort Largest to Smallest from the second dropdown list.

The PivotTable will be sorted on total amount month wise.

You can observe that February has highest order amount while March has the lowest.

MORE SORT OPTIONS 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 box containing Region. Click Sum of Order Amount.

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.

SORTING DATA MANUALLY 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 Labels.

in Row Labels or Column

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.

SETTING SORT OPTIONS 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. •

Uncheck the box – Sort automatically every time the report is updated.

Now, First key sort order option becomes available. You can use this option to select the custom order you want to use. •

Click the box under First key sort order.

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.

POINTS TO CONSIDER WHILE SORTING PIVOTTABLES 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.

Excel Pivot Tables Filtering Data 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 − • • • • • • • •

Filtering using Slicers. Filtering using Report Filters. Filtering data manually. Filtering using Label Filters. Filtering using Value Filters. Filtering using Date Filters. Filtering using Top 10 Filter. Filtering using Timeline.

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.

REPORT FILTERS 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. o Sum of Order Amount Salesperson-wise appears in the column Grand Total. o 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.

MANUAL 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 that a filter is applied. Place the cursor on the

to indicate 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 on the

to indicate that a filter is applied. Place the cursor 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.

FILTERING BY TEXT 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 displayed indicating the following − • •

The Label Filter is applied on the field – Title, and What the applied Label Filter is.

FILTERING BY VALUES 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.

FILTERING BY DATES 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.

FILTERING USING TOP 10 FILTER 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. o Click on Items to filter by number of items. o Click on Percent to filter by percentage. o 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.

FILTERING USING TIMELINE 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.

• • •

Check the box HireDate. Click OK. The Timeline appears in the worksheet. Timeline Tools appear on the Ribbon.

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.

•

CLEARING THE FILTERS 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 −

CLEARING ALL THE FILTERS IN A PIVOTTABLE You can clear all the filters set in a PivotTable at one go as follows − • • •

Click the HOME tab on the Ribbon. Click Sort & Filter in the Editing group. Select Clear from the dropdown list.

CLEARING A LABEL, DATE OR VALUE FILTER 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.

Filtering data using Slicers

Using one or more slicers is a quick and effective way to filter your data. Slicers can be inserted for each of the fields that you want to filter. Slicer will have buttons denoting the values of the field that it represents. You can click on the buttons of a slicer to select/ unselect the values in the field. Slicers stay visible with the PivotTable and so you will always know what fields are used for filtering and what values in those fields are shown or hidden in the filtered PivotTable. To understand the usage of slicers, consider the example of sales data region-wise, month wise and salesperson-wise. Assume you have the following PivotTable with this data.

INSERTING SLICERS Suppose you want to filter this PivotTable based on the fields – Region and Month.

• •

Click on ANALYZE under PIVOTTABLE TOOLS on the Ribbon. Click on Insert Slicer in the Filter group. The Insert Slicers dialog box appears. It contains all the fields from your data table. Check the boxes Region and Month. Click OK.

Slicers for each of the selected fields appear with all the values selected by default. Slicer Tools appear on the Ribbon to work on the Slicer settings, look and feel.

FILTERING WITH SLICERS As you can observe, each slicer has all the values of the field that it represents and the values are displayed as buttons. By default, all the values of a field are selected and hence all the buttons are highlighted. Suppose you want to display the PivotTable only for the regions South and West and for the Months February and March. • • • •

Click on South in the Slicer for Region. Only South will be highlighted in the Slicer – Region. Keep Ctrl key pressed and click on West in the Slicer for Region. Click on February in the Slicer for Month. Keep Ctrl key pressed and click on March in the Slicer for Month.

Selected items in the Slicers are highlighted. PivotTable with summarized values for the selected items will be displayed.

To add/remove values of a field from the filter, keep the Ctrl key pressed and click on those buttons in the slicer of the field.

CLEARING THE FILTER IN A SLICER To clear the filter in a slicer, click on corner of the slicer.

at the top-right

REMOVING A SLICER Suppose you want to remove the slicer for the Region field. • •

Right click on the Slicer – Region. Click on Remove “Region” in the dropdown list.

SLICER TOOLS Once you insert a slicer, Slicer Tools appear on the Ribbon with OPTIONS tab. To view Slicer Tools, click on a slicer.

As you can observe, under the Slicer Tools – OPTION tab, you have several options to change the look and feel of the slicer that include − • • • •

Slicer Caption Slicer Settings Report Connections Selection Pane

SLICER CAPTION You can find the Slicer Caption box in the Slicer group. The Slicer Caption is the header that is displayed on the slicer. By default, Slicer Caption is the name of the field that it represents. • •

Click on the Slicer for Region. Click the OPTIONS tab on the Ribbon.

The Slicer group on the Ribbon, in the Slicer Caption box, Region is displayed as the header of the slicer. It is the name of the field for which the slicer is inserted. You can change the Slicer Caption as follows − • • •

Click on the Slicer Caption box in the Slicer group on the Ribbon. Delete Region. The box is cleared. Type Location in the box and press Enter. The Slicer Caption changes to Location and the same is reflected as header in the slicer.

Note − You have changed only the slicer caption, i.e. the header. The name of the field that the slicer represents – Region remains as it is.

SLICER SETTINGS You can use Slicer Settings to change the name of the slicer, change the slicer caption, choose whether to display the slicer header or not and set the sorting and filtering options for the items − • •

•

Click on the slicer - Location. Click the OPTIONS tab on the Ribbon. You can find the Slicer Settings in the Slicer group on the Ribbon. You can also find Slicer Settings in the dropdown list when you right click on the slicer. Click the Slicer Settings. The Slicer Settings dialog box appears.

As you can observe, the following are fixed for the slicer − • •

Source Name. Name to use in formulas.

You can change the following for the slicer − • • • •

Name. Header – Caption. Display header. Sorting and Filtering options for the items displayed on the slicer.

REPORT CONNECTIONS You can connect different PivotTables to a Slicer, provided one of the following holds good −

• • •

The PivotTables are created using the same data. One PivotTable has been copied and pasted as an additional PivotTable. Multiple PivotTables are created on separate sheets with Show Report Filter Pages.

Consider the following PivotTables that are created from the same data −

• • •

Name the top PivotTable as PivotTable-Top and the bottom one as PivotTable-Bottom. Click on the top PivotTable. Insert a Slicer for the field Region.

•

Select East and North on the Slicer.

Observe that the filtering is applied only to the top PivotTable and not to the bottom PivotTable. You can use the same slicer for both the PivotTables by connecting it to the bottom PivotTable also as follows − • •

Click on the slicer - Region. The SLICER TOOLS appear on the Ribbon. Click the OPTIONS tab on the Ribbon.

You will find Report Connections in the Slicer group on the Ribbon. You can also find Report Connections in the dropdown list when you right click on the slicer. Click Report Connections in the Slicer group.

The Report Connections dialog box appears. The box PivotTable-Top is checked and other boxes are unchecked. Check the box PivotTable-Bottom also and click OK.

The bottom PivotTable will be filtered to the selected items – East and North.

This became possible because both the PivotTables are now connected to the slicer. If you make changes in the selections in the slicer, the same filtering will appear in both the PivotTables.

SELECTION PANE You can switch the display of the slicers on the worksheet off and on using the Selection Pane. • • •

Click on the slicer - Location. Click the OPTIONS tab on the Ribbon. Click the Selection Pane in the Arrange group on the Ribbon. The Selection Pane appears on the right side of the window.

As you can observe, the names of all the slicers are listed in the Selection pane. On the right side of the names, you can find the visibility symbol visible on the worksheet. Click the

indicating the slicer is

symbol for Month. The

symbol changes to the symbol the slicer is hidden (not visible).

, indicating that

As you can observe, the slicer – Month is not shown on the worksheet. However, remember that you did not remove the slicer for Month, but you have just hidden it. •

Click on the

symbol for Month.

•

The symbol changes to the symbol indicating that the slicer is now visible.

When you switch the visibility of a slicer on / off, the selection of the items in that slicer for filtering remain unaltered. You can also change the order of the slicers in the Selection pane by dragging them up/down.

Excel Pivot Tables - Nesting If you have more than one field in any of the PivotTable areas, then the PivotTable layout depends on the order you place the fields in that area. This is called the Nesting Order. If you know how your data is structured, you can place the fields in the required order. If you are not sure about the structure of the data, you can change the order of the fields that instantly changes the layout of the PivotTable.

In this chapter, you will understand the nesting order of the fields and how you can change the nesting order.

NESTING ORDER OF THE FIELDS Consider the sales data example, where you have placed the fields in the following order −

As you can see, in the rows area there are two fields – salesperson and region in that order. This order of the fields is called nesting order i.e. Salesperson first and Region next. In the PivotTable, the values in the rows will be displayed based on this order, as given below.

As you can observe, the values of the second field in the nesting order are embedded under each of the values of the first field. In your data, each salesperson is associated with only one region, whereas most of the regions are associated with more than one salesperson. Hence, there is a possibility that if you reverse the nesting order, your PivotTable will look more meaningful.

CHANGING THE NESTING ORDER To change the nesting order of the fields in an area, just click the field and drag it to the position you want. Click on the field Salesperson in the ROWS area, and drag it to below the field Region. Thus, you have changed the

nesting order to – Region first and Salesperson next, as follows −

The resulting PivotTable will be as given below −

You can clearly observe that the Layout with the nesting order – Region and then Salesperson yields a better and compact report than the one with the nesting order – Salesperson and then Region. In case a Salesperson represents more than one area and you need to summarize the sales by Salesperson, then the previous Layout would have been a better option.

Excel Pivot Tables - Tools In the worksheet containing a PivotTable, the Ribbon will contain the PivotTable Tools, with ANALYZE and DESIGN Tabs. The ANALYZE tab has several commands that will enable you to explore the data in the PivotTable. The DESIGN tab commands will be useful to structure the PivotTable with various report options and style options.

You will learn the ANALYZE commands in this chapter. You will learn the DESIGN commands in the Chapter Aesthetic Reports with PivotTables.

ANALYZE COMMANDS The commands on the Ribbon of ANALYZE tab include the following − • • • •

Expanding and Collapsing a Field. Grouping and Ungrouping Field Values. Active Field Settings. PivotTable Options.

EXPANDING AND COLLAPSING A FIELD If you have nested fields in your PivotTable, you can expand and collapse a single item or you can expand and collapse all the items of the active field. Consider the following PivotTable, wherein you have Salesperson field nested under Region field.

Click the symbol to the left of East. The item East of the field Region will collapse.

As you can observe, the other items - North, South and West of the field Region are not collapsed. If you want to collapse any of them, repeat the steps that you have done for East. •

Click on the symbol to the left of East. The item East of the field Region will expand.

If you want to collapse all the items of a field at once, do the following − • • •

Click any of the items of the field – Region. Click the ANALYZE tab on the Ribbon. Click Collapse Field in the Active Field group.

All the items of the field Region will be collapsed.

If you want to expand all the items of a field at once, do the following − • • •

Click on any of the items of the field – Region. Click the ANALYZE tab on the Ribbon. Click Expand Field in the Active Field group.

All the items of the field Region will be expanded.

GROUPING AND UNGROUPING FIELD VALUES You can group and ungroup field values to define your own clustering. For example, you might want to know the data combining East and North regions. •

• •

Select the East and North items of the Region field in the PivotTable, along with the nested Salesperson field items. Click the ANALYZE tab on the Ribbon. Click Group Selection in the group – Group.

The items – East and North will be grouped under the name Group1. In addition, a new South is created under which South is nested and a new West is created under which West is nested.

You can also observe that a new field – Region2 is added in the PivotTable Fields list, which appears in the ROWS area. •

• •

Select the South and West items of the Region2 field in the PivotTable, along with the nested Region and Salesperson field items. Click the ANALYZE tab on the Ribbon. Click Group Selection in the group – Group.

The items – South and West of the field Region will be grouped under the name Group2.

To ungroup a group, do the following − • • •

Click on the Group Name. Click the ANALYZE tab. Click Ungroup in the group – Group.

GROUPING BY A DATE FIELD Consider the following PivotTable, wherein you have the employee data summarized by Count of EmployeeID, hiredate wise and title wise.

Suppose you want to group this data by the HireDate field that is a Date field into years and quarters. • • •

Click on a Date item in the PivotTable. Click the ANALYZE tab on the Ribbon. Click Group Field in the group – Group.

The Grouping dialog box appears. • •

•

Set the dates for – Starting at and Ending at. Select Quarters and Years in the box under By. To select / deselect multiple items, keep the Ctrl-key pressed. Click OK.

The HireDate field values will be grouped into Quarters, nested in Years.

If you want to ungroup this grouping, you can do as shown earlier, by clicking Ungroup in the group – Group on the Ribbon.

ACTIVE VALUE FIELD SETTINGS You can set a field options by clicking on a value of that field. Consider the example of sales data that we used earlier in this chapter.

Suppose you want to set the options for the Region field. •

•

Click on East. On the Ribbon, in the Active Field group, in the Active Field box, Region will be displayed. Click on Field Settings. The Field Settings dialog box appears.

You can set your preferences for the field – Region.

PIVOTTABLE OPTIONS You can set the PivotTable Options according to your preferences. • • •

Click on the PivotTable. Click the ANALYZE tab. Click Options in the PivotTable group.

The PivotTable Options dialog box appears. You can set your preferences in the dialog box.

Excel Pivot Tables Summarizing Values You can summarize a PivotTable by placing a field in ∑ VALUES area in the PivotTable Fields Task pane. By default, Excel takes the summarization as sum of the values of the field in ∑ VALUES area. However, you have other calculation types, such as, Count, Average, Max, Min, etc.

In this chapter, you will learn how to set a calculation type based on how you want to summarize the data in the PivotTable.

SUM Consider the following PivotTable wherein you have the summarized sales data regionwise, salesperson-wise and month-wise.

As you can observe, when you drag the field Order Amount to ∑ VALUES area, it is displayed as Sum of Order Amount, indicating the calculation is taken as Sum. In the PivotTable, in the top-left corner, Sum of Order Amount is displayed. Further, Grand Total column and Grand Total row are displayed for subtotals field-wise in rows and columns respectively.

VALUE FIELD SETTINGS

With Values Field Settings, you can set the calculation type in your PivotTable. You can also decide on how you want to display your values. • •

Click on Sum of Order Amount in ∑ VALUES area. Select Value Field Settings from the dropdown list.

The Value Field Settings dialog box appears.

The Source Name is the field and Custom Name is Sum of field. Calculation Type is Sum. Click the Show Values As tab.

In the box Show Values As, No Calculation is displayed. Click the Show Values As box. You can find several ways of showing your total values.

% OF GRAND TOTAL You can show the values in the PivotTable as % of Grand Total. • • •

In the Custom Name box, type % of Grand Total. Click on the Show Values As box. Click on % of Grand Total in the dropdown list. Click OK.

The PivotTable summarizes the values as % of the Grand Total.

As you can observe, Sum of Order Amount in the top-left corner of the PivotTable and in the ∑ VALUES area in the PivotTable Fields pane is changed to the new Custom Name - % of Grand Total. • •

Click on the header of the Grand Total column. Type % of Grand Total in the formula bar. Both the Column and Row headers will change to % of Grand Total.

% OF COLUMN TOTAL Suppose you want to summarize the values as % of each month total. • • • • • •

Click on Sum of Order Amount in ∑ VALUES area. Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears. In the Custom Name box, type % of Month Total. Click on the Show values as box. Select % of Column Total from the dropdown list. Click OK.

The PivotTable summarizes the values as % of the Column Total. In the Month columns, you will find the values as % of the specific month total. • •

Click on the header of the Grand Total column. Type % of Column Total in the formula bar. Both the Column and Row headers will change to % of Column Total.

% OF ROW TOTAL You can summarize the values as % of region totals and % of salesperson totals, by selecting % of Row Total in Show Values As box in the Value Field Settings dialog box.

COUNT Suppose you want to summarize the values by the number of Accounts region wise, salesperson wise and month wise. • • • • • •

Deselect Order Amount. Drag Account to ∑ VALUES area. The Sum of Account will be displayed in the ∑ VALUES area. Click on Sum of Account. Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears. In the Summarize value field by box, select Count. The Custom Name changes to Count of Account. Click OK.

The Count of Account will be displayed as shown below −

AVERAGE Suppose you want to summarize the PivotTable by average values of Order Amount region wise, salesperson wise and month wise. • •

• •

Deselect Account. Drag Order Amount to ∑ VALUES area. The Sum of Order Amount will be displayed in the ∑ VALUES area. Click on Sum of Order Amount. Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears.

•

In the Summarize value field by box, click on Average. The Custom Name changes to Average of Order Amount. Click OK.

The average will be displayed as shown below −

You have to set the number format of the values in the PivotTable to make it more presentable. • • •

Click on Average of Order Amount in ∑ VALUES area. Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears. Click on the Number Format button.

The Format Cells dialog box appears. • •

Click on Number under Category. Type 2 in the Decimal places box and click OK.

The PivotTable values will be formatted to numbers with two decimal places.

• •

Click on the header of the Grand Total column. Type Average Order Amount in the formula bar. Both the Column and Row headers will change to Average Order Amount.

MAX Suppose you want to summarize the PivotTable by the maximum values of Order Amount region-wise, salesperson-wise and month-wise. • • •

Click on Sum of Order Amount. Select Value Field Settings from the dropdown list. The Value Field Settings dialog box appears. In the Summarize value field by box, click Max. The Custom Name changes to Max of Order Amount.

The PivotTable will display the maximum values region wise, salesperson wise and month wise. • •

Click on the header the Grand Total column. Type Max Order Amount in the formula bar. Both the Column and Row headers will change to Max Order Amount.

MIN Suppose you want to summarize the PivotTable by the minimum values of Order Amount region wise, salesperson wise and month wise. • • •

Click on Sum of Order Amount. Click on Value Field Settings in the dropdown list. The Value Field Settings dialog box appears. In the Summarize value field by box, click Min. The Custom Name changes to Min of Order Amount.

The PivotTable will display the minimum values region wise, salesperson wise and month wise. • •

Click on the header of the Grand Total column. Type Min Order Amount in the formula bar. Both the Column and Row headers will change to Min Order Amount.

Excel Pivot Tables Updating Data You have learnt how to summarize data with a PivotTable. The data on which the PivotTable is based might be updated either periodically or on occurrence of an event. Further, you also might require to change the PivotTable Layout for different reports. In this chapter, you will learn the different ways of updating the Layout and / or refreshing the data in a PivotTable.

UPDATING PIVOTTABLE LAYOUT You can decide whether your PivotTable is to be updated whenever you make changes to the layout or it is to be updated by a separate trigger.

As you have learnt earlier, in the PivotTable Fields task pane, on the bottom side, you will find a check box for Defer Layout Update. By default, it is unchecked, which means the PivotTable Layout gets updated as soon as you make changes in the PivotTable areas.

Check the option − Defer Layout Update. The UPDATE button next to it will be enabled. If you make any changes to the PivotTable areas, the changes will be reflected only after you click on the UPDATE button.

REFRESHING PIVOTTABLE DATA When the data of a PivotTable is changed in its source, the same can be reflected in the PivotTable by refreshing it. • • •

Click on the PivotTable. Click the ANALYZE tab on the Ribbon. Click Refresh in the Data group.

There are different options to refresh the data in the dropdown list −

• • •

Refresh − To get the latest data from the source connected to the active cell. Refresh All − To get the latest data by refreshing all sources in the workbook. Connection Properties − To set the refresh properties for the workbook connections.

CHANGING THE SOURCE DATA OF A PIVOTTABLE You can change the range of the source data of a PivotTable. For e.g., you can expand the source data to include more number of rows of data. However, if the source data has been changed substantially, such as having more or fewer columns, consider creating a new PivotTable. • • •

Click on the PivotTable. PIVOTTABLE TOOLS appear on the Ribbon. Click the ANALYZE tab. Click Change Data Source in the Data group.

Select Change Data Source from the dropdown list. Change PivotTable Data Source dialog box appears and the current Data Source will be highlighted.

Select the Table or the Range you want to include in the Table/Range Box under Select a Table or Range. Click OK.

The data source for the PivotTable will be changed to the selected Table/Range of data.

CHANGING TO EXTERNAL DATA SOURCE If you want to change the data source for your PivotTable that is an external one, it might be best to create a new PivotTable. However, if the location of your external data source is changed, for example, your SQL Server database name is the same, but it has been moved to a different server, or your Access database has been moved to another network share, you can change your current data connection to reflect the same. • • •

•

Click on the PivotTable. Click the ANALYZE tab on the Ribbon. Click Change Data Source in the Data group. The Change PivotTable Data Source dialog box appears. Click the Choose Connection button.

The Existing Connections dialog box appears. • •

Select All Connections in the Show box. All the Connections in your Workbook will be displayed. Click the Browse for More button.

The Select Data Source window appears. • •

Click on the New Source button. Go through the Data Connection Wizard Steps.

If your data source is in another Excel workbook, do the following − • •

Click on the File name box. Select the workbook file name.

DELETING A PIVOTTABLE You can delete a PivotTable as follows − •

Click on the PivotTable.

• •

Click the ANALYZE tab on the Ribbon. Click Select in the Actions group.

Select Entire PivotTable from the dropdown list. The entire PivotTable will be selected.

Press the Delete Key. The PivotTable will be deleted.

If the PivotTable is on a separate worksheet, you can also delete the PivotTable by deleting the entire worksheet. Right-click on the worksheet tab and select Delete from the dropdown list.

The entire worksheet along with the PivotTable is deleted.

Excel Pivot Tables - Reports

Major use of PivotTable is reporting. Once you have created a PivotTable, explored the data by arranging and rearranging the fields in its rows and columns, you will be ready to present the data to a wide range of audience. With filters, different summarizations, focusing on specific data, you will be able to generate several required reports based on a single PivotTable. As a PivotTable report is interactive, you can quickly make the necessary changes to highlight the specific results, such as data trends, data summarizations, etc. while presenting it. You can also provide visual cues such as report filters, slicers, timeline, PivotCharts, etc. to the recipients so that they can visualize the details they want. In this chapter, you will learn the different ways of making your PivotTable reports appealing with visual cues that enable quick exploration of the data.

HIERARCHIES You have learnt how to nest fields to form a hierarchy, in the Chapter – Nesting in a PivotTable in this tutorial. You have also learnt how to group / ungroup data in a PivotTable in the Chapter – Using PivotTable Tools. We will take few examples to show you how to produce interactive PivotTable reports with hierarchies. If you have an in-built structure for the fields in your data, such as, Year-Quarter-Month, nesting the fields to form a hierarchy will enable you to quickly expand/collapse fields to view the summarized values at the required level.

For example, suppose you have the sales data for the fiscal year 2015-16 for the regions – East, North, South and West, as given below.

Create a PivotTable as shown below.

As you can observe, this is a comprehensive way of reporting the data using the nested fields as a hierarchy. If you want to display the results only at the level of Quarters, you can quickly collapse the Quarter field.

Suppose you have a Date field in your data as shown below.

In such a case, you can group the data by the Date field as follows − Create a PivotTable.

As you can observe, this PivotTable is not convenient to highlight significant data. •

Group the PivotTable by Date field. (You have learnt grouping in the Chapter – Exploring Data with PivotTable Tools in this tutorial).

• •

Place the Salesperson field in Filters area. Filter the Column labels to East Region.

REPORT FILTER Suppose you want a report for each Salesperson separately. You can do it as follows − •

Ensure that you have Salesperson field in Filters area.

• • • •

Click on the PivotTable. Click the ANALYZE tab on the Ribbon. Click the arrow next to Options in the PivotTable group. Select Show Report Filter Pages from the dropdown list.

The Show Report Filter Pages dialog box appears. Select the field Salesperson and click OK.

A separate worksheet for each of the values of the Salesperson field is created, with the PivotTable filtered to that value.

The worksheet will be named by the value of the field, which is visible on the tab of the worksheet.

SLICERS Another sophisticated feature that you have in PivotTables is Slicer that can be used to filter the fields visually. • • • •

• • •

•

Click on the PivotTable. Click the ANALYZE tab. Click Insert Slicer in the Filter group. Click Order Date, Quarters and Years in the Insert Slicers dialog box. Three Slicers –Order Date, Quarters and Year will get created. Adjust the sizes of the slicers, adding more columns for the buttons on the slicers. Create Slicers for Salesperson and Region fields also. Choose the Slicer Styles so that date fields are grouped to one color and the other two fields get different colors. Deselect Gridlines.

As you can see, you have not only an interactive report, but also an appealing one, that can be understood easily.

TIMELINE IN PIVOTTABLE When you have a Date field in your PivotTable, inserting a Timeline also is an option to produce an aesthetic report. • • •

Create a PivotTable with Salesperson in ROWS area and Region in COLUMNS area. Insert a Timeline for the field Order Date. Filter the Timeline to show 5 months data, from November 2015 to March 2016.

DESIGN COMMANDS The PIVOTTABLE TOOLS - DESIGN commands on the Ribbon provide you with the options to format a PivotTable, including the following − • • •

Layout PivotTable Style Options PivotTable Styles

LAYOUT

You can have PivotTable Layout based on your preferences for the following − • • • •

Subtotals Grand Totals Report Layout Blank Rows

PivotTable Layout – Subtotals You have an option whether to display Subtotals or not. By default, Subtotals are displayed, at the top of the group.

As you can observe the highlighted group – East, the subtotals are at the top of the group. You can change the position of subtotals as follows − • • • •

Click on the PivotTable. Click the DESIGN tab on the Ribbon. Click Subtotals in the Layout Options group. Click Show all Subtotals at Bottom of Group.

The Subtotals will now appear at the bottom of each group.

If you do not have to report the Subtotals, you can select Do Not Show Subtotals.

GRAND TOTALS You can choose to either display Grand Totals or not. You have four possible combinations − • • • •

Off for Rows and Columns On for Rows and Columns On for Rows Only On for Columns Only

By default, it is the second combination – On for Rows and Columns.

REPORT LAYOUT

You can choose from the several Report Layouts, the one that best suits your data. • • •

Compact Form. Outline Form. Tabular Form.

You can also choose whether to repeat all the item labels or not, in case of multiple occurrences.

The default Report Layout is the Compact form that you are familiar with. Compact Form

The Compact form optimizes the PivotTable for readability. The other two forms display the field headers also. Click on Show in Outline Form.

Click Show in Tabular Form.

Consider the following PivotTable Layout, wherein the field Month is nested under the field Region −

As you can observe, the Month labels are repeated and this is the default. Click Do Not Repeat Item Labels. The Month labels will be displayed only once and the PivotTable looks clear.

BLANK ROWS To make your PivotTable Report more distinct, you can insert a blank line after each item. You can remove these Blank Lines anytime later.

Click Insert Blank Line after Each Item.

PIVOTTABLE STYLE OPTIONS You have the following PivotTable Style Options − • • • •

Row Headers Column Headers Banded Rows Banded Columns

By default, the boxes for Row Headers and Column Headers are checked. These options are for displaying special formatting for the first row and the first column respectively. Check the box Banded Rows.

Check the box Banded Columns.

PIVOTTABLE STYLES You can choose several PivotTable Styles. Select the one that suits your report. For example, if you choose Pivot Style Dark 5, you will get the following style for the PivotTable.

CONDITIONAL FORMATTING IN PIVOTTABLE You can set Conditional Formatting on the PivotTable cells by the values.

PIVOTCHARTS PivotCharts add a visual emphasis on your PivotTable reports. You can insert a PivotChart tied to the data of a PivotTable as follows − • • •

Click on the PivotTable. Click the ANALYZE tab on the Ribbon. Click PivotChart.

The Insert Chart dialog box appears. Click Column in the left pane and select Stacked Column. Click OK.

The stacked column chart is displayed.

• •

Click on Month on the PivotChart. Filter to February and click OK.

As you can observe, the PivotTable is also filtered as per the PivotChart.

Donate link: https://paypal.me/sutranxt

Thank you!