Create an Excel Dashboard with Power Query (Office 365) | Leila Gharani | Skillshare

Create an Excel Dashboard with Power Query (Office 365)

Leila Gharani, Excel Instructor, Business Consultant

Create an Excel Dashboard with Power Query (Office 365)

Leila Gharani, Excel Instructor, Business Consultant

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
15 Lessons (1h 20m)
    • 1. Introduction

    • 2. Class Expectations

    • 3. How To Use Downloadable Resources

    • 4. Prepare a Draft of the Dashboard

    • 5. Import Master data from External Workbook with Power Query

    • 6. Import Data from Text File with Power Query

    • 7. Create the Data Model & Define Relationships in Power Pivot

    • 8. Create Logic for Latest and Previous Month in Power Query

    • 9. Setup Calculations with Pivot Tables for Latest Month

    • 10. Link Excel Shapes to Data & Linked Picture Trick

    • 11. Top 3 Sales Managers & Numbers (Sorted Excel Pivot Table)

    • 12. Linked Table for Sales by Product Category

    • 13. Excel Pivot Chart for Monthly Sales

    • 14. Pivot Slicer Connected to Multiple Pivot Tables

    • 15. Finalize the Excel Dashboard

25 students are watching this class
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Creating a Dashboard in Excel from scratch can feel overwhelming. But it does not have to be!

In this class we will use Excel Power Query together with Excel's data modeling and Pivot Table tools to get it setup REALLY fast. 

In this class you will learn:

  • Import from Text/CSV and Excel files
  • Apply regional settings to make sure dates and numbers are shown correctly
  • Use Excel's data model and Power Pivot
  • How to create an interactive dashboard that can be refreshed with the click of a button
  • How to use Pivot Charts and Pivot Slicers
  • Useful Excel Dashboards Tips and Tricks

The techniques we cover in this class will give you additional ideas to apply to your own reports to give them an edge and make them more professional. 

Follow along with me and create this interactive dashboard from scratch!

Meet Your Teacher

Teacher Profile Image

Leila Gharani

Excel Instructor, Business Consultant


I help companies use Excel better to improve their reports by providing customized training sessions, Dashboard Training and Enhanced Visualization Techniques. I also help improve processes, by designing interfaces and tools with VBA for Excel.

I have over 15 years of experience implementing and training users on Management Information Systems of different sizes and nature. Aside from onsite training, I offer comprehensive Excel online courses.I am also a Certified Microsoft Excel Expert and have a Masters degree in economics from University of Toronto.

