Excel Dashboard with Power Query and Power Pivot
Learn how to build an interactive Excel dashboard using Excel's power query and power pivot tools
Introduction
- Presenter: Mindy Tracey
- Experience with Excel for 23 years
- Passion for Excel and helping people get more out of it
- Runs online Excel training courses and received the Microsoft Excel Most Valuable Professional award
- Expectations for the webinar and tools available
Getting Started with Power Query
- Explanation of Power Query and its availability in different Excel versions
- Demonstration of how to use Power Query to grab data from a folder
- Steps to clean and transform data in the Power Query Editor
- Preparing the data for loading into Power Pivot
Using Power Pivot for Data Modeling
- Explanation of Power Pivot and its role in data modeling
- Importing data from Power Query into Power Pivot
- Creating relationships between tables in Power Pivot
- Adding additional dimensions and calculations to the data model
Building Pivot Tables and Pivot Charts
- Creating pivot tables and pivot charts from the data model
- Formatting and customizing pivot tables and pivot charts
- Creating dynamic named ranges for sparklines
- Inserting and formatting sparklines
Creating Interactive Elements with Slicers
- Inserting slicers to allow users to filter data
- Mapping slicers to pivot tables and pivot charts
- Managing slicer connections to ensure consistent filtering
Building the Dashboard
- Copying and pasting charts and tables to the dashboard sheet
- Formatting and arranging charts and tables to create a cohesive dashboard
- Adding titles and headers to the dashboard
- Assigning dynamic named ranges for sparklines
Conclusion
- Review the steps covered in the webinar
- Encourage further exploration and learning of Excel's power query and power pivot tools
- Provide resources for continued learning and support