Build 5 ADVANCED Excel Charts from Scratch

welcome to the one video that will take you  from excel charts newbie to excel charts pro   and for this we'll be going over five charts  and ranking them in terms of difficulty   from one star all the way to five stars so let's  get into it first up in level one we've got a   donut chart and this can make sense to track  something like a customer satisfaction score   out of a hundred so let's look into the excel file  which you can download in the description below   so over here you can see that we already have  two of these donut charts made one for europe   which as you can see they're dynamic so if  the satisfaction score drops to 50 percent   then that's going to change accordingly over here  press ctrl z to go back let's do the same thing   same thing with age over here let's say that  their satisfaction score is 75 percent so if   that's the case the unsatisfied is the remainder  so we'll go 100 percent equals 100 minus the   satisfied proportion that's gonna be that so  from here to go ahead and do the donut chart   we're just gonna select it first ctrl shift down  ctrl shift right we're going to go under insert   from here you're going to go under the  pie chart that you can see and then just   below that towards the bottom you're going to  find the donut chart that we're interested in   let's go ahead and put it over here to the side  let me resize it a bit and so first things we   got to do here is we're going to have to make it a  bit thicker so we'll go ahead and select it go to   right click we're going to go form a data series  and over here the doughnut hole size is what we   want to decrease so we'll just make it more or  less kind of like that then for the colors this   orange part is going to be the remainder for us  so let's say we put it in a light lightish color   so go under format under shape fill we're going to  put this light light orange here and then for this   other area go ahead and select it we're gonna put  the darker orange color like this one over here   we can delete the legend so click on it and  press the lead there and then for this text   box that actually updates automatically it's a  bit more complex so let's go about that firstly   let me close this area we'll select the chart  and then from there you wanna go under insert   here we're gonna insert a text box so to do that  just go under text text box and we're just gonna   put it more or less right in the center kind of  like so nice now this we want it to be dynamic   so we want to want it to link essentially for this  we're just going to have to go under the data bar   so once we have this this area here selected  we're going to go to the formula bar sorry   go to the equal sign from here we're gonna select  the satisfied area that's gonna autofill for you   there and hit enter great now we just need  to format this so we'll put it in the center   both top and from the sides as well we'll put  in the center there say ctrl b to bolden it and   let's just make it a bit bigger there so we'll  select something like just keep pressing the a   a bit so it looks a bit better so that seems about  right then for the color you simply have to change   it from here let's say we go for this dark orange  over here and that's the basic idea with the donut   chart obviously now it's all gonna be dynamic so  if i change this to a 60 then that's gonna change   accordingly all right moving on to level two and  over here we're gonna have a comparison between   our actual expenses and our budgetary expenses for  our company so let's take a look at that over here   as you can see you have all of the different line  items so the employees the office space etc etc   we can start to see what the difference is going  to be like here so let's go ahead and select the   whole data set you can just press ctrl a for  that then we're gonna go under insert again   and in this case we're just gonna go  to the column chart drop down here   we're gonna select this very first one which is  the most standard type great now from here what   we want to do is essentially have the actuals go  inside of the budget area so we can see how far   in we are within the budget region now to do so  we'll just select that orange side but make sure   you select all of them from the right click  and here we're gonna go to format data point   so here we want it to be the secondary axis go  ahead and click on that and now you can see that   one is basically inside of the other we just gotta  make it a bit skinnier so we can actually see it   so on the gap width here let's say we go for  something like a 400 hit enter there now you   can clearly start to see where it's looking what  it's looking like so for this one because these   are expenses let's just put them in red so we're  gonna put them go under format here and the shape   fill we're just gonna select a red color like  this one over here and then for the background   side let's go with the shape outline of that  same red so that's gonna be the the border area   and for the shape fill we're gonna go with a  lighter lighter reddish color like this one over   here now you can clearly see that for marketing  this is sort of gone out of out of line over here   and over here for the office space it's looking  a lot better one final note here is that the   proportions don't seem to be exactly the same on  both sides so that's something we need to correct   so for this area over here let's go ahead and  correct that go under these columns here then   we'll go to access options for the maximum  value we want that to be at 600 so it's   it's accurate to compare with the other one so  we'll go at 600 now they're in gaps of 100 as   we want them here so it makes a lot more sense  then we can go ahead and delete that one side   just by clicking the lid there and there you  go and thank you to morningbrew for sponsoring   this video instead of aimlessly scrolling through  social media you can start your day with knowledge   from morningbro a free daily newsletter to get  you up to speed on business finance and tech   in just 5 minutes while reading traditional  news can sometimes feel a bit dry and dense   morning brew is witty relevant and informative  for example i recently learned about the labor   shortages that are causing airline chaos and how  amazon is shifting its strategies by slowing down   its expansion under its new ceo andy jassy who's  reducing the number of warehouses and brick and   mortar stores so if you're interested in learning  about all of this and more go to the link in the   description below it's completely free and it only  takes 15 seconds to sign up alright back to the   video next up in level 3 we've got the waterfall  chart and this makes the most sense when you have   a starting value something that happened along the  way and an ending value so for example it could be   with employee headcount if we look at this example  over here you can see that we've got the 2021   total employee headcount then we hired some people  we demoted some we laid some off and eventually we   reached a 2022 figure and so if you want to see  the whole progress a waterfall chart makes a lot   of sense let's go about that by pressing ctrl  shift down ctrl shift right we'll go to insert   from here we're just going to go to this one over  here double click on that and then we're gonna go   under waterfall which is the first one so this is  currently what we've got but it doesn't quite make   sense in the the waterfall doesn't quite fall it  just keeps going up for some reason so this last   figure is the one that is probably off so we'll  click on it just double click it to isolate it   right click we're gonna set it as total and so  now that's gonna make a lot more sense for us   and over here in orange we've got the motions  let's change this to something like a red color   so we're just going to isolate the decrease sign  there just by clicking on it again for the format   we don't want an outline so no border basically  no outline and for the shape fill let's go with a   red color and so for the increase we should have  we should have them in this blue color now for   this total one for the two totals let's say we put  them in a darker blue so they seem a bit separate   let's go with this one and this total figure as  well so we'll just click inside the legend for it   click on the shape fill and we'll change it to  a dark blue so as you can see here even though   we hired a good number of people 50 in this case  it seems like quite a few left the company which   probably isn't too good a sign you can get rid of  the gridlines as well just by clicking on it on   them and pressing delete coming in at level four  we've got the forecast chart and this obviously   makes the most sense when you want to forecast the  trend into the future so looking at the excel file   that we've got down over here under the forecast  tab you can see that we've got um netflix which   is the thicker the thicker symbol is this one  over here so netflix shares with their date and   their closing price let's say that this is the  share price that we invested in about a year ago   and this is how it's been progressing for  us and want to determine whether it's worth   continuing to own the share or whether we should  sell it if you go to control down arrow you'll see   that we own this share pretty much up till today  and so we've got the share prices for every monday   over here now to forecast it into the future we're  just gonna go under the data tab then you're gonna   go to forecast sheet which is this tab over here  click on that now you can start to see the pop-up   of excel's forecast of what it's gonna do based  on the previous data so this whole year's worth   of data from the past and so here you can see  when the forecast is ending in this case it's   around september and this is this is the estimate  of what it's going to do you can go under options   to see more of the different variables that you  can tweak from here so for example in this case   you can change the confidence level and other  things like that that are related to statistics   this case we'll just hit create for now so as you  can see excel created a new sheet for us and what   this is saying these curved lines are saying is  that within 95 percent of the time the share price   should fall within this range so it's saying  that it's probably not going to be any higher   than around 300 which isn't too great a sign if  you want to keep investing in it now you can see   that excel also made this table this is basically  how we calculated all the data as you can see over   here these are the calculations that it's made  um going into the future you can look into them   if you're interested on a serious note though i  don't recommend using excel to try and forecast   whether you should buy or sell a share this is  just an example so don't use it in real life   lastly in level five we've got a column chart with  percentage changes and i'll be very impressed if   you already knew how to do this one so let's go to  control page down all the way to the bottom here   and this is the data set that we'll be working  with so over here we've got the monthly breakdown   of the revenue and how that compares versus the  next month if you click inside the next month it's   basically the following month here so you can see  the comparison and i've gone ahead and made some   things like the variance variance percentage press  the f2 key to look inside that which is basically   the percentage change from one to the other the  variance which is just one minus the other and   over here i've got a table where it basically  says hey if the figure is positive then you've   got to put it on this side and if it's negative  you're gonna put it on this other side as you can   see i put the positive changes as a negative  figure based on this figure right over here   the condition here just says that if the variance  over here is greater than if it's greater than   zero then i want it to put a negative sign in  front now the reason i'm doing this you'll be   able to see after in the chart that's an overview  of the data set so let's get into actual charting   we'll go ahead and select this this top area over  here we just select these first three columns then   we'll go under insert and for now we're just  going to go with a simple 2d clustered column   like this one over here let me just drag that  down a bit more so we can see it we'll just put   it down over here we're going to remove the legend  so go ahead and click on it and hit the lead there   we don't really need the title for now just  because i'm going to show you how to make the   the chart more generally and so for this this  orange oranges area which is basically the next   month figures we want to make this transparent  so we got we're going to go under format   then for the shape fill we're gonna go to no fill  same thing with the outline we want no outline   great now we're gonna go to chart design  and here's where we're gonna start putting   the percentage changes basically so go to add  chart element go to error bars then more error   bar options i know this is a bit daunting here  and towards the bottom you're going to reach a   custom area click on that then under specify value  the positive error value is going to be this whole   range that we set up earlier that i set up earlier  it's going to be this area here then the negative   one is going to be this other area over here click  that again and hit ok there now you can start to   see that you've got this these lines which are  going to give you the range of that difference   then we'll click inside that that transparent um  data bar so just click inside it then go to right   click and actually we already have it up here go  to format data series if you don't see it it's   going to be this button over here now for this  the series gap we're just going to put at zero   zero percent and same thing goes for the gap  width we're going to put it at zero percent   and now we want to add a few labels which are  going to be the percentage change for us so right   click go to add data labels now for now you can  see that we just get the standard numbers which is   not quite what we want so we'll click and click on  them then go to this column area go to that then   we're going to go value from cells which allows  us to select specific values within the range so   go to value from cells and the select data label  range is what's gonna pop up for us in our case   we're interested in the percentage changes which  are these figures here hit okay there and we don't   want the the values anymore because that's kind of  too much info we'll deselect that values area and   now that's looking more like it so let's go ahead  and change these by pressing ctrl b to bolden them   first and let's also go to home and for the text  here let's say we go for a red color let me go   inside there and select actually let's put a green  for the positives and then for the negatives which   is once this one over here and the other one's  this one which you can see they're not very well   very well placed we'll work on that in a bit we'll  just put them as red first and then i'm just gonna   put them to the inside end that's gonna be  on the bottom here same thing with this one   we're just gonna make it red and then put it under  the inside end let me scroll down so you can we   can see it a bit better there let's stretch the  graph out a bit like so and let's change the color   colors of these columns to something like a  darker blue so go to format shape fill let's go   with the dark blue let's remove the grid lines as  they're a bit distracting and now that's looking   a lot more like it and that's how you go about  creating a column chart with percentage changes   let me know down in the comments below  out of the five charts how many you knew   check out this video over here for excel formulas  or this other link over here to learn more about   our courses hit that like and the subscribe button  if you liked it and i'll catch you in the next one

Channel: Kenji Explains

Views: 110,329

Keywords: excel charts, advanced excel charts, advanced charts, waterfall chart, doughnut chart, awesome excel charts, awesome charts, column chart, forecast chart, forecast chart excel, column chart with percentage change, budget vs actuals chart, forecasting stocks on excel, excel stock forecast chart, trend chart excel, progress chart excel, improve your excel charts, create excel charts from scratch, advanced excel charts from scratch, professional excel charts, excel visuals

Id: sxDptpBgorY

Length: 14min 14sec (854 seconds)

Published: Sun Jul 17 2022