I place great value on keeping my training sessions not only informative but also interesting. With technical topics like Excel, students learn the most when they attempt to solve a problem on their own. They are most engag... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction: 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 presented in a compelling way? In this course, we're going to use Excel's Power Query data modeling as pivot table tools to get it all set up really fast. My name is Lily granny, ME, 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, create this interactive, easy to read and best of all, easy to manage dashboard. It works on its own. The moment new data's 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 cell phones. 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'll 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're 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 linked 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. 2. Class Expectations: Before we get started, let's quickly cover what you need and also what you need to know before starting. What assumption is you already use Excel and that you have Excel from Microsoft 365. This course was created with Excel for Microsoft 365 in 2020. And another assumption is that you are familiar with basic formulas and functions like if, sum and count. That you can create basic charts and also create a basic pivot table. They would also help if you're familiar with the Power Query interface. But it's not necessary to know Power Query Well. So even if you're not that familiar with Power Query, you can still follow along with me and create this dashboard because I take you through all the steps you need to do to get there. They'll just make it easier for you. If you've used or you've seen Power Query before, nine incase you haven't, and you decide that Power Query Is something that you are interested in that you could actually use at work. Make sure you check out the other courses that are available in the library. Next, let me quickly introduce you to the exercise files And how you can follow along with me. 3. How To Use Downloadable Resources: If you want to create this dashboard from scratch with me, what you need to do first is to saved exercise files to your dr. Now this is a zipped file so you can and need to unzip it. Next you have a choice to make. Either you can start from scratch. So you will start from the file that's called dashboard underscore version one. And then you're going to continue with this file until we reach the end. Or if you don't want to start from scratch, but you want to jump in somewhere in the middle. What you can do is to download the latest status after dashboard that's available after each lecture. You're gonna find the label of the dashboard at the beginning of that lecture. But here's something that you need to keep in mind. Because if you're working with one of these solution files, you're gonna need to update the source in Power Query for two of the queries that I've listed here. Now the reason for this is that we're importing external data. This external data was for me sitting on my C drive in this path right here. If you've saved your files somewhere else and you have a different path for your file, then you need to do this step right here because otherwise you're gonna get this error. Now to do that, I've put this up here. You need to open power queries. So you need to go to queries and connections. Click on the queries here, jump back to the source step and browse for the file on your drive. Once you found your file, click OK and you'll establish a connection. Okay, so next, let's start with a draft of our dashboard. 4. Prepare a Draft of the Dashboard: The first step to creating any type of dashboard or report is to start with a draft. So this draft tap here, I've just laid out What I'd like to see in the end, the purpose of the dashboard is to give me a sales overview for the latest month. I went to see the sales value for that month. And also for the previous month, I went to get the names of the top three sales managers. Have a table that takes a look at different product categories and the sales values for the latest mug and compare that to the previous month. And also like to see the trend, sorry, the sales trend for the year up to the latest month. Then I wanted to look at the customers, specifically to top five customers by product category and also the top five sales employees by product category. Okay, So based on this and need to figure out what type of fields or what type of KPIs I need to get from the system. The main KPI need is sales information. I need to have it on a monthly basis. I also need to have sales manager information and product and customer information so that I can create this report. Now if you're wondering how I set this up, this is just basically using shapes in Excel. So if you go to Insert and they're illustrations, we have shapes. I just inserted a few shapes and adjusted them by adding textboxes. So for example, if I just draw this out, you can right mouse click and edit text or add a text box on top of this. You can also change the shape outlines under Shape Format. When their shape outline, you can use sketched. So that's what I did in this examples here, and then removed the shape fill to get this effect. Ok, so once we have our draft setup and we know what we need, we can figure out where we can get this all from. The sales data is something we can extract from the system. We get order line ID, order id, customer ID, salesperson, AD, date, product, item-item, quantity and unit price. So notice sales is not there by itself. We need to calculate it from quantity and unit price from order dates. We need to get the latest Month. And we also have a lot of IDs in here, but we need to get the names in the end or the description of these for our report. That's where our master data comes in. So our master data is in an Excel file. It has three separate tabs, one for sales employees, products, and customer. Each one has the ID included in there and then additional information. So product group, for example, is what we call here product category in the draft than we have product name and we have some additional information that we're not using in the dashboard. Also when it comes to customer, we have the ID, the name, and then we have the four number, FAX number, website and so on. And this information we don't need in the end, all we're interested in here is the Customer Name. The sample data we're going to be using for this dashboard is actually coming from a Microsoft sample database called wide world importers. I've adjusted the data for our dashboard example. So you have an idea on how the files look. This is our sales data.txt file. It's a comma delimited file. And our master data is sitting in Excel tables in separate tabs. Now as a next step, what we need to do is figure out what calculations we need to do to create the dashboard that we've already drafted in here. So let's start first by importing the data that we need. 5. Import Master data from External Workbook with Power Query: Let's start off by importing the master data into Power Query. So our master data is in a separate Excel file. We have information on customers on a separate tab in its own table called table customer. Then we have products also formatted as a table called table product. And then the information about sales employees. So the cells Employee ID and their full name in a table called table sales MP. Miss import this into our final dashboard file. Power queries. I'm just gonna close this file, go to data, get data from file, from workbook, browse for the file, it's sitting inside dashboard data and it's called Master Data dot XLS X, click on import. Here we can see the different tables and a sheet names. We went to import the tables here. But here's the thing. I am able to select multiple items. So if I go ahead and select these three and then click on transformed data, Power Query creates three separate queries for me. Now this is great. But if you ever need to change the source, Let's see, the name of the file changes or the path of the file change. Then you have to go back to source for each of these queries and update the source right here. You'll have to do this three times. In order to avoid this. I'm going to import this in a slightly different way. So I'm just going to close and discard this. And let's do it again. Let's go back to get data from file, from workbook, master data imports. Instead of selecting the individual queries here, I'm gonna select the folder and creates a connection to the file itself. Select transform data. And Power Query opens up with all the objects and sheets that I have in place. So inside here we can see that these are the tables and these are the sheets. So the information we want to take is from the tables because notice the sheets here, they orient formatted properties of the column headers are just the default headers. Whereas when the data is formatted as an Excel table, I already get it in the proper format with proper column headers. So what I wanna do with this query is treat this as a lending query. Let's call this master detect connection. So the purpose of this is just to create a connection to our master data file. I'm going to load this to the workbook as a connection only so close and load two will only create a connection. And ok, now what I'm gonna do is referenced this query. So right mouse click on the query from the queries and connections window here. Select reference. Now our source is referencing our original query here. So in case the name of the file changes or the path changes, the only place we need to make a change is for this query right here. The other queries will be connected to the original query. Now I'll create my three tables, but start with products. For products, I'm going to click on table here. It's gonna navigate into it and it already applied. It change types that before I forget, let's rename this to master product. Let's double check the change type to see if everything looks good. These are texts number, that's fine. Let's do the same thing for the next lookup table, which would be our customer. Let's reference the source query right mouse click and reference, press F2 to rename it, call it master customer. And now click on the table for customer and double-check the change types. Now for to customer, I get a lot of information that I will not use on the dashboard side. So I'm never going to use the website URL defects number, the phone number, and the primary contact. The only information I need is the customer id and customer name. So I'm just going to reverse the change type step here. Keep what I need. So let's just hold them control and select Customer AD and customer name. Rightmost click and remove other columns. Now let's apply it change types step to both of these. Go to transform and detect datatype. Number is fine and text is good. Here are master customer data is also set up. So we have one lookup table left and that's for our sales employees. So right mouse click Reference, rename this to master sales MP for sales employees. Click on the table for sales employee, double-check the change type. This looks good. So we're done with our last master data. What we need to do to these queries is to load them as a connection only, but also add them to the data model. So let's go to close and load two. Only creates a connection and place a checkmark to add this to the data model because this is where we going to create the relationships. Click on OK, there being loaded right here. And our master data has been successfully imported. 6. Import Data from Text File with Power Query: Now it's time to import the sales data. So we receive our sales information as a text file. Let's take a closer look at what's inside. We have a comma delimited file with order information. We have quantity in unit price, and we have dates in there. When you upload data from a takes the file or a CSV file and you have dates in it or numbers with decimal places. You have to take care about your regional settings. In this example, the regional settings empower query for the file that we're using has been set to us regional settings, which is identical to the file that we're importing. So we're not going to have a problem. Now you can check the regional settings by going to get data here. Query options under current workbook go to regional settings and check the locale here. This is identical to the file that we're importing. So I'm not going to run into an error when I import the dates in case it's not the same thing. And you don't want to change the Power Query settings for the current workbook, you need to make sure you update the data type for each column and use locale. I'll show that to you as well. So let's go and import the data indices as hab. Let's go to from.txt.csv, find the file. It's called Sales data.txt and imports. The delimiter was picked up properly. Everything looks good here. Let's go and transform the data. I'll leave the name as is. Headers were promoted and the change type step was applied. So because it's a lot of data, sometimes the change type step might not pick the correct type. Especially if you're working with dates by default, column profiling is based on the top thousand rows. And it could happen that your top 1000 rows have dates that work regardless of region, you will only run into problems when the day is bigger than 12 and it's being mistaken for the month. So it's always good practice to change your column profiling here and base it on the entire dataset. It might take some time to update. But then this way you can see if you run into any errors, because then if you notice errors for your date, that means you're using a different locale to your source file. And what you can do there is removed the change types that here, and then go and apply a change type and use locale. So I'm not gonna do it here because we don't have a problem with regional settings. But I just want you to note that because in case you're starting your dashboard on a brand new file and your computer has different regional settings to the US regional settings, then this is something that you need to take care about. So from the local oscillation here, you would select the locale of your source file. So in this case, I would have to go and find English, United States. Ok, so make sure you do this. Just don't forget to remove the change type step here and apply the correct locale to the columns that you need. You would also have to repeat that if you had decimals or 1000 separators in your datasets. In this example, we're dealing with whole numbers. Okay, so at the datatypes here, all look good. These look good as well. There's just one more thing we need to do before we send this to the data model. And that's to calculate the sales value because we have quantity information, we have unit price information. But we need to get the sales value out of this. So I'm going to highlight the Quantity column, hold down Control, highlight unit price at a column. Go to standard, select, multiply, rename this to sales value. Okay, so everything is set up. Let's send this to the DSA model, home, clothes and load two. Lonely creates a connection and add this to the data model. And okay, now we have our sales data also uploaded. Next is to create a relationship between our fact tables are sales data and our lookup tables, which are master data. 7. Create the Data Model & Define Relationships in Power Pivot: Now that we uploaded the master data and the sales data into the data model, we're ready to create the relationships. Let's go to the Data tab and take a look at our current queries, queries and connections. We see the list here to create the relationships between our master data and the sales data. We can go to data tools and relationships, select new, and you get to select the table on the one side and the related table and the columns on the other side. So this is one way to create the relationships. But if you already have your data uploaded to the DSA model, you can do it directly from the PowerPivot window from Diagram View. And I personally preferred that view because it's so much easier to create the relationships that way. So I'm just going to close this and close this. Let's go ahead and bring up the PowerPivot window. We get to see all the data that we loaded to the data model on a separate tab, we can see exactly how many rows they have. So notice the sales employee, for example, has over a thousand lines, are sales deta is over 23 thousand lines. Now, in diagram view right here, we get to create our relationships. Now, this is our sales data. I'm just going to drag it and bring it to the middle here. All I have to do to create the relationships is to drag and drop. Lets start off with customer ID. What Customer ID is related to customer ID right here. So just click and drag and let go of the mouse. And we have our first relationship. It's a one to many relationship. Next is salesperson person ID. And this is related to the person ID right here. So let's select it and drag it unrelated to the other table now Parkway or figures out what is one and what is many are last relationship is for product item ID, and it's called the same thing in the Master Product table. So just drag and drop and we have a relationship. And anytime you hover over the line here, you can see what is connected to which field. Okay, so now that we have a relationship set up, we can create pivot tables that take data from the sales data, but also show related data from the master data tables. So we could take a look at our sales value by customer name or sales value by employee name. Before we start with our pivot table, though, there is one thing we need to do and that's to figure out how we can get the latest month automatically extracted from our dataset. Because in the future, every time we refresh our dashboard, we want to show the data for the latest month and compare that to the data from the previous month. So somehow we need to be able to flag these in ourselves. Data. 8. Create Logic for Latest and Previous Month in Power Query: As a next step, let's try to figure out how we can identify which month is the latest month and which month is the previous month. Because if you take a look at our draft here, anytime we press Refresh, we went to grab the data for the latest month. So somehow we need to flag this in our dataset. And we can create this logic either under PowerPivot side or the Power Query side. In this example, I'm going to do it in the Power Query side. Let's bring up our queries, data queries and connections. We have the dates in the sales data query. This is our fact table. This is where we collect all the details about the orders. Now notice our dates here are obviously repeated because we have many orders on the same day. What we want to do is to create a separate query that holds unique values for the dates here that you could create a separate calendar table from scratch in power query. And that table could include every single day even if we didn't have a sales. So you would look at the minimum date in our query here, take a look at the maximum date and populated with every single day in between. Now that would be good if we had different fact tables with dates in it. In this example, we just have the sales data table. So we can also just use our date column from here. So I'm just going to keep it simple and go with this, these column and then remove the duplicates and somehow tried to flag the latest month. Let's go and reference this query. So right mouse click and reference, call this date info. Now, all we want to keep in here are the dates and we would have removed the duplicates. So let's start by removing the duplicates first, rightmost click, Remove Duplicates. Now let's remove all the other columns. This is the only column I went to keep. So rightmost click, Remove other columns. I'm gonna rename this to just date. Now I have a unique value for the days. I have a 104 rows. What I want to do is extract some information from this. So for example, what would be good to have in the pivot table is to go and add a column, just grab the name of the month. It would also be good to note the month number. So let's go back to date, month and select. Another thing that would be good to have is to get the first of the month. So let's go back to month and go to Start of month. We can also extract the year from here if we have detailed going over multiple years. So in this example, we just have data for 2020 and we're going to be updating our CSV file and adding into data. For the future months. So right now our latest data is for April. Okay, so now that I have this setup, let's try to figure out how we can get the latest month. Well, one way is to take a look at the month number column, go to transform and grab the maximum number from here. Under Statistics, select maximum. This changes our query to just one number. It's called calculated maximum. Now I'm going to rename that step, so it's going to be easier to reference later on. I'll call this calc Max and press enter. Now, I want to move back to my previous step here. So let's add a step by clicking on this icon here. By default, it references to previous step, but I went to skip that and jump back to this step. So let's carefully start to type in inserted startup Month. We see it pop up here, so the correct step is inserted, start off month, select that and press enter, and we go back to this step. So I'm just going to rename this step as well. So let me just press F2 typing, go back and press enter. So this just means that bring back my table. You can call it anything that makes sense to you. Now the reason we added this next step and then we went back is because we want to calculate this once and then reference it later on. What I could do here is to add a column that checks the Month Number and compares it to the calc max number. If they're the same, then we're in the latest month. If we're won't be load the max, then bring the previous month. This sounds like we can use the IF function even Power Query. Now I could go and add a column at a custom column and write this from scratch. Or I can let Power Query do some of the work for me by starting off with a conditional column called this month flag. Now what we want to have is if the month here equals, now, I want to say if it equals that calc Max, but I can't type in variables in here. So I'm just going to type anything as a placeholder, just typing equals four. And we're going to change that later in the formula bar. So let's see if that equals the calculated maximum. Then I went to see latest. I also want to add a clause for the previous month. So let's say if month equals now it's going to be Calc max minus one. I'm just going to type a placeholder in there. Just put it three. Then I went to see previous. These are going to be my flags. Otherwise, I went to see null. Ok, so let's go with a K. Now. Here we should see latest for April because that is our latest months and we should see previous FOR march. But we've hard coded the numbers here. This is when we can go to the formula bar and adjust the function as we see fit. So instead of four, I'm going to reference calc Max because I didn't have a space in between. I don't have to worry about the hash sign or the quotation marks. I can just typing kept Vmax. Just make sure the capitalization is correct. Instead of three, I want to replace that with calc max minus one, then its previous otherwise null and this press enter, we get the same results except that it's dynamic. So if we get information for the next month, these values will adjust automatically. Before we send a Saturday, somato must adjust the datatype to texts. Everything else looks good here. Let's go ahead and create a connection to this and send it to the data model as well. Only create a connection at the City Data Model. And ok. Now, as a last step, we need to create a relationship between the date field in the sales data and a date field in the day's info. So let's bring up our relationships from PowerPivot window. Let's go to diagram view here. This is our data table. Click on date and drag it to order date. One-to-many relationship is made. Everything is employees never ready to create our pivot tables, which are going to be feeding our dashboard. 9. Setup Calculations with Pivot Tables for Latest Month: Now that we've imported to data, to the data model, and we've also created the relationships that we need. It's time to do the calculations for our dashboard. We're going to use the help of pivot tables to get these done really fast. Now the first thing that we need is to figure out which month is the latest month so that we can grab all the information we need for the Dashboard. Let's go and set up our first pivot table. So let's go back to the calculation tab, goes to insert pivot table. Use this workbooks data model and OK. We get our pivot table fields here and we just drag this and bring it closer to our pivot table. These are our different tables. And inside we can see the column headers. Now I prefer to organize this slightly different. Let's go with field selection and selection area side-by-side. This gives us a better view of our fields. What we need is our month flag. Let's add this to our pivot table. But I don't want it in the rows. I'm going to drag it and drop it in the filter here. But I went to see in the rows is the month name. Let's change the filter to just show latest. And okay, this means that this cell here will always show the name of the latest month. This is the name I can use a my dashboard. Now what I don't need from here or grand totals. So let's just turn that off and let's also updates the report layout to be in tabular form just so that we can see the header here. This is the information that we're going to use on the dashboard side. But what I also want to get is the name of the previous month. So I'm going to copy this pivot table. Just press control C. Go to the side here. Let's paste this in. And now let's update this to show the previous month. So just change the filter selection to previous. And okay, the first part of the dashboard is set. Let's take care of our header here. Go to Insert under illustrations, select shapes, and just select a shape that you want to add in the header. Adjust the shape format. I'll take away the shape, outline and also update the shape fill color to this dark blue color as the title I'd like to have sales overview for, and then the name of the latest month. Now if I want to add that text inside the box here, this means I need to use a cell reference. I have the option to use a name from named Manager or use direct cell references. I can type in any Excel functions or combined cell references in here. So what I need to do first is at the title that I want to have as my dashboard title in a separate cell and then connect my title box to this cell. So what I want to have here is sales overview for, and I'm gonna add a space quotation mark and connect this to the month name. So this is the latest month name and presenter. That's what I went to see on the header of the dashboard. Now let's go back to the Dashboard tab. Select the box here, directly go to the formula bar, type in equals, and reference the cell for this height. Oh, that's my reference. Press enter, and I get that into Title. I can't really see it because it's dark. So I'm gonna select the box, change the font color to white, and adjust the positioning so that it's in the middle. The title is done. Let's move on to the first part of the dashboard. 10. Link Excel Shapes to Data & Linked Picture Trick: Now let's calculate the total sales value for the latest Month and the previous one, and bring it to the dashboard here. I've drafted it to look something like this. I also want to calculate the percentage difference to previous month that to set this up, we're going to need the total sales value. We're also going to need our flag for the latest mud and the previous month. So let's go and create another pivot table with this information. I'm just going to copy one of the existing PivotTables, paste it right here, and then adjust the fields as we need from the all fields here. Let's bring in the total sales value. So I'm just going to place a checkmark to push it to the values section here. I don't need the month names, I'm just going to drag it and kick it out. I need the month flag, bring that to the columns. This is the information that I need for the dashboard, latest and previous. I don't need the blanks. These are all the other months. So what I'm gonna do to fix the positioning of this to make sure latest is always first and then its previous is to sort this. So right mouse click sort and Sort a to Z. And I'm also just going to filter out the blanks from my pivot table here. As a next step, we want to calculate the difference to the previous month. So one thing I can do is to write a formula, latest divided by previous minus1. But watch this. When a select this cell, I automatically get the GETPIVOTDATA formula. This function ensures that even if my pivot table fields change, if I have more fields in my pivot table and this cell gets shifted, the GETPIVOTDATA still pulls out the correct cell from the pivot table because it's not looking at a specific cell of our friends. Instead, it's looking at specific fields. So GETPIVOTDATA is generally a good thing, and I do use it in some dashboards. But in this specific case, I don't really need it because this pivot table is only restricted to two values, two latest and previous, it's not going to expand and it's not going to shift. Its sole purpose is to give me the values I need for the dashboard. So in this case, I prefer not to use it. Now you have a few options. How you can avoid it. One way is to directly type in a Salvia friends. So you can do you can also turn off GETPIVOTDATA from your global options. To do that, you need to go to File Options under Formulas, takeaway to checkmark for US GETPIVOTDATA functions. Now, I prefer not to do this because sometimes I need it. So I'm going to leave the checkmark on and go with okay, another option that you have is to copy the values from your pivot table. So just highlight them. Press control C, go to an empty cell, right mouse click, Paste, Special. And paste the links. Now you can reference them directly without using the GETPIVOTDATA function. But these are the different options that you have. It's really up to you which one you wanna go with. You can of course, use the GETPIVOTDATA function and everything will work as well. It's really based on your requirements and your preference. So in this case, I'm just going to use the direct self-referencing to calculate the difference to previous month. That's B5 divided by C 15 minus1. And that's my difference. Let's format this as a percentage. Not it would also be good to add some conditional formatting to this. So with the cell selected, go to conditional formatting and add a new rule. We want to format all cells based on their values, the format's style. Let's go with Icon Sets. And I'm going to select the first choice here and adjust this so that I can see the green arrow if the difference is greater than 5%. So since the percentage here is a number, I need to change this to a number and then put in 5%, so that's 0.05. Then if it's less than minus 0.05, I went to see the down red arrow. Otherwise, I went to see the yellow arrow here. So let's change this first to a number and then update this to minus 0.05. Okay, so this looks good. Click on OK. And now we have our arrow in place. Now that we've done the calculations on the calculation side, let's bring in this information to the dashboard side. First off, we need to insert a shape. So let's go to Insert illustrations and pick the shape that you like. Now you can also bring in shades from PowerPoint if you're already using shapes in your presentations that you like, that's what I sometimes do. You can create your own shapes by inserting a shape and then editing it. So in this case, if I go to Edit Shape and edit points, you get the ability to drag the points or add points and create your own custom shapes. To add a new point to adjust, you just have to click and then drag and then click and drag. And this can become accustomed shape that you use throughout your dashboard. Now I'm not going to use this one. I've already created a custom shape. So I'm going to remove this and replace it with my own shapes. To these shapes, let's add our information. We need the formation for April, that's the name of the latest Month and the values are right here. So let's also format these accordingly. Highlight the cells, press control one to bring up number formatting, use a thousand separator, 0 decimal places, and OK. Now let's go to the dashboard side and insert a textbox. Once you insert a text box, directly go to the formula bar. So just make sure that you click on the border of the textbox so that you're not inside the text box. Then go directly to the formula bar, type in equals goto, the calculation sheet, and let's select a seven. So this is where we have the name for the latest month and enter. Okay, so that's what we're gonna be showing here. Now, let me just copy and paste this again. Now, this time we want to show it the sales value. So let's replace this formula with the value we have here. Now, here's another reason why I copied these as links. Because check this out. If I instead select the other cell that's inside the pivot table. But do I get I get the GETPIVOTDATA formula and I can't press enter because I just need to use a direct cell reference here. So what I would have to do if I want to use that cell is I have to go and type in the solver friends, which would be 13. Instead, what I prefer to do is to directly link to the new cells that I created right here, because they don't give me any problems. I'm going to repeat that for March as well. Now all I need to do is update the formatting of these. So let's select all of them. Just hold down the Control key while you make the selection, go to font color and change this to whites. One thing is missing from here. I wouldn't be able to see the percentage difference. So I want to also bring this on top of my shape because this is a cell that includes conditional formatting, I need to use a trick. The trick is to copy the cell, go to Dashboard, go to paste right here, and to paste it as a linked picture. Not this is a picture, so I can position it on any object that I want. My doesn't look so nice here. So let's just update the way it looks on this side because it's a live picture. Anytime a change the look of this or anytime the value here changes, my linked picture will also change. So let's update this to be a lot lighter and let's adjust the cell color to be identical to the color of my shape, which I think is this color. This is now easily readable. It looks good on here as well. One thing I might want to do is to also add the currency to this. So instead of formatting them just as a number, let's actually adjust these and format these as a currency. I'll just remove the decimal places. Let's look at our dashboard. And everything. Looks great. 11. Top 3 Sales Managers & Numbers (Sorted Excel Pivot Table): Okay, so we already created the first part of our dashboard. Before we move on, let's make some minor cosmetic adjustments to this. I'm just going to change the header to bold. Let's also remove the grid lines and also group these shapes together. Because every time I went to move this shape, I don't wanna have to worry about selecting the other shapes as well and moving them together. So let's select all these shapes by holding down the control key and clicking on the text boxes and the pictures. And then we can right mouse click. So a group from here and group again. Now we can just move this object in one go without having to worry about selecting all the objects together. Let's do the same for March as well. So click, click and click. But you can also group by going to the menu here. So from Shape Format under group options, select group. Okay, so far so good. As a next step, we want to create this part of the dashboard, which looks like this. We need to get the names for the top three sales managers together with the sales amount. And we want this sorted as well. Actually, I'm going to copy this text box and bring it over to the dashboard. Let me also bring the shapes that we can use for this. I already created them as just pull them up here. So these are the standard shapes that I used before. I just changed the color additive circle in there with 123 because these positions are all fixed. Now let's take a look at how we can do the calculation for that. Let's start off by copying one of our existing pivot tables. So I'll just copy this one and paste it right here and adjust this as we need. I need sales value in there, so that's fine. We can leave that in the month flag is necessary, but I'm going to drag and drop it in the filter section because I want to fix that to the latest month because I only want to see the tops resales managers for the latest month in the rows. I need the sales managers, but I don't wanna get their IDs. I went to get their names. So I'm going to find that table in the all tab here under master sales m. Let's expand this. I have person ID and person name. So I need their names. Let's just put a tick mark here and it's gonna put it in the rows. Let's go and switch our selection here to just show the latest so I don't need multiple items. I wanna fix this to latest. Next thing I wanna do is to make sure this is sorted. By sorting it from largest to smallest. I am only interested in the top three values. So let's use the same trick we did before and copy what we need from this pivot table and paste it here as links. Since we're gonna be using textboxes, it's easier for us to just reference the linked cells here. So I'm going to press Control C over here, right mouse click, Paste Special, and paste delink. Next, let's also update the formatting of this. I'll apply the currency format and remove the decimal places. Okay, so this is the information I want to have on the dashboard side. Let's add our textboxes. Similar approach to what we did before. Let's go to Insert texts, textbox, and let's start off by referencing our first sales manager, which is Hudson in this case. And enter. Now we just have to repeat this for their sales value and for the other names. And then we're done with this part of the dashboard. So I'm just gonna do it really fast. And I'm back with all the textboxes done. Now, one thing you can do to better organize this is to use the align options under shaped format. This way you don't have to worry if everything is perfectly aligned or not. So with these textboxes highlighted, let's go to Shape Format under a line options here, I'll go with a line left. And let's do the same for the values here. Align Left. Now, let's also group all of these together. I'll group each section separate. So let's group this on its own to so that we are flexible in moving these in case we need to. And the last one as well. Now, let's also make sure that these are aligned, align left. Okay, so you can do all of these later as well. And in fact, a recommended that you don't spend a lot of time when design at the very beginning, but concentrate more on getting the detail onto the dashboard side. Okay, so this part of the dashboard is done. In the next lecture, let's take a look at how we can get our sales by product category in a table on the dashboard. 12. Linked Table for Sales by Product Category: Now we're ready to set up the third part of the dashboard where we're gonna create a table this time that captures the sales value by product category. And we want to do this for the latest and compare it to the previous month. Let's set up the calculations that we need for this. But before I start to add more PivotTables to this, let's just organize this part a little bit better, give each section a title. So I'll call this top sales managers. I'm also gonna formatted slightly just so that I know this area belongs together. Give this one a name as well, and format this too. Okay, so, so far we've set up our different pivot tables. Whenever we press refresh, our pivot tables will go and fetch the data from the external files. Now currently, which still have April as our last month. But once we're done setting up our dashboard here, we're going to upload my data and check if everything updates automatically. Now notice what, hey, press refresh these PivotTables collapsed. It's because it's trying to auto fit them and it auto fits the first one, it messes up the other ones. So one setting the I'm going to change for these in Pivot Table Options. I'm going to take away the checkmark to auto fit column width on update. So I'll do it quickly for these ones as well. And for the last one, the pivot tables will stay like this. Let's quickly try it. I'll press refresh all again and the fit didn't change. Okay, so now that we have this part setup, let's start to work on the next part of the dashboard, which is to get the sales by product category. I'm just gonna copy this, paste sit here. And let's start with our title. Let's set up the pivot table that we need. I'll just copy this pivot table and paste it in here. You don't necessarily need to copy the pivot tables. You can also go and insert a pivot table from the data model, but I prefer to copy it because now I've also adjusted the auto fit for each pivot table. I've changed the formatting to tabular format. So at least these settings come along with it when I copy and Existing Pivot Table. Now let's bring up the field's list under PivotTable analyze, and let's select what we need. So we still want to take a look at the sales value. We want to have the latest and previous in the column, so this is fine. But what we're missing from here is the product category that's sitting inside our master product table, and it's called product group. So I'm going to place a checkmark here and it jumps to the rows. This looks good. This is actually what I want to show on the dashboard side. The only change I am going to make two it is two sorted from largest to smallest for the latest month. This all looks good. Now as one check here, what I'm going to do is to activate the grand totals just so that we make sure to total of this. And this is the same as this. They should be. We just have different views of the data of one is by sales manager, the other one is by-product group, but they should all add up to the same number. So let's just double check that by going to design an activating the grand totals. I'm going to turn this on for the columns. So that's the same number as here. That's good. And let's also do it here. Goes through design grand totals on, for columns as well. And these are the same numbers. Okay, so as a next step, I just have to bring this to the dashboard side. So let's go and check what we need to do here and where we're going to add it. Well, I'll be adding it to this side here. Now, it would be nice to add some type of visual separation to our dashboard. So let's go quickly and insert a shape. I'll insert this rectangle here. Let's add this somewhere here. Take away the outline and change the shape, fill to a light grey. Now just copy and paste this, turned us around. Make it slightly thicker, and let's make it longer as well. Okay, so far so good. Our title for this section is sales by product category. I'll just copy it from our draft page, paste that here and adjust the formatting. The other thing I wanna do is bring over the table from here. This is the data I want, but I don't wanna show latest and previous as the title. I actually wanna show April and March. So let's copy these two cells. So I'm going to hold them control. Click on E7 were marsh is press control C. Go back to the Dashboard, go to the cell where I want to have the headers. So I'll put them in the C column here. Right mouse click, Paste, Special, Select Paste link. And I get a link to the calculation sheet, which is perfect. So all they have to do is to format these. As I like that they're a bit too big, so just, let's make it a bit smaller. Now to grab the data from and the calculation sheet, I can just highlight this. Press control C. Go back to the dashboard and right mouse click, Paste Special and paste the links for these as well. Now all I have to do is adjust the formatting to make these fits better. What I sometimes like to do is to use a rho as my border. Notice how I have this dark green for the latest thetas are for the latest month and then lighter green for the previous month. I'm going to keep that consistent throughout my report. So the moment someone sees that color, they know that this information is for the latest month. So I'll highlight this this dark green color. And for March it's going to be this color, which is this one. Can we can make this thinner as well. Okay, so the other thing is there's too much gap here. I'd like to collapse this, but check this out when I collapse this right now, these shapes also get collapsed. I don't want the shapes to change their style. So I'm going to press Control Zed and adjusted properties of these shapes. So let's click on both of them and press control one to bring up the properties from the Size and Properties options. Let's expand Properties. I wouldn't move these, but I don't want to size them with the cells. So select bad, sometimes I select this, sometimes I select don't move or science which sells its up to you and how you want these to behave when you expand or shrink the columns. So now when I shrink this, this can move with it, but it's not going to change its shape, kill it's also give the B column a little bit more space and adjust the number formatting for these numbers suppress control one to bring up the format cells options go to Number Formatting, use thousands separator, and 0 decimal places. As a last step. Let's also calculate the difference in percentage between the latest month to the previous month. That's simple. All we need is the value for the latest month divided by the value for the previous month minus one, uh, send us down, change it to a percentage, and increase it to one decimal place. And I'm also going to make it a bit smaller and make this column a little bit narrower. That's it. We're done with the third part of the dashboard. In the next lecture, let's set up our pivot line chart. 13. Excel Pivot Chart for Monthly Sales: Now it's time to take a look at our sales trend. We wouldn't do that with a line charts. So we went to have months here and plot the sales values. Let's take a look at the type of detail we need on the calculation sheet. Now I'm just going to bring this a bit down. Let's copy and paste this here and rename this to sales by month, what I need on one side or the months and then the sales value. So I don't need to restrict this anymore to latest and previous. I want to have all the Munson here. Let's start off with any pivot table. I'll just copy this, since it's right here. Bring up the field's list. Let's update our values here. So sales value, that's fine, that's what we want. But we don't need the product groups. I'm going to kick that out. We also don't need a month flag. I'll kick that out as well. What we do need is to get every single month in Nero's. I'll go with the start of the month. So not in the columns and you just drag it and bring it to their rows. That looks good. This is the data that I want to plot a my line charts. So let's go ahead and insert a line chart based on this, select a pivot table. Go to insert and insert a pivot chart. Instead of a column chart, I want to go with a line. Let's just go with the first choice here and click on OK that sit. Now all we have to do is to format this the way we like. I don't need these buttons here, so let's remove them from the chart. Go to pivot charts, analyze, and this is a toggle. So if you click on it once it removes them, if you ever want them back, just click on it again and they come back. I removed the title from the chart as well because I'm going to include a title on the dashboard side in a separate text box. We can update the color of the line charge if we need to from the format dialog box under shape, outline, this color is fine. I'm just gonna go with that. What I am going to add to it though are markers just to bring more attention to the data points with a line selected, press control one, or just double-click on the line to bring up the charged properties and their color options. Here we get Marker options. Expand the marker options here, selects built in. And I'm gonna go with the round markers and make this a little bit bigger. Let's also make these lines a little bit lighter or thinner for the wait, let's go with a much thinner weight. For these data points. I can also add data labels to it, but because this line is going to expand as I get more months here, it's could get too cluttered. So instead what I'm going to do is to update this values here to show in millions instead of the full value. And we can do that by updating the number formatting in the pivot table. So rightmost click on any of these numbers, go to Number format. Go to custom, select one of these number four minutes that has a 1000 separator and check how a 1000 separator is in your system. If it's a common like me, we have the same regional settings. This means that if I add a comma here, I'm going to show the value in thousands. And if I add another comma, I'm going to show it in millions. If you have different regional settings. So for example, you have German regional settings. You're gonna see a dot here instead of a comma. So you would need to use a dot to show your value in thousands and then two dots to show it in millions that to make sure that everyone knows this is in millions. And I'm gonna add a quotation mark as space and then put an M and a quotation mark again and click on OK. This changes my data here, but it also changes it on the chart side. We can also add decimal places to these. So let's go back to number format. An ad right after the commas and a dot, and then 00. So I'm adding a dodge because a decimal in my original settings is the dots in case yours is a coma, can need to add a comma here and click on OK. And we get the values updated and the chart as well. Now our charts is ready so we can bring it over to the dashboard. Let's cut this rightmost click cut or use the shortcut key Control X. Go to the dashboard side, and let's paste it Control V and then adjusted to fit our dashboard. Now one thing I would like to remove is the border of this chart. So select the chart, go to format under sheep outline, select no outline. What we're missing here is a title. Let's go and see we have it on the draft side. We have sales by month, so let's copy that, bring it over to the dashboard and adjust these to fit. Now, if you ever want to copy the formatting of one of your text boxes to another textbox, You can use the shortcut key control shift C. So now controls C because that's going to copy the content, but control shift c and then go to the textbox. You want to paste the formatting in and use the shortcut key Control Shift V to don't forget the shift there, the case. So our second part of the dashboard is done. As a next step. We want to create our top five customers by product category and a top five cells employees as well by product category. And here it's going to make sense to add a slicer for product category. So let's see how we can do that next. 14. Pivot Slicer Connected to Multiple Pivot Tables: Now we're ready to create the last part of the Dashboard. What we wanna do is to create two bar charts. One is for the top five customers by product category, and the second is for top five sales employees by product category. So we want to show to sales value. But we went to have the product category as a slicer so that we can easily control and filter for the product category that we're interested in. For each of these, we're going to set up a pivot table. So let's think about what values and fields we need. But we know we need the sales value for this one. We need customers. For the second one we need the employee names. We want the product category as a slicer, and we want to show both of these just for the latest month. So we need to have that as a filter. This go quickly and set up our calculations. I'm just going to pick a pivot table that's closest to the one that we need. This one looks good because it already has the month as a filter and it even has our employee names. That a difference between this one and the one that we're going to make is that this one is not connected to a product group slicer. The values we see here is for all product groups, the new one that will create will be connected to a slicer. But this is gonna give us a good starts that I'm going to copy this, go down here, and paste it here. Now, also add the headers here. So this will be top, let's call it top five sales managers, byproduct group. Let's replicate this as well. On this side, this one is going to be top five customers buy product group. Ok, so now we have our placeholders. Let's start to change them to what we need. This is going to be our customers. Let's bring up the field list for the pivot table. Instead of the full name, we want to bring in the customer, which is sitting in the master customer table, and it's called customer name. Now, notice that we have a lot of customers and I don't want to populate my calculation tab with a lot of information that I'm not going to need. Since I only need to top five customers, it makes sense to add a filter. I can use a value filter here. Select top ten instead of the top ten items, I went to see the top five items by sum of sales value. That's good. Click on OK. Let's update the number for matching for this to show currency and 0 decimal places. Now, one thing you can do as well, because this is a PowerPivot and not a normal pivot table, is to change the grand total to show the complete grand total. So the total, including the filtered values, not just what's visible here. That's one advantage of using PowerPivot. So if I right mouse click, go to Pivot Table Options. Under Totals and Filters, I can put a check mark beside, include filtered items in totals and I get the complete grand total. I'll just keep it like this. I can always check my totals are matching in all views. This quickly repeat these steps for the sales managers. So to restrict this to the top five sales managers, we can go to value filters, replace the ten with a five. And okay. Let's also apply our adjustment to, to Pivot Table Options for totals here as well. So we're consistent across des PivotTables. And other thing we need to do is update the number format to show currency and 0 decimal places. The case. So, so far so good. We have our two pivot tables set up. Next is to add a slicer. I'll start off by adding a slicer to the sales manager Pivot Table to with this pivot table selected. Let's go and find the products group, because our slicer is for product category, which is called product group in our database. I'm going to right mouse click and add a slicer. Now, this pivot table is connected to this slicer. When I click on clothing, I see the total sales bind a top five sales managers for clothing. This one is not connected to the slicer yet, so I'm going to connect it. Just activate the pivot table by clicking inside any of the cells. Goes who PivotTable analyze under Filter connections, this is our current slicer. It doesn't have a check mark because it's not connected to it. So I'm going to place a checkmark and OK. Now let's close this. Every time I change my selection in the product category here, these values adjust as well. Okay, so the hard part is done. Now let's set up the bar charts that we need. Let's start off with the customer. Go to Insert Pivot Chart. This time, let's add a bar chart. I'll just go with the first election here. And ok. Now we just have to update the formatting of this to what we need. I don't need this legend here. I'm gonna get rid of the axis labels. I also remove these gridlines here. Instead, let's add our own data labels to select the series right mouse click and add data labels. Now, it would probably be good to add them inside instead of outside. So let's select the data labels. Either double-click to bring up the DSA Label Options, or you can use the shortcut key control one under Label Options here, let's put these inside and it would be good to also adjust the font to be a lighter colors. Let's go with white. Now another thing I'd like to do here is to reduce the gap between the series. So select a series under series Options here for the gap width. Let's reduce this to a smaller number. I'll go with 80%. Let's open up some space here. I've removed the title because we're going to add the title separately to the dashboard. We also don't need these buttons here. So let's go to pivot charts, analyze, and click the fields buttons here to remove them. Anytime you want to get them back, just click on it again and they come back. The case. So this is our first bar chart that's set up. Let's do the same for the second one. Go to Insert Pivot Chart, bar charts, select the first one and OK. Now the good news is that we can copy and paste the formatting of the first chart to the second chart. The way you can do that is selected first charge and press control C to copy or go to home and copy from here. Then select your second chart and then go to Paste and under paste options here, just like paste, because it's the pivot chart is just going to copy and paste the formatting and not the actual values. Okay, so we have everything set up. We should just make this one smaller as well. Now, one thing you notice here is that these are not sorted in any order. It would be nice to get them sorted in descending order. Now, check this out. If I change the sort order here and go and sort this largest to smallest, it actually looks the other way round in the pivot chart. That's the default behavior of Excel. Whatever is on top here is always on the bottom in the chart. Now you can adjust this by updating the settings chart. If you select the axis here and go to x's options, you have something called categories in reverse order that's gonna switch everything. Your other option is to note Sort Largest to Smallest, instead sort smallest to largest. So I'll just go ahead and do that. And the same for the second one, right mouse click and go and sort smallest to largest. Okay, so both are done. Let's bring everything to the dashboard. Well, we just need our charts and we also need our slicer. So I'm going to select the charts first, Control X to cut them. Go to the dashboard side and Control V. Let's go and bring over a slicer as well. Cut this control X, go to dashboard and Control V. Ok, so it would be nice to get this in a horizontal format and not vertical. So we can place it up here with the slicer selected. Goto, the Slicer tab, and adjusts the columns to the number of product categories we have. And then let's just expand this. And now we can place it on top here. Now another thing you can do is to remove the header in case you don't need it. You can do that from Slicer Settings, uncheck, display header and OK. Now another thing I prefer to do when I'm using slicers is to remove the border around the slicer. For that, you need to create your own slicer style. Because all of the slicer options here, they have borders. What I usually do is first pick the slicers filed that I like that fits my Dashboard best and then adjust that to the way I need. This way I need to make adjustments to it. So I'm gonna go with this color. Then, right mouse click and duplicate a slicer. You get to give your new custom style and name. I'll just call it slicer, no border. Now here you can tweak any part of the slicer that you want. If you want to adjust the color, you want to change a font. You can do that from the format options here. In this case, all I wanna do for the whole slicer is to remove the border. So let's go to border, select None and okay, that's the only change I want to make here. I'm gonna go with okay. Now, when I click away, it still has the border because the slicer style is still the old ones. I have to go and select a new style I just created, which is this one. So select it. Now when I click away, the border is gone. Now, all I need to do is to adjust the formatting of these. One thing I like to do is to remove the border of my chart as well. So let's go and remove the shape, outline and also the outline of the second chart. Now as the last step, I'm just gonna go ahead and add the titles to this. Ok, so now we have our top five customers and top five employees by product category. So if I select a specific product, I get both charts to update y because the pivot tables are connected to the slicer and the charts are connected to the pivot tables. That you can go ahead and adjust these to fit the dashboard better. You have control over the plot area as well as the chart area. This looks good. This gives the labels enough breathing space, the case. So we are done with our dashboard. In the next lecture, we're going to get the days have for me and we're gonna refresh this dashboard and see if everything pulls through properly. 15. Finalize the Excel Dashboard: Now we're ready to upload the latest data, which includes sales data for me. So let's go to our folder. I'm just gonna drag and drop the new file with the same name. Duly difference is that this file also includes made eta. I'll just drop it into our folder, replaced the file into destination. Now, all I have to do is go back to the dashboard and refresh the report. So let's go to the Data tab and select refresh. All we should see here is me than April, we should get the new names for our top three sales managers in May, which we do. The table updates, the period line chart here, updates as well. The moment we refresh, we get to see the latest information with a click of just one button, has a few final steps. What we can do, or what I prefer to do is to hide any columns or rows that I don't need. Basically, I just want to restrict my dashboard to the US area. So I'm just gonna select that last column and then use the shortcut key Control Shift and the right arrow key to jump to the last column and highlight everything in between. The right mouse click and select Hide. Let's repeat that for the rows. This time, the shortcut key is control shift and the down arrow key, right mouse click and hide. Now my dashboard is restricted to only this area. Another thing you can do is to group your queries together. So under queries and connections, you can group two queries so you can find them easier. The logic you choose is really up to you. The way I'm going to group these in this example is to group the queries that have connections to an external data source. If I happen to change the location of my source data or the name of the source files. I know where to look. The two queries that have external connections is this one here for the master data connection and the sales data. So just hold down Control and select these. Then right mouse click, move to group and new group. Let's call this new group external connection. You can also give it a description if you like, and then click on ok. So now whenever the location of the sources have changed, I know I should go to the external connections and update these. All I would have to do, double-click, go to the source tab and change the past resource file here. So I can do that directly in the formula bar or click on the gear icon and browse for the file. Okay, so that's for the master data. And then for the sales data, I can jump back to the source step, select the gear icon, and browse for the file as well. Okay, that's it, and our report is done. Now there are some additional steps you can take to optimize this reports. So remember, the purpose of this is to take a look at the numbers throughout the year. It doesn't compare one month to month of the previous year. If you wanted that. You'd have to set up the logic a bit differently. You'd have to take a look at the date column to get the latest state and then the latest month and the year. So you always have to keep your eye on the year and the month information as well. Here we're just dealing with the month number. Now, another thing you can do is tweak this a little bit. So when you have January data, you don't get errors in here because you're not going to have information about the previous month. So what you can do is to put the formulas inside an if error function. We can also set up a logic here that if this month happens to be January, you don't show anything, otherwise, you showed the vile whew. What I've also done here is to take the value from G7 instead of from our pivot table here. Because if this is January, this is going to be incorrect, it's probably going to be January as well. So what this does is it takes a look at this month is January, it shows nothing. Otherwise, it shows this value here. So there are a few things you can tweak here to make sure that their results he's okay for January as well. Now, in addition to this, as you are working with real data, there might be more adjustments to make. For example, we haven't accounted for null values. So what if we have a lot of empty lines? We could filter these out, right? So these are things that you're gonna come across when you're working with actual data. The great thing about using Power Query Is that you can go back to your code and tweak it at any point in time, a case. So that was our project. Let's now summarize everything you learned by building this tool. So in this section, what you learned was how to important connect to external data. In this case, both excel in CSV files. We also use Power Query to program a logic to automatically figure out the latest month is. So we can report on that. We use the PowerPivot data model to create our relationships. We also use shapes to create more elegant and professional looking dashboard. We use a lot of pivot tables to get the calculations we need for our dashboard. It didn't require a lot of complex calculations. Oh, we had to do was to click a few buttons, update the formatting so that we can implement this professional interactive sales dashboard. I hope you'll learn some new tips and tricks in this section that you can apply to your own dashboards. Thank you for taking part in this project.