Download the Exercise Files

Because practicing is an integral part of the learning process, I am providing all the exercise files so that you can follow step by step.

You can download the exercise files from the link:

https://officeinstructor.com/public

Be ready to elevate your Excel Skills and learn lots of Tips and tricks along the way.

The Basics of Pivot Tables

What is a Pivot Table?

A Pivot Table is an interactive way of summarizing huge amounts of data in a quick easy friendly method with no formulas. you can look at the same data from many different perspectives.

Having tens of thousands of rows of data, doesn’t help in getting any insights about the data to make an informed business decision. Also creating summary reports using formulas and functions is tedious, time consuming, requires expertise and is prone to errors. With very little effort you can generate the best reports with few mouse clicks and a simple process of drag and drop. To better understand the story behind the numbers we can visualize the data by creating Pivot Charts then consolidate the charts on a Dashboard so that information can be monitored at a glance.

What are the requirements for creating a Pivot Table?

When we create a Pivot Table from a source data set, Excel uses the column headers in the source data as field names for drag and drop in the Pivot table. Therefore, having column headers is the only mandatory requirement. Other requirements are optional.

Four requirements:

1. Having Column Headers at top (Mandatory)
2. Records arranged in rows (Optional)
3. No blanks in between (Optional)
4. Blank cells all around (Optional)

How to create a Pivot Table?

In our example we have a list of over 300 records and 6 columns. Select any single cell in the list then click on the Insert Tab of the Ribbon and Click on PivotTable.

The Create PivotTable dialog box opens and the entire list is selected and, in the background, you will see the blinking dots all-around the list

You have to select a destination for your Pivot Table, by default “New Worksheet” is selected ►Hit OK.

In the newly created worksheet Excel inserts a Placeholder for the Pivot Table (called the Pivot Cache) and a Pivot Table Field List on the right side of your window and 2 contextual tabs “Analyze” and “Design” are added to the ribbon.

In older versions of Excel, the “Analyze” Tab is named “Options”.

The Pivot Table Field list shows the Field names (originally the column headers from the source list) and 4 drop areas:

1. Filters
2. Columns
3. Rows
4. Values

Note: By default, the PivotTable Fields list is docked the right side of your window. You can drag it from the title to float it, reposition it or resize it. When floating, you can dock it back by double clicking on the title.

At this point, you have to envision in your mind how do you want your Pivot Table report to look like. It is a matter of “Drag & Drop”. You drag a field from the upper part of the PivotTable Fields list and drop it in one of the 4 drop areas. When you drag a field either to the “Rows” or “Columns” area, Excel Creates a Unique list of values of the selected field in that area. When you drag a numeric field to the “values” area, excel creates a Sum of that field (Can be changed). If you drag a text field to the “Values” Excel Creates a Count of that field.

We can simplify it with a diagram as follows:

The Classic PivotTable appearance (2007 and earlier) clarifies the concept. You can switch between the new and classic layouts from the Pivot Table Options dialog box (Display Tab ► Check the box “Classic PivotTable Layout).

So, let’s see the “Sum of Sales” for each “Region”. This requires dragging the “Region” to the “Rows” drop area then dragging the “Sales” to the “Values” drop area. And our Pivot Table report will look like this:

Note that a field in use appears bold in the PivotTable Fields list.
A report has been created, in just few seconds. without the need of creating functions.

What if we want to break down our “Sales” for each “Manager” in each “Region”? Then simply drag the “Manager” field and drop it below the “Region” in the rows drop area. The Pivot Table restructures automatically:

if you prefer to see the “Region” at the column headers, then simply drag it and drop it to the columns drop area and the report is created instantly. I recall the definition saying: “A Pivot Table is an interactive way of summarizing huge amounts of data in a quick easy friendly method”.

How did Excel recognize the list although I was selecting a single cell? That’s because of the blank cells all around.

Your report looks like this:

How much time would you spend creating the same report by using functions (SUMIFS or SUMPRODUCT)?

Back to the Source Data

We used a list as a source data for the previous Pivot Tables. What if more records are added to the source below the existing records?

In such case, the newly added records are not automatically included in the report, even if you go to the Analyze tab and click Refresh (or right click in the Pivot Table and select Refresh).

