Dashboard Reporting in Modern Excel

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.

Dashboard Reporting in Modern Excel

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.

Dashboard Reporting in Modern Excel


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

[ESPRESSO_EVENTS category_slug=dashboard-reporting-in-modern-excel]