With the huge amount of data available to us every day, a well-built and desig
ned 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 material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheet users. It 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 advanced 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.
Course Format
This course is available as a one-day public workshop in Sydney, Melbourne or Brisbane, or in-house at your company. Each participant receives a USB 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.
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.
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!)
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
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
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 - Selecting the correct chart to display data
Getting your message across most effectively by choosing the correct medium of display - 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
