Excel Pivot Tables And Why You’d Use Them

Excel Pivot Tables And Why You’d Use Them

Excel Pivot Tables can make quick work of analysing huge amounts of data. At first they may seem complicated and only for the ‘Advanced Excel User’, but in reality they’re actually quite simple.

In fact they’re great for people less confident in Excel, because you can’t break anything, as the Pivot Table only reads the underlying data, it doesn’t actually modify it.

Pivot Tables allow you to take columns of data and summarise and change the orientation of it. For example, you can take data that’s in columns and ‘Pivot’ it so that you have a grid of headings spanning both columns and rows with your data summarised in the body of the table.

In doing so, you can quickly get answers to questions, and meaningful information about the underlying data. Imagine you have a year’s worth of sales data (1000’s of rows) laid out with the following column headings:

• Date

• Product

• Region

• Salesperson

• Units

• Sale Amount $

And you might like to have a report that answers the following questions:

• Total $ Sales per month, by product, by salesperson.

• Count of product sales, by region, per month

• Count of sales by product, by salesperson

• Total $ Sales by region, by product, per week

• Average Sales by region, per month

The permutations are almost endless, as are the benefits. One of the great features is how easy Pivot Tables are to change, taking seconds to alter, so you can build them by trial and error, build multiple tables from the same set of data, all with varied and useful information.

Sure, you can achieve similar analysis with formulas but Pivot Tables are a better solution. They’re faster to build, easier to update, easier to change, require less memory.

As a bonus you can also create Pivot Charts from the same set of data. Update the Pivot Table, and the chart updates too.

The most important requirement before you can build an Excel Pivot Table is to have your data laid out in columns, with each column having a unique heading, and no blank columns in your table of data. It’s also preferred that your data is sorted and there are no blank cells.

There are plenty of Excel Pivot Table Tutorials available, and for anyone who works with data that suits a Pivot Table, it’s worth taking the time to learn how to use them, as it will be time well spent.