This short story shows how I gained the respect and trust of 3 departments in the big company I worked for.
Before I knew how Pivot Tables in Excel worked, I found a big opportunity to become valued at my office…
…I noticed that one single Excel table contained all the data that the Planning, Cost, and Engineering department needed.
This was a huge realization at the time…
…in a way, the data and its insights were dormant!
The Planning department needed the length of pipes by diameter and by zone, the Cost department needed pipes length by diameter for suppliers, the Engineering department needed the total length of pipes to plan the work of drafters.
And nobody was providing these insights in a timely manner!
I knew Pivot Tables were good for mining data but I didn’t understand how they worked yet.
And the table was monstrous…
I have always known the benefits of trying new things so I created a Pivot Table report out of the table, and started playing with it.
I asked to each department head, what do you want to know about this data?
The table contained tens of fields but I just focused on the fields they care about: Pipe length, Pipe zone, Pipe Diameter, etc.
For example, Mirian, from the Planning department wanted to know the distribution of pipes by zone, so I dropped those fields into the Pivot Table Excel canvas. And patterns started emerging. I did a lot of trial and error until the pattern she wanted was established.
Do it now!
Pick your company data and see how you can help different people, maybe your boss and the boss of your boss. Ask them what do they want to get out of this data? And they will tell you because people is craving for more understanding.
Drag and drop the most relevant category fields (Department, Pipe diameter, Name, Year, etc.) to the Row area of your PT report. Drag and drop the most important data fields (Length, Sales, Amount, etc.) to the Value area of your PT report. If you are not satisfied with the layout, move the Pivot Table Excel fields around. This is the way I did it and it worked because any layout you create is right (maybe not needed) and you can play safely until you find something that is needed.
The worst part was when they started asking me for new reports. My trial-and-error approach was a huge limitation because it was embarrassing playing with data when they were looking over my shoulder.
Hopefully I stumbled upon a simple grocery analogy that revealed the mechanics of Pivot Tables in Excel and you can grasp it in minutes from now.
If you’re looking for an “insider” shortcut guide to finally get comfortable with Pivot Tables in Excel, creating insightful Pivot Table Excel reports (consistently) that will supercharge your data mining and presentation skills, then you’ve got to sign up for my free Excel Pivot Table Tutorial.
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:
- Why you should learn how to do a Pivot Table in Excel
- What is a Pivot Table
- 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!
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.
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:
Let’s imagine the box contains oranges, apples, lemons and bananas. This is your source data.
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:
Here’s the answer and a nice subtotal:
You can also organize the fruits according to its type: citric and other.
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: