Microsoft Excel - Pivot Tables

What is a PivotTable - and Why Do You Care?

PivotTables open up your world to quickly summarize data and build solutions that others can explore.

Try it!

---

The PivotTable Life Cycle

Understanding how the life cycle of a PivotTable solution is different than an ad-hoc reporting solution.

---

Creating Your First PivotTable

Creating your first PivotTable is the first step to unlocking serious Excel power in data optimization.

---

Field Well Options

Determining the Field Well options you’d like to use to view your PivotTable data.

Source Data Best Practices

Setting up your data sets to ensure that they will be able to be consumed by a PivotTable.

---

Using Excel Ranges

Choosing between building quick and dirty PivotTables or future-proofing PivotTables for future updates.

Try it!

---

Preparing Source Data

When you need to aggregate multiple tables or create new fields in order to display properly in a PivotTable.

---

Using External Data

Connecting to external data sources (such as databases), and pulling the data into Excel.

---

Managing Power Queries

Updating a Power Query solution to a new file path, allowing us to refresh the file with a click.

Totals and Sub-Totals

Use to turn totals and subtotals on/off where needed in your PivotTable.

---

Choosing PivotTable Layouts

Determining the basic layout you’d like to use to display your PivotTable data.

---

Grouping

Use to group different row or column fields together for a more logical display.

Aggregation

When you wish to show different aggregation on your PivotTable fields than what is provided by default.

---

Running Totals

When you need to display a running total on your PivotTable.

---

Percent of x Calculations

When you need to display a value as the % of another value on your PivotTable.

---

Difference From Calculations

When you need to display values that are a difference from another value in a PivotTable. This is very useful for showing growth/decline from previous periods.

---

Percentage Difference From Calculations

When you need to display values that are a percentage of difference from another value in a Pivot Table.  This is very useful for showing growth/decline from previous periods.

---

Top and Bottom x Items

When you want to filter your PivotTable to show the top/bottom x items or top/bottom x% of items in the data set.

---

Calculated Fields

Understanding how to create calculated fields in a PivotTable.

---

Calculated Items

Understanding how to create calculated items in a PivotTable.

Value Field Formats

Various ways to display numbers in the values area of a PivotTable.

---

Preserving Cell Formats on Update

How to force a PivotTable to preserve cell formats and column widths when it is updated.

---

PivotTable Styles

Using pre-defined styles with a PivotTable.

Try it!

---

Custom PivotTable Styles

Creating your own style to use with a PivotTable.

---

Conditional Formatting on PivotTables

Basics of applying conditional formats to a PivotTable.

Basic Sorting

Basic sorting of row and column headers in a PivotTable.

---

Custom Sorting

Creating custom sort orders for a PivotTable.

---

Sorting Values

Sorting the values area of a PivotTable.

Report Filters

Using Report Filters to narrow down results in a PivotTable.

---

Built-in Filter Controls

Using row and column filters to narrow down results in a PivotTable.

---

Slicers

Slicers are user-friendly filters for PivotTables.

---

Timelines

Timelines are date specific filters for PivotTables.

---

Filtering Multiple PivotTables

Filtering multiple PivotTables at once with Slicers.

Show Details

Using Show Details to extract details from a summarized value.

---

GETPIVOTDATA

Using the GETPIVOTDATA function to extract specific fields from a PivotTable.

Creating PivotCharts

Creating PivotCharts out of a PivotTable.

Automating Refresh

Automating PivotTable refresh operations.

---

Pre-release Considerations

Items to consider before releasing your file to users.