Why pivot tables rock

Why pivot tables rock, especially compared to arrays (but arrays are still awesome)

Do you ever run into situations where you have a large amount of data, and you need to perform some type of mathematical operation on a subset of your data?  For example, maybe you want to sum up all the sales within a certain region, find the average of sales for a certain manager, or find the sales value of the highest seller for each manager in the example below.

One method to do each of these calculations is to write an array.  However, I think that it is much quicker to use a pivot table.

To Find the Sales within a Certain Region

1. Pivot the data
2. Set the Report Filter (at the top) to be “Region”
3. Set values to be “Total Sales”
4. Right click on the data, choose Value Field Settings, then choose Sum.
5. Now you can adjust the region and see the sales for each region.

To Find the Average of Sales for a Certain Manager

1. Pivot the data
2. Set values to be “Total Sales”
3. Set row labels to be “Manager”
4. Right click on the data, choose Value Field Settings, then choose Average.
5. Now you can see the average sales for each manager.

To Find the Sales Value of the Highest Seller for each Manager

1. Pivot the data
2. Set values to be “Total Sales”
3. Set row labels to be “Manager”
4. Right click on the data, choose Value Field Settings, then choose Max
5. Now you can see the highest sales value for each manager.
6. If you want to see who the sales person is, add “Salesperson” to the Row Label. The pivot table will stack Manager and Salesperson together, and show you the sales for each salesperson.

Pivot tables are an extraordinarily powerful tool that lets the user slice and dice data very quickly.  Spend some time playing around with the pivot table by putting fields in different areas and stacking them on top of each other.  Also, experiment with changing the field settings around from Max to Sum to Min to Average, etc.  Trust me, this will pay huge dividends in the future.

Another thing to note about pivot tables is that they can aggregate similar lines of data together.  For example, if you have 2 rows of data for Joe, the pivot table will automatically sum up the sales for Joe if you run a pivot to view Salesperson and Total Sales.  In other words, the pivot table will automatically combine sales together if they are derived from the same source.  Joe will not show up as 2 separate lines in the pivot table, instead, he will show up as 1 line, but include the sum of both lines of data.  If you have a massive data set with many lines of data for the same salesperson, the pivot table will automatically sum up all those sales for you.

Below is an example of data with multiple entries for certain salespeople.

A pivot table can quickly summarize the data for you.

Then you can copy and paste this pivot table and reformat it to your liking.  Please click here to check out the sample spreadsheet associated with this post.