To have them included you need to click on “Change Source Data” on the Analyze Tab ► The “Change PivotTable Data Source” dialog box opens ►Click and drag to re-select the source data with the newly added records. What a hassle!

I definitely prefer to have any new records automatically included in my report whenever I refresh. This requires converting your source list into a “Table” (preferably prior to creating your Pivot Table).

To convert any list into a “Table”, select any cell in the list and hit the shortcut CTRL + T followed by hitting Enter. The Table Tools Design Tab pops up into the Ribbon. You can also name the table by typing in the box to the left side of the Design Tab, let’s call it “MyData”.

Now, create the previous Pivot Table report from the Table, exactly as we did earlier. Look at the name in the Create PivotTables dialog box.

From now on, whenever you add more records to the source table, just hit the Refresh button and everything will update.

In “Part Two”, I show you eight different Sources for creating pivot Tables! That is still too advanced at this point. Now it’s time to explore the basic properties of Pivot Tables.

Properties of Pivot Tables

For this exercise, I restructured my Pivot Table by dragging the “Region” and “Manager” to the rows area, the “Customer” to the Columns area and the “Sales” to the Values area.

Show/Hide PivotTable Fields list

After creating your Pivot Table, you might want to make some room in your worksheet by hiding the PivotTable Fields list. You can do that by clicking on the Close button in the upper right corner of your PivotTable Fields list. To bring it back, click on the “Field List” button to the right side of the Analyze Tab.

This is a toggle button that turns On/Off the PivotTable Fields list. The same command is also available in the right click menu of the Pivot Table itself.

Dealing with Headers

By Default, Excel uses “Row Labels” & “Column Labels” which are not very descriptive. You may prefer to hide them by clicking on the button “Field Headers” at the right side of the Analyze Tab. This is also a toggle button.

However, you may prefer to make it more informative by manually typing the field names:

This is not the best way to show the field headers because by changing the Pivot Table Layout, Excel can automatically retrieve the field headers along with more benefits. So, I will Undo the manual typing. But, how do we change the layout?

Changing the Report Layout

You may have noticed when we dragged 2 fields (Region and Manager) to the Rows area, that both fields are in the same column (Column A): This is the default layout called “Compact layout”. We do have 2 other layouts that separate the fields into 2 columns, bring the Headers automatically and change the position of the Subtotal row. These are the “Outline Form” and the “Tabular Form”.

To change the layout, we go to the Design Tab and click on the down arrow of “Report Layout” on the left side of the Design Tab. Apply each one of the layouts and watch the difference.

Manage Subtotals

You can control whether to Show or Hide the Subtotals, and if you Show them you may want to show them at the Bottom or Top of each group. These options are available by clicking on the Subtotal button to the left side of the Design Tab. With the “Tabular Form” layout, you can only see the Subtotal at the Bottom of the Group. To move it to the Top of the group you need first to switch layout to “Outline Form” layout then from the Subtotal drop list select “Show all Subtotals at the Top of Group”. Both options are active in “outline Form”. In addition, bringing the Subtotal at Bottom of the group in “Outline Form” displays a blank row for each “Region”.

Manage Grand Totals

When creating a Pivot Table, Excel displays by default a “Grand Total” for the columns and for the rows. This default behavior can be changed from the Pivot Table Options dialog box, on the “Totals & Filters Tab”

I keep the default settings for Grand Totals checked, because I can change them as needed by clicking on the Grand Totals button to the left side of the Design Tab. There are 4 self-explanatory options to choose from. I simplified my Pivot Table keeping only the “Region”, “Manager” and “Sales” then, tried the 4 options one by one:

Note: The Pivot Table Options dialog box has so many options to control the appearance and the functionality of your Pivot Table. You can open this dialog box by either going to the Analyze Tab then click on Options (Far left side) or by right clicking in the Pivot Table and select “PivotTable Options…”

Formatting Pivot tables

Formatting Values

