Excel for Mac 2016: Pivot Tables in Depth

Learn how to use PivotTables to summarize, sort, and analyze your data in Excel for Mac 2016.

Learn how to summarize, sort, and analyze your data in using one of the most overlooked features in Microsoft Excel for Mac 2016: PivotTables. Author Curt Frye shows you how to gain more valuable insights from your organization's data—demystifying the complexity of PivotTables while demonstrating their power. His tutorials show how to build PivotTables from Excel workbooks or external data sources, add calculated fields, filter results, and format your layout to make it more readable. Plus, learn how to use conditional formatting to intelligently highlight cells, apply color scales, create data bars, and share your PivotTable results in printouts or via PDF.

Introduction

• Welcome
• What you should know before watching this course
• Using the exercise files

1. Creating and Pivoting PivotTables

• Introducing PivotTables
• Formatting data for use in a PivotTable
• Creating a PivotTable
• Creating a Recommended PivotTable
• Pivoting a PivotTable
• Configuring a PivotTable
• Connecting to an external data source
• Managing PivotTables

2. Summarizing PivotTable Data

• Managing subtotals and grand totals
• Changing the data field summary operation
• Summarizing more than one data field
• Creating a calculated field
• Drill down into an underlying data collection

3. Sorting and Filtering PivotTable Data

• Sorting PivotTable data
• Filtering a PivotTable field by selection
• Filtering a PivotTable by rule
• Filtering a PivotTable using a search filter
• Filtering a PivotTable using slicers
• Formatting slicers
• Filtering with report filter fields
• Clearing and reapplying PivotTable filters

4. Formatting and Printing PivotTables

• Applying a PivotTable style
• Creating a PivotTable style
• Changing the PivotTable layout
• Changing the data field number format
• Printing a PivotTable

5. Applying Conditional Formats to PivotTables

• Highlighting cells by applying a rule
• Highlighting the top or bottom values in a PivotTable
• Formatting cells using data bars
• Formatting cells using color scales
• Formatting cells using icon sets
• Editing a conditional formatting rule
• Controlling how multiple rules are applied
• Deleting a conditional formatting rule

Conclusion

• Next steps