98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel Dashboards for Business Intelligence training course
Face to face / Virtual closed & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
- 1 day Instructor-led
Syllabus
Who is this course for?
This course is best suited to anyone in Business Intelligence, data managers, data analysts, or Project Managers. This course would also suit anyone looking to extend their knowledge of Excel to understand some of the more advanced features and how they can be used to work together.
The downloadable post-course material also contains an expanded glossary of further ways to enhance your dashboards with Speedometer charts, Tornado diagrams, Waterfall charts and the Camera Tool. There are also further resources for connecting to external data sources.
You may also wish to consider one of our VBA training courses.
Prerequisites
Completion of our Excel Advanced course or equivalent knowledge.
Benefits
At the end of this course you will understand what makes a dashboard.You will learn how to build some of the most useful components when constructing your own dashboard reports.
During the course you will build three complete dashboard projects to give you inspiration for your own solutions.
Course Syllabus
What is a dashboard?
Common features of a dashboard
Why use Excel?
PivotTables and PivotCharts
Creating PivotTables
Formatting a PivotTable
Refreshing a PivotTable
Grouping fields
PivotCharts
Slicers and Timelines
Useful functions
Nested IFs
COUNTIFS & SUMIFS
EDATE
INDEX & MATCH
OFFSET
CHOOSE
Conditional Formatting
Formatting values
Colour Scales to show heatmaps
Icon Sets to show at a glance performance
Form Controls
Understanding the different controls
Using them on a dashboard
Working with Charts
Creating charts
Formatting charts
Secondary Axes
Combination charts
Creating chart templates
Working with Sparklines
Creating & modifying Sparklines
Customizing Sparklines
Design & Layout
Gridlines & outlines
Lining up Excel objects
Theming a dashboard
Sample Dashboard Projects
Worldwide Salary Index
Sales Performance Analysis
Helpdesk Efficiency
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Virtual training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
Training formats available
|
Testimonials
Equinix
Keeley Mcnaney,
Purchasing Supervisor
This course was brilliant, I think there should be a bit of time in these session where you use your own spreadsheets to work on so what you can start using what you have learnt while the trainer is there to ask a few questions.
Excel Dashboards for Business Intelligence
Catalyst Housing
Habib Hanif,
Capital Accountant
The course topics were very good and well structured. I can honestly say I learned so much on this course and am extremely satisfied with Jen's training. The course has given us the knowledge to put in practice in our reports.
Fantastic value for money.
Excel Dashboards for Business Intelligence
FastNet Ltd
Chris Lamb,
Finance Director
Jens was fab - I liked his approach and enthusiam, he related to us well and we were lucky there was only 2 people here.
Excel Dashboards for Business Intelligence
Training manual sample
Below are some extracts from our Excel training manuals.
Excel Dashboards for Business Intelligence
Unit 1: What is a dashboard?
Dashboards are a type of report. They help turn often large amounts of data into a digestible page that enables easy analysis. This form of data analysis can be part of Business Intelligence (BI).
Why use a dashboard?
Many companies have access to large amounts of data, perhaps in a data warehouse or spread across multiple file servers. Decision makers often want to cut through this data and look for patterns. Dashboards are an excellent way to turn large amounts of data into usable and actionable reports.
A dashboard is a visual interface that provides at-a-glance views into key measures relevant to a particular objective or business process. A dashboard consists of three key attributes.
- Displays data graphically (such as in charts). Provides visualizations that help focus attention on key trends, comparisons, and exceptions.
- Displays only data that is relevant to the goal of the dashboard.
- Contains predefined conclusions relevant to the goal of the dashboard and relieves the reader from performing their own analysis.
Common features of a dashboard
Dashboards will often have the following features:
- A single page. One page of information gives a high-level analysis. This one page view forces the most prominent trends to the front.
- Highly visual. To enable quick takeaways, the data is represented in a very visual way.
- Interactivity. Simple controls on the dashboard allow the user of the report to modify the view, perhaps choosing different parameters.
- Timely. Dashboards will be most effective by having a method to be as up to date as possible.
- Multi-format. To offer different views on the same data, different formats of data representation will be used, combining charts, tables, images and other visualisations.
- Relevant. Only data appropriate to the dashboard is going to be displayed.
Why use Excel to create dashboards?
There are a wealth of BI tools available to help create dashboards. However the variety of tools available, the ubiquity of Excel on people's computers, combined with the ability to export these interfaces to the Web make it a straightforward tool to use. A formalised BI platform could be overkill and too expensive for a company's needs. Excel is familiar and most companies have already budgeted for installing Microsoft Office.
Preparing to build a dashboard
Before you start
Before you create a dashboard, you should take time to research and define the following points:
- Define the message - what is the purpose?
- Establish the audience - what level of user is going to consume the report?
- Define the measurements - which metrics will support the message?
- List the data sources - build an inventory of data feeds you will need to create the measurements.
Separating data from presentation
Many people rush in creating charts and directly working with the data. This will work in the short term but you will find as you move forward it becomes harder and harder to maintain. Strive to separate the data sheet from the presentation sheet. Better still, have at least three sheets:
- Data -source data, cleaned but not extensively formatted. Often driven by a refreshable data feed. Data is often repetitive, with recurring items. Sometimes referred to as a ‘flat file'
- Analysis - interstitial ‘helper' tables that coerce the source data into a usable format. Often aggregating, totalling etc. This could be in the form of a PivotTable, or a cross-tabular table you have compiled with formulas and functions.
- Presentation - the formatted report that users will consume. Could be charts, simple tables, images, one line of commentary etc.
How to use this manual
Everyone will have a different use case, a different audience and a different set of reporting metrics required for their industry. In this manual you will walk through a number of Excel features commonly used in dashboard reporting. You can mix and match these features in any combination to create your own reports. In the appendix you will find three projects to give you some ideas of how you could develop a company dashboard. You should select the most suitable components for your report.
Different Excel versions
This manual focuses on the 2010 and 2013 versions of Excel. Most of the following is also possible in 2007, apart from a few PivotTable features such as Slicers and Timelines.
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...