The secrets to generate the most automated Excel Dashboards

Name: The secrets to generate the most automated Excel Dashboards
Rating: 3.7 (70 reviews)

Learn all the functionalities, shortcuts, formulas, interfaces and interactions between objects to generate dashboards

Created byDan Prodi, Pipi Rahayu

Last updated 11/2018

English

What you'll learn

Create their own Excel Dashboards
Create Excel reports with charts and formulas
Create automated reports in Excel
Created automated reports and applications with Excel functions and VBA

Course content

14 sections • 173 lectures • 15h 45m total length

Introduction2:35
About the Author1:44
Organization of the course7:47
What you need to know before starting3:00
What is a Dashboard6:57
Summary of useful Lookup & Reference Formulas9:33

Overview of the case study6:27
Excel dashboard to monitor testing activities of a software development project - Overview of the case study
Preparation of the data (part I)10:54
Preparation of data (Part II)4:47
Format the tables of data (Part I)4:46
Format the tables of data (Part II)5:16
Format the tables of data (Part III)10:56
Format the tables of data and set formulas (Part IV)7:59
Prepare the calculation worksheet (Part I)14:54
Prepare the calculation worksheet (Part III)2:09
Prepare the calculation worksheet (Part IV)6:29
Prepare the calculation worksheet (Part V)6:09
Prepare the calculation worksheet (Part VI)1:46
Prepare the calculation worksheet (Part VII)1:27
Use conditional formatting to highlight the date of today in a table
Generate the charts for the Dashboard (Part I)1:29
Generate the charts for the Dashboard (Part II)11:04
Make the chart be updated dynamically11:55
Prepare the ticket table4:29
Prepare the ticket summary table3:29
Prepare the formulas for the ticket figures6:18
Define formulas for totals and references6:18
Define formulas for daily figures12:45
Customize the dashboard (Part I)7:49
Customize the dashboard (Part II)2:25
Customize the dashboard (Part III)9:26
Customize the dashboard (Part IV)4:14
Customize the dashboard (Part V)3:21
Re-elaborate some formulas for the calculation worksheet11:30

Overview of the case study5:59
Preparation of data5:02
Prepare the calculation worksheet (Part I)8:33
Prepare the calculation worksheet (Part II)6:36
Prepare the calculation worksheet (Part III)3:16
Prepare the calculation worksheet (Part IV)1:41
Prepare the calculation worksheet (Part V)5:34
Prepare the dashboard (Part I)4:36
Prepare the dashboard (Part II)3:53
Prepare the dashboard (Part III)2:19
Prepare the dashboard (Part IV)5:21
Prepare the dashboard (Part V)7:36
Prepare the dashboard (Part VI)3:16
Prepare the dashboard (Part VII)4:42
Prepare the dashboard (Part VIII)4:13
Prepare the dashboard (Part IX)4:49
Prepare the dashboard (Part X)2:32
Prepare the dashboard (Part XI)4:55
Prepare the dashboard (Part XII)4:11
Prepare the dashboard (Part XIII)1:56
Prepare the dashboard (Part XIV)8:31

Overview of the case study and preparation of data9:26
Preparation of data (Part II)10:44
Prepare the calculation worksheet6:34
Prepare the dashboard (Part I)2:30
Prepare the calculation worksheet and the dashboard (Part II)5:45
Prepare the calculation worksheet and the dashboard (Part III)4:45
Prepare the dashboard (insert and customize charts)4:43
Prepare the calculation worksheet (call durations)3:52
Prepare the dashboard (Part IV)9:55
Prepare the dashboard (Part V)4:54
Prepare the dashboard (Part VI)3:35
Prepare the dashboard (Part VII)11:25
Prepare the dashboard (Part VIII)12:42

Overview of the case study and definition of project management3:13
Preparation of the Gantt chart (Part I)5:23
Preparation of the Gantt chart (Part II)7:06
Preparation of the Gantt chart (Part III)2:04
Mark the date of today on a Gantt chart
Preparation of the Gantt chart (Part IV - display the project progress)9:05
Preparation of the Gantt chart (Part V - display the milestones)4:24
Prepare the dashboard (Part I - the milestone summary)8:29
Prepare the dashboard (Part II)4:54
Prepare the dashboard (Part III)3:37
Prepare the dashboard (Part IV)4:06
Prepare the resource plan sheet (Part I)1:42
Prepare the resource plan sheet (Part II) and the burn down chart8:59
Prepare the resource plan sheet (Part III)3:37
Prepare the resource plan sheet (Part IV)4:08
Prepare the resource plan sheet (Part V)3:00
Prepare the meta data for the resource plan sheet (Part I)7:35
Prepare the meta data for the resource plan sheet (Part II)3:28
Prepare the resource plan sheet (Part VI)7:56
Prepare the risk assessment sheet (Part I)6:04
Prepare the risk assessment sheet (Part II)7:21
Finaliye the risk assessment sheet and report figures to the dashboard4:39
Report risk assessment figures to the dashboard (Part I)3:45
Report risk assessment figures to the dashboard (Part II)1:06
Finalize the dashboard with project status information12:25

Overview of the case study0:51
Preparation of data and setup the calculation sheet2:51
Prepare the calculation worksheet (Part II)4:49
Prepare the dashboard (Part I)2:00
Prepare the dashboard (Part II)6:30
Prepare the dashboard (Part III)0:58
Prepare the dashboard (Part IV)1:36
Prepare the calculation worksheet (Part III)2:18
Prepare the dashboard (Part V)7:07
Prepare the dashboard (Part VI)3:42
Prepare the dashboard (Part VII)9:04

Requirements

Basic knowledge of Windows or OSX for MAC
Basic knowledge of Microsoft Excel

Description

In this course the students will learn how to create automated Excel dashboards. In details:

how to connect form commands or ActiveX to values in cells and ranges
how to generate automated charts which will be updated just modifying a single value in one or a few cells
how to create dynamic images, which will be updated by changing some values in cells
how to use Excel functions and formulas to create tables and data sources and summary reports
how to use combined lookup and reference formulas to retrieve joined values between different tables
how to create variable charts with data series generated dynamically

The course is conducted through examples.

These examples covers Excel dashboards in the most popular business areas, in details:

Dashboard testing activities
Interactive Dashboard in Excel using Hyperlinks
Interactive Sales Chart
several KPI dashboards
Make Dynamic Dashboards using Pivot Tables & Slicers
Customer Service Dashboard
Project Management Dashboard / Project Status Report
Financial Dashboard

During the course and especially at the end some example will be done implementing Visual Basic for Applications (VBA). The programming language to create macros and functions to expand the functionalites of Excel.

Who this course is for:

High school or university students
Financial controller
Project Managers
Sales managers or employees
Business analysts
Project controllers
Risk analysts
Bank clarks

The secrets to generate the most automated Excel Dashboards

What you'll learn

Explore related topics

Course content

Introduction6 lectures • 32min

User Acceptance Tests Dashboard27 lectures • 3hr 1min

Interactive Dashboard in Excel using Hyperlinks5 lectures • 20min

Dashboard with Interactive Sales Chart6 lectures • 36min

Complex KPI Excel Dashboard21 lectures • 1hr 40min

Make Dynamic Dashboards using Pivot Tables & Slicers6 lectures • 35min

Customer Service Dashboard13 lectures • 1hr 31min

KPI Dashboard2 lectures • 10min

Project Management Dashboard / Project Status Report24 lectures • 2hr 8min

KPI Dashboard11 lectures • 42min

Requirements

Description

Who this course is for: