Excel Dashboards and Reports

Excel Dashboard Training Course

Excel Dashboard can provide an at-a-glance view that helps you quickly monitor the key indicators of performance. It contains small graphs on one page and management can easily visualize trends and correlate data. It is a great way to save time because users can replace the old data with the new one in each month on the dashboard templates. So the small businesses can also benefit with it.

The instructor will lead students using different exercises to create tabular, summary, matrix and join reports. Students will learn advanced reporting functionality such as charting, report summary fields and building custom report types.

Excel Dashboard Training Course

What you'll do : Exercises Explained

Exercise : Using form controls

In this exercise, student will learn using form controls to create various interfaces for data input. With the help of these interfaces, end users can operate the dashboard more efficient.

Exercise: Data extraction

This exercise demonstrates the techniques of feeding data from the raw data to the dashboard. It works as placing a small window in the dashboard, users can browse and scroll the underlying data with the scrollbar inside the ”window”.

Exercise: Interface design

In this exercise, student will learn how to an interface which allows users to see the results from different factors. The interface consists of several form controls and the results are calculated dynamically with the appropriate functions and formulas.

Exercise: Put it to work

The exercise wraps up what students learned in class and put them to work. Students are required to create a dashboard which can show results for different input, and present them pictorially with a series of charts. Through the exercise, students will gain a more concrete concept how to create a management dashboard from start to finish.

What you’ll learn

Getting started

  • Defining Dashboards and Reports
  • Guidelines on contructing dashboards
  • Data Modelling and different data layouts
  • The most used Excel Functions in building dashboard

Building Basic Dashboard Components

  • PivotTable
  • Creating views by month, quarter and year
  • Creating a percent distribution view
  • Creating a running (YTD) totals view
  • Excel Charts
  • Creating a combination chart
  • Formatting chart elements
  • PivotCharts

Data Formatting techniques

  • Using Highlight Cells Rules
  • Top / Bottom rules
  • Using data bars, color scales and icon sets
  • Customizing your own formatting rules
  • Using formulas in formatting rules
  • Using conditional formatting with PivotTables

Adding interactivity

  • Creating dynamic labels ( form controls or formula driven )
  • In-cell charting
  • Creating visualization with some special fonts
  • Building interactive interface
  • Using form controls
  • Display charts and data interactively with button and checkboxes

Trending Techniques

  • Side-by-side time comparisons
  • Stacked time comparisons
  • Using a secondary axis
  • Smoothing data

Performance visualization Techniques

  • Performance with variances
  • Using thermometer-Style charts
  • Using bullet graph

Powering dashboard with Macros

  • Why Use a Macro
  • Introducing the Macro Recorder
  • Recording macros with absolute and relative references
  • Assigning a macro to a button
  • Building navigation buttons
  • Creating an interactive pivottable with macro

EDB4085 - 廣東話 22 Aug enrol
EDB4092 - 廣東話 23 Sep enrol
EDB4105 - 廣東話 12 Oct enrol
EDB4104 - 廣東話 22 Oct enrol
  Excel VBA X ChatGPT/GPT
  Excel-formulas & functions
  Financial Accounting with Excel