Microsoft Excel Dashboard & Reports

Course Objectives

At the end of the training, participants are able to:

  • Analyze large amounts of data and report those result in a meaningful way
  • Get better visibility into data from different perspectives
  • Add interactive controls to show various views
  • Automate repetitive task and processes
  • Create eye-catching visualizations
  • Create impressive dashboards and what-if analyses

Target Audience

This course is aimed for Excel users who wish to work with data with a strong grasp of concepts such as aggregating data, table structures, filtering, sorting, and using formulas.

Methodology

Demonstration, practical learning, case discussion and exercises

Prerequisite(s) or equivalent knowledge:

Microsoft Excel Advance

Part 1: Getting Started with Excel Dashboards

Chapter 1: Introducing Dashboards

  • What are dashboards and report?
  • Establish user requirements
  • A quick look at dashboard design principles
  • Key questions to ask before distributing your dashboard

Chapter 2: Table Design Best Practices

  • Table design principles
  • Enhancing reporting with custom number formatting

Chapter 3: Using Excel Sparklines

  • Understanding sparklines
  • Applying sparklines
  • Creating sparklines
  • Customizing sparklines

Chapter 4: Chartless Visualization Techniques

  • Enhancing reports with conditional formatting
  • Using symbol to enhance reporting
  • Using excel’s camera tool

Part 2: Introducing Charts into Your Dashboards

Chapter 5: Excel Charting for the Uninitiated

  • What is a chart?
  • How excel handles charts
  • Parts of a chart
  • Basic steps for creating a chart
  • Working with charts

Chapter 6: Working With Chart Series

  • Specifying the data for your chart
  • Adding a new series to a chart
  • Deleting a chart series
  • Modifying the data range for a chart series
  • Understanding series names
  • Adjusting the series plot order
  • Charting a noncontiguous range
  • Using series on different sheets
  • Handling missing data
  • Controlling a data series by hiding data
  • Unlinking a chart series from its data range
  • Working with multiple axes

Chapter 7: Formatting and Customizing Charts

  • Chart formatting overview
  • Adjusting fills and borders
  • Formatting chart background elements
  • Formatting chart series
  • Working with chart titles
  • Working with a chart legend
  • Working with chart axes
  • Working with gridlines
  • Working data labels
  • Working with a chart data table

Chapter 8: Components That Show Trending

  • Rending dos and don’ts
  • Comparative trending
  • Emphasizing periods of time
  • Other trending techniques

Chapter 9: Components That Group Data

  • Listing top and bottom values
  • Using histograms to track relationships and frequency
  • Emphasizing top values in charts

Chapter 10: Components That Show Performance Against a Target

  • Showing performance with variances
  • Showing performance against organizational trends
  • Using a thermometer style chart
  • Using a bullet graph
  • Showing performance against a target range

Part 3: Advanced Dashboarding Concepts

Chapter 11: Developing your Data Model

  • Building a data model
  • Data model best practices
  • Excel functions for your data model
  • Working with excel tables

Chapter 12: Adding Interactive Controls to Your Dashboard

  • Getting started with form controls
  • Using the button control
  • Using the check box control
  • Using the combo box control
  • Using the list box control

Chapter 13: Marco-Charged Reporting

  • Why uses a macro?
  • Recording your first macro
  • Enabling macros in Excel
  • Excel macro examples

Part IV: Pivot Table Driven Dashboards

Chapter 14: Using Pivot Tables

  • Introducing the pivot table
  • Customizing your Pivot Table
  • Examples of filtering your data

Chapter 15: Using Pivot Charts

  • Getting started with Pivot Charts
  • Alternatives to Pivot Charts

Chapter 16: Adding Interactivity with Slicers

  • Understanding Slicers
  • Creating a Standard Slicer
  • Creating a Timeline Slicers
  • Using Slicers as Form Control

The course is conducted with the following:

Date: 13 – 14 October 2021

Time: 10.00am – 5.00pm

Fee: RM1099.00/pax

* To register, please fill in the ONLINE REGISTRATION FORM (please click) https://forms.gle/viyoTb189PCpW9A36

Leave a Comment

Your email address will not be published. Required fields are marked *