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
Use the bottom-left quadrant (labeled Row Labels) for 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.
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).
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.
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 YoY Data - Chart showing Year on Year difference and trends
Comparison Design View - how to get the YoY chart
Check also