- Published 12/23/2020
- 1st Edition
- Online video 978-0-13-691557-7
7+ Hours of Video Instruction
This video course takes youthe business user, business analyst, or data analystthrough an end-to-end solution for collecting, cleaning, importing, managing, and sharing data for operational and reporting purposes using Excel.
A story-based approach is used to teach you the data and analytics lifecycle. Learn the how and why behind the options available to you in Excel. These best practices go beyond just tips and tricks; learn pros and cons of the techniques to make you a more proficient analyst.
Instructors Chris Sorensen and Ammul Shergill have decades of experience helping companies and business professionals get the most out of their data. Together, Chris and Ammul will be your guides as you learn the fundamentals of data analysis with Excel and gain skills to design, build, and share Excel solutions.
Skill Level
Beginner-Intermediate
Learn How To
* Get and transform data
* Structure data for analysis in Excel
* Design data models
* Write formulas
* Create PivotTables and use PivotTable commands
* Create and format PivotCharts
* Build a reusable interactive dashboard
* Present meaningful, accessible analysis for effective decision making
Who Should Take This Course
* Excel users who need to level-up their analytics skills
* Professionals who need to produce accurate and timely analysis of business performance
* Business analysts who want to work on an end-to-end data analysis use case with Excel
* Business analysts who want to learn best practices for building sustainable data models
* MBAs who need to learn or refresh their knowledge of analyzing data with Excel
Course Requirements
This video assumes you have a basic understanding of Microsoft Excel, including menus and navigation, workbook components, and formulas.
Table of Contents
Introduction
Lesson 1: Getting Started with Data Analysis with Excel
Learning objectives
1.1 Discuss course style
1.2 Introduce the Data Lifecycle
1.3 Understand Excel versions
Lesson 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
Lesson 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
Lesson 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
Lesson 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
Lesson 6: Setting up for Collaboration and Distribution
Learning objectives
6.1 Inspect a document for issues
6.2 Check for accessibility and compatibility
6.3 Use collaboration and distribution features
6.4 Protect sheet contents and workbook structure
6.5 Setup a printable package
Summary