Schedule your class   Schedule >

Excel Dashboards for Business Intelligence

Course Length:  1.0 day

Exercise Files

This course is delivered using Office 2016. With the exception of the new modern look and touch screen support, there are few differences between the Office suites; therefore, users currently operating on Office 2010 and later will benefit from the material in this course.

Course Description

Learn to use tools that are available in Excel to create an interactive and dynamic Excel Dashboard. Build an environment that will allow users to go to a single page to gain insight and analysis through graphs and tables. Use Excel tools to display key performance indicators, data trends and comparisons on one screen.

Examine methods that make it easy for reports to be updated quickly (often at the click of just one button!). Learn to load, organize, and display data in a fully interactive, dynamic, and easy to read format.

Target Student:

The ideal student is comfortable navigating in Excel and is familiar with using mathematical and lookup formulas and functions. Students should have created standard charts and graphs in Excel, and should be somewhat familiar with Pivot Tables and Pivot Charts.

The materials learned in this course will apply to anyone who works with large volumes of data and needs to display the data in a meaningful and easy to understand report that can be updated and drilled down to gain specific insight.

You may also be interested in MCSA: BI Reporting

 

Prerequisites:

Excel Level 1, Level 2, Level 3, Excel Charts and Graphs, Excel Pivot Tables, and Excel Formulas. This is an Advanced level class. Students should have taken the listed classes or have equivalent knowledge.

Course Content

Lesson 1:  Introduction to dashboards

  • What are dashboards?
  • Key questions to ask before building a dashboard

Lesson 2:  Getting your Data Ready

  • Using static workbook data
  • Importing or connecting to external data

Lesson 3:   Building a Dashboard

  • Planning
  • Layout and design
  • Navigation and user interaction

Lesson 4:  Functions

  • Text Functions
  • Lookup Functions
  • Other Helpful functions

Lesson 5:  Using Pivot Tables, Pivot Charts and Slicers for Data Analysis

  • Creating a dashboard with Pivot Tables
  • Using Pivot Charts
  • Using Slicers for user interaction

Lesson 6:  Choosing the Right Chart for Your Data

  • Overview of chart types and their uses
  • Creating and modifying charts and graphs

Lesson 7:  Additional Dashboard Tools

  • Tables
  • Sparklines
  • Conditional Formatting
  • Data Validation Lists
  • Graphics
  • Scenarios
  • What-if Analysis
  • Interactive form Controls (check boxes, list boxes, buttons, etc.)

Lesson 8:  Using Macros to automate your dashboard

  • Create simple macros
  • Apply macros to your dashboard

Schedule your class   Schedule >