Applying dynamic formatting with data validation

So if we select our chart, we can drag these across to add the new points to our chart. Now, if we go to “format” and we want to select our High Point, so our High Point is now selected on a chart. With our High Point selected, we can select “format data series”. Now, in our “format data series”, if we go to our “fill”, what we want to do is add a marker. So we select “marker” and “marker options” and we’re going to say a “built-in marker” and we are going to select a Circle. Now, we’re going to increase the size of the circle.

Let’s increase the size of the circle to ten, and we’ll come down here and we will say “no fill” on the circle. And we want to make it a green color because it is the highest point. And we’re going to increase the width on this as well to two points. So now we’ve created our highest point, we formatted our highest point and we can go ahead now and we can select our lowest point. So again, if we go to “format”, this time, if we format our low point and we will select our marker for our low point, marker options again, we’re going to select a circle so we come down and select the circle, we’ll increase the size to ten.

We will have it “no fill” and we will make the border a solid line and we’re going to make this a red solid line. Now, we’re going to increase the width of this as well to two so it stands out that little bit more on the chart. So now we have formatted our high points and our low points. Let’s check and see if these work. So, let’s change this to 156.23 and our low point name changes on the chart. Let’s change our high point to 181.05 and we see our high point also moves on the chart. So now we’ve created two dynamic points that are formatted differently using Helper Columns.

What we’re going to do now is add another Helper Column that will allow the user– that will be, that will be used when the user selects different dates in a drop-down to highlight a certain part of the chart. So up here, I am going to put in “start” and we’ll put in “finish”. And we will put in some data validation. So we will go to our data ribbon, data validation. And in our data validation, we are going to select a list. Now, the source of our list is going to be our dates, so we will select our dates and say, “okay” and I’m going to copy this and put this into our finish as well.

I’m going to select both of these cells and I’m going to make these cells Input Cells. And then my “start” and my “finish”, I’m going to align to the right and I am going to make Bold. So now, the user can select a start date and the user can select an end date. And what we want to happen is that that part of the line is highlighted in a different color. So we will need to create a new helper column, so this is user date. So it’s just called as a “user”. And what we need again is to create “NA’s” if the dates don’t fall within the selected range and if they do, we want to have the close price.

So we will use an “if statement” and with the “if statement”, there is going to be an “and statement” and we’re going to test to see if our date is greater than or equal to the start date. I’m going to press F4 to lock in the start date. And our second criteria or a second logical test is if our date is less than our finished date, less than or equal to our finish date and I’m going to lock in our finish date here. So now we have our start date and finish date in. So the value that we want, if it’s true, well, if it’s true, we want our closed price.

If it’s false, we want an “NA” and we can hit hit “enter” and fill the formula down. Now, I need dates that have been selected will be pulled into this column of the chart. So all we need to do now is add this additional column to our chart and format this column in a separate way. So again, we can just select our chart and we can drag the chart area over. Now we see this is highlighted now in yellow. What we can do is change the formatting to the required formatting.

So we can format this data series and let’s format this line to a, I don’t know, a red line and let’s make this one a stand out a little bit more. So let’s say this is 3.25, so it stands out just that– it’s just that little bit thicker on the chart and we will also go to our shadow and we can add a little bit of shadow to this as well. So now when the user selects dates, so let’s change the dates, the area on the chart that is highlighted is automatically updated using the helper columns.

Now this technique of using helper columns is a great way to get around and overcome all these extra formatting options that you can add to your charts and to your visualizations in your dashboards to give them that little bit of an extra appeal and to highlight different sets of data that may be of value to your– to the users of your dashboard or your charts. I really do hope that you enjoyed this video. Thank you very much for watching. My name is Paul Guilfoyle from the Excel club. Goodbye now. [END]