Data Analysis and Dashboards

Data Analysis and Dashboards

Course ID: HLT 10100

Course Length: 2 days

Course Description: With so much data available in business today, decision makers need to have the analysis at their fingertips to make informed decisions. This course teaches you how to take your raw data and perform the analysis using multiple functions, then have that analysis presented in a format where decisions can be made. This course covers 3 areas. The Data Layer: How to get your data into a useable format including importing, text to columns, concatenate, and date functions. The Analysis Layer: How to use formulas and functions such as Logical functions, SumIFs, CountIFs, Lookups, SumProducts, and an in-depth look at Pivot Tables and Pivot Charts. The Presentation Layer: This layer takes the analysis and presents the data with meaningful charts using macros, Index and Choose functions and Form Controls. You will leave this course with 4 completed dashboard presentations and the ability to choose different ways to show you data analysis.

Prerequisites: Students should be familiar with how to create basic formulas and should have completed the Microsoft Excel Level 2 course or have equivalent knowledge.

Delivery Method: Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

Course Content

Dashboards
Topic A: Design Principles
Topic B: Building the Dashboard

Formulas
Topic A: Order of Operation
Topic B: Absolute Cell References

Functions
Topic A: IF
Topic B: AND-OR Logic
Topic C: SumIF, SumIFs
Topic D: CountIF, CountIFs
Topic E: Vlookup and HLookup
Topic F: Match
Topic G: SumProduct
Topic H: Data Validation
Topic I: Choose

Pivot Tables
Topic A: Pivot Table Functions
Topic B: Show Value as
Topic C: Working with Grouping and Dates
Topic D: Slicers
Topic E: Refresh the Data Source
Topic F: Pivot Charts

Macros
Topic A: Trusted Locations
Topic B: The Macro Recorder

Form Controls
Topic A: Check Box Control
Topic B: Option Button Control
Topic C: List Box Analysis
Topic D: Combo Box Control

What if Analysis
Topic A: Goal Seek
Topic B: Scenario Manager