Using pivots without value calculations is like driving a Ferrari but never taking it out of second gear. Sure, you can do quite a bit by simply aggregating and summarizing your values, but calculations like Difference From, % of Parent, Running Total, and Rank allow you to explore your data from entirely different perspectives. In this tip, explore several of the most common PivotTable value calculations.
- [Instructor] All right, let's talk about … one of the most useful and powerful pivot table tools, … analyzing your data using value calculations. … Now, in a nutshell, value calculations … allow you to display underlying raw values … from all sorts of different angles. … So instead of just displaying a standard SUM … or COUNTOF or AVERAGE, you can show those value-based fields … in very interesting ways … like the percent of a given column or row, … or the percent of a given parent category. … You can show values as the difference from … or percent difference from a given base item. … So that's great for things like month over month … or year over year changes. … You can calculate running totals, … which displays those values as a cumulative total … within a given base field, … or you can show those as ranks, which, just like it sounds, … doesn't show the actual volume, … but it shows the rank of that volume based on low to high … or high to low rules. … Now, keep in mind, this isn't a comprehensive list. …
Note: The instructor uses Office 365 ProPlus. Some features may not be available in all versions of Excel.
- Customizing a PivotTable field list
- Grouping dates and values
- Enabling multiple filters
- Conditional formatting
- Refreshing source data
- Adding custom sort lists