O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Microsoft Excel - Creating Dynamic Dashboards

Video Description

In this Microsoft Excel - Creating Dynamic Dashboards training course, expert author Erin Olsen teaches you how to use Excel features and techniques to create dynamic dashboards in order to present complex data. This course is designed for users that already have a basic working knowledge of Excel.
You will start by learning how to prepare the data, then jump into building the dashboard. Throughout the course, you will be working to create a dashboard that provides a summary of a person's health information. Erin will teach you how to optimize Excel data for use in dashboards, create visual dashboard components, and add interactivity to customize them for each user. This video tutorial also covers how to create a distribution histogram, use a waffle chart to show progress, and add actions to buttons. Finally, you will learn how to generate dashboards as output, produce them as a PDF, and present them with PowerPoint or share via OneDrive.
Once you have completed this computer based training course, you will have gained the knowledge and skills necessary to create your own dynamic dashboards in Excel. Working files are included, allowing you to follow along with the author throughout the lessons.

Table of Contents

  1. Course Introduction
    1. About This Series 00:03:06
    2. What You Will Learn 00:03:43
    3. About The Author 00:02:08
  2. Deciding On Dashboards
    1. Deciding On Dashboards 00:08:04
    2. Reports, Dashboards And Scorecards 00:06:42
    3. Pondering The Plan 00:03:21
    4. Selecting Measures And Metrics 00:08:36
  3. Preparing The Data
    1. Preparing The Data 00:03:06
    2. Data Modeling Basics 00:04:27
    3. Structuring The Workbook 00:05:06
    4. Connecting To Data - Part 1 00:04:36
    5. Connecting To Data - Part 2 00:08:34
    6. Simplifying Data With Named Ranges 00:06:55
    7. Leveraging The Efficiency Of Tables 00:07:54
    8. Harnessing The Power Of Pivot Tables 00:10:13
    9. Managing Connected Data 00:05:29
    10. Simplifying With Custom Number Formats 00:10:22
  4. Building Dashboards
    1. Building Dashboards 00:02:49
    2. Dashboard Design Rules 00:07:06
    3. Managing Branding With Custom Themes 00:07:31
    4. Creating The Navigation Bar - Part 1 00:06:55
    5. Creating The Navigation Bar - Part 2 00:06:57
    6. Creating The Navigation Bar - Part 3 00:05:07
    7. What Is In A Word? 00:08:00
    8. Building Out Worksheets And Navigation 00:01:57
    9. Summarizing Data Using Groups 00:02:25
    10. Configuring Show Values As For Pivot Tables 00:04:53
    11. Assigning Categories Using Nested If 00:04:03
    12. Identifying Top Ten Values 00:03:36
    13. Finding The Last Value In A Series 00:06:19
  5. Getting Visual
    1. Getting Visual 00:05:03
    2. Customizing Display With Text-Based Elements 00:05:47
    3. Alerting With Conditional Formatting 00:05:17
    4. Chart And Sparkline Review 00:08:09
    5. Creating Sparklines For Dashboards 00:04:05
    6. Creating Charts For Dashboards 00:07:26
    7. Using A Waffle Chart To Show Progress 00:06:54
    8. Using The Excel Camera 00:03:39
    9. Creating A Distribution Histogram 00:08:37
    10. Hitting The Mark With Bullet Graphs 00:05:09
  6. Adding Interactivity
    1. Adding Interactivity 00:03:26
    2. Displaying The Developer Tab 00:01:34
    3. Understanding Interactive Dashboard Controls 00:06:35
    4. Adding Actions To Buttons 00:08:03
    5. Creating Custom Content With Combo Boxes 00:07:44
    6. Simplifying With Check Boxes 00:05:17
    7. Facilitating Filtering With Slicers And Timelines 00:09:36
    8. Finalizing By Color-Coding, Hiding And Protecting Worksheets 00:05:33
  7. Generating Dashboards As Output
    1. Generating Dashboards As Output 00:02:31
    2. Producing Dashboards As PDF 00:04:27
    3. Presenting Dashboards With Powerpoint 00:02:59
    4. Sharing Dashboards Via OneDrive 00:05:04
  8. Conclusion
    1. What You Now Know 00:05:56