Overview

This three-day course is designed to transform students with a basic knowledge of Microsoft Excel into skilled data analysts. This is accomplished via two primary mechanisms:

  1. A focus on the most important and commonly used analytic techniques.

  2. Experiential, hands-on learning.

Topics covered include cleaning and manipulating data, visualizing data, and developing sophisticated models. Throughout the course, students will be trained using a case-based approach with either real or simulated data sets. In this workshop, students will be rolling up their sleeves and getting their hands dirty. With our hands-on learning, students will confidently solidify their knowledge so they can apply the techniques they have learned to their own work.


What You Will Learn

On the first day of the course, attendees will learn the essentials of data cleaning and matching functions, including how, when, and why to use them. They will also learn foundational analytic techniques such as aggregation-based analysis with Pivot Tables. The second day is focused on visualizing complex data sets using Excel and Power BI. Additionally, they will also learn how to frame and solve optimization problems via linear programming and how to create interactive reports for executives. Participants will finish the course on day three by learning how to implement a supervised learning algorithm, perform linear regression, and run simulations with tens of thousands of iterations to model events and outcomes.


Course Outline

This course will cover the following topics:

Day 1: Data Manipulation & Analysis

  • Sorting and Filtering

  • Deduplication

  • Data Cleaning and Matching

  • Conditional Aggregations

  • Advanced Aggregation with Pivot Tables

Day 2: Data Visualization & Modeling

  • Data Visualization Best Practices

  • Creating and Interpreting Analytic Charts

  • Introduction to Power BI

  • Advanced Visualization with Power BI

  • Linear Programming and Optimization in Excel

Day 3: Advanced Data Modeling

  • Regression Fundamentals

  • Performing Regression in Excel

  • Monte Carlo Simulation

  • Custom Macros and Visual Basic for Applications (VBA)


Course Requirements

This course will require participants to have access to computers with Microsoft Excel installed. Microsoft Excel should have add-ins enabled, such as the Analysis Toolpak. They should also have familiarity with Excel’s basic functionality, such as formulas, VLookups, and basic charts and graphs.


 

Interested in having us teach this course at your organization?