Pivot features, learn by examples

How to aggregate, summarize, filter and compare data.

Pivot tables in Excel are a very good way to leave your source data as it is and work on its mirror copy. By creating a pivot you instantly get a brand new workspace to filter, group, summarize and compare data without even touching the original table. Let's go through those functions every pivot table is supposed to feature.

Grouping or Aggregating Data

This is by far the very core feature pivots have been created for. Let's start with an example to make it clearer.

If you're dealing with dates (which means days, months, years and so forth) a common way to read data, especially when you have large dataset to analyze is by grouping lines in more meaningful subsets such as weeks, quarters, or so. This lets you have less data to look at and probably more usable information.

Automatic Grouping - Quarters

Grouping months into quarters

Use the bottom-left quadrant (labeled Row Labels) for Grouping.

Row Labels quadrant - Grouping

Grouping can be done either manually or automatically since Excel is able to detect data types (e.g. dates), thus suggesting possible groups.

Sub groups are also a great feature, and they support multiple levels of aggregation so that pivot table itself becomes an information discovery tool.

Subgroups - monthly, weekly, daily earnings

Summarizing Data

Summary is the simplest thing you can do with a pivot table. Just drag a field you'd like to view summarized to the bottom-right quadrant (labeled Values) and you're done. Excel automatically suggests an operation that makes sense for that field, which is usually Sum, but you can easily change that to Count, Average or whatever is good for you. Summary is as even more powerful when used together with Filtering or Grouping (as seen before).

Sum data example

Summarize Data - Design View

Filtering Data

Relevant information doesn't often show up immediately when you look at your pivot, that's why you probably need different views of the same dataset. Here comes filtering (top-left quadrant labeled Report Filter), which lets you easily include or exclude stuff from your dashboard. Multiple filters are obviously allowed and this one's by far one of my favorite feature, since built-in filters in the source table aren't that flexible as these are.

Pivot table filter example

Comparing Data

Last but not least: data comparison. Pivot tables are the best tool for comparing figures, especially when you make a chart out of them. Let's try it immediately and see how to calculate percentage difference between this year and last year profit (YoY) ending up with drawing a line chart to display compared yearly trends. We're going to use the top-right quadrant labeled Column Labels.

Comparing YoY Data - How to get Year on Year difference with a pivot table

Comparing Year on Year Data

Comparing YoY Data - Chart showing Year on Year difference and trends

Comparing Year on Year Data

Comparison Design View - how to get the YoY chart

Comparison Design View

Check also