Quick, clean, and to the point

Pivot Table Example - Sales by sales person

In this example, we're going to look at sales data by salesperson.

In the data, we have the fields: Name, Date, Amount, and Region.

To start off, let's look at total sales by region.

This would easy to graph with a Pivot Chart as well.

Next, what are total sales by month and year?

To answer this, we'll need to group by Date.

How about total sales by salesperson in 2014?

The easiest way to build this report is to set Year as a Report filter.

Next, what are the 5 best and worst months in 2014?

This is actually a good job for conditional formatting, which hooks into pivot tables nicely. I'll use green for the top 5 months and red for the bottom 5 months.

Finally, for the last question, what are the top 10 salespeople in 2014?

Notice, the conditional formatting automatically adjusts to show the top and bottom months for this group only.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.