Microsoft Excel - Dashboards
Course Description
This is a Microsoft Excel Dashboards course. A dashboard report is a way to visually present critical data in summary form so that you can make quick and effective decisions. Some Excel users may not know what a powerful tool Excel can be for dashboard reporting. This course teaches delegates how to use Dashboards in Excel. The teaching approach contains a mix of lectures, practical and one-to-one work and varies according to the people attending. For the most part, candidates are able to work at a pace which best suits them, and emphasis is placed upon quality of learning, rather than quantity.
Course Duration
1 day
Course Objectives
The course outline below lists the topics that trainees will be competent in performing on completion of this course.
Course Content
MOVING FROM SPREADSHEETS TO DASHBOARDS
What are Dashboards and Reports?
Define the data dimensions and filters
Dashboard Design Principles
Use layout and placement to draw focus
Format numbers effectively
Use titles and labels effectively
DEVELOPING YOUR DATA MODEL
Building a Data Model
Data Model Best Practices
Excel Functions for Your Data Model
The VLOOKUP function
The HLOOKUP function
The SUMPRODUCT function
The CHOOSE function
The OFFSET function
The MATCH function
The INDEX function
Working with Excel Tables
EXCEL CHARTS
Creating a chart
Switching the row and column orientation
Changing the chart type
Adding and deleting chart elements
Formatting chart elements
Understanding Chart Types
Creating Combination Charts
Creating and Using Chart Templates
Charting a Noncontiguous Range
EXCEL CHARTS CONTINUED
Converting a chart to a picture
Using graphics for series formatting
Linking title text to a cell
Working with Chart Axes
Working with Gridlines
Working with Data Labels
USING PIVOT TABLES
Creating a basic pivot table
Customizing Your Pivot Table
Formatting numbers
Changing summary calculations
Suppressing subtotals
Hiding and showing data items
Sorting your pivot table
Producing pivot table views
CHARTLESS VISUALIZATION TECHNIQUES
Dynamic Labels
Linking Formulas to Text Boxes
Excel’s Camera Tool
Formula-Driven Labels
In-cell charting
Using symbols
CREATING INTERACTIVE DASHBOARD COMPONENTS
Adding a scroll bar to a data window
Adding option buttons to a chart
Using a spin button to sort data
Using a combo box drop-down menu
Using a list box control
Using the Check Box Control
AUTOMATING DASHBOARDS AND REPORTS
Recording macros
Assigning a macro to a button
Building navigation buttons
Dynamically rearranging pivot table data
SECURING YOUR DASHBOARDS AND REPORTS
Securing access to the entire workbook
Limiting access to specific ranges
Protecting the workbook structure
Linking Excel Dashboards to PowerPoint
Distributing Your Dashboards Via PDF
What are Dashboards and Reports?
Define the data dimensions and filters
Dashboard Design Principles
Use layout and placement to draw focus
Format numbers effectively
Use titles and labels effectively
DEVELOPING YOUR DATA MODEL
Building a Data Model
Data Model Best Practices
Excel Functions for Your Data Model
The VLOOKUP function
The HLOOKUP function
The SUMPRODUCT function
The CHOOSE function
The OFFSET function
The MATCH function
The INDEX function
Working with Excel Tables
EXCEL CHARTS
Creating a chart
Switching the row and column orientation
Changing the chart type
Adding and deleting chart elements
Formatting chart elements
Understanding Chart Types
Creating Combination Charts
Creating and Using Chart Templates
Charting a Noncontiguous Range
EXCEL CHARTS CONTINUED
Converting a chart to a picture
Using graphics for series formatting
Linking title text to a cell
Working with Chart Axes
Working with Gridlines
Working with Data Labels
USING PIVOT TABLES
Creating a basic pivot table
Customizing Your Pivot Table
Formatting numbers
Changing summary calculations
Suppressing subtotals
Hiding and showing data items
Sorting your pivot table
Producing pivot table views
CHARTLESS VISUALIZATION TECHNIQUES
Dynamic Labels
Linking Formulas to Text Boxes
Excel’s Camera Tool
Formula-Driven Labels
In-cell charting
Using symbols
CREATING INTERACTIVE DASHBOARD COMPONENTS
Adding a scroll bar to a data window
Adding option buttons to a chart
Using a spin button to sort data
Using a combo box drop-down menu
Using a list box control
Using the Check Box Control
AUTOMATING DASHBOARDS AND REPORTS
Recording macros
Assigning a macro to a button
Building navigation buttons
Dynamically rearranging pivot table data
SECURING YOUR DASHBOARDS AND REPORTS
Securing access to the entire workbook
Limiting access to specific ranges
Protecting the workbook structure
Linking Excel Dashboards to PowerPoint
Distributing Your Dashboards Via PDF
Who should attend?
This course provides attendees with information on how to build Microsoft Excel Dashboards to present information in a clear and easy to understand view.
Pre-requisites
Those attending should have an understanding of Microsoft Excel to an intermediate level, be competent Windows users and be familiar with the basics of file and disk management. Prior completion of c2’s Excel Intermediate course would be beneficial but not compulsory.