Microsoft Excel Dashboards Course

Gather and Prepare Data

Importing data from external sources (CSV, databases, web, etc.)

Using Power Query to dynamically update externally linked data

Removing duplicates, handling missing values, and correcting errors

Standardise data with the TRIM, CLEAN, or TEXT() functions

How to use Excel’s Remove Duplicates and Power Query's Transform tools for preprocessing

Organising raw data into a separate sheet or area

Using tables for easy referencing, sorting, and filtering

Analyse Data

Identify key metrics and the purpose of your dashboard

Use Excel formulas (SUMIFS, COUNTIFS, AVERAGE, IF, XLOOKUP) to derive insights

Leverage pivot tables for dynamic analysis

Identify key performance indicators (KPIs) relevant to your audience (e.g., sales trends,

profit margins)

Breaking data into manageable sections

Design Visualisations

Choosing the right chart for your data

Highlight key numbers with heatmaps, data bars, or custom formatting

Using charts directly tied to PivotTables for interactive visualisations

Building the Dashboard

Designing a clear and logical flow

Using text boxes or shapes for labels and instructions

Adding Slicers for easy filtering on PivotTables/Charts

Using Dropdowns via Data Validation for user inputs

Align charts, slicers, and shapes perfectly

Tie metrics to raw data dynamically with cell links

Finishing Touches

Using named ranges for dynamic data updates

Creating dynamic chart titles using formulas like ="Sales for "&A1

Incorporating a colour palette (e.g., corporate branding)

Design tips

Test Functionality and Share

Verifying interactivity (e.g., slicers, dropdowns)

Double-checking for broken formulas or incomplete data links

Hiding raw data sheets

Locking cells to protect data

Saving as .XLSX or .XLSM (if macros are included)

Exporting as a PDF for static sharing