THIS EVENT WAS HELD IN NOVEMBER 2020. PLEASE REGISTER FOR THE NEXT SESSION HERE.
Further dates can be found here.
With the huge amount of data available to us every day, a well-built and designed dashboard report is one of the best ways to interpret and communicate large quantities of information. Using new Modern Excel tools, learn how to synthesise information into a logical framework, summarise it into a meaningful format, and then display the summary into easy-to-read tables and graphs using Excel or Power BI.
What is Modern Excel?
Any version of Excel released from Excel 2010 onward is referred to as “Modern Excel” because it introduced new tools such as Power Pivot, Power Query and Power BI. The introduction of these tools was the most exciting thing to happen in the Excel world since the PivotTable! Many of us struggle to make sense of all the data that is available in this information age and with such a deluge of reports and statistics which don’t mean anything or can’t be made sense of, it’s no wonder that data is often misinterpreted or not communicated at all.
Understanding how Modern Excel tools can help analyse data and create dashboards to communicate your message is a critical skill for Excel users today.
Prerequisites
The course is designed for users who do use Excel on a regular basis and are comfortable with using its tools and functions. At minimum, it is assumed that participants will know how to:
- Navigate confidently in Excel
- Create and use Excel tools and functions
Prior knowledge of Power Pivot, Power Query or Power BI is not required
Who should attend this course?
This course is aimed at managers and other professionals who need to create dashboards and other reports in order to analyse interpret and present information. It is expected that students should have had some exposure to business, finance and reporting principles. This course builds on students’ existing knowledge of Excel tools and functions and incorporates these into complex and dynamic reports and dashboards.
Delivery Format
This course is conducted as 3 x 3-hour online sessions on three consecutive days. At the conclusion of each formal session, a one-hour clinic is held where the instructor gives individual assistance and feedback. Each session is highly interactive, and participants are expected to complete each task independently and then share with their break-out group, or the class as a whole.
A download folder is provided, loaded with examples and templates. Students are encouraged to share their own experiences with the class and are welcome to bring examples of their own data or reports to discuss during the course.
All sessions are recorded, and participants can access recordings for 12 months following the course.
At each step during the course, participants build and practice each formula, tool and technique and will build their own dashboards to take away for future use.
Software used
You must have a Windows laptop or workstation (not Mac) with your preferred version of Excel installed; Excel 365 is recommended to get the best from this training. Please load Power BI Desktop (free install) prior to the course. A good Internet connection is required, along with a quiet room, microphone and webcam (preferable).
Learning Objectives
This course is very hands-on and practical. Each participant will be expected to create their own reports, charts and models utilising the tools and techniques covered during the course. Participants will:
- Understand the principles of data analysis
- Learn to synthesise and summarise information into a logical framework.
- Know how to use visual effects to improve their reports and presentations
- Explore how to summarise, present and communicate data clearly and concisely
- Consider the principles of good dashboard design and presenting data graphically
- Evaluate the new Modern Excel tools available for data analysis and dashboards
- Discover the tools to analyse data & build reports using your existing Excel tools and skills (and perhaps a few you didn’t know you had!)
DAY ONE (3 hours + clinic)
Data Visualisation for Dashboards
- Turning data into information
- Debunking the big data myth
- Presenting financial data
- How dashboards combat information overload
- Find the story. Tell the story.
Data Analysis Tools
- What’s new in Excel 2019 / Excel 365?
Technical differences between versions. Considerations when building data models for users of different versions. - Shortcuts
Speed up your data manipulation in Excel with the use of shortcuts - Tables & PivotTables
Analysing data more quickly and efficiently using structured reference table and summarise using PivotTables
Power Excel Tools
- Introduction to Power Pivot, Power Query and Power BI
Evaluation of Excel vs. other BI alternatives in terms of speed, usability and cost - Data modelling
building relationships between multiple tables - Get and Transform your data
extracting and cleansing data with Power Query - Power Pivot
introduction to data warehousing - Dashboarding with Power BI
creating visuals in Power BI Desktop
Using Excel as a Data Analysis Tool
- Essential Excel tools & functions
Overview of must-know tools and functions for data analysis - Creating a robust formula
Nest functions together to create succinct and robust formulas
DAY TWO (3 hours + clinic)
Practical Dashboard Building 1
- Building a dashboard using standard Excel, PivotTables and PivotCharts
- Preparing data using Power Query and then building a dashboard in Excel
- Building, modelling and sharing reports in Power BI
Charting & Table Design
- Working with charts in Excel 2019 / 365
Assess the use of waterfalls, treemaps and sparklines - Data visualisation for visual analytics
Show your message such as correlations by charting on different axes and chart types - Tables vs. Charts/Graphs
Deciding the best method for visual display - Selecting the correct chart to display data
Getting your message across most effectively by choosing the correct medium of display
DAY THREE (3 hours + clinic)
Dashboard Reporting & Design
- Purpose and benefits of Dashboards
Why dashboards are so popular - Common mistakes when building a Dashboard
Critique of poorly built dashboards - Principles of dashboard design best practice
Dashboard layout, colour choice and display principles - Adding icons and images to dashboards
Quick and easy ways to add automated colour and icons to your reports - Effective use of colour and logos
Ways to make an impact with colour
Practical Dashboard Building 2
- Using your own data, or examples provided, we spend the last part of the course creating dynamic dashboards using Excel, Power Query, Power Pivot or Power BI incorporating the tools and techniques discussed during the course.
Comments from past students
“It was an outstanding course with relevant and dynamic content, tips and shortcuts to creating interesting and informative dashboards.”
“The course covered a wide range of topics related to Excel data processing and modelling.”
“Structured over 3 half days & very professionally run and interesting with proper workshops & exercises.”
“Danielle knows her stuff and imparts with humour; Susan administered excellently.”
“Great introduction to Power BI – I feel more likely now to be able to progress setting up monthly reports in Power BI and export to pdf for distribution.”
“I liked that the course didn’t just take us through the technical aspects of how to build a dashboard, but also included info such as which chart type to use, how visual information is interpreted, dos and don’ts and simple, understandable dashboards vs bad ones.”
THIS EVENT WAS HELD IN NOVEMBER 2020. PLEASE REGISTER FOR THE NEXT SESSION HERE.
Further dates can be found here.
The recent sessions have now finished. Please join the waitlist here for our next round of sessions.
* By signing up to this course, you agree to the terms and conditions
