Mastering Microsoft Excel: Dashboard Creation

 

Dashboard is one of the advanced features of Excel that provides graphical
visualizations that provides at-a-glance views into key measures relevant to a particular objective or business process. This training will expose you how Dashboards are designed with key pieces of information that are relevant to the goal of dashboard and enable you to present your analysis and conclusions effectively

Improve Your Microsoft Excel Skill Now !

Course Objectives
  • Understanding Dashboards in MS Excel
  • Creating and Customizing Dashboards
  • Dashboards Visualization Techniques – Charts
  • Interactive Controls in Dashboards
  • Using PivotTables
Duration

2 days (9:00am to 5:00pm)

Venue

In-House Training

Who should attend?

Individuals who use Excel in their day-to-day work or those involve in Reporting and Data Analysis but would like to discover the power of Dashboard.

Learning Methodology
  • Instructor-led Training
  • Group Discussion
  • Hands-On
  • Exercises
Pre-requisite before Attending the Class

Excel Foundation or equivalent experience.

Quality Assured

Generate New Idea

Telephone Support

Certified Instructor

Improve Productivity

SMS Support

Practical Example

Updated Resources

Email Support

Course Outline

Module 1: Introducing Dashboards

  • What Are Dashboards & Reports?
    – Defining reports
  • Defining dashboards
    – Establish the User Requirements
    – Define the message(s)
    – Establish the audience
    – Define the performance measures
    – List the required data sources
    – Define the dimensions and filters
    – Determine the need for drill-down details
    – Establish the update schedule
  • A Quick Look at Dashboard Design Principles
  • Rule number 1: Keep it simple
    – Use layout and placement to draw focus
    – Format numbers effectively
    – Use titles and labels effectively
  • Key Questions to Ask Before Distributing Your Dashboard

Module 3: Visualization Techniques

  • Enhancing Reports with Conditional Formatting
    – Applying basic conditional formatting
    – Adding your own formatting rules manually
    – Show only one icon
    – Show Data Bars and icons outside of cells
    – Representing trends with icon sets
    – Using Symbols to Enhance Reporting
  • Using Excel’s Camera Tool
    – Finding the Camera tool
    – Using the Camera tool
    – Enhancing a dashboard with the Camera tool

Module 5: Introducing Charts (II)

  • Understanding Series Names
    – SERIES formula syntax
    – Changing a series name
  • 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
    – Converting a chart to a picture
    – Converting a range reference to arrays
  • Working with Multiple Axes
    – Creating a secondary value axis

Module 7: Developing Data Mode

  • Separating the data, analysis, and presentation layers
    – The Data Layer
    – The analysis layer
    – The presentation layer
  • Data Model Best Practices
    – Avoid storing excess data
    – Use tabs to document and organize your data model
    – Test your data model before building presentation components
  • Excel Functions for Your Data Model
    – Understanding lookup tables
    – The VLOOKUP function
    – The HLookup function
    – The SUMPRODUCT function
    – The Choose function
  • Working with Excel Tables
    – Converting a range to an Excel table
    – Converting an Excel table back to a range

Module 8: Adding Interactive Controls

  • Getting Started with Form Controls
    – Finding Form controls
    – Adding a control to a worksheet
  • Using the Button Control
  • Using the Check Box Control
    – Check box example: Toggling a chart series on and off
  • Using the Option Button Control
    – Option button example: Showing many views through one chart
  • Using the Combo Box Control
    – Combo box example: Changing chart data with a drop-down selector
  • Using the List Box Control
    – List box example: Controlling multiple charts with one selector

Module 2: Table Design Best Practices

  • Table Design Principles
    – Use colors sparingly
    – De-emphasize borders
    – Use effective number formatting
    – Subdue your labels and headers
  • Enhancing Reporting with Custom Number Formatting
    – Number formatting basics
    – Formatting numbers in thousands and millions
    – Hiding and suppressing zeros
    – Formatting dates and times
    – Adding conditions to customer number formatting

