Easiest Step Chart in Excel (this trick will surprise you!)

some time ago I created a video under step chart together with Bart settlers inputs I'm going to put the link to it in the descriptions in case you missed it now after some time I got an email from Matty on YouTube who wrote this if you're curious the fire a step chart can work with arrow bars stay tuned after I saw Matty's email I thought that's a great idea I had initially looked into arrow bars but I stopped because I ran into a problem with the X error bars for the horizontal lines before I even got to the y ones for the vertical ones so I thought it's probably not gonna work for the y ones either but when I take a look at Matty's solution I realize it actually works great for the vertical lines so there was only one challenge to overcome and that was to find a way to make the horizontal Aero bars dynamic since we are gonna have days at irregular intervals and we had to do this without running into problems for the last date on this table and still have the table properly update our chart when new data gets added to this so let's see the final solution this is our data set the first thing I'm gonna do is to convert it into an official excel table so just click anywhere inside and press ctrl T I'm just gonna go with ok here first thing I'm gonna revert to my own formatting by clearing the table formatting here and second thing is I'm gonna give it my own name so I'm gonna call it P our table now first let's just insert a scatter plot and then let's see how we can use the arrow bar technique here so with this data set highlighted let's go to insert to the chart side and let's insert a scatter plot let me just remove the gridlines we know that we can't just activate the lines that connect these points together because that's going to give us the wrong visualization like for this point to this point it's going to look like that the price slowly dropped to here but in fact it didn't because the price stayed this amount what was that date on the 1st of February until the 20th of February the price stayed at 47 and then right on the 20th of February it dropped to 30 so we need the line to go from here and all the way down and not from here to here like this this is where the arrow bars can come in handy I personally use error bars quite a lot in the visualizations because it kind of gives you another dimension of control when it comes to manipulating your data series if you don't know what error bars are if you've never used it basically their main purpose is when you do statistical analysis you can visually show the range of error that's associated with your analysis so if I select this series and I activate the error bars you can see how they look like this you have the option of showing the horizontal error bar so the range of error based on the horizontal axis and the vertical error bar now we're not going to be using this for their statistical purpose we're actually going to be using this to give us the right visualization for our step charts but the good thing with these error bars is that you can control their length and their values so if I just click on the horizontal arrow bars and either right mouse click to format them or double click them or use the shortcut key control one we come to our error bar options here so we have the ability to choose their direction to choose their style and the best part is we can define the amount and the amount is their length right so let's just see quickly take a look at the links here right now it's just some standard length that was given to us but we can't go with the standard lengths right because if this is not going to connect this point to this point we need this line to go all the way here and then all the way up so what we could do is to use the custom so let's just go back here on the bottom you can give your own values to it right here so you can actually reference this to a range on which you previously have done your calculation so that's what's going to work for us in this case so first off let's just decide the direction of this horizontal arrow bars which of these options do you think is going to work for the step charts the plus right because we're always going in this way now in style that's an easy one I don't want to cap the cap is that little tiny line that you see on the bottom when you select no cap you'll remove that line so now we're only manipulating the horizontal arrow bars right after that we're gonna get to the vertical ones now to switch between them you can either select them from here but sometimes they might be very small might be very difficult to select so you can always select them from up here here you can see all the elements that are inside your chart so if I want to move on to Y I can select Y from here if I want to go back to X I select this if I want to go back to my original series I select the series here it's a good way of changing your selection from here instead of trying to click on the right series okay so now let's define the length of the X arrow bars how could I get them to be as long as my other point here well that number that difference is the difference between my dates here right so that's all I need actually I'm just gonna add that here and call this X error now immediately my tables going to expand and my charge is gonna add this new series to it but I don't want that because that's not a separate series on the chart these arrow bars belong to my original series so first thing I'm going to do is to kick it out of my charts I'm going to go to select data click on the X error and remove it from the chart now let's do our calculation we said that's the difference between the next state and the current date okay so these are in dates let's just highlight the area and let's change that number formatting to general just so we see the number of days between them so these look good except the last one right because the problem is we have nothing on the next day so how do we overcome this in a dynamic way so that we always get these numbers except for the last role inside the table there we want to have zero because for that last point we want to stop here we don't want to have any line going this way one formula we can use here is to count the number of rows the table has and then to check which row we're on if we happen to be on the last row of the table we could bring in our exception and we could say put a zero otherwise it should do this calculation so let's add that in if rows of our tables I'm just going to highlight this first column here equals the current row that we're on so just roll this one the date one now there is always going to be a difference here because my table doesn't start from the top here right so I need to adjust for that and I'm gonna say - the role of my header okay so that should give me the right number I'm just going to put this part in brackets so that means if this condition is true which means if I'm on the last row then it should give me zero otherwise it should calculate the difference and that looks good so let's just double-check here I'm going to click on that last one and highlight this part of the formula and press f9 we see it's row eight and how many rows does this table have let's press f9 eight right so 8 equals 8 that's why it gives me the zero now that we have our calculation let's make the length of these x aero bars equal to these numbers so I just clicked on it I'm on the horizontal arrow bar here let's go to custom for the positive error value I'm gonna select this one right here and say okay and I get my lines and I don't get in line for the last one and the length of the line looks great because they're all different and they're all as long as the next stage okay so now let's try to figure out the Y error bars for the first one I don't need any Y error bar for the second one I need to go down and for the third one I needed to go up then for this one I needed to go down so what type of calculation do I need for that well that's just the difference between my two points so basically if I'm right here that difference is the difference in price between the previous one and the current one hey that's my difference the only thing is that I get a problem for the first line because I have text here right so here I could also make an exception if the result of this is an error give me a zero okay and that takes care of that now I'm just gonna take away the Euro formatting here because we're just interested in the numbers and that should be the distance between this one and this one so that's - that looks good so let's test it out let's activate the Y error bars if you can click on it click on it from here otherwise just activate your chart and select the Y error bars from here let's go to the main options up here which one do we need to go with you can go with the plus here as well we don't need a cap and we're going to go with custom again and for the positive error values we're gonna select this and go with okay and that's our step chart everything in one place in one table now the rest of this tutorial is just to optimize the data labels for it because if I activate the data labels as they are right now they're not going to be placed properly right even if I select above I'm going to be covering the line from the ones on the bottom if select below it's the other way round so it would be nice to put the labels on top up here and on the bottom down here this means that we can work with two additional series here one series is only going to plot the points that are on top and the other series is going to plot the points that are on the bottom okay so let's start with the positive series first I'm going to say this is for positive labels and this one is going to be for negative labels the formula here is very simple if the Y error number is less than or equal to zero I want to show the price otherwise I want to show nothing okay let's just go with nothing to begin with okay so that should show numbers for this one this one which it does and the last one is also positive and these ones are also positive okay so that looks good let's just copy this and paste it for the negative one and just change this to the other sign so if it's greater than zero then we want to see this and that only shows the number for these three ones okay so all we have to do is to bring these two series in here let's right mouse click select data add a new series series name is positive labels always give you series of labels so that you can find it in the drop-down here our series x axis that's our dates the y axis that's our positive labels here let's add the negative ones as well now notice what's happening in the background here we get these points here but we also get a lot of points on our axes these are the empty stuff in here because in the formula when we say nothing nothing translates to a zero for our chart and it plots a zero we don't want that so instead of noting what we can do is to write an A it's an error function that's actually going to generate an error here and these errors are not going to get plotted so that's kind of a way of hiding your zeros we're gonna do the same for this one as well so don't forget the N a function for these type of purposes in charts then it looks great we have even two different colors here for the top series I'm just gonna click on it right mouse click add data labels activate the labels and let's add them above now we want them to be in Euros right so we can change the formatting of the cell here but I'd also like to add in the date as well the data is on the x value so I'm going to put a tick mark here I'm gonna take away these show leader lines and for the separator here I don't want the comma I actually want a new line so I'm gonna select that from the drop-down now let's just change the formatting of these both the positive and the negative to the accounting formatting I'm just going to highlight that range behind the table as well and let's go with accounting guess what that looks really nice and the same thing for the negative labels right mouse-click add data labels we're gonna put these on the bottom I'm going to add in two x value and put these on a new line now the good thing about having two separate series for this is that you can also use a different color so for the negative ones you can change their font color to this red here and I can also change the color of this but you can also hide these markers so let's say for the positive ones I actually don't want to see those points I just have to click on them go to format for shape fill so like no fill and for shape outline select no outline now what it's done is that it's hidden my markers for these ones but it's showing the markers for my original series right so if I go and select my series from here which is the price series I can do the same for that I can take away the shape fill and I can take away the shape outline so I just get a smooth line here you can do the same thing for the bottom ones as well or if you'd like to highlight the price drops you could actually decide to keep that marker in there and just format it a bit differently for the marker options I can select a white color for the border and for the fill color I can change that to a red color and also make my marker a bit smaller under marker options so let's just change that to number three okay now we're pretty much done with the step chart because we have the date and the value in here I can actually remove both axes I'm just going to add a title to this now to make this stand out a bit more what I can also do is to change the thickness of the lines here to do that you can change the thickness of the error lines so under the error bar options for the fill and line that's the default I can change the color to let's say a lighter gray and I can make it thicker just going to go over to here so that takes care of the vertical error lines and I can apply the same formatting to the horizontal arrow lines and I'm gonna remove the outline of my chart let's do a final testing increase the price to 55 it jumps to 55 decrease to 30 it falls to 30 so that's another version of creating the step chart it just uses one table and the error bar technique I have to say this is actually quite simple to set up once you know how many thanks to Mattie for coming up with such a great idea and actually sharing it with us thank you also to Bart for sharing the original idea now this way if you ever need to create a step chart you just have to pick the one that works best for you as usual if you like this video I'm going to be very happy if you gave it a thumbs up and do subscribe if you like to receive updates when new videos come out

Channel: Leila Gharani

Views: 30,500

Rating: 4.9709244 out of 5

Keywords: step chart excel, dynamic step chart, step chart for price and inventory, price chart, inventory chart, custom error bars excel, error bars scatter plot, step line chart, vertical error bars, horizontal error bars excel, excel table with step chart, Advanced Excel Tutorials, Leila Gharani, Excel 2016, Excel 2013, Excel 2010, Advanced Excel tricks, Excel online course, Excel tips and tricks, XelplusVis, Excel for analysts

Id: 2HXigXrTE9I

Length: 18min 35sec (1115 seconds)

Published: Thu May 03 2018