Business charts in Excel (inhouse) –
IBCS-compliant reports and dashboards

This 5-halfday training (4 hours per session) focuses on advanced techniques for implementing IBCS-compliant charts in MS Excel. Develop reusable templates for charts, entire report pages and even interactive dashboards with these special working techniques.

Objectives

The training explores working techniques to considerably extend the range of generally known and used Excel charts. This includes waterfall charts, pin charts and small multiples. You will create dynamically driven charts and tables, which in turn populate your interactive dashboards.

Prerequisites

Each participant will need a Windows-based computer with a physical mouse and an installation of both MS Excel (Version 2013 or higher) and MS PowerPoint.

Prerequisites for participation in Excel seminar

Trainer

Markus Wolff

Markus Wolff is Co-Founder and Managing Director of chartisan GmbH, a service provider for data visualization and reporting solutions.

Jörg Knuth

Jörg Knuth works at Ceteris AG, a German BI consulting company. He is responsible for Microsoft BI and software development as well as the contact person for strategic BI consulting.

Program

DAY 1 (4h)

Principles of the SUCCESS Formula

  • Design user-oriented reports
  • Choose appropriate chart types
  • Provide an overview, avoid distractions
  • Standardize design elements

Dynamically driven chart templates

  • Synchronize scaling across multiple charts
  • Incorporate additional labels and lines
  • Switch colors and highlight chart elements
  • Automate controls for all chart functionality

DAY 2 (4h)

Waterfall chart

  • Understand formula logic and chart setup
  • Provide functionality for null values and subtotals
  • Improve legibility with labels and connecting lines
  • Add variance indicator for cumulative variances

Pin chart

  • Build pins based on scatter data series
  • Control positioning and scaling with formulas
  • Integrate color switching and labelling
  • Position pins matching adjacent bar charts

DAY 3 (4h)

Advanced chart templates

  • Build multi-level variance charts
  • Synchronize bar charts, waterfall and pin charts
  • Automate controls for positioning and scaling
  • Dynamically link values and control parameters

Embed report pages in PowerPoint

  • Define page layout accurately in Excel
  • Arrange chart templates in a sensible way
  • Create presentations that update automatically
  • Understand peculiarities of OLE-embedding

DAY 4 (4h)

Small multiples

  • Use sparklines for simplified small multiples
  • Build small multiples with real chart objects
  • Duplicate chart templates quickly and accurately
  • Synchronize scaling and parameter controls

Table visualization

  • Standardize row, column, and number formatting
  • Use data bars and symbols for visualization
  • Automate layout with conditional formatting
  • Sort and filter with formulas

DAY 5 (4h)

Dashboard examples

  • Utilize pivot table data in user-friendly layouts
  • Link data selections with control elements
  • Link self-made chart templates with data sources
  • Create a sales dashboard with charts and tables

Additional examples and outlook

  • Create a KPI overview with bullet charts
  • Implement advanced and dynamic tables
  • Link multiple dashboard pages via hyperlinks
  • Q&A, implementation tips

.