PivotTables really are great and we’re not just saying that because it was one of Bill Gate’s favorite features. The original PivotTable feature was clumsy and difficult so you’re forgiven for trying them and giving up in disgust.
Mayurakshi wants to show you some of the nifty parts of PivotTables but before that she’ll of PivotTables and why they are so useful.
Personally, I feel PivotTable is one of the most useful tools in Excel if your works demands you to play around data. Even if you are not a data analyst or data scientist, you can impress your boss by creating some amazing PivotTable or charts to represent a larger set of data.
PivotTables have been around so long that there’s an assumption that all Office users are familiar with them. Our experience is that many people don’t know about PivotTables, are scared of them or don’t use even a fraction of the feature.
In this starter, we’ll explain the basics of a PivotTable with many examples of how they can be used to turn a list of information into something summarized and understandable.
Why create a pivot table?
It helps you to manage a lot of numbers, compare and sort them in many ways with a few clicks.
Look at the spreadsheet below, we’ve created a table using 6 columns: Order ID, Product, Category, Amount, Date, and Country. It could be almost any list; your personal budget, list of students or staff, the Spanish Inquisition, whatever.
There are so many questions to ask from this table and PivotTables can answer them all and many more besides:
Which product sells the most or least by value?
Which Category sells more?
Which date is best/worst for sales, overall and on average?
This example also has a glimpse into the maths options available. It’s not all about SUMing values, you can count items and calculate averages among many things.
Which country has the best/worst sales?
Drill down for details
A PivotTable summary can have levels to drill down for more details. Here’s the same starting data by Product, then sales each month and finally the sales for a single day.
And there’s automatic sub-totals as well.
2D PivotTables
All that’s before you get to more interesting ‘2D’ PivotTables like this one that groups the sales by month with the option to reveal more detail for a month. We’ve ‘opened’ the June details while the other months sales are grouped together.
Or this breakdown of sales by country and product.
PivotCharts
PivotCharts are just charts based on a PivotTable but Excel (optionally) lets you skip the PivotTable bit and just the chart.
One List, many PivotTables and PivotCharts
PivotTables let you work from a single list that can be transformed in many different ways.
Before PivotTables you’d have to make complex calculations from different versions of the same table.
List changes, the PivotTables change too
If the data changes, the PivotTable will change too.
Add more sales, can PivotTable adjust itself.
Change past data to fix an error, PivotTable will change in the blink of an eye.
List or Database
Advanced users can bring in large amounts of data from another worksheet or a direct connection from a database. In our examples, we’ll stick to simple and single Excel worksheet lists.
List Tips
Your Excel list data has to be normalized which a data nerd speak for ‘being consistent’.
For example, each country must be same name not a mix of “USA”, “U.S.A.”, “US”, “U.S”, “America”, “United States” etc. PivotTables will treat each of those as different countries. Same goes for Products, Categories or any other text labels.
Dates must all be in Excel data format, otherwise Excel won’t know to treat them as dates for grouping and formatting.
PivotTable Versions
PivotTables have been in Excel for many years and got a lot better over time. Things that were difficult or impossible are now easy or, at least, possible.
However, it’s the usual Microsoft Office story when it comes to PivotTables. Excel 2016 for Windows (Office 365) has all the latest and greatest features. PivotTables in earlier Excel’s, Excel for Mac and on other devices don’t have the same features and abilities as Excel for Windows.
That especially applies to Excel 2016 for Mac which has PivotTables but not all the power or options that you’d expect.
Our articles will use Excel 2016 for Windows (Office 365). If you’re using another Excel, be prepared for the possibility of missing some option we’ll talk about.
