Binary ranges - Microsoft Excel Tutorial

- [Instructor] For the next demo, I want to talk about adding binary values to highlight date ranges within a chart. So, head to the second tab, the Binary Date Ranges tab. And here you'll see we just have two columns of data. We're looking at daily revenue trended out. And what we want to showcase here is a circumstance where you might have dates, or ranges of dates, that are meaningful in some way. Dates that you want to draw attention to or highlight within a chart. In this case, in column A you'll see some values, or some dates, formatted with a light green fill. And let's just say these dates represent sale or promo periods for this particular example. Realistically, that can be anything. Maybe you just want to showcase weekends in your data or maybe you want to highlight every Wednesday or Monday. Whatever it is, the point is that you're trying to highlight certain dates or date ranges in your chart. So we'll kick it off by selecting A and B and just inserting a basic line chart. And I'll drag it up and spread it out just a little bit. And why don't we go ahead and format that ugly x-axis. We give it a major unit of four simply to make that a bit more readable. So there you go. We've got trended revenue shown in the form of a line chart. And now when it comes time to actually featuring those date ranges within this chart, what I'm going to do is add a new column in C called Promo and it will have a default value of zero. So I can just double click to apply that zero all the way down. And now all I need to do is replace the zero with a one for any dates that fall within a promo period. So, 18 through 20 for instance. I can copy that one and apply it down here to rows 29 through 34, 51 through 62, 67 through 69, and finally 81 through 85. What I'm left with is a binary series in column C that takes either a value of zero if the date does not fall within a promo period or one if the date does. So at this point it's just a matter of getting this binary series into this chart in a way that makes some sort of sense. So simple enough I can right click, select data, add a second series, let's just call it promo. And for series values, I'll select cell C2. Control, shift, arrow down, to take me all the way down to the edge of this array which is C91, press okay twice, and scroll back up. And as you can see, we're not quite there. All it's done is add a second line on the same axis which either takes a value of zero or one and is just completely drowned out by the revenue totals. So, clearly we need to use a combo chart in this case. I'll head up to Chart Tools, Change my Chart Type, select Combo here at the bottom of the list. And, as you can see, Excel tried to get fancy and change the chart type on me. In this case I don't want Revenue to be a clustered column. I want to go back to the original line chart that I had selected earlier. And for the promo period which is my binary series, this one I want to be 100% stacked column on the secondary axis. And when I do that, you can see we're starting to get a little bit closer here. And so what I want to do is select that series, format it, and I want to give is a very nice light, solid fill. 'Cause I want to draw attention to those dates but I don't want to drown out everything else and I don't want to add too much noise to this visualization. So if I go down to Series Options, last thing I'll do with this series is just eliminate the gap width. And what that essentially does is it creates these columns of data the width of which represent all of the dates that fall within a particular promo period. But you might notice that we still have the secondary axis here which at this point is kind of meaningless. There really is nothing that falls along this zero to 100 scale but the problem is if I delete it it deletes the series with it. So, I'll control z to undo that and we'll just use a little shortcut. Go into the Home menu and we can do the white fill here. Another option, if we undo that, is to use the conditional formatting trick that we used during one of the earlier lectures. Format that axis. Go down to the Number options, and we're going to use a Custom Category and the code will be three consecutive semicolons and that essentially makes the text of the axis completely invisible. So, at this point it's really just whatever formatting you like. In this case I'll make it a solid, dark blue line. I'll smooth it out just because I like smooth lines. And then obviously you can add axis labels, chart titles, whatever your heart desires. But there you go. Pretty cool, pretty interesting way to feature or highlight specific date ranges within a chart in Excel.