Duration

1 day(s)

MS Excel: Visualize your data with dashboards

Duration

1 day

Audience

This course is designed for people who already have a thorough knowledge Excel, but who want to improve in assembling dashboards to analyze and make clear (large) amounts of data.

Prerequisites

A good knowledge of the basic principles of Excel is necessary.

If the knowledge of formulas and functions, tables, pivot tables and graphs is insufficient, it is strongly advised to first follow these modules. If you have already followed the course "MS Excel Automating without programming" or the course "Automating tasks with macros in MS Excel" that is an advantage.

It is also possible to request an extra day before this module to refresh the necessary knowledge.

Objectives

Display data in a well-arranged way via a dashboard by using tables, pivot tables, graphs, slicers, controls, ...

Methods

An interactive hands-on classroom-based training.

Description

After a brief introduction (preparation of a dashboard), all possible parts of a dashboard are first explained.

The basis for a dashboard is the data. This can be either directly in the Excel file in a table or can be supplied from external data sources (Access, text files, ...).

Calculations can be added to this data or data can be summarized directly in the dashboard. That is why we see some useful functions.

The use of defined names can also simplify maintenance of objects. So we will also discover how to create dynamic pick lists based on a defined name.

Pivot tables are the ideal tool for analyzing data and making summaries.

We will also briefly see the tools PowerPivot and Power Query to use data from external data sources.

To create clear dashboards, we will not only use graphs and sparklines, but also use advanced formatting techniques, such as conditional formatting.

All these objects will then be used to compile a dashboard.

To make it easy for the user, controls (check boxes, selection lists, buttons, ...) will be added with which the display of dashboards can be changed. In that case, it will sometimes be necessary to attach macros to the objects to automate tasks.

Contents

• Introduction
• Parts of a dashboard
  • Tables
  • Defined names
  • Data from external sources
  • PivotTable
  • Power-tools
    • Power Pivot
    • Power Query
  • Useful functions (logical, search, text, date, statistical)
  • Format
    • Custom number format
    • Conditional formatting
  • Graphical representation
    • Sparklines
    • Charts
• Composing a dashboard
  • Combining dashboard parts
  • Controls (Check Box, Combo Box, Spin Button, ...)
  • Automate via macros
    • Introduction
    • Record macros
    • Run macros (Button, graphic objects, ...)