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.