 Hello everyone and welcome back to another video. In this video we're going to be showing you how to create the line chart you see on front of us that simply will highlight the maximum point, the green, and highlight the minimum point red. And the great use of this is if you change your data, so our maximum moment is June, so if we had to change this to a lower number like a hundred, you can see that the chart dynamically updates for you and highlighting the new maximum value. So as soon as you're ready, we'll jump in with the video and show you how this is achieved. So now that you've seen what the final chart would look like, we'll step through obviously from scratch to show you how we achieve that result. So we start with our basic data. So we can see we've got two columns. We've got a month column and we've got the results column. So this will give us the information we need to build out our basic line chart. So all we need to do is highlight our data range, we'll go to our insert tab, and then we'll go over to the options here to insert chart and it's going to do the drop-down. I'm going to go to this option over here, which is to select the line with markers chart. So when I enter those in, you can see it looks something like this. So we've got our line chart and each of our data points has got that blue marker on. Obviously if I wanted to change any one of these dots and do it manually, so let's say we could pick out the biggest one up here, it's currently made and let's reformat, format that, so we'll go format data point and then we can go down to our fill and we'll go to that's the line settings, we want to do our marker settings, so we'll go to mark our fill and then we'll change that maybe to a solid fill such as green, let's go for green to highlight the maximum. So we can see that has now changed into our line chart, so we've got that green icon there rather than the blues. The problem with this is if May now changes to a different value, so maybe it goes down to 120. You can see the chart will update to reflect that, but obviously because we've manually changed the color of that one marker for May, it's not going to update when our data does. It's not going to be dynamic. So this is why we want to use some conditional formatting in our line chart to enable to when a change like this happens, so different totals have come into our data, it will automatically look and then reconditional format the new maximum value. So in this example, it would now be January. So let's just do undo those changes I've made there and undo that coloring just to get back to where we are. So what we actually need to do is we need to enter two additional series to our line chart and just quickly for those not familiar with our line chart or any chart we have, each column in this example of data represents one series. So for us, our series is currently the result. And if I just collect one of these data markers, you can just see it's now highlighted obviously the month and also our series of result that were on the right there. So in order to achieve the result we want, we're actually going to enter these two additional series, one for max and one for min, and it's upon those series that we're going to do our conditional formatting to pick out these max and min as they occur. So we're just going to move our chart over to the right-hand side a bit more to create some more space and we can then obviously enter these additional two series. First I'm going to do is we're going to type in here max. So we've got a max column and then lastly we'll do a min. So we've got our two additional columns. What we now need to do is we'll quickly manually enter this in. Well, actually we won't manually let this go straight in with our formula. We're going to use a formula for each of these at the max and min formulas, so you're probably familiar with our previous videos we've done, which is going to go and identify the max value. So for us it's simply going to be a matter of saying using if statement. So if this value is equal to the max for the data in this range here. And I'm just going to enter the dollar symbols in front of all this just so that that range does not change because it needs to be on there. Because obviously if you don't lock down this range here as we drag our formula down that range is going to move with it. So if that value, so 217 is equal to the max, then we simply want to just pull through that value else the time being we'll just leave this as blank and close our brackets. And then we can pull this formula down and we can see it's only going to populate for where our max value is. So for us at the moment it's May. We're going to do exactly the same for our min column. So we're going to go equals if that value is equal to the minimum of this range. And once again, we're just going to enter in our dollar symbols here. So dollar dollar dollar dollar. So if it is the min, then obviously you want to pull out the value else. Once again, we just want to show black blank. Sorry, not black blank, if not, and then we can drag that formula down as well. So you can see we have now got these two additional values, one value per each column. And obviously we know that each of these, so the max is one and the min is the second of our two additional series. To add these to the chart, we can really quickly do this just by highlighting our chart. And in the bottom right corner, just drag that range across and let go and you can see that those series have been added in there for us. So it looks a bit messy at the moment. But you can see this is the starting point of achieving what we're after. So at the moment, our max is being highlighted by the color orange and our min is the color gray. So we can see that by you leaving these cells blank, the ones that do not contain the max, that we're obviously going to have, it looks a bit messy basically. We're going to have this continuous line chart, what peaks when we've got the max and also then peaks when we've got the min. So we want all these values other than the max and the min to just be invisible to us. And the way we do that is instead of using the hashtag, not hashtag, the blanks, these two quotes, all we're going to do is replace that with na and open and close brackets, the na function. And what this does as you'll see as we go along and predominantly if we go to min. So at the moment you can see we've got a gray here in January. If I just replace these quotes with the na function, I was now put in there, you can see that that will then make a blank reference in that month. So if you leave each one of these cells blank, you're going to have a data point. If you put the hashtag na, it actually just hides it or removes it even, just so it doesn't even appear on our chart. And obviously now when we pull down these, oh, done that by wrong, well, I now pull down these two formulas to get rid of those other blanks. You can see that our data has been updated into chart and we can now only see the max and the min as well. The benefit of this is if we now change our main number. So let's say let's make this something a lot lower. So let's make this a value of 90. You can see that the main one actually now becomes the minimum value for us. And that has now been updated to be the gray color. And January is now the max and that is now the orange. So never one quick demonstration of that. Let's change this back to 263. If we now take our June number, what's currently our largest at 263, and let's go and make August. Let's make August our largest number. So go to August and we'll change that to 280. You can see how that's now updated to be our max and the color has followed it. The conditional formatting has followed it to highlight that as the max. All we now need to do having got this in place is we can just simply select each series. So we'll select the max first and you can see when I select that series it's just the max column that's been highlighted. All I need to do is go down to our fill settings for the marker. We'll go into a solid fill here and let's just change the color to a green. Maybe that's quite a bright green there. And then likewise for the min, we just select that and we know it's selected because this column has been selected for us. And we'll again just go into a solid fill and let's make this red. Red seems to go quite well with the minimum ones. So you can see that those two colors have been updated there as well. We don't need this side section anymore. We can make our chart larger so we can see what we're looking at. Let's just move that like that. And then lastly, so the bit we obviously then do to tidy this up is we can simply highlight all this information here and very basically we could either hide our columns. It's good like this, hide. Or alternatively, if you wanted to, you could just... Ah, that doesn't work. So what we'll do instead is we'll just go for these two columns and we'll just change the text to the color of white. Oh, when my Excel keeps up. So let's just change the color to white. So you can see you can't see those columns any longer. We'll then take our chart, we'll just hover over here and just fill in that gap. And you can now see our end result. We've got our line chart and it's now highlighting our maximum in. And if we change our value, so let's change our August number to 100. You can see how the chart automatically updates the data for us and it automatically will populate the new max and the new min as required every time the data changes. So we hope you enjoyed the video. And if you did, please do give the video a like. It would be greatly appreciated. If you haven't already, do subscribe to the channel. Hit that bell notification so you're notified of all of our future videos that come out. You'll find a few links in the description to this video for our social media and also our website. So again, check those out. Great ways to get in contact with us. But alternatively, if you do have any questions, please just leave a comment under this video and we'll get back to you as soon as we can. Thank you very much for watching and we'll see you in the next video.