The main goal of this handy tool is to summarize and make sense of large amounts of data. They’re also interactive, meaning Google and Microsoft’s pivot table functions help us sift through data to exclude unnecessary points, combine large chunks of information and find big-picture ideas, such as averages and sum totals. In a business setting, we may want to use pivot tables to examine:
- Total spending on expense reports
- Average annual profit
- Most successful store branches and ranking order
- Our most popular selling products
Pivot tables also offer the ability to quickly sort data in a variety of ways, which saves time and energy when examining different areas of our data.
Example of a Pivot Table at Work
For example, if we ran a chocolate business (yum) and had a spreadsheet that mapped out chocolate sales, purchases, shop locations, price and profit, we could use a pivot table to demonstrate a variety of different figures.
Here’s what our spreadsheet might look like
We could use a pivot table to highlight the most popular chocolate by choosing to group by chocolate name and sum the total sales. This gives us a chart of total sales per chocolate that we can order by sales amount to find the most popular.
That might look something like this:
In this example, it let us identify that while we sell fewer Bacon Chocolate bars than Milk Chocolate bars, they make us more money, which is really good to know.
If we wanted to restrict our research to sales within a particular time period, we could easily ad a filter for “order date.” This could be accessed through the filtering tool in the pivot table.
While this example is fabricated, I hope you can see the power of pivot tables in exploring all sorts of data. The best part is that they only take a minute or two to build.
Pivot tables help us consolidate and summarize information to make a ton of data much more readable. It allows us to easily shift and combine information to identify trends and drill down specific findings us are looking for. When large Excel files or Google Spreadsheets seem unmanageable, pivot tables make sense of the mess.
Pivot Table Resources
- Five Minute Lessons has a great Pivot Table walkthrough
- Another example from Excel-Easy
- ExcelJet has a list of ‘things you should know’ about Pivot Tables
Here’s a great video walkthrough of how to build a pivot table by Jon Flynn
Using Pivot Tables for Reporting?
Using pivot tables to explore data is great, but once you start trying to share the insights with colleagues, customers, and partners it can become time consuming to keep on top of everything. If you need to share reports, you might want to check out our reporting product, we make reporting simple and effective.