Great Shelford, Cambridge / Online
1 day course
£495.00 (ex. VAT)

This extensive 2 day course is designed for advanced users who need a comprehensive understanding of how to build dashboards in Microsoft Excel, from initial basic concepts to advanced techniques. Attendance of our Excel Advanced courses is required or equivalent knowledge.

Creating dashboards in Excel offers the perfect combination of customisation and engaging visual representation. With our qualified experienced trainers this course will provide you with the skills you need to craft your own dashboards and provide customised high-level management reports, using techniques such as utilising ActiveX controls and building interactive charts.

At the end of this course, you will be able to manipulate tables of data & use relevant functions, structure data efficiently in order to create dashboards, use advanced charting techniques to visualise data and create interactive dashboards.

Course Outline

Overview

  • Excel Dashboards, what they can do for you?
  • Dashboard Design – considerations & tips

Preparing data for the dashboard

  • Connecting to data sources

A review of essential functions:

  • Logical functions: IF, AND, OR
  • Dealing with Errors
  • Statistical functions: AVERAGE/IF, COUNT/IF, MEDIAN, TREND, FORECAST
  • Look up functions: VLOOKUP, INDEX, MATCH, OFFSET
  • Date functions: TODAY, DATE, EDATE
  • Text functions: CLEAN, TRIM, LEFT, MID, RIGHT
  • Importance of range names and Name Manager

Data validation:

  • Data integrity issues
  • Setting validation
  • Cleaning invalid data
  • Protecting your Data
  • Working with sheets

Essential tools for dashboards

Conditional formatting:

  • Manager
  • Tips and tricks

Excel Tables

Pivot tables – Advanced features:

  • Comprehensive overview
  • Grouping fields
  • Calculated fields
  • Using slicers
  • Pivot Charts

Charts and chart types

  • Overview of chart types and their suitability to dashboards
  • Bar, Line, Bubble charts
  • Advance charting:
  • Combining different chart types within one plot area
  • Creating user defined charts, Saving and using chart templates
  • Interactive charts
  • Charts tips and tricks

Adding ActiveX controls

  • Why use ActiveX controls
  • Add dashboard interactivity via controls:
  • Dropdown lists
  • Check boxes
  • Spin buttons

Macros

  • Macro basics
  • Automating with useful macros
  • Creating navigation tools
  • through protected workbooks

User Defined Functions

  • Importance of user defined functions
  • VBA functions basics

Book This Course

Public Course Dates

This course is taught at our training centre in Cambridge.  Check our current available dates for this course below and register your place. Please note that payment is not made by the booking form below – Instead, you will be sent an invoice with instructions on how to pay and confirm your place shortly afterwards.

Company Group Training

We also provide group training for companies on dates that work for them. This can be done via our training centre, on-site or virtually. Please submit your details here for more information.

Online Training

This course is also taught virtually. To enquire about virtual remote training please submit your details here.

Related Courses

Microsoft Excel Introduction

Microsoft Excel Intermediate

Microsoft Excel Advanced Formulae & Functions

Microsoft Excel Advanced Validate & Summarise

Microsoft Excel Dashboards Made Easy

Microsoft Excel Expert Formulae

Microsoft Excel Power Pivot and Power Query

Microsoft Excel VBA