Dashboarding with Excel Course

Dashboarding with Excel Full Day Workshop

This comprehensive Excel dashboard course will teach participants a range of Excel skills to apply in the workplace to make stunning and interactive dashboards.  Through the application of the theory and the practical application of designing a dashboard, participants acquire the skills to be able to create report which are time saving and easy to read and interpret.  This course is designed for accountants, analysts and finance professionals who want to understand the key considerations, design rules, and how to promote insight and increase understanding when designing dashboard displays. 

This course delivers:
  • A broad view of the organisation, displaying interactive data from operations, finance, human resource and social media.
  • The creation of an interactive calculations page which moves in line with selections made on the output (dashboard) page.
  • The creation of active X combo boxes to let users choose different metrics to display in the report.
  • The creation of a report by region or team/department which highlights these displays with conditional formatting.
  • The build of summary tables which highlight active years with conditional formatting and create charting trends using sparklines.
  • The creation of a highly interactive sensitivity tool through the creation of a custom function and use this function to apply sensitivities to the model.

Tools Used

  • Data Summary using SUMIFS Formula
  • Working with OFFSET Formula
  • Working with INDEX & MATCH Formula
  • SUMPRODUCT formula
  • HLOOKUP, VLOOKUP formula
  • Nested IF with AND statements
  • HYPERLINK formula
  • LARGE formula
  • COLUMN & ROW formula
  • RIGHT & TRANSPOSE formula
  • Creation of a custom function
  • Creation of a form control combo box and an active x control

Practical Component

Participants will pull together a calculations page which requires source data to be ranked and summarised based on multiple criteria.  Participants will create the sensitivities on the assumptions sheet, which will involve the creation of a VBA custom function, this will enable the dashboard to change by isolating metrics. 

Who Should Attend

Professionals who produce management information and want learn design rules which will produce reports with a clean, clear layout which are able to convey a compelling message. The course will help participants develop sound formula, layout and structuring of workbooks for analysis and develop design skills to expedite report production and enhance output productivity.  These techniques will be helpful for finance professionals who regularly generate management reports and want to add the flexibility, simplify calculations and increase the readability of their financial models.   This course is for analysts who summarise data and want to beautify the output while creating interactivity.

The course material includes a 60+ page user document which takes you through the theory and practical exercises.  Participants will gain the maximum benefit from this course if they are already competent spreadsheets users.  The course is designed for people who use Excel on a regular basis, and are comfortable with using its tools and functions.  The course offers students the opportunity to create a robust, best practice financial model which is pliable and adds some advanced financial modelling techniques to their toolset.

It would be most helpful if participants bring a USB to class so they can take the file with them upon completion.