Data Analysis Fundamentals with Excel (Video)

Data Analysis Fundamentals with Excel (Video)

by Chris Sorensen / Ammul Shergill
Released December 2020
Publisher(s): Microsoft Press
ISBN: 0136915566

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

7+ Hours of Video Instruction

This video course takes you—the business user, business analyst, or data analyst—through 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.

Download the companion files for this LiveLesson from https://www.informit.com/store/data-analysis-fundamentals-with-excel-video-9780136915577.

About the Instructors

Chris Sorensen, MCSE (Data Management and Analytics) and Microsoft Certified Trainer, is the founder and president of Iteration Insights. Chris is a consultant, architect, educator, author, and coach with nearly 20 years of experience in the analytics space.

Ammul Shergill, MBA, is a principal consultant at Stratsyn Consulting where he designs, implements, and supports solutions for strategic business analysis.

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

  1. Introduction
    1. Data Analysis Fundamentals with Excel: Introduction
  2. Lesson 1: Getting Started with Data Analysis with Excel
    1. Learning objectives
    2. 1.1 Discuss course style
    3. 1.2 Introduce the Data Lifecycle
    4. 1.3 Understand Excel versions
  3. Lesson 2: Using Get and Transform Data
    1. Learning objectives
    2. 2.1 Introduce Get and Transform data
    3. 2.2 Load an Excel spreadsheet
    4. 2.3 Load a folder of data
    5. 2.4 Load data from a website
    6. 2.5 Understand data source settings
    7. 2.6 Introduce the Power Query Editor
    8. 2.7 Remove columns
    9. 2.8 Filter rows
    10. 2.9 Rename columns and set data types
    11. 2.10 Add new columns
    12. 2.11 Append queries
    13. 2.12 Merge queries
    14. 2.13 Transform and cleanse data
    15. 2.14 Unpivot data
    16. 2.15 Better manage Get and Transform
  4. Lesson 3: Structuring and Referencing Data for Analysis within Excel
    1. Learning objectives
    2. 3.1 Review cell reference styles and types
    3. 3.2 Use names in Excel
    4. 3.3 Structure data within Excel tables
    5. 3.4 Review formulas, functions, and compatibility
    6. 3.5 Spill data with dynamic arrays
    7. 3.6 Troubleshoot spill errors
    8. 3.7 Understand implicit intersection
    9. 3.8 Apply concepts for a data analysis solution
  5. Lesson 4: Utilizing Best Practices for Preparing and Analyzing Data
    1. Learning objectives
    2. 4.1 Design complex Excel models
    3. 4.2 Author formulas for complex models
    4. 4.3 Implement and maintain data integrity
    5. 4.4 Create a reusable data quality dashboard
    6. 4.5 Design a Budget and Forecast model
    7. 4.6 Identify required data structures and functions
    8. 4.7 Populate data within the model
    9. 4.8 Highlight results and data insights
  6. Lesson 5: Authoring Interactive Reports
    1. Learning objectives
    2. 5.1 Create and understand basic PivotTables
    3. 5.2 Interact with a PivotTable
    4. 5.3 Understand layout options
    5. 5.4 Format PivotTables
    6. 5.5 Use PivotTable commands
    7. 5.6 Filter and sort data in a PivotTable
    8. 5.7 Review chart types
    9. 5.8 Create PivotCharts
    10. 5.9 Format PivotCharts
    11. 5.10 Create an interactive dashboard
  7. Lesson 6: Setting up for Collaboration and Distribution
    1. Learning objectives
    2. 6.1 Inspect a document for issues
    3. 6.2 Check for accessibility and compatibility
    4. 6.3 Use collaboration and distribution features
    5. 6.4 Protect sheet contents and workbook structure
    6. 6.5 Setup a printable package
  8. Summary
    1. Data Analysis Fundamentals with Excel: Summary

Product information

  • Title: Data Analysis Fundamentals with Excel (Video)
  • Author(s): Chris Sorensen / Ammul Shergill
  • Release date: December 2020
  • Publisher(s): Microsoft Press
  • ISBN: 0136915566