Table of contents
- Introduction
- 1: Getting Started with Data Analysis with Excel
- 2: Using Get and Transform Data
- Learning objectives
- 2.1 Introduce Get and Transform data
- 2.2 Load an Excel spreadsheet
- 2.3 Load a folder of data
- 2.4 Load data from a website
- 2.5 Understand data source settings
- 2.6 Introduce the Power Query Editor
- 2.7 Remove columns
- 2.8 Filter rows
- 2.9 Rename columns and set data types
- 2.10 Add new columns
- 2.11 Append queries
- 2.12 Merge queries
- 2.13 Transform and cleanse data
- 2.14 Unpivot data
- 2.15 Better manage Get and Transform
- 3: Structuring and Referencing Data for Analysis within Excel
- Learning objectives
- 3.1 Review cell reference styles and types
- 3.2 Use names in Excel
- 3.3 Structure data within Excel tables
- 3.4 Review formulas, functions, and compatibility
- 3.5 Spill data with dynamic arrays
- 3.6 Troubleshoot spill errors
- 3.7 Understand implicit intersection
- 3.8 Apply concepts for a data analysis solution
- 4: Utilizing Best Practices for Preparing and Analyzing Data
- Learning objectives
- 4.1 Design complex Excel models
- 4.2 Author formulas for complex models
- 4.3 Implement and maintain data integrity
- 4.4 Create a reusable data quality dashboard
- 4.5 Design a Budget and Forecast model
- 4.6 Identify required data structures and functions
- 4.7 Populate data within the model
- 4.8 Highlight results and data insights
- 5: Authoring Interactive Reports
- Learning objectives
- 5.1 Create and understand basic PivotTables
- 5.2 Interact with a PivotTable
- 5.3 Understand layout options
- 5.4 Format PivotTables
- 5.5 Use PivotTable commands
- 5.6 Filter and sort data in a PivotTable
- 5.7 Review chart types
- 5.8 Create PivotCharts
- 5.9 Format PivotCharts
- 5.10 Create an interactive dashboard
- 6: Setting up for Collaboration and Distribution
- Summary
Introduction
Introduction