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.