PivotTables – A ‘Must Have’ Excel Skill
One way of judging the importance of different Excel features is to look through the ‘Must Have’ section of relevant job descriptions. You’d probably be surprised at how often PivotTables appear.
One of the great benefits of PivotTables is that they are not just the best, but also often the simplest, solution to most Excel problems that involve creating any sort of summary totals. You can use functions like COUNTIFS(), SUMIFS() and SUMPRODUCT() to extract totals from a list of transactions or values, but with a PivotTable you can do it without entering a single function or formula, or even touching the keyboard at all. Let’s count the mouse clicks:
- Click any cell in a table of data
- Click the Insert ribbon tab
- Click the PivotTable command in the Tables group
- Click OK
- In the list of PivotTable fields click a category
- In the list of PivotTable fields click a value
Instant Summary: It has taken 6 clicks to summarise all our transactions by day.
Just to demonstrate the power and versatility of PivotTables, we’ll change our summary to show totals by month and year rather than by day:
- Right-click on any day cell
- Click on Group
- Click on Year
- Click on OK
…and on to add a graph
- Click the PivotTable Tools, Analyse ribbon tab
- Click the PivotChart command in the Tools group
- Click OK
Unlucky for some, but not bad. 13 clicks to go from thousands of rows of data in a table to a graphical summary by year and month.
Hopefully, this shows that it’s worth knowing about PivotTables as they are often best way to accomplish common and simple Excel tasks. It’s not a vast step from there to an interactive graphical dashboard that will help you take key business or personal (or fantasy football) decisions. And of course becoming a PivotTable expert could land you that job.
This post was written by Simon Hurst, author of our PivotTables course. Simon is a chartered accountant and has been involved in computer software for 26 years, with his expertise lying in all the main Microsoft applications. He’s been running Excel training courses for over 20 years and this practical experience is reflected in our PivotTables course.