[MUSIC]

Let's talk about one of my favorite

tool in Excel the Pivot Table.

A pivot table is a dynamic report that can summarize and

display data in various ways.

I love how interactive pivot tables are.

They allow you to rearrange data quickly so

that you can analyze interrelationships between the data.

A few people here at Treehouse have told me they're excited for

me to explain how pivot tables work.

Pivot tables are an awesome tool because of how they expose relationships

between data.

Let's talk about how they work.

Here we have a spreadsheet that shows sales for a fictional store.

And if you'll like to follow along we can find a link to this spreadsheet in

the project files.

Before we create the pivot table,

we need to make sure that the data is formatted correctly.

For the pivot table to work properly,

we need to make sure that each column represents a category.

And that each column has a header, telling us what that category is.

In addition to properly formatted columns, you'll also need properly formatted rows.

Each row should contain an entity, or instance of a particular record.

Also, make sure there are no blank columns or rows, or summary columns or rows.

Let's look at an example of a spreadsheet that is not formatted properly.

On the Store Data2 tab you'll notice we have a blank column for

column D and we have blank rows and subtotals.

Also, there are no category headers for the first three columns.

We would not be able to use this data for a pivot table until we cleaned it up.

Now let's look at one that is formatted properly.

You'll notice there are no blank rows or columns and no summary or sub total rows.

You'll also notice we have headers for each category.

Now that we have conferred our data is formatted properly,

let's move on to the next video where I'll show how to build a pivot table