Visual Analytics Course

Visual Analytics Workshop

This course will guide you through various techniques, providing a comprehensive understanding of creating highly interactive Excel spreadsheets.  This will cover sales analysis, financial analysis and corporate KPIs all consolidated into a pliable easy to update workbook.  The course will focus closely on the set up of a robust calculation sheet and the use of Excel’s inbuilt tools to make trend analysis. 

This course delivers:
  • Practical guidance on how to layout and structure data to aid with reporting;
  • Learn how to properly incorporate big data sheets into Interactive reports for better data visualization and better support decision making.
  • Techniques to design and construct reporting dashboards, using tables, shapes a range of chart types;
  • The creation of dynamic named ranges which will be used to generate linked combo boxes.
  • A wealth of practical information on the design and creation of advanced chart types;
  • Discover a range of sophisticated techniques that will improve the usability and reliability of your Excel reports.

Tools Used

  • Data Summary using SUMIFS Formula
  • Working with OFFSET Formula
  • Working with INDEX & MATCH Formula
  • MIN/MAX AND RANK formula
  • COUNTIFS & LOOKUP formula
  • Charting with conditional formatting
  • Dynamic ranges for cascading combo boxes
  • Sorting ascending/descending on cell check box change with formula
  • Working with Form Controls (Combo Box, Option Buttons, Check Box)
  • Adding Flexibility with the INDIRECT formula

Practical Component

Participants will create a summary report from scratch detailing historical v actual sales, quantity, profit, expenses and customer satisfaction and net promoter scores.   The practical exercise will involve the creation of highly flexible calculation tables and interactive charting which will change in conjunction with the information chosen in a series of combo boxes, check boxes and option buttons. The course will cover important layout techniques to make your trend analysis stand out.

Who Should Attend

Professionals who prepare presentations (financial, statistical or holistic organisational) of any kind that are required to refresh these reports with new data on a regular basis.  These techniques will be helpful if you work with charts and summarised datasets and want to create reports in a visual appealing way.

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheets users. It is designed for users who use Excel on a regular basis, and are comfortable with using its tools and functions.  Knowing how to create incredibly visual, highly interactive Excel workbooks will take participants career to the next level.

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