CASE STUDY ~ FINANCIAL: Pivot Table Slicer & Chart Dashboard

In this chapter we've created a picture dashboard. We've done this by creating three different pivot tables one for a top five channel partner which you can see in here and then we've created a pivot chart and cut and pasted it into here. The same thing for the number of sales group we create another pivot table and I picture. We've cut it out and put it in here. And the same thing for the sales and cost per month. We have a separate pivot table. We create a pivot chart. And then we've placed it in here. We've also done is inserted for different slices and we've connected them. And by choosing the different years the pivot charts change automatically. So you get a nice looking dashboard with lots of metrics. You can choose the different months. You can also choose the different regions. Let's highlight everything again. You can also choose the different sales ranges with this dashboard. You are sure to where your boss and get noticed. And I'll show you how to do this in a few steps. So this is our dataset that we've been using and we have all the different information here. The channel partners sales the different regions salesperson and so forth. So now going to create three different pivot tables and from there went to create three separate pivot charts one for the top five channel partners. The next one for the different sales groups and the last one for sales and costs. So let's kind of the first one and create a top five channel partners. Got to insert pivot table and put into a new worksheet just get our channel partners and put into that Row Labels we'll get our sales and drop it into the values. So we have our pivot table here. Now first of all let's write like in the values and we're sort of from largest to smallest. And from the dropdown box let's go to Valley filters and we'll do the top five. So let's putting the top five items impressed. Okay. So we have our top five channel partners. Now let's go to Options and pivot chop and insert a pivot chart from here. Let's choose a bar and press. Okay. Now let's just make some changes. Let's get rid of these buttons by clicking in there and let's get rid of the total they click and press delete. Let's get a design and it's choose this here with a wide outline and click on the grid lines and press delete. Melissa Click on the y axis and group home and let's put in a dark font in May and from here We can delete that because we don't want it. Now let's click once in a graph and right click let's add some data labels in there. Now we can color them in. Let's put it into a blue there. And the press control one and the number let's put in a thousand separator and zero one places in the title list stop clicking there and change the name to top five channel partners and then click to highlight. And let's put it into a dock right there. Now let's right click in the chart and then let's fill it in with a light gray background. And again within the graph click on that right click. And once again let's fill it with I'd like right now let's click on the edges and then press control 1 and the border color would do a solid line and we'll have a white border and the Border Star we can just put in number five and let's make it around corners in here and press okay. So you see the chart is taking shape. Not only is this a similar format for the other chart. So what we can do now say this template and then when we create the next pivot char we can apply these different formats so we don't have to go through all the steps again. So once you click on the char good design and save template as you get this directory here and he goes to Microsoft templates and charts and in here you can save your child let's call it dashboard char and press say OK we'll credit our first chart while it's clicked puts control X get about dashboard click any way make price control V. So this is our first child in a religious play just like that and we can reorganize that later. So let's go through our data table and create our second pivot table and pivot chart. What's going to be sales groups. So go to insert pivot table new worksheet in here we're putting the sales in our row labels and from in here right click and press group and want you grouper into ranges starting from 10000 all the way to 100000. And then the increase will be ten thousand we'll leave it like that and press okay. Now again get the sales and drop it into the values area and because we've grouped the sales we automatically get a count of sales and leave it like that. That's fine because that's what I want to use it including that a pivot to click the picture. I'm going to use a column and press. Okay. Now let's go to the change chart type and in our template is get the previous template that we created which is this one here called Dashboard chart. Click on that and press Okay so you see we get the format as we had before but now we can just right click in there and again change the chart type and let's put into a column and press okay. So we've got the same format but we have the different chart type. Now I mean here is double click and rename this to number of sales per group. You can amend whatever you like and let's put that in gray colors click on chart press control X and go to a dashboard clicking away in here. Press Control V we can just bring it all the way up here. You see now we have our second pivot chart in there. Let's go back to our data table and when we create a final pivot chart 2000 costs insert pivot table and press. Okay. In a row labels are going to put the financial year and the sales month in the values are going to putting them the sales and then the costs. Now we get a can of sales because before we grouped ourselves so once we group our sales the next time we create a pivot table we get a count of sales. But that's okay from the top down arrow go develop all settings and to some. Let's go to our pivot chart. And he said a column chart and press. Okay. Let's get rid of the balance there. Now we're not going to use the same chart style as before. Because if we do that it'll mess things up. Any one look good so let's go back and show you how it's done in press okay. It just doesn't make it nice. So this price controls it and get out of that and now we're just going to manually make some updates here. So we have our sales and our costs here. I want to pool our costs as a line in the secondary axis here on the right hand side. So to do that we've clicked in our some costs which are in great price control 1 and then plus serious on the secondary axis and press close and then change chart type. And it's pretty night a line and press Okay so now we have the two charts on different access to the south side in blue and the depicted on the left hand side axis here. So let's put in a blue color there to distinguish the like fierce control one. No. And we can't just put it like that. The right hand side. We have out some of cost. This. Click on that. And then let's put in a red color they can again control one and let's form the number let's get some layout and get access titles. The primary vertical axis this choose that and we can put in their sales we can right click in there and just put it in a blue color. Now let's go again to the axis titles and go to the secondary vertical axis title. Just like that and putting that cost just to distinguish it and let's put that in red this click in there and press control one and we can move the legend to the bottom and press close and click on our grades there and we can delete that. This clicking our axis there and putting a blue color in Atlas click in our line and press control one and the line color. Let's put it into this light red color and then the marker options let's put in a circle and the mark feel again. We just put it in like that into a light red color and press. Okay let's go on to our layout and putting a chart title choose above chart and double click in there and want to call it sales and cost per month then double click in there. Would put into a great color. Okay now let's put in a great background for our graph just click anything that right click. And then in here which is a slide right and click within the graph and you can press f for a repeat the last action. So we have our graph there. And now one last step is to put in a border click on the border press control on the border color we'll put in a y color and the border style will make it a big thick number five and import round corners and press close. So we have our chart. And now over there just click on a press control legs and control we and we have it. And we can just resize a church to make it a little bit bigger. Okay so now let's just double click in the Home tab just to get a bit more space so we can see that. So we have our three different paper charts there that were created from the three separate pivot tables. Now the fun if you wanna do is insert slices and then connect them. So every time we change a slice of the three pivot charts are in sync. So to do that we can simply click on any chart Cisco analyze and insert slicer and let's put in there the financial year sales month the sales region and the sales and press. Okay. So we have four different slices there. So let's grab the financial year and bring it up here and we just make some adjustments to it. There's plenty to three different columns and then smack it dark and we can move like this right click and slide the settings get rid of the display header let's make the height for the balance a little bit bigger. So we have one slice there they describe our sales month and again right click just to get rid of the header and from the options let's put into three different columns that shows the different quarters and then we can just resize it in here just like that. And then once again this may get the balance a little bit bigger. And then we can put in that a dark color. The next one are the sales regions. Let's grab it in here get rid of the splay header and then let's put into two different columns and resize it accordingly. Again let's make this a little B. The balance will be bigger. We can put in a different color if we like just to distinguish her. And finally we have their sales. Now they grouped in to the different ranges. The is of the header. And then let's put it into separate groups. Then again resize these and we can make it a little bit bigger again. Let's drop it into two columns just so we can see them better or the way the bottom there and then a color. Let's choose that is click on the different slices pest control cake and click on all of them just to align them go to align with say align center. Now we need to connect the slices because if we choose one slicer it's only going to change the bottom up because that's a chart that we chose to insert a slice of. So it's right click in each of the slices and go to pivot table connections and let's pick on the empty boxes. So what we're doing here is we're saying that pivot table number three and shift to pivot table number two in Sheet 1 are being connected. Press okay the same thing for all of them. You could choose 2012. The chart changes 2013 and 2014. Same thing for the month. Let's highlight all of them like that and then we have the different regions. And also we have the different sales ranges there as well. If you see there we've got the different sales ranges to get to our first shape. We can see the different sales ranges as we choose that he changes the pivot table accordingly. So this gets updated even though you don't see it in it's another shape. He gets filtered accordingly. Let's go back in here. Now let's highlight everything this hole in the on the mouth and selecting it all so you can see we've created a pretty impressive dashboard in just a few steps. And I'm sure that with this dashboard you're going to get noticed by the next round of promotions. I'm sure that your name will be mentioned.