Pivot Tables are one of the most popular features of Excel. Out of the hundreds of students I’ve had in Excel Intermediate classes, I would guess that about 75% of them come to class specifically to learn this feature. Pivot Tables are actually very easy to use, but one of the obstacles many learners need to overcome is understanding the W’s of Pivot Tables
What are they?
When would I use one?
Why are they better than other Excel tools?
In this blog, I’ll answer those questions.
What are Pivot Tables?
A Pivot Table is a table that summarizes your data. Let’s say, for example, you have a list of your sales orders for the past year. Your data set includes sales rep, product number, customer’s state, quantity sold, and price, among others. There are lots of ways you may want to summarize this data. To name a few:
Total sales dollars for each sales rep
Total sales dollars for each product
Total sales dollars for each state
Quantity sold for each product
Quantity sold for each product, by sales rep
States with the highest quantities sold
States with the highest sales dollars
Pivot Tables make it quick and easy to generate all of these, and more.
When Would I Use One?
There are many use cases for Pivot Tables, but the most important one, in my opinion, is that they will help you decide how to refine your business.
A Pivot Table might make it obvious that you sell a very high quantity of product in a given state, but the sales dollars in that state are low, which might lead you to adjust your sales or marketing efforts in that state. Or you might find that a given sales rep is a super star when selling a particular product and ask that person to share his or her strategies with the rest of the sales team. We all know that knowledge is power, and Pivot Tables transform raw data into knowledge.
Here are a few scenarios that should make you think about using Pivot Tables:
You have a list of sales orders, and you need to determine commissions for each sales rep
You are planning to launch a marketing or advertising campaign, and you’re not sure which products to feature
One of your sales reps has retired, and you need to figure out the best way to cover his or her territory
You are thinking of discontinuing a product and need to understand the impact
Your Board of Directors frequently asks for different slices of your data
These are just a few of the reasons for using Pivot Tables. In short, any time you need to analyze a set of data, a Pivot Table is a good candidate.
Why are They Better than Other Excel Tools?
Excel is full of useful tools: charts, graphs, data tables, subtotals and functions, to name a few. So why would you choose Pivot Tables over these other tools? I could give you many reasons, but it all really comes down to one: FLEXIBILITY.
The other Excel tools I mentioned are great, and you shouldn’t abandon them. But they are more static in nature than Pivot Tables. Once you build a great chart, you can reuse it over and over again, for example, reporting your sales by product category every month.
Businesses tend to be dynamic, though, and the question that was important last month might be superseded by a more important question this month. Or two questions. Or three.
We’re in the midst of the COVID crisis as I write this, and that has caused upheaval at many companies. Many are downsizing, shifting or pivoting. While your Sales by Sales Rep report may have been your most important report a few months ago, maybe you now need to see Sales by Product Line, so that you can consider dropping a product line. Or maybe you need to see which states have fallen off the most in the past few months, so that you can decide where to focus your advertising dollars.
The point is, things change.
And not just in times of crisis. An agile business is better able to ride out the storm. And when times are better, an agile business is better able to surf the waves of progress.
That’s the power of Pivot Tables: they provide a quick and easy way to change how you look at your data. It only takes a few minutes to build a Pivot Table, and then it’s a matter of seconds to change your data points. If you’ve got a Pivot Table that shows you Sales by Sales Rep, it takes two or three clicks to change that to Sales by Product Code. With a couple more clicks, you can have it show you Sales by Product Code for each Sales Rep. And with a couple more clicks, you can flip it to show Sales by Sales Rep for each Product Code.
Just Jump In
A lot of people are intimidated by Pivot Tables and wait until they get some formal training before using them. I recommend you jump in rather than waiting. Pivot Tables are remarkably easy to use. If you spend 5 minutes reading about them or watching an introductory video (like this one: https://www.youtube.com/watch?v=qu-AK0Hv0b4), you’ll be ready to get started. Of course, it takes longer to master them, but once you start, you’ll be hooked.
Want help getting started with Pivot Tables? It’s one of my favorite subjects! Fill out the contact form (available on my Excel page), and we’ll set up a time to chat.
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2020 North Port Solutions, LLC. All right reserved.
Comments