Why pivot tables?

And that’s exactly what we’re going to do in today’s video.

And, I want to be really upfront with you here.

I love pivot tables.

So, I hope by the end of the video you’ll agree with me.

Let’s go take a look at some examples.

This data represents chocolate sales to several customers in different locations, at a wholesale level.

you could see we have Date and Customer.

Now we have a name for each column in the data.

Now let’s build a manual report.

Now I’ll add in some labels and formatting.

So, we have the bones of the report in place now, but no data.

And it’s time to write out the first SUMIFS formula.

Now we have the basic report your manager wants…a one-page sales report with a breakdown by product.

Now let’s build the same report using a pivot table.

Since I already named the data, I can just use that name here.

We’ll need product as a row label, and revenue as a value.

And then apply a number format to revenue.

And, that’s it.

We now have a pivot table with the same results.

And notice that I didn’t use any formulas at all.

Of course, no manager will ever be satisfied with their original instructions.

They’ll all request changes.

So, let’s look at how both of these reports handle a few change requests.

First, let’s say your manager comes back and asks for a breakdown by product and region.

She wants region across the top.

This time, I need to transpose the regions across the top.

Then I need to clean things up a bit.

Next, I need to extend theSUMIFS functionto handle both product and region.

Now I can copy the formulas across the table and add totals.

Again, it’s not too terrible, but lots of clicks and plenty of formulas.

Changing the pivot table report is a bit easier.

All I need to do is add region as a column label and we’re done.

Again, there’s no need to update any formulas.

So you hand that back to your manager and an hour later she tells you it’s great.

But can she also see a breakdown by year instead of region?

This will be trickier.

Now I just need to update the formulas.

The first criteria matches dates that are greater than or equal to the first of the year.

The second criteria matches dates that are less than or equal to the last day of the year.

In both cases, we’re referencing the year directly and we need to lock the row.

Once I copy the formula down and across, we’ve got our report.

What about the pivot table?

To update the pivot table, I need to first get rid of region.

Then, I need to add the date field, and group by Year.

As before, there are no formulas to update.

So, you get the idea.

But it’s a lot faster and easier to use a pivot table.

Ultimately, a pivot table can build much more complex reports than you’d ever want to tackle manually.

As you might see, it’s not even close, pivot tables are way faster.

But, beyond simple convenience, pivot tables give you a real competitive edge.

So whether you’re competing internally with your peers for a raise or promotion, or just recognition.