| Print |

Excel Dashboards Made Easy

Location Cambridge
Price ex. VAT 485 GBP
Start Date 23 February 2019
Course type Standard
Course code FROG231
Duration 2 Day

Description

Excel dashboards enable visualisation of data to produce high level management reports. Dashboards often have user interactivity to convey the most important information at a glance. 

At the end of this course, delegates 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; understand the use of basic macros/VBA for automation.

Target Audience

Designed for users who need to interpret and communicate large quantities of information clearly by summarising it into easy-to-read tables and graphs.

Prerequisites

Delegates should attend Excel Advance Validate and Summarise and Formulae and Functions courses or have equivalent knowledge.

Outline

Overview

  • Excel Dashboards, what they can do for you? 
  • Dashboard Design - consideration & 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 overviews
    • Grouping fields
    • Using slicers
  • Pivot Charts
  • Charts and chart types
    • Overview of chart types and their suitability to dashboards
    • Bar, Line, Bubble charts
  • Advanced Charting:
    • Combining different chart types within one plot area
    • Creating user defined charts
    • Saving and using chart templates
  • Interactive charts
  • Chart tips and tricks

Adding ActiveX controls

  • Why use ActiveX 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 the course: Excel Dashboards Made Easy

Course Search