Custom pacing chart - Microsoft Excel Tutorial

- [Instructor] Alrighty, for our next demo I'm going to show you how to build a really cool, really unique custom pacing chart. And if you open up the custom pacing chart tab in our Excel workbook, you'll see a bunch of columns of data here that essentially show revenue pacing towards a goal. So in column B, these are daily revenue totals that are hard coded in as values. Column C basically just translates that into cumulative revenue, day over day. Looks like we've got a goal of 150,000 to hit by the end of the month. Gap is just the short fall between cumulative and goal. Percent of month is a really cool use of date and time functions to calculate each day as a percentage of the month. And then target basically says given how far you are through the month, and given what our goal is, how much revenue should we have driven on each day to maintain pace so that we hit that 150K by the end of the month. So what we need to do now is create a visual that really tells that story and makes it clear how we're currently pacing, in this case for the month of April. So why don't we just get started by inserting a line chart. I'm going to manually add data series to this. So the first series that I'd like to add here is my goal. And my values will be all of the values from row two through 31. Then we go in my X axis labels, are all of my dates in column A. I'll add a second series for target. And you know the drill. G two through G 31. X axis, same dates. And then I'll add one more for now. Which is going to be my actual cumulative revenue. I'm only going to select down through row 21. But when I do select my X axis labels, I will bring in the entire array, down to row 31, because basically I want this line or this data series to end at the current date, but I don't want to limit the X axis to that date, I want to see the X axis extending through the whole month. So here's our starting point. And we can do a little formatting right now and format this goal data series. Maybe you want a solid green line with a lighter fill, and maybe some of these compound types where you can do a double line or something like that. Obviously totally up to you. For the target line, the daily pace that I'm trying to hit, let's make this kind of a light gray. Two point width. And this is a nice opportunity to use a dash type here. Because it helps indicate that that's kind of a guideline more than anything else. And then finally I can edit my actual cumulative revenue line, I want to make this one stand out since this is kind of the key metric here. Let's do a nice dark blue. Smooth line, and we can go ahead and add markers to that as well. You know I'm a fan of these diamonds. Bump them up to size six, give them a solid fill. White and a solid dark blue border. There we go. Okay, so we've plotted three of these series and we've already done a pretty good job showing how our cumulative revenue is pacing through the first few weeks of the month, and how that relates to the target pace that we need to keep track of to reach our 150K goal by the end of the month. So a lot of people would stop here because it is a pretty solid visual, but this is a next level exercise, so obviously we've got to take things a step further. And what I want to do here is highlight these gaps. So when cumulative revenue falls beneath the target, I want this space between the two lines to be formatted dark red. And if revenue exceeds the target, I want this space to be filled with green, just as an additional visual indicator that things are pacing in a negative or positive direction. First thing that we need to do is actually define those areas in a mathematical sense. And that's what these four placeholder columns are intended to do. Let's drag that out of the way. So for lower, essentially what I'm looking for here is, on any given date, what's the lowest point that you hit? It's either cumulative revenue or the target line, it's got to be one of the two. So all we need to do is type a min function, or minimum, and it's going to be the minimum of either cumulative revenue, column C, or target in column G. Upper is just the flip side, so on any given date, what's the highest value? Is it the target, or is it the cumulative revenue? So we'll just type the max function. Same exact idea here. Now, under and over. This is where we're actually going to calculate the difference between these two lines, depending on which one's higher. So our under target column is where we want to populate values that reflect the difference between the two lines, in cases where cumulative revenue is beneath the target line. Over target is where we want to calculate the space between the lines where revenue is over or outpacing the target line. So in cell J two, we can type equals, if our cumulative revenue is beneath the target, then the value we want to return is the difference between the lines, which is just the upper bound minus the lower bound. Value if false is just a blank, so two quotation marks. And then very similar case for over target. Equals, if our revenue is greater than or equal to the target, then again return that difference, which is upper minus lower. Otherwise, blank. So now if I grab all four of those fields we just calculated, and double click, you'll see that values populate in column J, where cumulative revenue falls beneath the target, and values populate in column K, in any case where cumulative revenue outpaces the target. And these are the two series that we're going to build into our chart, that can be formatted with those green and red fills. So let's start adding some series here. I'm going to select a new series. First one I'm going to add is lower. And you'll see why in just a minute. Values for lower are going to be rows two through 31. X axis dates, okay. Now I'm going to add under target, I'll drag down the values only through the date that we have data for. Press okay. And again, same case, I want my X axis labels to extend all the way through the month, even though I don't have data for all of those days. Last but not least, over target, drag down to row 21. And then we'll grab all of our dates in column A, and there you go. So things don't look quite right yet. And that's okay, because we've got to do a little bit of work to turn this into what we need to. So I'm going to go into chart tools, change the chart type, and go into combo. So you're probably thinking, oh my God, what has gone wrong here, this is just absolute chaos. But we're smarter than Excel, so we're going to tell Excel exactly what we're trying to do here. One by one, let's go through each of our series and decide what it needs to be visualized as. So our goal, you see it was changed to a clustered column. We just want that to be a line again. Our target, same thing. Just a line, and our cumulative revenue. Let's do a line with markers. Those are the three that we originally set back to their original form. And now these three, the new ones that we added. Lower, under, and over, these are all going to be stacked areas, so we're going to do the same thing for all three, all on the same axis, and press okay. And now you're seeing that we're getting a little bit closer here and in fact all we need to do is edit some of these area series and we should be golden. So first and foremost, this is our lower, which basically gives us the area up to the minimum point, whether it's revenue or target. So all we need to do is give it no fill and no border, which really just leaves us with the under and over that need to be adjusted. So here's the under, which we can fill with a red, and some transparency if you want. That looks fine. Then I can select the over target, and we'll give that one a nice dark green with a similar transparency. So now when we extend this out, we've got these custom shaded areas above and below our target line. So at this point it's really just a function of doing whatever formatting you want to do. Maybe we change the units to every two days, space things out a little bit. Probably helpful to add a legend here as well. So let's put a legend at the bottom, and space this out. And we don't need a legend for lower 'cause it's really just invisible anyway. Now I'll make that bold. And of course you can add data labels if you want, maybe you just want to add a data label to your current date revenue value. And you can give it a nice dark blue fill with bold white font, that's kind of a nice effect. And you can go into the options, put it above your data point. And really from here it's just up to you how you want to tweak and personalize this. But there you go, very unique and effective way to visualize something like pacing against a goal.