Excel Dashboards Made Easy - 2 Days


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.

Dates Tuesday, November 24, 2020 to Wednesday, November 25, 2020 - Download as vCalendar
Event Times: 09:30 to 16:30
Venue Microsoft Teams
Contact Name Debby O'Gorman
Email debby@frog.co.uk
Contact Phone 01223 841848
Website Event website

Event details:

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

Description
This two day 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. 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.

Objectives
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.

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

For over 30 years we have improved the skillset of over 20,000 individuals with our exceptional and enjoyable courses. Our training and support started back in 1986, when our founder was working with Stephen Hawking as his PA. But you don’t have to be a world-famous theoretical physicist to take part in our training – All our courses from beginner to advanced level provide you with actionable, hands-on training that you can put into practice immediately and save you valuable time!