Microsoft Excel - Data Visualization & Reporting with Charts and Pivot Tables

1. 1. Introductions and the Course Pre-requisites: Hundreds of millions of people use Excel every single day for various tasks and spreadsheet operations from financial analysis to budgeting, to planning, to project management. Some people even use Excel to store their data. When it comes to decision making, Excel is also very useful in crunching numbers and visualizing data to improve decision making. Hello and welcome to this Microsoft Excel course on visualization and reporting with chart and pivot tables. My name is Ahmed Olo. And at the time of this recording, I am a five times Microsoft most valuable professional for the data platform. I'm a Microsoft certified trainer, a Microsoft certified educator, and also certified Microsoft Office specialist Excel expert. I'm also the lead trainer at foresight BI and Analytics. Before you can derive value from this course, you need to have a Windows version of Microsoft 365 desktop applications or Microsoft Excel 2013 and above. Also, you need to have a basic understanding of Excel tools and be able to write on formulas in Excel using Excel functions. By the end of this course, you would have learned how to create charts to visualize data in Excel. You would have learned how to configure and format your chart to meet your taste and demands. You also will learn how to summarize Excel data using Pivot tables. You are going to learn how to configure Pivot tables for reporting purposes and also how to build Excel dashboards. So without wasting more time, let's get started. 2. 2. How to create charts with Alt + F1 Keyboard Shortcut: Going to start with charting, and I'll be making use of this workbook here named one dot Charting. One of the simplest things to do on Excel is to create charts from given data. All we are going to need to do is to flex a simple keyboard shortcut. So I'm going to start with the first set of data here. And to create a chart in Excel, what you have to do as recommended practice is to highlight the data that you want to use on that chart. So on this data set, for example, I have country sales and profit. If I want to create a chart with countries and sales only, I'm just going to highlight country and sales. Then I'm going to press T and F one on my keyboard. Now, depending on the type of keyboard you have, you may need to hold down the old key and then press the function key, that is the F N key. So while holding those two down, you now press the F one key on your keyboard. Now, if I wanted to do country and profit only, for example, I'm just going to select this and delete. So if I want country and profit only, I'm going to highlight the countries. Then I'm going to hold down Control key on my keyboard to highlight the profits. Now notice that my range of highlights covers only within the dataset, and they are of equal sizes. What I mean by that is if I highlight the full list of countries like this, then it doesn't make sense for me to highlight profit up to this point. I need to make sure my highlighting are of the same range with both the categories and also the values or the numbers. So I'm going to highlight my countries. Again, I'm going to hold down Control key on my keyboard to highlight the profits. Then I'm going to press Alt F one. Then I'm going to have a chart for my profits by country. And if I want to do for everything, I can also just highlight the entire range of data and press Alt F one on my keyboard. Now, if your data is so clean and everything seems to be together in a clean fashion without any obstructions around it, then you probably don't need to highlight as long as you are making use of all the data set within that range. So if I want to do sales and profits for countries and I do not have any obstructing values around this range, then I can just stay anywhere within the data range and press my old F one. Now, if I have obstructing things around, say, for example, somewhere here, I just put in a comment or something. Maybe I just type in a simple number like this and I try to use this last method of staying somewhere within my data range to press Olds F one, then there's going to be an issue with my chart because I'm going to have something that I'm unable to explain. And that's why it's recommended that you first highlight the range that you want to have in your chart before you press your Olds F one. 3. 3. Updating Excel charts data range: So we have created this chart using keyboard shot calls Olds and F one. But if you don't like cool things and you want to have a different method to create your chart, another thing you can do is to go to the Insert tab right there. So if you click on the Insert tab, you are going to have this group of buttons for chart, and you can start with the recommended chart if you wish, or you can just go straight to select any of these chart types that we have here. But we are going to stick to the cool ones for now, which is old and F one. So apart from that, so once you have created your chart, when you select your chart the way I have currently now, you see if I click inside the cell, the chart is no longer selected, but if I click somewhere on the chart, then the chart is selected. It shows me the range of data that we have inside the chart. Now, one thing about selecting your chart is your chart is made up of some elements or certain elements inside a placeholder. So if I want to select the chart as a whole, then I'm going to make sure that I am not clicking inside of the main element. Rather, I want to click on an empty area within the chart, preferably somewhere along the top right hand side to make sure you are not obstructing with anything or somewhere along the top left hand side right there. That helps you to select the chart as a whole, which makes it easy for you to do general chart operations or for you to even delete the chart with the delete key if you want. Now, if I were to increase my amount of data here, so let me come here now and just add Nigeria for country. And then for sales, let's make this 30 million. And let's make this profit around 5 million. You are going to see that that doesn't automatically update my chart because Nigeria doesn't show up here. So if I need to update this chart, what I'm going to do or what I can do is to first click on the chart to select the chart. Then at the top of your tabs, you should see chart design and also format, but we are going to be heading straight into the chart design. So if I click on the chart design, somewhere on the right hand side where you have data, we are going to select select data. So when I click on Select Data, I'm going to have this dialog box that shows me my chart data range. And it also highlights it down there. All I need to do is to update this range. So currently, it goes 5-10, but then I have added Nigeria on 11. So I'm going to change my ten here through 11, and I'm going to click Okay, and then you can see that my Nigeria has now been added. Now, I'm just going to press Control Z on my keyboard to undo that, right? So Nigeria is no longer there. If you want something more of a cool method to still update the Nigeria part, what you can do is highlight this latest data record that you have like this, then Control C to copy it, and then select your chart the right way and Control V to paste it, you are going to see that we now also have Nigeria's right inside the chart. So I'm going to delete this chart. Again. And now, I'm just going to create sales by country. So if I highlight sales by country, I'm going to press or F one on my keyboard. This shows me sales by country. I know that if I need to update this chart with profit, what can I do? I can go to my chart design at the top. Then I'm going to select select data. And of course, because the range is currently between B and C, I'm going to need to change this C to D. Alternatively, you can use that cool method of highlighting profit Control C. Then select your chart the right way and page with Control V. 4. 4. Using data in an Excel table as chart data source to automate data update: Alright, so I'm going to delete this chart. I'm selecting at the bottom left hand side, an empty area. Then I'm going to press delete on my keyboard. So you have seen two methods for updating our chart. Let's go to data number two now. You probably will not need to use any of those two methods for updating chart data if the data has been stored in a data table format. So because in Excel, your data can be stored in several different ways. One of them is just as basic cell entries. Another one is to store the data as a data table. So to store data as a data table, first, we need to highlight the data range or if the data is very clean and super clean without obstructions around it, without empty rows and empty columns, we can just stay somewhere within the data range and press Control T on the keyboard, T four table. To Control T on the keyboard, we need to ensure that our table has headers. That box needs to be checked. Then I'm going to click Okay. So with my data in a data table like this, if I want to create a chart, let's just say for sales by country, I can highlight my sales and country and press or F one on my keyboard. I have my sales by country, of course, with five columns one, two, three, four, five. So if I happen to update this data set, if I go to the next line to try to update the data with Nigeria, first, the table is going to expand and immediately you can see Nigeria already appears on the chart. Then I just need to update my figures 30 million, one more zero here, and profit of 5 million. Of course, the profit side is not even on the chart currently. All right. So I think one more zero is also necessary there. So if your data is stored in table format, then the chart should automatically pick up any increment of data. It's a different discussion entirely whether data should be stored in table format or not, it all depends on a lot of things. Of course, as far as update is concerned, it is better to have your data stored in table format. But then as far as performance is concerned, then it may not be a good option sometimes, especially when you work with large amounts of data set or very, very large Excel workbooks that can be prone to crashing. 5. 5. Creating a chart from data that is not summarized: We create charts, what we are trying to do is to be able to visualize how categories of data perform against themselves typically based on something that we have been measuring. So for example, in this case, we are trying to compare different countries based on the amount of sales that each country has. What it also means is that for us to create a chart, our chart must have a category, and then it must also have some number columns for measurements that we are using to compare each of those categories. So you're going to see that every single category on the chart is unique in the sense that on our chart, we don't expect to see Canada appearing twice. Neither do we expect to see any of these countries appearing twice because it's going to defeat the purpose. So let me delete this chart, and let's go to number three data. So for number three, data, if this is what our data appears to look like, and we are supposed to show something like sales by country or maybe profit by product, then if we try to use the method that we have described so far. So let's say I want to show profit by product. So I'm going to highlight product. So a quick smart way to do that is when I click on the heading for product, I'm going to hold down Control and shift keys on my keyboard. Then I will press the arrow down key once. So control shift, arrow down. So this highlight the entire range of products. Then I'm going to scroll up and hold control on my keyboard to select profit. Then I will leave control and then press Control Shift and arrow down again. So this way, I have highlighted product and profit. And when I press all F one on my keyboard, then this does not make any sense because I have multiple bars for this particular product alone. Whereas what makes sense is for me to be able to see the entire amounts for this product so that I can compare it by the entire amounts for that other product instead of having them separated. This chart right now is as good as data rather than chart. So when our data is not summarized, the first thing we need to do before we use such data to create chart is to summarize the data. So if we are going to summarize this data for product, for example, then we need to have a unique list of products first. So it means that I have to pre work on this. What I will do is highlight the range of products. When I click on the heading, I'm going to hold Control Shift and down arrow Control C to copy it. I'm going to try to go to just a different worksheet, and I'm going to paste that list there. And then I will go to the top of my tabs where I have the data tab. When I click on the Data tab, I will go over to the group of data tools, and I want to use this one that has the X mark. When you hover on it, it should show remove duplicate. So I'm going to click on that button, and I'm going to click Okay, to remove the duplicate. So this is a unique list of products that we have. I can copy out this unique list and I can bring it into this port if I wish, I'm going to paste it with Control V. Then because I want to summarize for profit, I can create a new heading here for profit. And the next thing I'm going to need to do is to create a summary of the total profit for each of these products, which I'm going to do in the next video. 6. 6. Creating a chart from data that is not summarized (part 2): One of the reasons you need to understand how to write Excel formulas to be a better data visualization experts in Excel is because sometimes the data you need to visualize might need some preparation, and you may have to make use of Excel functions for such preparations. So now I want to calculate the total profit for each of the products on this list, and I'm going to be using the sum I function in Excel to do that. So if I come here to profit, I'm going to type equal to sum Eve and I'm going to press tab on my keyboard to open my parenthesis, and I'm going to press Control A on my keyboard to open up my function arguments dialog box. In the first input, I need to highlight the range of my categories. So I'm going to start from the heading of products. So just by selecting that C 27, I have selected the starts and for me to highlight the range up until the ending, I'm going to hold Control Shift and down arrow key on my keyboard. For this ring to remain fixed, before doing anything extra, I have to press F four on my keyboard to lock it. Now, if F four doesn't work to lock and it just shows something like brightening of screen or something like that, and you don't see dollar signs in between the columns and the rows of the ranges, that means you have to hold down the function key, that is the FN key on your keyboard before you press the F four. So now that range has been locked. Now, the criteria I am trying to some for in this range, if I go to the next IP to supply that criteria, that criteria is for I'm currently calculating for this particular first product, so I need to click on that, which is on my H 30. Then I will go to the third input for some range and remember you have to start from the start of your range. So the start of my range is profit when I click on the heading, E 27 to highlight all the way down, I'm going to hold down Control sheet and down arochyOce. Then I also need to lock this range with Four AO or function F four, depending on your keyboard. Now I'm going to click Okay. And then I can drag down this formula to calculate for the other products, and to make these numbers to appear properly with commands separators, I'm going to press Control Shift and one on my keyboard. So now I have the summarized version of that data. I can highlight it now and press F one to create my chart. 7. 7. Editing chart axes in Excel: Okay, so let's delete these charts and minimize Data three and go to Data four. So for Data four, I'm going to create a chart that is going to show my sales by year. Remember that our charts must always have categories and values. In this case, my sales represent my values or my numbers or my measurements while the years represent the categories. So if I highlight my year and my sales and I press on F one on my keyboard, this is what I have. I'm supposed to have my chart categories as 2020, 2021, 22, 23 and 24. But what I have here is one, two, three, four and five. So why exactly is this happening? So because the two columns contain numbers, even though the numbers in my years are not being used for measurement, we are rather using them to identify a period. So they are meant to be categories, but Excel Chart has classified both of them at numbers right now. Whereby it is placing the orange columns as sales and then trying to use blue to represent the year. So it's like for each one of the five groups that I have here, it is trying to compare for each one of them the year and the sales because comparing 24,000,020 20 is almost impossible. That's why you cannot see any blue bars here. But as you mean, I change all of these sales here, for example, to like 2025 I'm just highlighting and typing over it with 2025. Then I'm going to press Control Enter to Enter it. You will see that for each one, it is just comparing sales as orange with values of 2025. And then it is using the years also as values in the chart. Going to press Control Z to undo that. All we need to do here is to correct what is happening. And the problem right now is that first, these years are not supposed to be used as values, and then secondly, we have to use them as categories. So when I select my chart correctly, I'm going to head straight back up to where I have my chart design. So when I select my chart design, I'm going to go over to that same select data. When I click on Select Data, and I have back this dialog box, press things first is on the left hand side is where I can see the values and remember that year is not meant to be a value in this case. So what do I do? I'm going to select the year and I'm going to remove it. Then secondly, or before I go to secondly, the first thing you will notice is that the blue legend has disappeared. So second thing here is that those years actually are meant to be used as my categories. To what I have one, two, three, four, five, I'm supposed to have the years. So I'm heading over to click on this Edit button. Then I'm going to highlight the years from 2020 all the way to 2024, and I'm going to click Okay, and I'm going to click Okay, and the chart should now look better. 8. 8. Changing default chart to other chart types in Excel: Alright, so now that you have already learned how to create your chart, it's time to learn how to make different changes. So first, I'm going to delete this chart right here and I'm going to go over to my data number five. So I'm going to create a chart that is going to show me my sales by month. That means I'm going to highlight month and sales, and I'm going to press F one on my keyboard. So this naturally gives me a column chart just like we have seen all through up until now. If I want to change this chart, for example, to a line chart or to any other type of chart, but in this case, a line chart is going to be more appropriate. So what I need to do is having selected the chart this way, we find our way up to where we have our chart design, and then we are going to go over to the chain chat type option. When I click on the chain hat type option, I get this dialog box where, first of all, on the left hand side, I see different types of groups of charts, and then in the box to the right, you are going to see different options of implementing them. So currently, it's a column chart, but I want to go with a line chart. So if I select line, then I can pick any of the options that we have here as preset for the kind of line chart that I want. I'm just going to go with the simple line chart right now, and I'm going to click Okay, and my chart is going to be changed to my recent selection. And then also, based on the different chart types that you would have selected, just at the top of the chart design, you are also going to see different chart types here. That you can pick from. However, most times this default is usually the best for you to stay on. And if you need to customize something, you can actually customize them yourselves, which we are going to start to look at from the next video. Another option that you have, if you don't want to go over to the chart design there to select change hat type, is to actually click somewhere on the chart. Now, you need to make sure you are not right clicking on the main chart element rather, you are clicking some empty area on the chart, and you're going to see the option to change chart type right there, and that's going to give us exactly the same options and the same results. 9. 9. Introduction to Chart elements in Excel: We're going to make changes on this chart. Right now, we are going to configure or what we call format the chart. What it means is that we are trying to format our chart elements. So apart from the fact that there is the chart itself, which you can select by clicking on an empty area within the chart, as I have said before now, you also have different chart element inside the chart. So this title is an element of a chart. The Y is where we have our numbers is an element of this chart. The X is where we have our different mountier is an element of the chart. The grid lines that we have over here is an element of the chart. Also, the line itself that represents the type of chart we are using is an element of the chart. But these are all the visible elements of the chart. So when you select your chart like this, on the right hand side of the chart, you are going to see this plus icon, and this plus icon allows us to be able to add or to remove chart elements. So if I go to tick grid lines, for example, then you see the grid lines are gone. And also, if I go to tick on data labels, then you see that for each of the month, I am seeing the exact values of sales for that particular month. I'm just going to tick that and then if I go to Axis and I just uncheck the box for axis, then both my Y axis and my X axis are gone. Again, I'm just going to go back to enable the axis. So you are also going to find out that for the different chart element, there can be an arrow on the right hand side that gives you additional options. So for example, instead of on taking all of the axis at the same time, if I click on this arrow to the right hand side of axis, then I can say, I only want to take out the horizontal axis, and I still want to leave the vertical axis or I only want to take out the vertical axis and leave the horizontal one. You also see there is more options there. We're going to come back to the more options later. But typically, our chart axis always include titles. For majority of chart, we always have Y axis. For majority of chart, we always have X axis. And then we also always have our grid lines, and we also always have the main type of chart. If it's a column chart, this is going to be columns. If it's a line chart, this is going to be line. Then sometimes we also like to make use of our data labels that is going to place the exact values on each of the categories. And many times when we are breaking categories down by several things, then we can also have legend. Those are the popular chart element that we have. Now, in Excel, some people also prefer to include a summarized data underneath the chart that is called data table, how necessary this is, I'm not particularly sure. 10. 10. Introduction to formatting chart elements: It comes to formatting of chart elements in Excel, there are so many options. Remember that we have so many different chart elements. Now, within each of the different chart elements, we also have several options. We're going to focus on looking at the standard chart formatting options that we have. The ones that you are likely to make use of on a regular day. So first things first, the easiest way for you to format any chart element is to click on the particular chart elements you want to format only once. Then you can do a right click and Google format it. We are going to come back to that shortly. So once you have your chart selected like this, remember that at the top, you are going to have these two tabs. There is the chart design. That's where we get to select our chart data, and that's also where we get to edit our chart data, where we get to change our chart type. Then we also have the formatting section where we can format our chart element. So if I click on the formatting section now, you will see that on the left hand side, I have a drop down right there where I can choose which of my chart elements do I want to format. So currently, because I have selected the entire chart, it is currently on chart area, but I can use a drop down there to select the chart tattoo. See it gets highlighted, or I can choose the horizontal axis, it gets highlighted. I can choose the vertical exit, it gets highlighted and so on and so forth. Now, whichever one you have selected, then you will go ahead to click on Format selection to proceed to have the formatting Axis pane on the right hand side. But for a lot of people, that could be a long way to go about it because what you can just do instead is to click on the particular chart element you want. If I want to format the title, for example, I can click on the Title one time, then I can do it right click there and select the formatting option that comes up to me. But remember, we have a lot of chat elements, and we also have a lot of options for each of the chart elements. So from the next video, we're going to start to look at some of those standard formatting options that we are likely to apply when it comes to formatting chart in Excel. 11. 11. Formatting fill color for Chart area and text colors on chart: Alright, so we are going to absolutely start with the one that gets to affect the entire chart, and that is when we don't pick any chart element in particular, rather, we select the entire chart by clicking on one of the empty areas right there. Then I'm going to do a right click and I'm going to select former chart area. So for most of the chart element formatting that we have, you should expect to see two tabs in our formatting panel. There is the main part, and then there is also the one for formatting the text options. So on the main part, you are also going to see at least two or three or four options right here. The first option, when you hover on the first option, that should show you feel and line, and that's where you get to change colors of things and deal with lines. Then the second option where you have effect does more harm than good. So I wouldn't look at this side. It does more harm than good most times. Also, you might also get to see a third option that is for size and properties, which you typically might not be using a lot of times because you can manually change the sizes of your chart just by directly interacting with it on your Excel grid. And sometimes you will also see a fourth one that we are going to see shortly that also has to do with dealing with the main chart element. So for fill, it's mostly about colors and lines. So for example, if we change the fill color for this chart from automatic to a solid fill, which is where you get to use most times, then you can select the color that you want for the fill option. And you can also choose the transparency to choose how light or dark it is going to be as far as that color is concerned. I'm just going to go back to automatic here, which is essentially no fuel color. Then you also have borders as well. So something has been applied automatically for the border as well. When I scroll down, you are going to see this light gray color. So if I click on my Excel cell, you can see the border around the chart that is light gray. So if I go back to select the chart, I can go to my formatting area under border. You can change that color as well if you want. It is usually not necessary. Then if we go to the text options, this is where we can globally change the colors of all the texts within the chart, even though we can still go to each of the chart elements to make changes after. So for example, the title here represents a text, so we can change the color from here, as a matter of fact, for all the text within the chart, including this title, including what I have or my Y axis, also including what I have or my axis that has my moons. So if I change the color now to kind of red, it's going to globally change all text colors within the chart. So I'm going to go back to default, which is just color black. 12. 12. Formatting Lines on a Line chart: What makes this chart to be a line chart is because the visual is made of line. So if I want to format the line on the chart, for example, I want to make certain changes to it, then I need to first of all, select the line by clicking on it just once. So I'll just go to any area on the line and click on the line to select it one time. Then I can click on it and select the format option that comes up, which is format data series in this case. Now, like I said earlier, we are going to have three sections or two or four as the case may be. The middle one we are not interested in. So the first part is where we have our feel and line that has everything to do with colors and lines. So for our line chart, if I want to change the color, for example, it's a matter of coming here to select a different color. And also, if I want the lines to have markers, then I can go to the marker session. Then I can open up the marker options here, and I can select built in and choose the type of marker I want. I can even increase the size of the marker if I so wish. But then I'm going to go back to the line because there is one other option on lines that people tend to want to use a lot also. And that is when I scroll all the way down, there's a checkbox here for smooth line, which you might not really appreciate the first time. So let me show you what the line looks like, first of all. If I click on the cell in Excel, this is what my line chart looks like. But if I come back to click on the line and I go to my formats where I have my feel and line, then when I scroll down and I check that box for smooth lines, then the lines have become smooth and no longer jagged. Now, what I can also do to also allow you to see what the smooth line does, if I select the line chart, then I can go back to my filler line, and then I can go to the markers section and when I open up the marker options, I want it to disappear. So I'm going to click on non so there's no longer markers, and it's just a plain line now, and you can see how smooth it is. 13. 13. Formatting chart numbers and display units: All right. So once my formatting panel, my chat formatting panel is active like this, then I can easily go to format any other chat element without using the right click option. So right now, I want to format on the Y axis, for example, where I have my values, my value labels are there. So if I go to select the Y axis by clicking one time, then you can see that I have up to four different options. Also remember that I am now seeing axis options, and I am seeing text options. Obviously, you know, that if I go to the text option side, that's where I can format or change things like the colors of the values of my Y axis here. So let's just try that. So if I go to click on my text options, there, I can see my text pill and I can change the color if I wanted to. I'm not going to do that. But I'm going to come back here to the Axis options now. So when it comes to charts, one of the things that you might want to control on the chart is the ranges between one number and the other. So currently, I am seeing my wire Axis having differences of 1 million each. So I can go to where I have units over here and I can change this major from 1 million with zero in six places to something like maybe 2 million when I'm done, I can just go to click on the minor side just to have a reflection of my changes. Then you are going to see that the Y axis now changes by 2 million differences. Also, another thing that we are likely to do with our values labels is when I scroll down a little bit, you are going to see I have these display units over here. And it's currently set to none which minute is going to reflect exactly the way it is on our data. But sometimes your numbers might have too many zeros, secually when you start to run into millions and billions or even some hundreds of thousands. So what you might want to do in such cases also is to change your display unit. For example, I'm going to change my display unit now to show as millions. So now instead of 8 million or 6 million, I just have eight and I just have six. Finally, one thing that you can also do with our value labels is when I scroll down on the value labels because I am currently formatting axis and formatting my values axis. So when I scroll down now, there is a section that is going to contain my number. So if I go to the number section and I scroll down, you will see that I can control the number of decimal places if I want to use thousand separators or not. And I can also choose what negative numbers will look like. So for example, I'm going to make this one to be in one decimal place, and then I also want to go to the formatting code. So on the formatting code, we can apply a custom format, which means first, we can untake this link to source and then if I change this guy back to one decimal place, I can come to this formatting code with a double quote open. I can put in an there to represent millions and a double quote close, and then I'm going to click Add. Then these numbers are going to appear with an suffix. 14. 14. Using Combo charts in Excel and formatting Columns Gap Width: Options we have in formatting chart sometimes depend on the type of chart. Now, whereas a lot of charts share different kind of chart elements and components such as grid lines, such as X axis and Y axis. Some are just so peculiar to the type of chart. So, for example, smoothing of lines is particular to line chart. So let's go take a look at some examples that we might find with columns. Columns are also similar to bars as well. Columns are the vertical bar chart, while bars are the horizontal bar chart as we used to call them. Whatever we apply for column chart will also mostly be applicable to bar chart as well. So currently, on this chart, we are showing sales by month, and I would like to just add target as part of what we are showing on the chart. So I'm going to highlight my target when I click on the heading, I'm pressing Control Shift down arrow to highlight for all the mounds. I'm going to press Control C to copy that. Then I will select the body of my chart, and I'm going to page with Control V. So now I have two lines in display, right? So let me close this my formatting area for once. So I have two lines in display right now. Unfortunately, I cannot really tell which line is what. So what do I do? I'm going to click on my plus icon, and I'm going to enable back my legend so with my legend, I can tell that the more reddish one is for sales and the more orange one is for target. Now, when visualizing things like actual versus target, or sometimes if you are visualizing two different numbers that you would like to compare against some categories, then you might want to use a combination chart or a combo chart. So a combo chart is going to be a chart that will have both one type of chart and another type of chart. Now, if I click on this chart and I go to select Change Chart Type, I could change it to a column chart, for example, and I'm going to click Okay. Now, this doesn't look completely bad because I can see my amount of sales and my amounts for targets for every single month. But when it comes to comparing actual versus targets, it is typically better to use combination chart of different chart types. So I'm going to click one more time, and I'm going to select change chart type. Then I will go down there to select combo for combination. Now, in this case, we are going to have a little series box here where we can select the function for each one of them. So I think I'm going to allow my sales to be the column and then my target to be the line just exactly the way it is right now. So like this, I'm just going to click Okay, and this looks better for comparing actual and Paget. And because I would like to format my columns, I can click on one of the columns just once. Then I'm going to click and select Format Data axis. So as usual, for our columns, we still have our three options. We have the one for selecting colors. We have the ones that we don't want to talk about, and we have the one for controlling the real properties of the element we have selected. So if I go to the field and line, that's why I can change my color, for example, to the reddish one completely. But where I'm really going here is on the series option. So if I go to the series options here, we have the gap width. I want to reduce the width of the gaps so that the column are closer to each other, and the child can look much better, right? So this looks a lot much better than what we had. And if I could just even reduce the height like this, then it would be a lot more presentable. 15. 15. Formatting titles, gridlines and chart legends: Some other little formatting options include things like titles and legends and even dealing with grid lines. So first, I'm going to select my chart, and because the title is currently missing, I will click on my plus icon, and I'm going to enable the chart title, which essentially is just a textbook. So I can go into that textbox now and edit what we have here. So I can call the sales versus Target and I can even drag this textbook all the way to the left hand side like that. And if I want to format it in specific ways, I can use the regular formatting options in Excel. So I can go up there to my home tab, and I can use this fill option, for example, and select a color to fill the textbox. And then I can use the front color here to change the front color to a color like color white. Also, I can come to select my legend right here, then click and select my format legend, and I can specify where I would like to place it within my chart. I can place it, for example, at the bottom of the chart, and I can even also drag and place it in any location within my chart area that I'll feel like keeping it. I prefer to have my chart legend at the bottom of the chart. And finally, grid lines. So grid lines, a lot of times are not meant to be prominent on our chart if they have to be there at all. I can select one of the grid lines. Then I can go to my formatting options. And of course, this is a grid line, so I need to go to what I have fill and line. Here, I can change the color, but most importantly, I can go to the width and minimize to as little as possible. So it is not very prominent on the chart. And finally, removing any of these chart element can be as easy as selecting one of them, like the grid line and then pressing delete key on the keyboard. 16. 16. Formatting data labels and using Pie Charts: Okay, so I know you are probably not going to forgive me if I don't do anything about Pie chart or D not chart. So let me delete this current chart that we have here. And also, I would like to use a smaller dataset to display something on Pie hat simply because ideally Pie chart and Dahart and all those rounded chart are not meant to have a lot of data categories in them for them to make sense. So let's go to data number one, and let's try to create a chart that is going to show sales by country. So I'm highlighting my country and my sales. And, of course, I'm going to use dF one to create my chart. Then I can right click on my chart and select Change Chart Type so that I can go to the Pi section, and I can either use a pie chart or I can use a Donald Chart. I know you attempted to make use of this three D Pie chart, but you have to know that any kind of three D chart in Excel, no matter how beautiful they are, are almost completely useless. So never ever use a three D kind of chart. So let's stay with the ordinary Pie chart, and I'm going to click Okay. So this is what I have for my Pie chart, and, of course, there's not a lot of things going on currently. So obviously, I'm going to need to add in some chat element. So let's close this formatting area and let's click on my chat element plus icon. First, the chart Pie tool is there, which is great, but then I cannot really tell which slice is what. So I need to add in my legend. I'm adding in my legend like that, then I can tell which country owns which color of slice. Now, remember that ideally we are not supposed to have a lot of categories on pie chart. But apart from that, I also personally feel like tie hat are better with proper data labeling much more even than using legend. So I'm going to select my legend and I'm going to enable data labels. So with data labels enabled like this, I can further click on the arrow on the data labels and choose a better option. I personally also prefer to have labels on Pie chart to be on the outside end of the chart. But when it comes to chart like bar chart and column chart, I prefer to have the labels on the inside end. So I'm going to keep this as outside end. And then I need to apply a little bit more of formatting to make everything make more sense. So if I select one of the data labels and I do a right click there, that means I can format my data labels. So for my data labels, first, if this is a pie chart, then the value is not really important. What more important is the percentages of share? And then also to make more sense of this, I would also like to include the category name so I can see every single country and their percentage share. And I also want to ensure that I have my show leader lines enabled. Now with the show leader lines enabled, I can go to drag these text boxes with my data labels a little bit away from where it is so I can be able to see the leader lines. And then this is going to make a lot more sense than what is currently there. 17. 17. Introduction to Excel PivotTables Reporting and Structuring workbooks: Have switched over to my number two workbook Pivot data. And in this section, we are going to be looking at building reports and visualizing our data with Pivo tables and charts. Pivot tables allow us to quickly summarize data for analysis without having to write any complex formulas. We just typically will have to click on a couple of buttons, and we can get a lot done very, very quickly. So we are going to be using this dataset here, which is a real estate data. So we have information about region IDs and the name of the regions. Then each region belongs to one state or the other. So these are states in the United States. Then we also have a column for date, though this date column contains end of month dates because all the numbers that we have in this dataset are monthly numbers. So we have a column for the number of property listings for renting, and we have another column for the type of building it is or the type of property listing, and also the average price of property listing for that particular month. So for example, for this region and this state for the month of June 2020, there were 73 property listings for multifamily, and the average price of the listings was 1,359 point $1. So that's our dataset. Now, if we are going to be building reports and dashboards on Excel, then we have to partition our work so since we have our data stored on this worksheet, I'm going to create another worksheet by clicking on my new worksheet button right there. And this new worksheet, I'm going to call calculations. Then I'm going to create a third one that I'm going to call dashboard. So the idea is that we have to separate our work. We have a different worksheet for data, and then we are going to be creating a lot of calculations as well, using pivot tables. And finally, we need a dedicated worksheet that we will use to create our dashboards where we are going to have all our visualization. So the next video, we're going to start with creating Pivot tables in Excel. 18. 18. Data Structure rules for PivotTables: Before we start to create Pivot tables, we have to ensure that the structure of our data is clean. So I'm going to go over to my data worksheet now. And how do we confirm if the structure of our data is clean? First thing is you have to go into one of the cells within the data range and then press Control A on the keyboard. After that, we are going to scroll all the way down to ensure that this highlighting covers for the entire data ring. Now, if our data also expands into many columns, we also need to scroll to the right, and we just need to ensure that the only ring that has been highlighted includes our data range. If we have something like this, then we know that our dataset has a very clean structure, and things that might make this structure not to be clean includes not having one single row of headings. Or having empty rows because if we have empty rows like this and I stay somewhere within my data ring to press the Control A, you can see that a part of the data has been excluded. So apart from having empty rows, if you also have empty columns, again, if I stay somewhere within the range and I press Control A, there's going to be an exclusion because of the empty column that we have. Also, if you have obstructions too close to your data range. So if somebody comes here and types any rubbish, so if I stay somewhere within my data range and I press Control A, then you will see that it's also going to capture that rubbish. And finally, the dataset is not meant to have two tiles because if the dataset contains two tiles, there's a possibility of double counting or double reporting on numbers. So if all of these things are properly in place, then we can say we have a clean data structure, and we can now use our data to create our Pivot tables. 19. 19. Creating basic PivotTables on new Excel Worksheets: Because I have a very clean data structure. I can go ahead and use this to create a Pivot table. So I'm going to stay somewhere within my data range, and I will head over to my Insert tab. When I click on Insert tab, I should see this button here for Pivot Table. I won't be clicking on the drop down, rather, I will click on the main body of that particular button. So when I select Pivot Table, I should have this Pivot Table dialog box, and it has already highlighted my range of data. Without empty rows without empty columns without obstructions and with my proper one line of heading and also without to tiles. So this is a click structure. It has pick everything up. Then I get to choose if I want to create my Pivot table on a new Excel worksheet or inside an existing Excel worksheet. So I'm going to go with a new worksheet for now. So if I click Okay, a new Excel worksheet get created. And then I'm going to have my pivot table place folder in my worksheet, and also I should have on the right hand side, my pivot table field area. Sometimes you can mistakenly close this Pivot table fields area. To retrieve it back, you just have to make sure that you can see your pivotable tabs at the top of your Excel like this. And once the analyzed one is selected, you are going to see over here on the right hand side, the field list. So if I select the field list, that pivotable fields panel should be back. Now, also, if I click away from this pivot table placeholder and I select an empty cell somewhere else, then the fields also disappear, and so does the pivot table tabs at the top of my Excel. So I need to make sure that if I want to work on the pivot table placeholder that have been created or even on an existing pivot table, I have to make sure that that particular pivot or somewhere around there is selected. So from this placeholder, if I want to show something like number of listings by state, then I'm going to come to my field panel and I'm going to tick the box for state name. Then I'm going to tick the box for a number of listings, and this is going to give me a summarized report for the total number of listings per state. So you are going to notice first that the first column here is meant to be our third column, and this is the sum of the number of listings. But the state column doesn't really appear properly. Instead, it is showing us row labels. So one thing you have to do immediately you create your pivot table is to go to the top part where we have our pivot table tab, and then we are going to click on the Design tab. And under the design tab, we will go over to report layout. So when you click on the drop down for report layout, we need to always select Show in tabular form. So once we select Show in tabular form, our label is going to be proper as the category that we have selected instead of displaying at raw label. 20. 20. Creating PivotTables in existing Excel Worksheets: Alright, so I'm going to be deleting this new worksheet. I will just click on the worksheet and select Delete. I'm going to confirm the delete operation because I already have a dedicated worksheet where I want my pivot tables to be. That's my calculations. So I'm going to recreate it now. I will stay somewhere within my data range. I'm going to head over to the Insert tab and I'm going to select Pivot Table. Now, instead of choosing new sheet, I will select existing sheet. Then I should have my crosso blinking in the box for location. So first, I will click inside that box for location. Then I will go to select the worksheet where I want my pivot table to be. And then I will also select the cell where I want my pivot table to start from. Then I can click Okay, you can see my Pivot table place folder. What did we create before now? We created number of listings by Tate, so I will come to my pivot table field area. I'm going to check the box for state name. I'm going to check the box for a number of listings. And remember, the first thing you want to do when you create your pivotable is to go to the top of our pivotable tabs, and then we're going to click on Design. So when I select Design, what I want to do is to go to where I have report layout. I'll be using the dropdown on report layout. Because I want to select Show in table form so that my table can have proper names. So when I click on the drop down for report layout, I will go to click on Show in tabular form. And this is going to give me a summarized number of listings for each of the things. So having the second column displaying athum of number of listings is not completely a bad idea. But we can make this better. So if I want to rename this heading, I'm just going to double click on that heading, and then I have my value field settings, and I'm going to call this total number of listings instead. So I'll call this total total number of listings. Then I'm going to click Okay. 21. 21. Understanding Row Field and PivotTable design layouts: I hope that you remember that summarizing data for analysis and analyzing data generally is usually by comparing categories with measurements. So when we create our pivot tables, our measurements typically go into this bucket that has values, whereas our categories can go into any of the three buckets that we have left. So let's explain what each bucket actually stands for. So typically, any data category that contains text values or that does not contain number values by default, once you tick that category on your pivot table field is going to stay inside the rows. So apart from ticking, we can also just drag something into the rows or into any of these field bucket. So whatever category we have on rows is what will be arranged in the rows of our Excel on our pivot. Whatever we have on columns is what is going to be arranged on columns. And it is also possible for us to have multiple items on the rows. So, for example, I can go and also check the box for region name. So that means on my rows now I have two things. So in the first column, I have the names of the state. In the second column, I have the region name. Remember that every region belongs to a state or every state is made up of certain regions as far as this dataset is concerned. While we have our pivot table using multiple categories under our rows, there are also some options that we might want to implement design wise. So I'm going to go over to my design tab at the top there. Here you can see how we can control if we want to have two tiles or not. So the subtotals is going to give me a subtotal for each of the states, which is the first level we have under the rows here. And when I scroll down all the way to the end, you also see that we have a grand total right there. So another thing that we can also do is control our grand two thuses. I can say something like off for rows and columns. So the grand total is gone, or I can go to the grand two thus and say on for rows only or on for columns only as the case may be. So I'm just going to return this one back on for rows and columns. And I'm going to scroll back up to the beginning of the pivot table. One other option we can enable when we are working with multiple categories under the rows is to repeat item label. So for example, currently now, we can tell that this particular stae covers for all these regions from this point all the way to we find another pad. But sometimes you might want to repeat the name of the state. So to do that, we're also going to head over to the design, and we're going to go to where we have report layout. And here we are going to select repeat all item labels. When we do that, the names of the state are going to be repeated as applicable to each of the regions or we can just go back to report layout and leave it at do not repeat item labels. 22. 22. Understanding Columns and Filters PivotTable Fields buckets: So that's what the rose field area is used for. So whichever column you drop under the rose, the item under that column are going to be listed inside the rows of the pivot table. So if I want to remove region name now, I just can go back to my pivot table field area and I can just on tick region. Or if you just feel like playing a little bit, then you can come back to the rose bucket here, take the region, and just drag it out and throw it away like that. So that's for rows. We can also put data fields on columns. So let's say I'm going to put the listing type in columns now. This is when I get to use my dragon feature because by default, when you tick on the box, your categories will either fly into the rows or they are going to fly into the values if it's a measurement column. So when we need to put something under the column section, then we have to drag the field we want and just place it inside columns like that. So now I have my listing type on columns. So whatever field you drop on columns are also going to be arranged in different columns. So we have two listing types in multifamily and single. So right now, for each of these states, I can see the amount of listing for multifamily and also the amounts for single family. And that's what the column field section is used for. Finally, the filters section. And again, I should also mention that you can drag and just change the location of each of your data fields in any of these field buckets. So because I'm going to try to drag this listing type now into the filter's bucket. So all I need to do is drag the listing type from here and I'm going to place it inside the bucket for filters. And what this will do for us is it creates a drop down on the pivot we can use to filter the result of our PVO. So for example, the stage AK right now is 13 326 listings in total. But if I go to click on that drop down, I can first check the box that says select multiple items. So if I pick only single family and I click Okay, then it should no longer be 13,000. It's now 7,000. So we can also use the filters bucket to allow or to enable filtering of our Pivot table report by one of our data columns. 23. 23. Understanding Values Field bucket and changing the Summary Type for values: Have you noticed that just by bringing in a numeric column into the values field bucket of our Pivot table field area automatically sums up the values inside that column. Now, sometimes we might need to use a different kind of summary apart from summing. We might want to do an average, we might want to do a count, and some other statistical summaries. So for us to do that, we have a couple of methods that we can use. One of them is to come to the value section over here. Then we are going to click on the drop down that we see, and we are going to select value field settings. Now, from the value field settings, which we already saw before now, we can change the name of the column in our Pivot table, and we can also change the summary that we are applying. So for example, I can change this now to become average. So it now becomes my average listing for each of the states. And also from this part, we can click on the number format. Where we can select the category of formatting that we want. So for example, here, I'm just going to choose number, and I'm going to change the decimal places to two just for us to see this in action. And then I'm going to click Okay, and I'm going to click Okay. So this is going to give me the average listings per state into decimal places. So the other way for us to achieve the same thing is to come inside one of the cells that contains our values. And then if you click on one of those cells, we are going to see right there the option for value field settings, which is going to give us that exact same dialogue box. Now, the third option is just to double click on the heading of the particular column in the Pivot table, and this also gives us our value field settings dialog box. 24. 24. Filtering and Sorting on PivotTables: To change this summary from average back to summation. So I'm just going to right click on one of my values here and I'm going to select value field settings, and I want to change this back to sum. And I want my title to be called total number of listings. Then also, I'm going to remove the listing type from my filters. So I will just come over to my pivot table fields area, and I'm going to play with it because I will just drag this listing type and throw it away like that. So we can also apply filters directly inside our Pivot table reports. That is if we are using one of the fields on the pivot table rows or columns without necessarily using the filter. If I click on the dropdown for states name, then I can decide to select or select some of the states. So I'm just going to say, let's pick this randomly pick this, pick this and pick this. Then when we click Okay, then our Pivot table is going to be filter to the list that we have selected. But I'm just going to clear that so I can come back to click on the dropdown, and I'm going to select clear filter from state name. So one of the useful filters that we can apply, especially when we have a report that contains a category with several items like states, in this case, there are about 50 states, and we might just want to create a report that is going to show for the top five states only. So that means, again, we want to filter these pivotable reports to only display for the top five states. So I'm going to click on the drop down for states name again. Then I will go over to where I have value filters. And under value filters, we really have a lot of filter options and most of them are just going to do exactly what they say they want to do. So for example, if we're going to filter to show values that are more than 5,000, then we are going to select greater than. In this case, we want to choose only the top five automatically. So the option to go with is the option that is described as top ten. Then we are now going to edit the number 10-5. And if we were to do the bottom five instead, we just need to change this top from top to bottom. Then I'm going to click Okay, and this report is going to be filtered to show only the top five states by the number of listings. And based on this, I don't know if you can tell what the top three states are. So this has to be number one, then I think this has to be number two, and this has to be number three. So sometimes it makes sense for us to sort our pivot table in a particular order based on the values. So to do that, I'm just going to right click on one of the numbers inside my values column here. Then I'm going to select the option for sort, and I want to sort this from the largest to the smallest. 25. 25. Displaying PivotTable values as different calculations: So here we have our top five states by number of listings. So the top five states are contributing 1 million, 309,872 listings. That is property listings. So one thing we can also now do is try to see what is the percentage contribution of the top five. So the top five are contributing 1.3 million. But we to look at what percentage each of them are contributing to the top five. So that means we need to do something like a division of each of these numbers by the grand total. So you can do that straight up like this. Or if we like to keep both the original values and the percentage of grand total, what we just need to do is to go and bring in the number of listings a second time. So that is possible. So all we just have to do is this. So if I come back to my Pivot table fields area, you see that I am already using my number of listings. That is what I have inside my values. But then I want to use it again for a different purpose. So I'm just going to drag it out again for the second time, and I'm going to put it just underneath the first one that is there. So now I have a second column for it, right? So 436622, 259282, this one just doesn't have the decimal places. And you know how we could do that by formatting it to have two decimal places if you wanted. Now, the second column of my number of listings, I do not want to use as raw values. I want to use it rather as a percentage of the grand total amount. So to do that, I have to stay in one of the cells on the second side. Then I'm going to do a right click on one of those numbers, and I will go for the option that says show values as the Show Values Ask option has a lot of options. And one of the most useful options we get to use a lot is the one that says, percentage of grand total or percentage of row total, it all depends on what we want. In this case, I want to select show values as percentage of grand total, which is the first option right there. So when I select this, then we can see that the first two guys, for example, are contributing over 50% of the top five. The last thing I just need to do is to properly rename this. I am going to double click on the heading and I'm going to call this percent contribution. I'm going to click Okay. 26. 26. Updating PivotTables data sources and refreshing the Pivots: So far, you have seen how to create Pivot tables, how to design the layout in tabular forms and other options. You have also seen how to format the values in pivot tables. You have understood how to use the Pivot table fields areas. We have also seen how to change our pivotable report summaries from the default summon to average and any other summary we would like to use, and we have seen how to show our values as percentages or select any other necessary options. Now, if our data were to increase somehow either with additional rows or additional columns, and we would like our Pivot tables to update our data range, what we need to do is to go over to our Pivot table Analyze right there. So if I click on my Pivo table Analyze, you are going to see this button that has change data source. So if I select the change data source, it's going to bring me back to highlight what my data range is. So if my data range has increased either in columns or in rows, I can update that range from this table slash range input box. And, of course, alternatively, we could have t our data to use the table feature by pressing Control T, just like we did when we're creating chart the other time so that a table is going to be the source of our pivot table. That way, any increment in data will automatically be picked up into our Pivot. So I'm just going to click Cancel here. So once we have updated our data or once we have new data that we like to reflect on our Pivot table, we can click on a Pivot Table Report, then select refresh. Or where we have created several pivotable from our data, we can go to the Pivot Table Analyze tab and we can use the drop down under the refresh and select refresh A. 27. 27. Working with Date Columns in PivotTables: Until now, we have covered nearly all the essential things that you need to be able to create Pivot tables in Excel. Just a few more things to go. And one of those things is dealing with dates. So if I go back to my data, you are going to see that I have a column for listing dates. Although this listing date is aggregating all the property listings for in for that month, that's why we are going to see that all the dates here are month end date. Now, PVO tables will allow us to be able to break down these dates into different components of quarters and months and years so that we can use those for easier reporting. So if I go back to my calculations worksheet, I'm going to remove everything I have on this PVO table currently. So let's go to Ontiqu number of listings. I'm going to Ontiq state name, and then I am back to my PVO table placeholder. Now, immediately I tick or I check the box for dates, watch what's going to happen. So once I check the box for dates here, you will see that it automatically now breaks down my dates into years, quarters, months and dates. Remember that my data does not contain anything like years, quarters, months, and dates. Now, also, if I come over to my Pivot table fields area now, when I scroll down, I'm going to see that those extra fields have been created inside my PIVO tables, and I can now continue to use them for any of my PIVO table reports. So right now, for example, I do not want to use the date. I want to use just the month so I can antique quarter and I can also tick the year. What I want to show now is the total number of listings by month. So I'm going to check the box for number of listings. And I also want to see the average listing price for the month. So I will also go to tick on average listing price. Yeah, of course, I need to make some changes. So let me come to my number of listings. I'm going to double click on the header, and I'm going to call this one total number of listings. And then I'm going to leave it as summation, and I'm going to click Okay. But for the average listing price, I'm going to double click on that header, and I'm going to change this one to average listing price, and I need to change my summary for this one to average, and then I'm going to click Okay. Right. So that doesn't seem to cut it because now it's displaying average of average listing price. And the reason is because the original data field is called average listing price. So if I'm going to name this also as average listing price without putting the average behind it, it's going to be importablele. So what I need to do is when I double click on the heading, I can remove this average that is there. You see, if I leave it as exactly the name that we have and I click Okay, it is not going to allow me to proceed with this. But if that's the best name I can have, what I can do is to come to the ending of this text and put an empty space there. So I will just press the space bar on my keyboard and I'm going to click Okay, and that's going to be it. 28. 28. Creating Additional PivotTables: All right so we are going to try to have this report on one of the charts that we put on our dashboards, and we also need to create more reports like this to include on our dashboard. So if you want to add a new pivot pable, we have options to do that. One option is to go to the data and start all over again by clicking on the Insert tab and then selecting Pivot Pable. Then we can go ahead to select existing worksheet. Click inside the location input box, and then we can go to our calculations and select a cell where we want to start with this new Pivot table. But the alternative we have in doing that is, I'm just going to cancel this one first. So alternatively, we can just highlight one of the created pivot tables that we have. Then we can copy it with Control C. We can go to a separate cell and just paste with Control V. So we now have a replica of that pivot table. But before we do this, I'm going to advise that we name each of our pivot tables, and we're going to see where this will make more sense later. So I'm just going to Control Z for now, and I will come back to this my current Pivot table. I'm going to write click anywhere within the pivot and I will go to select Pivot Table options. So here I want to rename this Pivot table because by the time we have a lot of Pivot tables all over the place, they are going to be called Pivot Table one, Pivot Table two, Pivot Table six. So I want to give sensible names to this. So for this one, I'm going to put listing price, and number by Month. Then I'm going to click Okay. So now, when I highlight to copy and I go to paste somewhere else with Control V, I need to change the contents of this new Pivot table. What I want to have here is the number of listings by hip. So I need to remove those fields that I don't need for that particular report. So number of listings I do need. I don't need average listing price. I don't need months. What I also need now is my listing type. So this is going to give me my number of listings by listing type. And that's going to be my second Pivot table. And, of course, I need to write click somewhere there and go to PivotTable Options. And then you see it's called Pivot Table four for me. It might be different when you practice this. So I'm going to rename this as listing or let's say, number of listings by type. And I'm going to click Okay. 29. 29. Creating addition PivotTables (part 2): So I'm just going to create two more pivotable reports. So that means I'm going to copy this particular one, Control C, and I'm going to pay somewhere there with Control V. So I want to show the top five regions, and I also want to show the top five states as two separate pivotables. Of course, by number of listings. So I want to throw away listing type from here. Then I just want to put region name there. So here I have my total number of listings by region. But it's showing for all the regions, and I think this is way above 100 of them. So I want to limit or filter this table to show only the top five. So I'm going to click on the drop down for region name. Then I'm going over to value filters, and I'm going to select top ten. Here, I want to change 10-5, and I want to click Okay. So I'm just going to leave this like that for now. Then I'm going to write click and select pivotable options so that I can change the name of this pivotable to number of listings by region. And I'm going to click Okay. Then I'm going to repeat nearly the same thing for the state. So when I highlight this and copy with Control C, I'm going to pay somewhere there with Control V, and I'm just going to untick region from here and I'm going to put state name instead. Then I want to go to filter the set name on the Pivot table because I want to keep only the top five. I will go to value filters, and I'm going to select top ten, and I want to keep five. So I will click Okay. And here I will also, click on this Pivot table to select pivotable options, and I want to call this one number of listings by PET, and I'm going to click Okay. Alright, so we can work with this. In the next video, I'm going to introduce you to slicers. 30. 30. Working with Slicers to filter PivotTable Reports: SRS provide us with a very user friendly way to filter our reports through buttons. So if I want to add slicers to this, my Pivot table report now, first thing I will do is to select one of my Pivot tables, the first one in this case. Then just at the top where I can see my Pivot table analyze. If I go to click on Pivot Table Analyze, I'm going to see Insert Slicer. Also, if I'm inside my Insert tab, you are also going to see the slicer option there. So whichever one we select is good. So I'm just going to pick this one right now from the Insert tab. Then we are going to have a list of all the fields within our Pivot tables, so we can choose the one we want to use as slicer. For the purpose of what we are creating now, I'm going to be creating a slicer for years, so I'm just going to tick the box for years, and I'm going to click Okay. So this is going to give me a slicer for all the years in my data. And as you can tell, if I click on 2020, then it's going to filter this particular Pivot table report I have created it for. So if I click 2020, then these are the numbers for 2020 in terms of number of listings and average listing price. So our 2020 data starts from June. If I click on 2021, then I can have for 2021 and 2022. And, of course, if I want to clear my filter, I can just select the CLR filter here. Okay. So one thing I want you to note is this slicer currently only works and only filters this table. It doesn't filter all the other pivotable reports that we have, right? And what's going to make sense is for the slicer to be able to filter the entire pivotable report, so we can have everything as one set of reports or one set of dashboards. So to do that, once I have my slicer selected like this, you are going to see that the slicer panel is active above there. And on the left hand side, you should see report connection. So I'm going to click on the report connection and here I can now connect it to the relevant Pivot tables. If I have a lot of them, I can choose those ones that would like to connect it to. And this is where the names of the pivot tables matter because I may not want to connect the slicer to all of the tables, so it makes it easy for me to identify what I'm connecting to right now. So I'm connecting to all of them in this case, so I will just tick the box for all the Pivot tables that are here and I'm going to click Okay. So now if I select any of the years, it is going to filter for all the pivotable reports that we have. Now, also, for our slicer, I'm going back to click on my slicer tab. You can also decide on using a vertical slicer like this or using a horizontal slicer. To do that, we're going to use the button section to control it. So right now, if you want to have a vertical slicer, then we might be using one column or two columns depending on how many we have and how wide we want the vertical slicer to look like. But if you want to keep a horizontal one, since we have five years of data here, 20, 21, 22, 23 and 24, then I'm going to make my columns to be five, and I can click outside that. So my pivot table now will make sense only when I expand the width. Finally, you will see that we have some funny looking dt values there, and these are as a result of empty data. So what we can do about those is to go and click on slicer settings. Then first, I want to rename this properly. Yes is years. And then I am going to check this box that says hide items with no data, and I'm going to click Okay, and now the slicer looks better. Okay? All right, so this header doesn't change. I need to go back to my slicer settings, and actually my change needs to happen here what I have my display headers. So that should be years. Then I'm going to click Okay, and it should now be fine. 31. 31. Building Excel Dashboard (part 1): Right, so we know how to create our chart, and we also know how to use PV tables to summarize our data. That means we technically have everything we need now to be able to create a dashboard in Excel. So I'm going to go to my dashboard worksheet now. And what I like to do with my dashboard is to leave the first column free. So I'm going to go to the second column that is column B, and I'm going to try to put in a title here. So let's call this real estate listings dashboard. And then I'm going to try to increase the font size to, like, let me just select this and go to home. Let's make this phone size to be 24, and I want to make it bold, and I want the color to be something like, let's use this blue right here. So that's my title, real estate dashboard. And I'm leaving the first column free. For now, I'm going to make use of it later. Then also, I want to have an extra space on row number two, where I can fit in my slicer. So I'm going to expand my row number two a little bit like that as a guide to fit in my slicer. So next is I'm going to go over to my calculations. I'm going to select my slicer and I'm going to cut it with Control X. I will take it to my dashboard. I'm paced with Control V. Then I'm going to try to place it somewhere within this space I have created here. With not enough, I can just adjust it a little bit more like that. I'm going to leave it like this for now. And I think I should just clear my slicer so that all my years can be fully selected. So the next thing we are going to do is to start to create our chart one by one. So I will go over to my calculations, and I'm going to stay somewhere within the first PV table, and I'm going to press my OTF one that gives me this chart. Now, remember that this PV table has number of listings and average listing prime. But if you look at the chart, you are going to see that we can see only one item. Now, the reason why we are seeing only one set of items is because the range that is being used is exactly the same thing for both of them. So you will see that our Y axis right now is almost in hundreds of thousands, whereas our average listing price is just ordinary thousand. So there is no way we can compare things that are in hundreds of thousands on a scale to those ones that are in ordinary thousands. That means we have to create a separate Y axis for the average listing price. As a matter of fact, it's going to make more sense to use a combo chart to present this because we have to use two different axis for so I'm going to right click on this chart, and I'm going to select Change Chart Type. And then I want to go to select Combo Chart. Then inside this particular box, I want to use my listing price as a secondary Y axis so that I can have its own measurement axis. So I'm just going to take the box for that and I'm going to click Okay. So now I have a secondary Y axis. And one thing you are going to note about this particular chart is that if you check at the top of our tabs, you will see that it is displaying a pivot chart. Because any chart we create on top of a pivot table is always going to be a pivot chart, and it's going to come with all manners of indicators. And what I like to do is right, click on the category Indicator dropdown and select hide all field buttons on chart. So when I do this, those buttons have now been hidden. And what we just have to do from the next video is to try to format this chart so it makes more sense. 32. 32. Building Excel Dashboard (part 2): I just want to edit this chart, then I can transfer it into my dashboard worksheet. And some of the things I want to do now, if I'm looking at it right now, first, I need to include a title. Then also, I want to make sure that these numbers are shortened, and these ones are also shorting and I want to make sure the listing prices are showing in US dollars. And I'm also going to try to adjust the gaps between each of these bars. So first things first, I want to format the background of the chart and the text color. So I'm going to right click on the chart, and then I can scroll down to look for format chart area. First, I will go to the fill section, and I want to change the color just to apply a little bit of simple design. I'll be making use of one of these grays here. Let me use the second to the last one. Now, if I'm using this gray color, then maybe my text should actually appear as white. So I can go to the text options. Then I will go down to color and make this to be color white. Alright, so the next thing I want to do is to reduce the width of the grid lines because they shouldn't be prominent. So I'm just going to select one of the grid lines. And then where I have width over here, I will reduce that to the barest minimum that we have there. Next is to make sure my Y axis are displaying in thousands of units. So if I select the first part of my Y axis on the left hand side, the one for the number of listings, I will go to my formatting Axis, and I want to make sure I am right inside the Axis series right here. So if I select the Axis option series right there, I will go down to number, and I want to make sure that the format category stays as number. And also, I want to scroll down and edit this code so that at the end of the code, I'm going to put a double code, and put a K there, I'm going to close the code. Then I can click Add, but the idea is to actually shorten the numbers, so I need to go to Axis Options up there. And what I have my display units, I'm going to change that from none to thousands. Then I'm going to try to do the same thing for the secondary Y axis. So if I go to select the secondary Y axis now, it's a number. Currently, I will scroll down, and on the code, first, I need to tie link to source. And then on the code just before the code begins, I'm going to put $1 sign, and right after the code, I'm going to put a double code and put a K, close the code, and then click on Add. Finally, we are supposed to shorten this. So where we have 1,500 K, I want to see 1.5 K. So to do that, I should scroll back up. Under my Axis options where I have display units, I'm going to change that from noon to thousands. And then it's actually going to make sense for me to rename these text boxes for the display unit, right? So I'm going to go to the first one that is showing thousands there. And here, because that Axis is for my number of listings, I'm just going to type number of listings. Then I will go to the second side of it. And for this one, I need to type over this average listing price. So I have just two more things to do here. Two or three more things, right? First one is I like my legends to stay at the bottom of the page, right? So I've selected the legend. I will go to the legend options and make sure I change this to bottom. Then I need to reduce the gap width of the columns. So I'm going to select one of those columns. For the gap width, let's use something like an 85 maybe. Let's write 85 a GAP width. Click outside. Okay, that looks better. And finally, I need to add titles. I'll go to click on the Chart Elements plus, and I want to enable chart title, and the title here, I need to edit this to show us number of listings and average listing price per month. So my formatting is done and complete. Now I need to transfer this chart to my dashboard page. So I'm going to select the chart from an empty area and press Control X to cut it out. Then I will take it to my dashboard and press Control V to paste it right there. In the next video, we are going to see how to adjust the size of the chart and fit it properly into Excel grids. 33. 33. Building Excel Dashboard (part 3): Want to be able to arrange my chart very well in Excel, it's going to make sense that I resize the chart and make sure that it fits within the grid in Excel. So for example, I want this chart to start from the grid on column B here, for example, and then I want it to probably end exactly inside the grid rather than in the middle of column I where it is now. So for me to do that, I can start to readjusting and try to target manually with my eyes or I can just hold the old key on my keyboard. So what I hold the skill on my keyboard while I drag. So if I select and I hold all to drag, it's going to automatically just move to grid like that. So I just want to resize using my grid, and I want to reduce the height also using my grid, okay? Just making sure that it fits perfectly into grids. So, and that's all good? 34. 34. Building Excel Dashboard (part 4): Okay, so let's create our second chart now, and that is number of listings by listing type. So if I stay anywhere within this Pivot table, I'm going to press F one on my keyboard. I have my chart. I do not like this indicator, so I need to right click and my right click doesn't seem to work. Me to hide all of these field buttons. So I'm going to use an alternative means which is to go to the top of my tabs and select my PIVOchat analyse. So when I select piv chart Analys, I need to disable these field buttons here. So I'm going to click on that option to disable the field buttons. Then this total as a legend, it also useless, so I will just select it and press the lead on my keyboard. Next thing I want to do is convert this chart into a pie chart. It has only two categories. So I'm going to click on the chart and select Change Chart Type, and I'm going to change this to a Pie chart. I'm going to click Okay. Next is for me to format my chart in a similar way to the previous one. So I need to click one more time on the chart and scroll down to select Format chart area. I want the fill to be a gray color. And I've been using the second to the last gray option. Also, I want my text options to all be color white. Now, for this, my Pie chart, I need to enable a couple of things. So I need to click on my Adhat element. I need to enable data labels. In this case, I'm just going to leave it at inside the chart because I want to make use of legend. And then I need to finalize by taking my legend to the bottom of the page. So when I select my legend, I want to go to my legend options, and I want it to be at the bottom of the chart area. And also, I need to format the labels to show in percentages. So when I select one of these data labels, I'm going to go over to the label options right there, and I want to take value and replace with percentage. And finally, I'm going to change my title. So this is supposed to be number of listings by listing type. And then I'm just going to select the chart area and cut it out with Control X, and I'm going to take it to my dashboard and pace with Control V. Now, one thing I want to do here is I'm going to leave a complete column between one chart and the other, you are going to see how I'm going to manage this later. So I want to drag this guy all the way up a little bit here. I'm going to hold my lt key to resize it and snap it to grid. I want to snap to the grid over here. I want to snap to some grid around this place, and I also want to reduce the height by holding Alt key and snapping it to that grid. Alright, so two charts done, two charts to go. 35. 35. Building Excel Dashboard (part 5): The next two charts one to create are very similar, so I will be demonstrating only for the first one while I complete the other one offline. So here we have our top five regions by number of listings. The second one is our top five states by number of listings. So I'm just going to demonstrate for the region one. So if I select my Tiv table anywhere within the region Pv table and press F one on my keyboard, first thing I want to take care of is these indicators. So I will go to click on my Pv chart analyze I'm going to unselect this field button. Then this total legend is completely useless, so I will select it and press the leads on my keyboard. And then I'm going to format the background of the charts and the text. So I will right click on my chart. I will scroll down to select format chart area. For the field, I still want to apply my same gray color. And for my text options, I want my color to be white. Next is the grid lines. So I'm going to select one of the grid lines and reduce the width to the barest minimum. And next thing I want to do is to reduce the gap between the columns. So I'm going to select one of the columns, and I'll make sure I stay within the series options where I can see my gap width, and I think I like to use 85 the other time. So I'm just going to change this also to 85. And I will click in the other box to implement that 85. And lastly, I want to change my titoon. So I'm going to rename these as top five regions by total listings. Then I want to select this chart and cut it out with Control X. I'm taking it to my dashboard worksheet. I need to scroll down a little bit or maybe minimize the size of the page a bit. Let's close this chart area. So I'm going to paste it right underneath the one for monthly listing price, so I'm just going to come here and contro V to paste it. And then I can hold Alt and resize this to grid, resize this other grid over here. Let me scroll down and see if I need to reduce the height of cost. So I'm going to also hold my lt and reduce the height up to that point or maybe just like this or like this. And then I'm going to go offline just to create the other version. All right. So now I've completed all of the chart. I just want to finalize on this my dashboard worksheet. And one thing I want to do first is that I have noticed that I have used between column A, all the way to column Q. But like I said, I reserved my first column for margin, and I also use my last column as margin as well. So therefore, from column S, all the way to the end, I am going to hide. So if I click on column S like this, I'm going to hold Control Shift and right arrow direction key on my keyboard to highlight all the way to the end, and I'm going to press Control zero to hide all of those columns. So when I scroll back to the left hand side, I can also scroll down a B because I have used from row one. All the way to row 27. So let's just leave row 28 as well. So I'm going to hide from row 29 all the way down. So if I select row 29, I will press Control Shift and down arrow on my keyboard, and then I'm going to press Control nine to hide the rows. Okay? Not sure what happened here, but if I select this slicer, I can just increase it to bring it back up. All right. So based on my own planning here, I have margins between column A and column. And then I have a space in between to separate my four charts, right? So what I'm going to do is to reduce the size of those columns all at the same time. So this is just based on my own planning. So you can always have different ways of designing dashboards. So I'm going to select my column A. I'm going to hold Control and select my column. And then I'll also hold Control and select my column R. Then I can go to the edge of this column K, and I'm going to drag it to reduce the width of that column as tiny as possible, just like that. And finally, I'm going to highlight my row number three, and I'm also going to highlight my row number 16 by holding Control to click on 16. Then from the edge of row three, I'm going to drag this up to reduce the height also to as tiny as possible. And finally, I'm going to click on my View tab. And I'm going to check grid lines, and then we have our dashboard. I can use my slicer to filter the dashboard to any year in particular and see all the visuals updates accordingly. Now, ideally, if this is going to stand as an Excel dashboard, then we need to hide the data worksheet and also the calculation worksheet. So the only thing we should have is the dashboard. So I'm going to click on calculations, and I'm going to hide that. I will also write click on data, and I will also hide that as well, and also to minimize the ribbon, so I can double click on any of the tabs right here, and that's going to minimize the Excel ribbon.