Module 4: Introducing Charts (I)

  • Basic Steps for Creating a Chart
    – Creating the chart
    – Switching the row and column orientation
    – Changing the chart type
    – Applying chart styles
    – Adding and deleting chart elements
    – Moving and deleting chart elements
    – Formatting chart elements
  • Working with Charts
    – Moving and resizing a chart
  • Working with Chart Series
    – Specifying the Data for Your Chart
    – Adding a New Series to a Chart
    – Adding a new series by extending the range highlight
    – Adding a new series using the Select Data Source dialog box
  • Modifying the Data Range for a Chart Series
    – Using range highlighting to change series data
    – Using the Select Data Source dialog box to change series data

Module 6: Formatting And Customizing Charts

  • Selecting chart elements
  • Adjusting Fills and Borders:
    – About the Fill tab
    – Formatting borders
  • Formatting Chart Background
    – Working with the chart area
    – Working with the plot area
  • Formatting Chart Series
    – Basic series formatting
    – Using pictures and graphics for series formatting
    – Additional series options
  • Working with Chart Titles
    – Adding titles to a chart
    – Changing title text
    – Formatting title text
    – Linking title text to a cell
  • Working with a Chart’s Legend
    – Adding or removing a legend
    – Formatting a legend
    – Changing the legend text
    – Deleting a legend entry
    – Identifying series without using a legend
  • Working with Chart Axes
    – Value axis versus category axis
    – Value axis scales
  • Working with Data Labels
    – Adding or removing data labels
    – Editing data labels
  • Working with a Chart Data Table
  • Adding and removing a data table

Module 9: Excel Macro

  • Building navigation buttons
  • Dynamically rearranging pivot table data
  • Offering one-touch reporting options

Module 10: Using Pivot Tables

  • Introducing the Pivot Table
  • Anatomy of a pivot table
    – Values area
    – Row Labels area
    – Column Labels area
    – Filter area
  • Creating the basic pivot table
    – Laying out the pivot table
    – Modifying the pivot table
    – Updating your pivot table
  • Pivot tables and worksheet bloat
  • Customizing Your Pivot Table
    – Changing the pivot table layout
    – Renaming the fields
    – Formatting numbers
    – Changing summary calculations
    – Suppressing subtotals
    – Removing all subtotals at one time
    – Removing the subtotals for only one field
    – Removing grand totals
    – Hiding and showing data items
    – Hiding or showing items without data
    – Sorting your pivot table
  • Examples of Filtering Your Data
    – Producing top and bottom views
    – Creating views by month, quarter, and year
    – Creating a percent distribution view
    – Creating a YTD totals view
    – Creating a month-over-month variance view
  • Using conditional formatting with pivot tables
    – Customizing conditional formatting

Require more support than our training courses?

Our full range of services can help you in many ways:

Custom Courses

  • We can work with you to deliver a tailored made course for your staff, not only in Microsoft Excel, but also PowerPoint, Word and other Microsoft Office software

Standard Business Documents

  • We can work with you to design standard business documents in Microsoft Excel, Word, and PowerPoint, making it quick and easy to produce professional, branded, stunning business documents

Automated Reporting

  • Unsure if Microsoft Excel gives you the information you need to run your business? We can design, develop and generate monthly reports in easily accessible formats such as PDF, giving you the information at your fingertips to run your business – no more days wasted in incomprehensible data reports

What You Can Expect From Our Course

Qualified Trainer

Our trainer is a Microsoft Office 2010 Master Specialist and well-sought after Microsoft Certified Trainer with more than 15 years of experience. He has conducted hundreds of Microsoft Office application classes with leading companies in the region and gets excellent ratings for
all his classes.

Small Class Sizes

We limit the number of participants to 15 pax per session. This means that participants will become more productive immediately after the training session.

Solutions

Real answers and solutions to your issues, not off the shelf training.

Ask Our Expert

Participants will be given an email for assistance on future inquiries.

Download PDF Version Here

Interested to know more about this course? Reach out to us now!

9 + 7 =