Description

Why would you build a dashboard in Excel? Simple. You can visualise all your data in it. It's the ideal way to report or present your figures. In the online course "Excel dashboards" you'll follow all the necessary steps: importing data, processing it, and finally visualising it in a dashboard.

This training makes use of Microsoft 365 features.

Objectives

After this training you will:

• Import data lists from external data sources via Power Query

• Transform data lists into usable data via Power Query

• Use Power Pivot to analyse data from multiple tables

• Analyse data with PivotTables and charts

Bring your analyses together on a dashboard, making it easier to draw the right conclusions faster.

Prerequisites

You have a good basic knowledge of Excel. You know the standard formatting options, you can sort and filter data via AutoFilter, you know the basic functions (Sum, Min, Max, Average, Count, Counta) and you know how and why to lock cells (A1 and $A$1).

Contents

Part 1: Importing and preparing data

• Retrieving external data via Power Query

• Transforming data with Power Query

Part 2: Power Pivot

• Loading data into the Power Pivot data model

• Defining relationships between tables

• Setting up custom sorting

• Making simple calculations in the data model

• Creating pivot tables based on the data model

Part 3: Creating a dashboard

Using a combination of Excel features:

• PivotTables (with slicers to filter and link multiple pivot tables)

• Conditional formatting (icons, background/text colour, custom icons)

• Visualising geographic data on a map

• Sparklines (charts in cells to show trends)

• Charts (e.g. speedometers, waffle charts, conditional formatting in charts)

• Tables and table names

• Named cells (to make formulas more readable)

• Functions

• Hyperlinks for navigation