Description
Why would you create a dashboard in Excel? The reason is simple: you can use it to visualise all your data. Dashboards are the ideal tool to report or present numbers. In our online ‘Excel Dashboards’ course, we’ll guide you through all the necessary steps, from importing and processing data to visualising everything in a dashboard.
During this course, you’ll use Office 365 features.
Objectives
After this training, you’ll be able to:
- Import data lists from external data sources via Power Query
- Transform data lists in Power Query into actionable data
- Use Power Pivot to analyse data from multiple tables
- Analyse data with PivotTables and charts
Collect analyses in a dashboard and effortlessly draw the correct conclusions
Prerequisites
Basic Excel knowledge is required. You know the standard formatting options, you can sort and filter data with AutoFilter, you know the basic functions (Sum, Min, Max, Average, Count, Counta) and you know how and why you should freeze cells (A1 and $A$1).
Content
Part 1 Importing and preparing data
- Importing external data via Power Query
- Transforming data via Power Query
Part 2 Power Pivot
- Uploading data into the Power Pivot data model
- Defining relationships between tables
- Setting custom sorts
- Creating (Simple) calculations in the data model.
- Creating pivot tables based on the data model
Part 3 Creating a dashboard
- To create the dashboard we use a combination of different functionalities in Excel such as:
- PivotTables (Slicers to filter PivotTables ; Linking slicers to multiple PivotTables)
- Conditional formatting to highlight cells (Icons, background colour, text colour, custom icons)
- Visualising geographic data on a map
- Sparklines (In-cell graphs to view evolutions)
- Charts (Speedometers, Waffle chart, Conditional formatting in a chart)
- Tables and table names
- Cell names (to make functions more readable)
- Functions
- Hyperlinks for navigation
