How to do a Pivot Table in Excel

The reason why many Excel users never learn how to do a Pivot Table in Excel is because they don’t understand why they should learn Pivot Tables neither what is a Pivot Table.

If you are still afraid of Pivot Tables or you want to establish a solid foundation to your data analysis journey, in this free Excel Pivot Table tutorial, you will learn the fundamentals first:

  1. Why you should learn how to do a Pivot Table in Excel
  2. What is a Pivot Table
  3. Learn how to do a Pivot Table in Excel

I was one of those who was not aware of the psychology and mechanics of Excel Pivot Tables.

Why you shoud learn how to do a Pivot Table in Excel

But I was determined to definitely get it because I realized Pivot Tables were a form of fast Excel dashboarding and fast data analysis and understanding.

Create Excel dashboards fast

You can create summarized tables out of big tables with a couple of clicks. You can add several views of the same data set (e.g. by department, by quarter, by region, sum, average, count, etc). You can also add charts and dashboard controls such as slicers and timelines  without using VBA, without writing any single vlookup or index and match formula.

I created the plain dashboard you see below in a matter of 5 minutes!

Dashboard using a Pivot Table

Analyze and understand data

For example you can turn a big 10K rows Excel table into a tiny Excel table packed with meaningful information because a Pivot Table finds patterns and show them to you. For example, in the table below, the thousands of “Delivery Truck” entries become a single one with a subtotal.

Simple Pivot Table example

 

When you learn how to do a Pivot Table in Excel, you become able to include in your analysis only the data that matters most to you. For example, if you are a manager, you can focus on departments or products or on both categories at the same time. You can focus your analysis even further by looking at a date range only. If you are a salesman, you can focus your analysis on the product you sell and narrow the date range to your sales period.

If you want to look at your data from another vantage point, you can do it easily because you can drag and drop any data field you want and change your Pivot Table report instantly.

Well, if you don’t know how to do a Pivot Table in Excel yet, then you need to learn what is a Pivot Table first. If you want to get Pivot Tables explained to you in a meaningful manner, you might find this explanation useful:

What is a Pivot Table

You are about to discover a simple analogy that will make Pivot Tables easier to learn and use.

If you don’t know what is a Pivot Table, if you are afraid of learning and using Pivot Tables, if you have only a basic idea of Pivot Tables in Excel and don’t know what to expect when you drop the fields to the PivotTable report, or if you just want to understand more about Pivot Tables, then keep reading…

…because this grocery analogy I stumbled upon will show you how to do a Pivot Table in Excel because it will make PTs simpler for you, and it will boost your reporting and dashboard skills, starting today.

It’s a shame…

I used to create Pivot Tables reports through trial and error but I was afraid my coworkers or boss would ask to me: how did you that? because I didn’t know exactly how my Pivot Table reports were generated, how the fields affected the layout of the report, and so forth.

I never had confidence in using Pivot Tables because it was a sort of hope-reporting. Every column, row, page, or value field I dropped into the fields’ area, produced unexpected results.

Everything changed when I discovered the Pivot Table relationships . Pivot Tables Paradigm!

A Pivot Table report is like a box. See below:

How to do a Pivot Table in Excel - Pivot table report explained

Let’s imagine the box contains oranges, apples, lemons and bananas. This is your source data.

How to do a Pivot Table in Excel - Pivot Table explained using a simple example

With this bunch of fruits as your source data, you can ask questions like: how many oranges, apples, lemons and bananas are in the box? You can achieve this by counting each type of fruit.

To better find the answer, you organize the box by fruit type as you see below:

Pivot Tables explained - Counting

Here’s the answer and a nice subtotal:

 

Pivot Tables explained - Counting

You can also organize the fruits according to its type: citric and other.

Pivot Tables explained - Counting and grouping

If each fruit have a price tag, then you will be able to answer questions like: how much the oranges, apples and bananas are?, how much the box is?, how much the citrics are?

To answer the question: how many green and mature oranges contain the box?, you would need to compartmentalize the box by fruit state: mature and green oranges.

Very simple, right?

To answer the question: how many big or small green/mature oranges are in the box?, you would need to compartmentalize the box by fruit size.

Would you compartmentalize the entire box again? Or just the existing compartments?

You would separate green oranges into small and big and mature oranges into small and big.

In other words, you compartmentalize the already compartmentalized box.

This simple principle applies to Excel Pivot Tables…

Learn how to do a Pivot Table in Excel

You simply group the data source on your Pivot Table report by any of the fields you choose.

The first item you add to your Pivot Table fields area (box) is what’s in your box (data source), the second field is how you compartmentalize the existing box, the third field is how you compartmentalize the existing already compartmentalized box, and so on.

For example, if you have a table with these fields: Department, Brand, Model, Salesman, Date, Units sold, Amount, and you add the “Department” field to the fields area, then you will answer questions like how much sales by department.

To answer the question how well each brand performed by department?, then you would add “Brand” to the fields’ area so you compartmentalize “Department”. If you add Brand, then you compartmentalize department by brand, if you add salesman, you split the brand compartment by brand, and so on.

You now should be able to drag and drop the Pivot Table report fields with confidence.

If you are more comfortable with using Pivot Tables in Excel, and want to get deeper into using Pivot Tables, you can definitely learn how to do a Pivot Table in Excel in my Free Excel Pivot Table tutorial

Or you can explore these good Pivot Table tutorials:

http://www.contextures.com/CreatePivotTable.html

http://www.excelfunctions.net/Excel-Pivot-Table-Tutorial.html