When we format numbers in regular Excel (outside a Pivot Table), we select the cell or range to format, then we either use the formatting options on the Home Tab or use the Format cell dialog box (by Right Clicking or using the shortcut CTRL + 1). The Format cell dialog box has 6 different tabs. These 2 locations are not relevant to the Pivot Table functionality. If you select any single cell in a Pivot Table and try using any of these options, only the selected cell will show the applied formatting. Remember the Pivot table is created in a placeholder so, it is one single block. Accordingly, if you use the formatting option specifically designed for Pivot Table, all the cells become formatted even if you were selecting a single cell.

I simplified my Pivot Table keeping only “Region” and “Sales”, select a single number and try both options. The formatting tool relevant to a Pivot Table is “Number Format” available from the right click menu.

This is a variation of the Format cell dialog box with one single Tab and is specifically designed for the Pivot Table Functionality.

You may also open the Format Cell dialog box with the single Tab, by Right clicking a single cell in the Pivot Table ► Click on “Value Field Settings” ► in the lower left corner click on “Number Format”.

Formatting by using Styles

A “Style” is a package of formatting options applied with a single click. With any cell selected in the Pivot Table ► Click on the Design Tab ► in the Pivot Table Styles Group, click on the down arrow of the styles gallery and explore the available styles. Styles come in different categories: Light, Medium and Dark. You can preview the style just by hovering over it with your mouse.

In a more advanced scenario, you can create your own style and save it with a custom name in the “Styles” gallery for reuse. It always appears at the top of the gallery.

Conditional Formatting in Pivot Tables

This is a special type of formatting stored in memory, and pops up whenever a condition is met. We cover conditional formatting in the Advanced Pivot Table topics.

Basic Calculations in Pivot Tables

Changing the Summary Function

In this topic we will explore how to change the default SUM function in a Pivot Table. You may want to use an Average, a Min or a COUNT function. To do that, right click on any number in the Pivot Table and Right click. From the Right-click menu you can simply hover on “Summarize Values By” ► and from the fly out menu select one of the functions.

You can access more functions than what you see in the fly out menu, by selecting “Value Field Settings” from the right click menu (or by selecting “more Options…” from the flyout menu). The “Value Field Settings” dialog box opens.

In the “Value Field Settings” dialog box, you can assign a custom name for the field and you can select from a wider variety of functions.

Note, the same dialog box can also be opened by clicking on “Field Settings” on the left side of the Analyze Tab.

Show Details (The Drill Down feature)

When you hover with your mouse over any numeric value in your Pivot Table, a Screentip shows what this number represents.

In this exercise, I restructured my Pivot Table to show the Sum of Sales for each “Manager” in each “Region”. I can read in the Screentip that the Sum of Sales for “Daniel” in the “North” region is $523.

What If I want to create a report that shows all the individual transactions that created this total, i.e. all the transactions for Daniel in the North region. I can do that by right-clicking the value and selecting “Show Details” or simply by double clicking on the number (we call it the Drill Down feature)

Excel generates a new worksheet with a report showing all the transactions from the source data where the “Region”= North and the “Manager” = Daniel.

Wow, that’s one of the magical options in Pivot Tables

Show Values as

In your Pivot Table you might want to create some more advanced calculations beyond the simple aggregate functions. Such as a % Of Column Total, a Rank or even a Running Total.

To do that I will restructure my Pivot Table to show the “Sum of Sales” for each “Manager”. I will then drag the “Sales” a second time to the Values area ► This will create a second instance of the “Sum Of Sales”.

Right click on any value in the second “Sum Of Sales” column ► from the Right click menu select “Show Values As” ► and from the submenu select “% of Column Total” (or any other calculation)

Note these options are also available in the ‘Value Field Settings” dialog box on a separate Tab.

Now the Pivot Table will show the “% Of Column Total” next to the Sum of Sales. I also edited the column header.

Calculated Field

In preparation for creating a calculated field, that does not exist in the source data, I removed the % Of Total.

In my Source data I have the “COGS” (Cost of Goods Sold) and the “Sales” fields, but I want to calculate the Profit, by subtracting the “COGS” from the “Sales”. Instead of creating an extra column in the Source data, I will be creating a Calculated field in the Pivot Table.

