-
Take an in-depth look at PivotTables and PivotCharts, two powerful data analysis tools in Excel. Learn how to more efficiently evaluate your data, by using them together.
While the power and time-saving value of PivotTables appeals to anyone who analyzes data in Excel, many users are too intimidated by the feature to add it to their workflow. In this courseâthe first installment in a two-part seriesâinstructor Chris Dutton breaks down how to use PivotTables, as well as how to add PivotCharts to the mix to summarize your data visually. Throughout the course, Chris shares practical tips and demonstrations that help you grasp how these tools are used in the real world, as well as how PivotTable values are actually calculated and displayed. Learn how to prepare your raw data for analysis; leverage formatting, sorting, filtering, and grouping options; display values in multiple ways; create PivotCharts; and much more.
Overview
Syllabus
-
Introduction
- Part 1: Introduction
- Getting to know IMDb
- Setting expectations
- Why PivotTables?
- Structuring your source data
- Inserting a PivotTable
- Navigating the Field List
- Analyze and design options
- Selecting, clearing, moving, and copying
- Refreshing and updating PivotTables
- Pro tip: Growing source data
- How PivotTables actually work, part 1
- How PivotTables actually work, part 2
- Number formatting
- Pro tip: Formatting empty cells
- Table layouts and styles
- Pro tip: Tabular layouts
- Customizing headers and labels
- Conditional formatting
- Pro tip: Data bars with invisible text
- Advanced conditional formatting
- Sorting options
- Troubleshooting incorrect sorting
- Label filters and manual selections
- Pro tip: Filtering with wildcards
- Value filters
- Pro tip: Enabling multiple filters
- Grouping options
- Automatic date grouping
- Filtering with slicers and timelines
- Breaking out report filter pages
- Value summarization modes
- Pro tip: Avoiding the "count of" trap
- Value calculation modes
- Show Values As: % of Column/Row
- Show Values As: % of Parent
- Show Values As: Difference From
- Show Values As: Running Total
- Show Values As: Rank
- Show Values As: Index
- Inserting calculated fields
- Calculations in PivotTables vs. source data
- Calculating using counts, part 1
- Calculating using counts, part 2
- Inserting calculated items
- Solve order and list formula tools
- Intro to PivotCharts
- Bar and column PivotCharts
- Pie and donut PivotCharts
- Clustered bar PivotCharts
- Pro tip: Prevent charts from resizing
- Changing chart types on the fly
- Stacked area PivotCharts
- Chart layouts and styles
- Moving PivotCharts to new sheets
- Applying slicers to multiple charts
- Designing interactive dashboards
- Part 1: Conclusion
