This course was created by Leila Gharani. We are pleased to offer this training in our library.
- Drafting a dashboard
- Importing data and connecting to external files
- Creating relationships with the Excel Data Model
- Setting up calculations with PivotTables
- Linking Excel shapes to cell values
- Using PivotCharts to display data
Skill Level Intermediate
(sparse upbeat music) - Creating a dashboard in Excel from scratch can feel overwhelming, especially if you're working with a lot of data. How do you do the calculations? How do you extract what you need from the data and present it in a compelling way? In this course, we're going to use Excel's Power Query, Data Modeling, and Pivot Table tools to get it all set up really fast. My name is Leila Gharani I'm a Microsoft MVP and the founder of Excel Plus where we help people become the go-to Excel expert at their company. In this course, we're going to get hands on and create this interactive, easy to read and best of all, easy to manage dashboard. It works on its own. The moment new data is available, just click refresh, sit back and get the latest insights from your data. Here's what you'll learn while we build this together. How to import and connect to large text files, CSV files, or other Excel files, how to use the data model to create relationships between your data file and the master data. You're going to learn to use Power Query to create a logic which allows you to grab the data from the latest month so you can immediately report on the latest information, whenever you refresh your query. You don't even have to select the latest month. It will be there automatically because of the logic you built. While we set up this logic, you're also going to learn some Power Query tricks, like using intermediate steps as helper steps. Now, since we're creating this report from scratch, you'll also learn some useful Excel dashboard tips and tricks, like how to link shapes and text boxes to cells, how to use conditional formatting and link pictures in your dashboard. On top of that, we're going to be using pivot charts and pivot slicers to optimize the layout of the dashboard. If this sounds exciting to you, let's build this together.