Excel: Creating a Dashboard with Power Query (Office 365/Microsoft 365)

Learn how to create data-rich Excel dashboards using Power Query.

Introduction

• Dashboard project overview
• What you need to know before starting
• Download files to follow along

1. Lay the Groundwork

• Prepare a draft of the Excel dashboard
• Import master data from external workbook with Power Query
• Import data from a text file with Power Query

2. Set Up Logic and Calculations

• Create the data model and define relationships in Power Pivot
• Create logic for the latest and previous month in Power Query
• Set up calculations with PivotTables for the latest month

3. Create the Dashboard

• Link Excel shapes to the data and linked picture trick
• Top three sales managers and numbers: Sort data in PivotTable
• Linked table for sales by product category
• Excel PivotChart for monthly sales
• Pivot Slicer connected to multiple PivotTables

Final Touches

• Finalize the dashboard