To create a Calculated Field go to the Analyze Tab ► Click on the down arrow of “Fields, Items & Sets” ► from the drop menu select “Calculated Field…”. The ‘Insert Calculated Field” dialog box opens

In the Name box, type “Profit” as a field name, and in the Formula box you create the calculation by typing the field names or by selecting them from the “Fields” box and click on “Insert Field” button, alternatively you can simply type the field names:

= Sales – COGS

When you hit Ok ► The “Profit” field is created and added to the Values area and to the Pivot Table.

Sorting in Pivot Tables

Sorting is a simple useful analytical tool in which we arrange our data in a logical manner A to Z or Z to A, ascending or descending.

By default, Fields dragged to the Rows area or column areas are sorted ascendingly. In our example, we restructured the Pivot Table to show the Region, Manager and sales. Note that “Region” and “Manager” are sorted ascendingly by default.

If we want to change the sorting order, we can do that either from the Field header in the Pivot Table, or from the Field Name in the PivotTable Fields list, or from the Data Tab of the Ribbon.

To explore more sorting options, I moved the “Manager” to the Rows area under “Region”.

In a more advanced scenario, you may want to sort a field based on another field, like sorting the “Manager” based on the “Sum of Sales”.

To do that, click on the down arrow of the Manager header ► Select “More Sort Options…” ► The Sort dialog box opens ► Select ‘Descending” and from the drop list select “Sum of Sales”.

Now the Manager field is sorted in each group, in a descending order (Z to A) based upon the “Sum of Sales” field.

There are still more advanced options, that we’ll explore in the second part of this course.

Filtering in Pivot Tables

What is Filtering?

This is another important analytical tool in Excel that allows us to display only records meeting our conditions and hide those records that do not meet our criteria.

Filtering Fields used in the Pivot Table

This can be done from the same locations as Sorting. So, from the field header you can use check boxes to keep items you want.

You can also use the “Label Filters” or the “Value Filters” from the field header drop list. The contents of these filters change based upon the selected field. These filters offer different comparison operators that you customize as needed.

So, if I want to filter by “Manager” name starting with letter “S” then I click on the Manager drop list ►Select Label Filters ► Begins with… ►Type “S” ► Hit “OK”

This filter returns Sheryl & Steve. To clear the filter, click on the Manager drop list and select “Clear Filter From Manager”

Filtering Fields other than those in use in the Pivot Table

Have you ever wondered, why when creating a Pivot Table in a New worksheet, the Pivot Table placeholder starts at cell A3 but not at cell A1? That is because cell A1 is reserved for the Filter functionality.

For this exercise, I am going to drag the “Manager” to the columns area, keep the “Region” in the Rows area and the “Sales” in the Values. Then I drag the “Customer” to the Filter area.

In cell A1, we see the “Customer” header and in cell B1 “All” denoting that the Pivot table reflects the sales for “All” Customers.

Click on the Drop list in B1 and select “Amazon” ► Hit OK. Now cell B1 reads “Amazon” and the Pivot Table reflects the Sales for Amazon.

To select multiple customers, check the box “Select Multiple Items”. This adds a checkbox for each customer. Deselect “All” and let’s check the boxes for “Amazon”, “Costco” and “Staples” ► Hit OK.

Note the numbers change and cell B1 reads: “Multiple Items”.

There is no way to recognize the selected customers in the filter except by clicking on the drop list of cell B1 and looking at the checked items. This problem will be resolved by inserting a Slicer. For now, let’s clear the filter by checking the box for “All” ► Hit OK.

Using Slicers

What is a Slicer?

A Slicer is a graphic interactive filter that allows us to see the selected filter items, unlike the classic filter. The Slicer functionality was introduced in Office 2010.

Inserting Slicers

Select any cell in Pivot table and click on the Analyze Tab ►Click on Insert Slicer. The Insert Slicer dialog box opens showing all the PivotTable Fields ► Check the box for “Customer” ► Hit OK

A Slicer is inserted into the worksheet and it shows all the Customers in the same color, which means no filter applied, and that matches the filter in B1 showing “All”.

Using Slicers

A slicer shows a unique list of items from the field to be filtered. If I use the same field in both the Slicer and the Filters area, then they synchronize together. If I use different fields for each then they complement each other.

