Pivot Table with Progress Chart and Dashboard

welcome to the office instructor channel my name is Nabil Murad and this video is about pivot tables progress charts and dashboards let me first introduce you to my friends Jessica and Mike Jessica and Mike are both junior analyst in the sales department of a large firm they have been asked by the boss to create some reports for an upcoming board meeting presentation they are only given this giant sheet of debt and list of reports that the bus needs although they are excited of the challenge they are wondering how to turn this huge data set of 10,000 rows into a nice-looking dynamic dashboard it's their lifetime opportunity to demonstrate their competency during the board meeting what they really need is to learn how to use Excel to create a pivot table which progress Donna charts slicers and timeline in this video I'll show you how to solve Jessica and Mike's problem we're going to take this dull sheet of debt and turn it into a nice-looking interactive dashboard using pivot table progress chart slicers and timeline okay let's get started with our source data this is my list which includes almost 10,000 rows the first thing I would like to do to analyze and summarize this list is to convert it into a table and to do that I have so many ways I can go to the insert tab and click on table I can click on the Home tab and select format as table but much easier I'll be using the shortcut table starts with letter T so I'll use the shortcut ctrl T Excel recognizes my list make sure my table has had as is check and then once I hit OK it has been converted into a table I'll go right away and name my table source data and then hit enter the next step is to create a pivot table and to do that I'm selecting one single cell and I'll go to the insert tab and click on pivot table alternatively I can use the shortcut alt + V and the same dialog box opens Excel recognizes my list because I have blank cells all around or what I need to do is to select the destination for my pivot table I want to create my pivot table in a new worksheet so I'll hit OK in the new worksheet I see my pivot table placeholder I can see the pivot table field list on the right side and two contextual tabs to analyze and the design tab up at the top so I'll start by creating my pivot table which is a matter of dragging and dropping because I would like to analyze my revenue for each region so I'll drag the region and drop it in the row area Excel creates a unique list of regions the next thing I would like to do is to analyze my revenue and because it's a numeric value I'm going to drag it to the values area so I'll drag the revenue and drop it in the values area by default for any numeric value Excel creates a sum of revenue which is not exactly what I want I'm going to change the summary function by selecting any single cell and then right click and from the right-click menu and select show values as percentage of column total and now I got the percentage of revenue compared to the total revenue I would like to apply some formatting and to do that I'll right-click and select number format in this dialog box or what I'm going to do because it's already percentage I want to reduce the number of decimal places to zero the next thing I'll be changing the layout of my pivot table and to change the layout of this pivot table I'll go to the design tab of the ribbon and select the third comment on the left side and click on the down arrow and change the layout to show in tabular form that will bring the labels here at the top and because I would like to analyze my data for each one of the sales rap so I need to add a filter and I'll drag sales wrap to the filters area and the filter has been created I can click on the down arrow for the filter and change to another option by checking the box for select multiple items I can deselect all and let's say I would like to select Mike and here is the revenue generated by Mike for each one of the regions and now I created my pivot table Jessica and Mike are happy with the PivotTable but would like to communicate their insights about the company revenue in a dynamic attractive way they're worried about the tight deadlines they have to get their dashboard ready let's continue helping Jessica and Mike visualize the report the next step is to visualize my data I would like to create a progress donut chart and to do that I need to prepare for creating my progress donut chart I'll be creating a helper column that I'll be hiding later on so to create this helper column in column C I'll type equal one minus the revenue and then hit enter and then I'll be copying the same exact formula for the different regions and you will see why am i creating this helper column when I create my donut chart I would like also to create a label that I'll be using in my title I type an equal sign and click on whatever value I have in my filter and then I'm going to join it with a joining operator the M percent shift 7 and then I'll type a space double quote space double quote another joining operator and then I'm going to type in double quotes sales when I hit enter it says Mike sales and if I change my filter that label will also change now let's stop creating our donut chart with nothing selected I'll go to the insert tab of the ribbon and click on the down arrow for the pie chart and then selected on a chart and that creates a container for my pie chart I need to adjust the size of my donut chart so I'll go to the format tab of the ribbon and then I'll adjust the size the height to be six centimeters and a width to be also six centimeters and let's create this donut chart so I'm going to right click and in the right-click menu I'll select it click on add and here I can add a name for the series or I can leave it blank and just type 1 comma 1 comma 1 20 times I already typed 3 I'll continue typing - any occurrence of number 1 separated by commas and I'll hit OK and here is my first series I'll make sure that I have two area currants and then hit OK and that's the beginning of my donut chart the next thing I would like to do is to remove this title and remove the legend I'll click on the short elements in the upper right corner of my chart and then take the check away from chart title and legend what I need to do next is to reduce the size of this hole so I'll click on my chart and then right click and from the right-click menu and select format data series it opens a pane on the right side and in this pane I'm going to select a slider for donut hole size and I'll drag it a little bit to the left let's say I want to keep it to 60% and then I'll click on the tab for the field color which is this bucket icon I'll click on the bucket and then I want to change the fill color and I'll make it solid fill and I'll change it to blue then I close this pane the format data series pane and with my chart selected I want to remove the outline and change the fill color to no fill so I'll click on the format tab and then click on shape Phil no Phil and I don't want the shape outline so I'll set it to no outline I created my first donut chart and I would like to copy it to create three occurrences of the same chart control-c and then click outside and then paste it control V and now I have three occurrences of my donut chart I copied and pasted three versions of the donut chart now I need to perfectly align them so I press shift and select a second press shift and select the third one I'll go to the format tab of the ribbon click on the down arrow for aligning and then I'll select align middle and then I click on the down arrow for align one more time and this time I want to adjust the vertical spacing between them so I'll select distribute horizontally after doing that I would like to modify the color of the second and third occurrences of my donut chart so I'll select the second one and then right-click and select format data series which color do you want for this one in the format data series pane I'll click on the bucket icon and I'll change the fill color and I'll make it red I'll repeat the same exact thing for the third one so I'll select the search art and because the pain is already open I'll click on the bucket icon and I'll change the color and I'll make it this time green so I finished the first part of my donut chart now let's go and add a second dataseries I'll right-click and select selected I'll be repeating the same exact step for each one of the three donut chart I'll click on add and the second series will have the name of the first region so with my blinking cursor in the series name I'll type an equal sign and I'll click on East and then I'll click in the series values and here I have to delete the contents and click and drag to select b4 and c4 which correspond to the east region and then I hit OK and then a second ok the second series has been created I need to put it on a secondary access to give the shape of my chart and to do that I'll select the secondly added donut chart and him right click and select change series chart type in the change series chart type I'll check the box for the east series and I'll put it on a secondary axis and then hit OK now I need to modify the secondaries here to reveal the primary series which is below it the one that I created first so I'll click once to select the donut and I click a second time to select the blue section and with that done I right-click and select format data point a pane opens on the right side the format data point pane I'll click on the bucket icon and here I would like to set this part to no fill and because there is no fill it reveals what's behind it and I'll do the same exact thing for the orange portion but this one will have a failed it's a solid fill and I'll set it to white and I want it to be semi transparent or let's set the transparency to 20% so now I can see the primary series underneath and I'm finished with the first donut chart let's repeat the same exact thing for each one of the second and third shots I'm selecting it right click and then select it click on add for this one the region is south and for the series values I'm going to select b5 and c5 and then hit OK and another ok let's repeat what we did I'll change the series chart type and I'll put it on a secondary axis and then hit OK click the first time click a second time set it to no fill to reveal the primary series underneath and then I'll select the second section the orange section I wanted solid and I'll make it white and I want it to be semi-transparent so I'll set the transparency as we did before to 20% let's do it one last time for the third donut chart I'll select it right click select data and then I want to add I'll select the series name and the series name this time will be the west region and the series value will be the sum of revenue and the value in the helper column I'll hit OK and another ok and now I need to format the color first of all I'll select the newly added on a chart right click change the Cirrus chart type put it on a secondary axis hit OK and now I'll click once click a second time no field for the blue section to reveal the green color and then select the orange section set it to a solid field I want this time the solid feel to be white and I'll set the transparency to 20% as I did before and then I close now I created the three progress donut charts I need to add some information some label some values and linked them to the source pivot table and that's easy adding labels is adding text boxes so I'll click on the insert tab and click on text box I'll create the first text box and I'll be copying it later on I'll type an equal sign and in the formula bar the value that I want here in the sum of revenue for the east region so I'll click on before and then hit enter I could apply some formatting right away like I need to select like a bulky font so I'll scroll down to find a bulky phone so let's select the impact font and I'll make it much bigger I'll set it let's say 238 or 36 and I'll change the color to blue to match the first on a chart and here I need to format this text box no fill and no outline once I have done that I can copy this text box for the other two donut charts before moving it in the center so I'm going to press ctrl and drag while pressing the shift key and then press ctrl and drag while pressing the shift key so I get two other copies and they're perfectly horizontal let's modify each one of them and notice when I link it to another cell I will be losing the formatting but we'll take care of that instead of before for the second one that should be b5 and then for the last one it should be b6 and I hit enter now let's copy the formatting from the first box so I'll click on the Home tab and double click on format painter and then paste the formatting to these two boxes I'll hit escape to exit or disable the format painter anymore I'm going to copy this box and put it in the middle of the donut chart and I need to change the color of this one to red to match its done' chart and then copy it and move it in the middle of the bar of the donut chart and put it in the middle of the donut chart as well and then do the same for the last one I'll change it to green and I'll move it and copy it inside my Donuts ah I created the most important part of the visualization 3 progressed on a chart let's test how dynamic they are so if I change the name of the sales rep and select a different name let's say Jessica instead all the numbers change and everything is dynamic before I hide my helper column I need to create a label that covers everything and I need to add the name of each region let's start by adding the name of each region in a text box so I'll click on the insert tab and select text box and then I click and drag and for this one I'll hit F to type an equal sign in the formula bar and select the first label East we need to apply some formatting so I'll change the fill to no fill and I'll change the outline to no outline and then I want to bump up the font so let's say I'll select an Arial font and I'll make it like size 18 or 20 whatever you like and then I'll make it bold and of course I need to match the color so I'll select the blue color and center it I need to copy this one for the other donut charts as well so I press ctrl and drag while pressing shift and then I'll press ctrl and drag while pressing shift and I get two cups for the second one this one is equal to a 5 so I click on a 5 and then hit enter and for the third one this one is equal to a 6 and then hit enter we need to copy the formatting so I'll select the first label click on the format painter and then double click and click on South and then click on west I'll hit escape and I need to change the color for the south and make it thread and change the color for the West and make it green and I created the labels for the different regions and that looks nice my next step will be creating a label for the entire visualization it's another text box so I'll click on the insert tab of the ribbon and click on the down arrow for the text box and select text box and then click and drag and create a text box I'm setting the size of the text box notice that's right now I'll be copying the contents of c1 so if I release the filter and select multiple sales wrap that will read multiple items so I need a large text box to accommodate for this large label let's do that in this text box with the text box selected I hit f2 and type an equal sign and click on cell c1 and then hit enter let's improve the appearance a little bit by applying some formatting let's say I'll be applying an Arial rounded I'll bump it up to 16 or 18 and I'll change the color to dark blue and adjust alignment so let's test how dynamic everything is I release the filter and select Mike and when I hit OK the label reads Mike and all the numbers here reflect the percentage of revenue for each region for Mike the final thing I would like to do is to remove this outline so I'll click on the format tab we don't need a fill and we don't need a border and we are done with the main label of our visualization the final thing I need to do is to add some interactivity to my visualization by creating a slicer and a timeline and to do that I need to select a single cell in my pivot table click on the analyze tab of the ribbon and select a slicer and I'll be adding a slicer a slicer is a graphic interactive filter it's a filter which shows you who are the elements selected in your date so I'll select the sales rap and then hit OK and here is my slice I can apply style to my slicer let's select a style I can change the appearance of my slicer by splitting the different options into multiple columns I'll split it into three columns I'll resize my slicer and then bring it below my visualization I can also expand it a little bit the next thing I would like to do is to add a timeline let's go and add a timeline by selecting any single cell in the pivot table click on the analyze tab and select timeline it online is a filter for date and time values so I'll check the box for date and then hit OK my timeline is created I need to position it at the same level of my slicer I might adjust the size of the slicer a little bit and I don't need this horizontal scrollbar so with my timeline selected I'll go to options and then take the check away from scroll box and the to become almost equal I can adjust them exactly the same height let's say I'm going to make both of them 3.4 so I'll select my slicer go to the options and adjust the height to make it 3.4 so they are exactly the same height for my timeline before I test it I want to change the time scale so instead of having it in two months I want it in years so I'll click in the down arrow in the upper right corner and select years and now I can reduce the width of my timeline just to accommodate the three years now let's test our timeline and see how dynamic it is if you want to see the revenue generated in 2016 click on 2016 and everything changes if you want to see the revenue let's say for Jessica click on Jessica and now the numbers even the label changes up at the top let's add our final artistic touch and this final artistic touch will include the slicer and the timeline into one single element and to do that I'll be inserting a rectangle so I click on the insert tab click on the down arrow for shapes and select a rounded rectangle I want to click and drag to create my rectangle which will include the slicer and the timeline if you click on this yellow handle in one of the corners you can drag it a little bit to the left so that you decrease the curvature of the corners and at the same time I would like to add a shape to my rectangle so I'll click on the down arrow of shape effects on the format tab go down to bevel and then select the relaxed inset bevel effect that looks nice I want to send it to whack so I'll click on the down arrow for send backward and I'll send it backward one more time now I can see my elements on one single interface I'll press the shift key to select the three of them and in order to be able to move them all together I'll group them so here is my slicer and timeline the final thing I would like to do is to hide the values for the helper color so I'll click on the View tab of the ribbon and then remove the gridlines so I don't see gridlines anymore and on the Home tab I'll change the color of the helper column to white I want to reduce the height of the ribbon so I'll double click on one of the labels and now I have my dynamic dashboard let's test it let's say I want to see the sales for Mike when I click on Mike everything to change the label should change the donut chart will reflect my selection I'll select Mike and look at that even the main label at the top changes if I select 2015 everything changes if I select multiple items let's say Julie and Nicole I press ctrl to select Julie and Nicole and then I want to expand the sales for 2015 and 2016 I'm selecting both of them my label says multiple items everything is dynamic link to my pivot table and that looks great should you wish to release your filter you click on the funnel icon in the upper right corner of the slicer if you want to release the filter from the timeline you click on the funnel icon as well and this is how our final project should look like Jessica and Mike are happy with the amazing project they put together and can't wait for the presentation day thank you for watching and see you in our next video

Channel: Officeinstructor

Views: 2,042,128

Rating: 4.9120221 out of 5

Keywords: Pivot Table, Dashboard, Doughnut Chart, Progress Chart, Slicer, Timeline, Excel, Jessica, Mike, OfficeInstructor

Id: cTl3rPLP1cE

Length: 26min 29sec (1589 seconds)

Published: Tue Jan 23 2018