DEFINITIONS:

• Pivot: a central point or pin, and in this case refers to the raw data, such as that entered into the rows and columns of this spreadsheet.
• Dynamic: Seeing results immediately based on an action as opposed to seeing results after an action.
• Transpose: to cause two or more things to change places with each other.

LECTURE SCRIPT (you can also turn on close captioning by clicking the CC button):

In this lecture, let’s make sure we understand what a pivot table is, and what it can do for you.

Open the exercise file ACMESales0101 located in the SECTION 01 folder of your exercise files if you wish to follow along.

Pivot is defined as a central point or pin, and in this case refers to the raw data, such as that entered into the rows and columns of this spreadsheet.

Here we see a typical worksheet with the raw data entered for two sales offices in the U.S.

That’s our pivot data, or the raw data that will be used to create our table.

Let’s say we wanted to see this data by year, or by month or quarter, add subtotals for each location, add grand totals and so on.

Further, if we wanted to hide and show this data depending on what we were looking at, this would be a lot of added columns and rows, formulas, and more.

A special table can be created from this data which allows you to quickly reorganize, rearrange, and summarize the data in a variety of ways without changing any of the raw data.

That’s our table, hence the name, pivot table.

You might hear more technical folks refer to this as a dataset, which is simply related sets of information.

This pivot table looks a lot like a regular worksheet with it’s rows and columns of data, and we’ve set this one up to show labels, subtotals, and grand totals.

As you’ll see later, we can set our pivot table to show our data in any format we chose.

We have revenue data for the Atlanta and Portland offices by year, quarter, and grand totals in the end column and at the bottom.

This type of worksheet could be created using the typical functions and formulas available in Excel, until you realize that this table was automatically created with a single click, and is extremely dynamic.

[click on collapse thingies]

We can collapse and expand the data as we see fit.

Since we have different rows which contain different sub data, we can filter out the ROWS, as well as the columns.

Let’s UNDO that.

Next, let’s say I want to add the month data into our table.

I simply drag the month field into the ROWS pane, and just like that, our table is formatted with the added month data, collapse controls have been added to the quarters, and grand totals have been added to the grand total for each month in each quarter.

Again, we didn’t change our raw data in any way – it’s still untouched over here.

More so, we didn’t have to add or delete rows, add formulas, or do anything really, except drag a field.

Pivot tables allow you to quickly change the presentation of your data, so you can dynamically emphasize what you want to see and how you want to look at it.

Let’s look at this PivotTables task pane, which controls what data is shown in your table.

The first shows what fields you can place in your table.

Any field with a checkmark has been added, in our case, all of them.

The bottom set of panes is where you drag these fields to designate whether to include them or not, and how.

Click and drag on the MONTH field and drop it back into the fields area.

Notice the MONTHS are removed from the table, and the MONTH field is now unchecked.

The other bottom panes show what columns are displayed in our table, in this case location, which is where the values Atlanta and Portland reside.

As you’ve seen, the rows data includes the years and the quarters.

Values includes the Sum of Revenue, and we’ll cover this completely in another lecture.

Lastly, the Filters area allows you to limit the data that is displayed within a PivotTable without changing its organization.

Let’s drag our MONTH field to the FILTERS area, and we see that the table stays the same, and a new filter is added above the table for months.

We click JANUARY and we see all the data in the table for January, with the correct calculated totals.

Let’s remove the MONTH filter and all is returned as it was.

We can completely change the organization of our table with a few simple clicks.

Click and drag the YEAR below the QUARTER and now the table changes to a completely different look.

Let’s UNDO that.

I can quickly exchange rows and columns in my table.

Drag the LOCATION to the ROWS pane, and drag the YEAR AND QUARTER to the COLUMNS pane.

We’ve completely changed how our data is presented, transposed rows and columns, and the pivot table has done all the work of keeping all the totals and labels in order.

I can also create a filter by clicking on the down arrow for a field.

In this case, let’s set our table to display data only from 2017.

Everything in the table is reformatted correctly and all totals are correct.

This was a very brief demonstration to familiarize you with pivot tables and a few of their capabilities.

In the rest of the course, we’ll go in-depth with hands-on examples so you become a master using pivot tables.