In the previous example, I will select “Amazon” from the slicer ► The Filters in B1 reads “Amazon” as well and the Pivot table updates to reflect the Sum of Sales for each “Region” for each “Manager” just for “Amazon”.

You can select multiple customers either by pressing and holding down the CTRL key while clicking or by clicking the button in the upper right corner of the Slicer, the Multi-Select button (also available by right clicking the Slicer), then click on the required “Customers” without holding the CTRL key. Note, cell B1 reads “Multiple Items”.

To Clear the Slicer, click on the funnel icon in the upper right corner. That is exactly as if you clear the filter from the drop list in cell B1 by checking the box for All.

As we learned, any change in the Slicer is reflected on the Pivot Table (and on the Pivot Chart if any).

Formatting Slicers

You can improve the legibility of the Slicer by using different functionalities from the Options Tab.

Applying a Style
In the Slicer Styles gallery, hover over the thumbnails to explore the different Styles. I have a preference to using dark styles as they make the selection more visible.

Resizing the Slicer
The Slicer is a floating object on top of the grid and it has 8 sizing handles. You can drag a slicer to reposition it where you want, or you can drag from one of the sizing handles to change it’s position. The size of the Slicer can be finely adjusted from the right side of the Options Tab.

Splitting the items into multiple columns
It’s a common practice to split the items into multiple columns, by using the spin buttons of the columns command on the right side of the Options Tab.

Advanced Slicer functionality

There is a lot more we can do with Slicers, like: adding multiple slicers, synchronizing the slicer functionality in a way that a slicer controls multiple Pivot Tables or changing the stacking order of Slicers and hiding the slicer header and creating a custom style.

I explain all that in the Advanced part of this course.

Inserting a Timeline

In Office 2013, Microsoft introduced a special filter for date and Time Values, called the Timeline.

To insert a Timeline, you must have a Date or Time field.

Inserting a Timeline

To insert a Timeline, select a cell in the PivotTable and click on the Analyze Tab of the ribbon, then click on Insert Timeline.

The Insert Timeline dialog box opens ► Check the Date or Time field (according to the situation) ►Hit OK.

The Timeline is inserted in the worksheet, and like a slicer, it can be moved and resized.

Modifying the Timeline options

When the Timeline is selected, the “Timeline Tools Options” Tab is added to the ribbon. Through this Tab, you can apply a style (dark styles are more visible) or fine tune the size of the Timeline, exactly as we do with a Slicer. You can also Show/Hide elements on the Timeline.

Show/ Hide Timeline elements.

There are 4 elements that can be shown or hidden in a Timeline by checking or unchecking boxes on the right side of the Options Tab, in the Show group.

1. Header
2. Scrollbar
3. Selection Label
4. Time Level

These are better explained with a figure:

You may also change the Time level to Years, Quarters, Months… from the drop list in the upper right side of the Timeline to modify your filtering options.

If you create multiple Pivot Tables out of the same source data, you can set a single Timeline to control multiple Pivot Tables. I explain that in the Dashboard section of this course.

Creating Pivot Charts

A Chart is a graphic representation on numeric values and it gives better insights about the data, showing variations, trends and help in making a better business decision.

Inserting a Pivot Chart

With a cell selected in the Pivot Table, you can insert a Pivot Chart either by clicking on Pivot Charts, to the right side of the Analyze Tab, or by going to the Insert Tab and select the chart you want from the Charts group. You can quickly insert the default Column chart by using the shortcut ALT + F1

For this example, I restructured my Pivot Table by dragging the “Manager” to the Rows area and the “Sales” to the Values area ► The I click on Pivot Charts on the Analyze Tab ► the Insert Chart dialog box opens

I select the Clustered Column Chart (the simplest chart) and I hit Ok.

A Column Chart is inserted in the worksheet. I can move it and I can resize it by dragging from the sizing handles.

Let’s explore the chart elements:

1. Field Button
2. Chart Title
3. Legend (the color key)
4. Horizontal Gridlines
5. Manager Data Series (the Blue Columns)

You may control, modify or hide different elements by clicking on the green plus sign (the Chart Elements button) in the upper right corner of the chart.

