EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

Data Analysis and Interactive Dashboard Reporting

Course outline

2 days
Discuss this programme →

INTRODUCTION

For those who are new to dashboards, it would be ideal to get an understanding of the dashboards first. In this chapter, you will get to know the definition of dashboard, how it got its name, how they became popular in IT, key metrics, benefits of dashboards, types of dashboards, dashboard data and formats and live data on dashboards.

In information technology, a dashboard is an easy to read, often single page, real-time user interface, showing a graphical presentation of the status (snapshot) and historical trends of an organizations or department’s key performance indicators to enable instantaneous and informed decisions to be made at a glance.

PREREQUISITES

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spread sheet 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 on a regular basis

LEARNING OBJECTIVES

Many of us struggle to make sense of all the data that is available in this information age. 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. 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. Covering dashboard reporting, balanced scorecards, visual design of charts and tables, we will also discuss the new charting and analysis features of Excel 2013.

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.

Course Outline :: Data Analysis and Interactive Dashboard Reporting::

By the end of the day, participants will:

  • 1. Understand the principles of data analysis and dashboard
  • 2. Learn to synthesise and summarise information into a logical framework.
  • 3. Consider when to use a chart or a table
  • 4. Know how to use visual effects to improve their reports and presentations
  • 5. Explore how to summarise, present and communicate data clearly and concisely
  • 6. Learn the principles of good dashboard design and presenting data graphically
  • 7. 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!)

MODULE 1: Introduction

Business Intelligence & Big Data today

What is Story Telling & Data Visualization?

Data-driven narratives & Dashboards

Components of a good Dashboard

MODULE 2: Excel Power Query

What is Power Query?

Connecting to various data sources & importing data

Using Power Query to replace the VLOOKUP

Shaping & customizing a data model from multiple data sources

Merging, appending & exporting queries

MODULE 3: Understanding Advanced Array Functions in Excel 365

What is an Array function and when should we use it

Unique Functions

Sort, and Sortby Functions

Filter Functions

Offset Function

MODULE 4: Important Excel Functions for Dashboards

SUMPRODUCT Function

INDEX/MATCH Function

Course Outline :: Data Analysis and Interactive Dashboard Reporting::

IFERROR Function

TEXT Function

ROWS/COLUMNS Function

MODULE 5: Data Validation and Conditional Formatting

Understanding Conditional Formatting

Using Conditional Formatting Options

Using formulas and functions with Conditional Formatting

How to Dynamically set Conditional Formatting on Data

Validating Data with Formulas

Visualizing Data with Conditional Formatting

MODULE 6: In-cell Charting Techniques

Visualizing Data with Sparkline o Use sparklines to show data trends o What are sparklines?

o Why use sparklines?

o Create a Sparkline o Customize sparklines o Control which value points are shown o Change the style of or format sparklines o Show or hide data markers o Show and customize axis settings o Handle empty cells or zero values

MODULE 7: Creating Interactive Charts

Table and Slicers o What is a Table?

o Creating Tables o Sorting Data in a Table o What is an AutoFilter?

o Creating a Custom AutoFilter o Using SubTotal in Tables o Create a slicer to filter your table data

Course Outline :: Data Analysis and Interactive Dashboard Reporting::

Advanced use of Pivot Table and Pivot Charts o Using PivotChart Reports o How to create a PivotTable Report o How to create a PivotChart Report o Slicing a PivotChart o Timeline with PivotTable/PivotChart (Optional – Excel 2013 Only)) o Differences between PivotChart and standard charts

MODULE 8: Dashboard Design

Types of Form Controls

Applying form controls to Dashboard Reports

Concept of Dashboard

Putting all Together and Designing the Dashboard – Ihe Interface

Dashboards Do’s and Don’ts

Programme scope, delivery arrangements and suitability are confirmed during enquiry.