What is a Pivot Table..?
A Pivot Table is a powerful tool used to calculate, summarise and analyse data that lets you see comparisons, patterns and trends in your data – a pivot table helps you utilise Excel better.
It will take a large amount of information and then condenses all that data into a much easier-to-read report, so you can see at a glance where your focus needs to be. The data it uses can be anything you store in a spreadsheet, for example, you might do your account management in a spreadsheet, and you can easily see where your money is going, which months you earn more etc. You could store your business inventory in a spreadsheet and have rows upon rows of information, a pivot table can show you exactly what you need in as little as 6 rows.
Basically, you can summarise large amounts of data in just a few rows.
The image below shows 20,000 lines of data, summarised into less than 15 rows, can you imagine how long it would take you to manually add up all the totals without a PivotTable…?
What would I use a Pivot Table for..?
You may spend hours creating a worksheet that sums up the information you need for a meeting/report, whereas a pivot table could do that for you within seconds. Wouldn’t you like to utilise Excel better too…
You might want to see which of your employees have been the most successful at sales in a month, quarter or year – a pivot table can do that for you much quicker than you can do it manually, you might even want to then print a report for each employee on the sales they’ve done, if you have multiple employees this task could take you the best part of a day to do manually, a pivot table will split it all out onto separate tabs for you so you can just print them off – literally seconds is all it takes.
A lot of businesses like to have KPIs they can check against on a regular basis, they might also want to have a dashboard that looks great to show stakeholders on how well the business is performing, a pivot table can do all that for them.
Only 10% of Excel users know how to create a pivot table…
…..That’s a very small amount of people who know how to make tasks quicker and easier, saving them time and effort.
How often are you sat at work stressing over doing a report in Excel and wondering how you are going to get all the information needed, thinking it’s going to take you hours and may require you to stay late at the office to get it finished, whereas you may have a colleague who seems to breeze through all these types of tasks and still manages to finish work on time to get to the gym or meet friends for dinner etc.
They probably know the tools available within Excel that make their job easier and quicker.
Some business owners keep all of their client info and the services they use in a spreadsheet and they use Pivot Tables to quickly see at the end of the year which services have been most popular and how much income they generate. Using the Pivot Table to quickly summarise that info can help them to decide whether they end some of their services or offer new ones.
Just using filters on the columns isn’t going to make the task quicker or easier, this is where a pivot table comes in.
In the example below, we’ve filtered a pivot table by genre and customer and by clicking on one of the coloured tabs to the right of the pivot table we can change the viewpoint and results that we receive back, it’s that simple… Considering we started out with over 20k lines of data and it’s summarised here in 10 rows…
Check out some more examples on our pivot table page where I also offer Pivot Table Masterclass training, meaning you can learn Excel at home.
Take a look at some of my other training options here