Hiding the Fields buttons

Your chart can grow bigger, by hiding the Fields buttons either by right clicking on one of them ► from the menu select “Hide All Field Buttons on Chart”, or, by clicking on the Analyze Tab and to the very right click on the down arrow for Field Buttons ► select “Hide All”

Changing the Chart Title

To change the chart Title, select it and start typing. You will not see what you typed in the Chart Title box until you hit Enter. However, you can see it as you type in the formula bar.

The Title can be formatted like regular text, using options from the Font group on the Home Tab.

If for any reason you do not want a title at all, then you can uncheck the corresponding box from the menu of the Chart elements button (green plus sign in the upper right corner of the chart)

In a more advanced scenario, you may want to create a dynamic chart title, by linking the Title to cell contents. We learn how to do that in Dashboard section.

Dealing with the Legend

The legend is useful when we have multiple series (more than one calculation in the Values area) to differentiate between the series. If we have just one series then the legend is not really useful and deleting it makes the chart bigger.

I will drag the “COGS” to the Values to see 2 series and 2 colors of the legend. The Pivot Table shows the Sum of COGS next to the Sum of Sales. A second (orange) data series is added to the chart.

The colors might be different on your PC if you have a different Theme. I have the default Office Theme.

The Legend is on the right side of the Chart, but you can put in different positions by selecting a different option from the Legend Submenu in the Chart element Menu. Explore the different positions.

Clicking on “More Options…” opens the “Format Legend” pane on the right side of your screen.

Sometimes, when we have multiple charts with the same colors of data series in the Legend, we delete the Legend completely and create a custom legend. We will cover that in the Dashboard section.

Horizontal Gridlines

Gridlines can be horizontal or vertical, primary or secondary. They are more useful in a scatter plot with multiple series to facilitate finding values on each axis. Gridlines can be turned On/Off from Chart Element button.

You can select all gridlines by clicking on only one of them and hit the Delete key to remove them.

Managing Data Series

To make more room for my Chart, I selected Top for the position of the Legend, but I moved the Chart Title and the Legend by dragging so they are at the same level.

If I click on any one of the columns, all the columns of the same color become selected (entire data series). If you click a second time, only one column is selected (Data point). Whatever you do in terms of formatting will apply to your selection.

Let us start reducing the space between columns. Click on any column once, then either Right click and select “Format Data Series” or use the shortcut CTRL + 1. In both cases the Format Data Series Pane opens on the right side. Drag the “Gap Width” slider to the left as needed (mine shows 219%)

With the same selection, you can change the color of an entire series, by clicking on the “Fill & Line” icon in the Top left side of the Format Data Series pane ► Under Fill, select “Solid Fill “► then select a color from the color picker. There is also an option (below Fill) to change the outline. This can also be done from the Format Tab of the Ribbon, thinking of a column as a shape (a vertical rectangle), then you have options to change the Shape Fill, the Shape Outline or add some Shape Effects.

I will add a Bevel Shape effect from the menu under shape effects.

Adding Data Labels

You can also add some Data Labels to an entire series or to a single Data Point, by making the corresponding selection then from the Chart Element button, click on the right pointing arrow of Data Labels to select a preferred position. In some cases, you may only want to keep the Data Labels and remove the Vertical Axis (Value axis).

If you want to format the numbers, you can click on “More Options” to open the “Format Data Label” pane► Click on Label Options Tab ►Expand “Number” ► Select a number format and decimal places.

You can then format the data labels from the Font group on the Home Tab. I bolded them and used the same color of the column for each series. I click on the vertical (Y) axis and delete it to make my chart bigger.

Finally, I will add a Slicer for the “Region” and format it to match my Pivot Table and Pivot Chart.

If you followed the same steps, you would have created a simple and nice interactive report, like the one in the figure here under.

We started with a large list and we ended with a nice interactive report by using the basic functionality of Pivot tables and Pivot Charts.

I think you are now ready to take your skills to the next level by learning the Advanced Pivot Table functionality, in the next part of this course. So, let’s continue.

Stay Tuned for the Advanced Pivot Tables Course

Download the Exercise Files

Watch the Video for this course