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 be easy to graph in 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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.