Dynamic Variance Arrows Chart with Check Boxes

[Music] in this video you will learn how to create a dynamic chart with variance arrow wherein I have displayed month on month data for year 2017 and 2018 and this is the difference between 2018 and 17 this yellow bar is for 2018 and this black is for 2017 so as you are seeing I have selected only India market and the sales difference is from the 2017 in 2018 there are 77% up in Gen 1 similarly in the second month is 2 percent up now in the March it is 3 percent down so you can see these red arrow down earlier if you want to see the US market theta then you can uncheck this and check the US so this is the US market data similarly you can select the other market also here I use the check boxes in place of option button because if you want to see the data together for to market let's say I want to see the data for the US and UK then I can select both of them now this is the data for US and UK I can select India or the other market also so this is all markets data now all right now I would like to show you the data so this is the data sheet and here is the data available for sales so this is market name here I'll show you the market we have five different market two years 17 and 18 and all the month falls 2017 and 2018 and this is the number of sales all right so here we have displayed the variance in the sales month one month using these arrows all rights now I will show you how can you create this beautiful and informative chart in Microsoft Excel so what I am going to do and select the data from here just copy this data and press ctrl n to add a new workbook and here I will paste this data this is my data sheet I'll add another seat I will rename this seat as support I will add another seat and I will rename this a chart we will display our chart here remove the grid lines from chart worksheet and we will do some working on the support worksheet so first of all I'll take here month then I'll take month from Jan to December just drag this and here we will take the data for year 2017 and here we will take the data for year 2018 in this seat I will insert five different checkboxes from developer time so just go to the Developer tab and just go to the insert and here from form control I'll take the check boxes so just click on this checkbox and just click here I will rename this as India okay I'll make a copy of this and paste then I will rename this the second market let's say us we have India US UK Poland and Germany so I will quickly make these check boxes so I have added all the checkboxes we can align these checkboxes properly go to the format align top and distribute horizontally all right now we've linked these checkboxes here on support sheet so first of all somewhere here on column Q I'll take all the market names or I can simply copy those name from here just copy these name and go to the support sheet and just paste a special value here and we can remove the duplicate from here so just go to the data and they move duplicate click on okay so we have the unique market name here now we will link our check boxes with this column which is our just go to the chart right click on the first India check box go to the format control and here I will go to these cell link just click here go to the support sheet and just click on our 1 infinity of India just click on ok now this is linked with the our one of support sheet if I will check this then you can see it will display true if I will uncheck this then it will display false similarly I will link the other checkboxes with this range right click on us go to the format control cell link go to support sheet and link with this all right so we have linked all the checkboxes now if I will select all the checkboxes here then you can see all the tools are available here if I will unselect any checkbox like I'm just unselecting UK then you can see for UK it is displaying false all right now here what I will do I will put a formula to get the sales of year 2017 we will get these sales for selected market only so I will use first of all if if this value is true then I will use some ifs formula sum it and I'll take some range so I'll go to the datasheet and I will take this sum range this one D column comma then I will take the first criteria range first criteria range I am taking the market name comma and then I will go to the support sheet and I will give this reference the market name then I will freeze this press f4 now I'll go to the datasheet again to give the criteria range to so criteria range - we will take the month name comma and then we will take this reference which is a to demand a comma now again go to the datasheet and I will take this time here okay this one comma so we are going to get the total of 2017 false so we can manually type here 2017 and just close the break it and now the else case we will take 0 if India market is not true than it should return 0 and just press Enter so we are getting this value now if I will fill this down so we are getting all the values for India market only if I will go to the chart and I will uncheck India market then what will happen it will return 0 because here our one is not true now we will make the changes in this formula what we will do will copy this and here we'll put plus and just paste this formula the only change we will make in place of r1 we will take our two because we are going to take for the US market this time and in place of q1 we will take you that's it and now we will plus and paste here and this time we will take three and three plus four plus five and fine and just press ENTER and fill this formula down so you can see we are getting these sales for us Germany and Poland because we have selected these three let's say I want to slept only India and us then I will get this sum of India and now I will copy this formula and I will paste this here let me fill this down so it is giving the same result so what we have to do we have to just replace 2017 with 2018 so just select this range press ctrl H to replace and here I will take 2017 and I will replace it with 2018 replace all now you can see it is giving the new digit now we have got the values for 2017 and 2018 month on month now we will add few other support column to create our chart and those columns will be first of all we will take here max left then max right cookie will use max formula here and will take the maximum value from these to low and max right will remain same so we can use equal to this and just fill this down now here we will take the other formula also up error bar then down for the up error bar we will use whether our data for 2018 is greater than from 2017 means the data is going up then we will use the up error bar so we will use a t2 which is the 2008 is greater than 2070 in that case we will use this c2 minus v2 and it should return an E and just close the bracket we will complete the same formula here for the down error bar the only difference is in place of greater than we will use the last thing and here in place of C 2 minus b2 people to use b2 minus c2 and just fill this formula all right now we will create a chart so first of all I will select this range and just go to the insert and charts and I will insert our 2d column charts we'll take this chart just change the size of chart right click on the chart go to the Select data so this makes left I will take on the top position so just move this up first of all max left then 2017-18 and max right click on ok here on the chart you can see the first one in gray is max left so just let this click on this plus button and error worse so just add the error bars here now for the max right which is in yellow click on this and add error bars here also so we have added the error bars for max left and Max white I will select the error bar for max left false or just click on any column right click go to the format data series and here you can go to the series options so just click here and we will select max left by error bars and here we will make some changes in the error bars first of all we will take - and then here we will take no cap in the error amount we will take custom values so just click on custom and a specified value just click here and now the negative value just press equal to and just drag this down till here click on open so we have taken this up error bar value for this one now I will select this error bar or you can simply go here and select max is right at about this one and again here we will take - no cap and the custom specify value positive value we can blank and the negative value we will take this range this time click on ok first of all I will select this max left go to the fill and has left nothing then I'll select the max right and I'll say no the only error bars are looking here so this is the up error bars right so just let this one or the max left are about so here I'll go to the line and we'll take solid line the color I will keep here green color and the built I'll take around 1.5 begin arrow type I'll take this one all right then I'll select this second error bar which was the max right by error bars and then I'll select solid line color this time I'll take red and again the width I will take 1.5 for this era bar we will take the end arrow type and that will be this one now here I will select any one like this right click go to the format data series and we have selected the max left so we will move it to this second react then we will select the max right this one and we will move this to the second reacts all right now we will put some gab bit and series overlap I will take around 20 25 percent gap width I'll take 70 or 80 or that's a 75% and similarly I will select the max left it will take automatically because this is also on secondary access we can select let's say series 2017 which is on primary access so we'll take the same 20 and 75 now I'll go to the fill and line and first of all I'll select for 2017 which is selected here solid fill and I will take the black color this one then I will select the 2018 series this one solid fill and I'll take color this one then a chart will look like this now here in the chart we will make some changes and first of all I will select the vertical axis the secondary axis and press Delete then I will double click on this max right and press Elite double-click on this max left past delete and I will uncheck the gridlines from here so our chart will look like this the chart is almost ready but we need to add the data levels also to add the data levels what we will do we will create here two more support column for date 11 so we will create the column for update 11 then down return label for the updated level we will put the formula if we are using the up error bar or up arrow then only we will display the updated level so what we can do here we can check if each error if this value is a error then we will display here blank otherwise we can take simply this up error bar divided by 2017 because base will be 2017 all right and just press Enter so this is displaying in percentage similarly here we can take if is error and this time you will take deep down error this value then we will take blank otherwise we can take this value divided by 2017 all right we can fill this down so for the down error bar we should display the negative number and here we should display the positive number so what I will do here I will add plus sign here plus and this value and press Enter so it is giving the result in this format so here what we can do we can use the tax function for this so use the text and we can use the format as zero percent and just close the bracket so it is simply giving the plus 90 in plus 13 and here also we can use the tax function first of all we will add here - and tax function then we can use here zero percent collide and just press ENTER this down so it is returning the negative number now we will add the data level on the max left and Mac side to add the data level what we will do we move this chart here and select this one so we have selected max left if you are not able to select the max left so what can you do you can go to the formate and you can go to the current selection this is max left if this is not max left you can select from here also then right click and add data level so it will look like this I will slap data level right click go to the format data series and here I will take the value from cells then I will select this range we will give the update 11 click on OK and check value and so later lines then go to the home and take the green color make this bold slightly bigger so it will look like this now I will select go to the format and I will select the max right then right click on the chart at data level then select these data level right click go to the format data level again I will go to the value from cells and will select this range click on OK and check values and so later lines then I'll go to the home I'll take the I think we have taken the correct range so let me take the ranger lens we'll select this range all rights go to the home and then go to the font fill this one and fill the red make this bold and slightly bigger so our chart is ready and now I will cut this chart press ctrl X go to the charts and I will paste this chart here if I will select let's say only India market then it will display the India market data I can select the other markets also like this you can insert a save the rectangle this one like this here just right click send it to back and move this chart you can fill some light background color here let's say this one you can select all the objects in a chart class control a right click make this group so these all the charts are selected together now this is a dynamic chart all right friends in the next part of this video you will learn how we can use these slicers in place of these checkboxes to create the similar chart so please watch the next part of this video I hope you have enjoyed this video friends if you like this video please hit the like button thank you so much for watching [Music] you

Channel: PK: An Excel Expert

Views: 65,566

Rating: 4.9473686 out of 5

Keywords: ADVANCE EXCEL, VBA MACRO, BI Dashboard, EXCEL dashboard, Dynamic Chart in excel, Form Controls in Excel, Variance Arrow Chart in Excel, Form Control Check Box in Excel, Error Bars in Excel, Percentage Change Chart in Excel, Sales Variance in Excel, Multiple Market Chart in Excel, secondary Axis in Excel Chart

Id: TSHdWPNkjTA

Length: 18min 48sec (1128 seconds)

Published: Tue Aug 27 2019