In this multi-series of blog, I’ll touch on different ways to categorize data in buckets, or bins, and summarize in meaningful ways. Some will use Pivot Tables, some will not. But we’ll cover 3 common scenarios.
Let’s do the third and final one here…
The Scenario
We have about 2,000 rows of data on sales from year 2000 to 2016. Each row contains a full date of transaction and associated sales amount (see Table).
Objective: Categorize the data by year, so that we can get yearly total Sales and also be able to drill-down to quarter per year, and month per quarter of any year.
Here’s the data:
The Solution & Steps
- Select the Date and Sales columns’ entire dataset Insert a Pivot Table to the same worksheet (or a new one, but my demo will show on the same sheet).
- In Pivot canvas), drag Years to Rows, and Quarters, Date to Filter boxes. (or just select them and let Excel make the best guess…it might work that way too). Excel will then will automatically split the date into Quarters, Months, and Years.
- Done!
Now, we can select any year using the pivot table’s column header (down arrow) or combinations.
Here’s a short animation of the steps…
This is the last of this series. If you missed the previous episodes, you can start at: Understanding buckets, bins, categorization (1/3) and follow along.
