Microsoft
Microsoft Excel Excel Dashboards
Build interactive dashboards that filter, drill down and update live - turning raw data into decisions.
Overview
Dashboards are powerful tools to summarise & communicate complex data; help with project management, customer service, retail management, financial forecasting and much more.
In this course - learn to leverage Excel functionality to build & manage sophisticated interactive dashboards.
Who Should Attend
Current users of Excel who wish to design visual dashboards and summary reports in Excel.
Prerequisites
Intermediate level skills. You needn't be an Excel guru; however a sound knowledge of formulas, cell references, filtering & sorting data and creating simple charts.
Benefits of Completing This Course
Learn to
• Link to various data sources
• Create a data model for data from multiple sources
• Summarise data to be displayed on the dashboard
• Visualise data through charts
• Add interactivity
Inclusions
- Training Manual with step-by-step instructions
- Practice files relevant to the training material
- Training Completed files - you are free to take away any completed files for review
- Unlimited online support, as training doesn't stop when you walk out our door
Learning Outcomes
• Dashboard design principles
• Preparation of data using functions and PivotTables
• Construct the dashboard using formulas and charts
• Creative charting techniques
• Creating an interactive dashboard
Topics Covered
Design Principles
● Overview of dashboard reporting
● Understanding the purpose of the dashboard
● How will the dashboard be used?
● Dashboard Design Principles
● Separating data, analysis and presentation
● Organising your data
● Laying out a Dashboard
Analysing Data
● Lookup functions - Vlookup(), Hlookhup()
● Choose() function
● Index(), Indirect() , SumIfs()
● Using Excel Tables to manage data
Dashboard Charts
● Creating Dashboard Charts
● Managing Legends and X/Y axes
● Adding a data series to a chart
● Using Primary and Secondary value axes
● Adding Autoshapes to a chart
● Creating Dynamic text boxes
● Creating better charts in Excel
Figures that Use Charts and Worksheets
● Simple chart formatting
● Row/Column control
● Resizing charts
● Forcing charts to work together
Formula Driven Visualisations
● Creating "in-cell" charts
● Creating visualisations with symbols
● Creating a formula histogram
● Adding a cumulative percent
● Using Conditional Formats & Symbols
Excel Camera Tool
● Introduction to the Excel Camera tool
● Using the Camera tool
● Cool uses for the camera tool - Tables with variable column widths/row heights - Rotating reports - Using dynamic variable length lists - Traffic lights
Showing Trends
● Line chart trends
● Area chart trends
● Vertical scale
● Labelling do's and don'ts
● Showing side-by-side comparison
● Using a secondary axis
● Marking significant events
● Representing forecasts
● Creating a sparkline
PivotTables
● Using pivot tables to create interactive views
● Creating a histogram with a pivot table
● Highlighting top values
Performance against target
● Thermometer style charts
● Bullet graphs
● Creating qualitative bands
● Performance against a target range
Designing an Interactive Interface
● Using Data validation and Combo Boxes
● Adding a check box to include a trendline
● Using Option buttons to dynamically feed data
● Creating synchronised Pivottables using a combo list
Using External Data
● Linking PivotTables and Tables to external data
Live online - learn by doing.
Our Live Online courses are conducted through a dedicated learning platform that enables us to provide instructor-led, hands-on training to develop real skills, in real time. Each participant logs into their own virtual environment set up specifically for the course. This is a fully interactive experience - just like in a classroom. No passive lectures, no videos to follow - real-time, hands-on practice with your instructor guiding you every step.
Instructor
- Provides real-time, live hands-on instruction and demonstration
- Provides step-by-step practical activities for participants
- Can assist participants on their screen, if necessary
- Can share participant screens to enhance discussion
Participants
- Receive hands-on instruction, step-by-step exercises and skill-builder activities
- See the instructor's screen while working in their own environment
- Can interact with the instructor and the group of participants
- Can ask questions at any time
- Join the course from any location with internet, using a course invitation
- No need for any software - just a computer with internet and a browser
- No downloads or installs necessary on the participant's computer
Inclusions
- E-Book Training Manual / activity booklet provided
- Certificate of Completion
- Post-course support to the level of the course
- Access to any training exercises used during the session
Session Times
- Full Day Course: 9:00am - 4:30pm
- Half Day Course: 9:00am - 12:30pm or 1:30pm - 5:00pm
Private sessions are booked exclusively for your team and can be facilitated as Live Online group training, or in a Classroom / Face-to-Face setting at your workplace or another organised location. Content and duration can be tailored to your needs - contact our Course Scheduling team to discuss your requirements.
Classroom / Face-to-Face
Classroom sessions are delivered at a nominated location, available for Private courses only.
Requirements (if training is delivered at your site)
- A computer is required for each participant, configured with appropriate software (if necessary)
- If participants are using laptops, please ensure power cords are provided for use during the session
- The instructor will bring their own laptop - please provide power
- A screen or projector for the trainer to connect their laptop to (please indicate the connection method - HDMI or Wi-Fi)
Inclusions
- Training Manual / activity booklet
- Certificate of Completion
- Post-course support for queries relating to the course content
Get in touch with our Course Scheduling team to arrange a private session or confirm delivery options for a specific date.