Excel for Mac: PivotTables in Depth

Learn how to summarize and analyze data using one of the most powerful and overlooked features in Excel for Mac: PivotTables.

Learn to quickly summarize, sort, and analyze data with one of the most overlooked features in Microsoft Excel for Mac: PivotTables. Follow along with Curt Frye as he shows you how to gain more valuable insights from your data—demystifying the complexity of PivotTables while demonstrating their power. Curt shows 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, and format cells using data bars, as well as how to create and format PivotCharts.

Note: This course was recorded in Office 365 for Mac. However, much of the content will be useful to those working with Office for Mac 2019 and 2016.

Introduction

• Use Excel PivotTables to summarize and analyze data
• What you should know

1. Create and Pivot PivotTables

• Introducing PivotTables
• Format data for use in a PivotTable
• Create a PivotTable
• Pivot a PivotTable
• Configure a PivotTable
• Connect to an external data source
• Manage PivotTables

2. Summarizing PivotTable Data

• Manage subtotals and grand totals
• Change the data field summary operation
• Summarize more than one data field
• Create a calculated field
• Drill down to the underlying data
• Use PivotTable data in a formula

3. Sort and Filter PivotTable Data

• Sort PivotTable data
• Filter a PivotTable field by selection
• Filter a PivotTable by rule
• Filter a PivotTable using a search filter
• Filter a PivotTable using slicers
• Format slicers
• Filter with report filter fields
• Clear and reapply PivotTable filters

4. Format and Print PivotTables

• Apply a PivotTable style
• Create a PivotTable style
• Change the PivotTable layout
• Change the data field number format
• Print a PivotTable

5. Apply Conditional Formats to PivotTables

• Highlight cells by applying a rule
• Highlight the top or bottom values in a PivotTable
• Format cells using data bars
• Format cells using color scales
• Format cells using icon sets
• Edit a conditional formatting rule
• Control how multiple rules are applied
• Delete a conditional formatting rule

6. Summarize Data Visually Using a PivotChart

• Create a PivotChart
• Pivot a PivotChart
• Filter a PivotChart
• Format a PivotChart
• Change a PivotChart chart type

Conclusion

• Next steps