How to Highlight When Line Drops or Peaks in Comparison Excel Chart

Let's say we have this week's site visit data and last week's site visit data.

I want to compare week 2 data with week one visually. Now, there are many methods to do that. But I want to have a line chart, where the line shows week 2's data. If Week2's data is less than week one, that area should be highlighted in red and if it is more than Week1's data then it should highlight that area in green.

Something like this.

So how do you do this? We will use a similar technique to the shading area below the curve line. Let's see:

Follow these steps:

Step 1: Add Three Helper Columns. To make a chart that shades the up and downs of the line in comparison, we will need three helper columns.

The first helper column will contain the same values as the first week. I name it week1 shade.

In Cell D2, write this formula and drag it down.

In the next column, we need all the UP values. It will be a positive difference between Week2 and Week1 data. In other words, we need to subtract Week1's data from Week2 only if Week2 is greater than Week1. The formula in E2 will be:

Drag it down.

Finally, we need a DOWN column that has all the points that are less than Week1's Data. The formula in F2 will be:

Step2: Select the Day and Week1 Column and Insert a Stacked Area Chart: In our Example, we select the range A1:B8. Go to Insert--> Charts--> Line and Area --> Stacked Area Chart. We will have a chart that will looks like this.

Step3: Add other columns as data series to the chart:

Right-click on the chart and click on the select data option. In legends and entries, click on the add button. Now in the series name, select C1 ("Week2").  In series value, select C2:C8:

Do this for all the columns, one by one.

When you will complete you will have a chart similar to this.

Step 4: Change the data series chart type of Week1 and Week2 to Line Chart.

Right-click on any data series and click on Change Series Type. Change the series type of Week1 to line chart and Week2 to Line With Markers.

Now we have a chart that looks like this:

Step 5: Change the areas on the chart.

Now we just need to change the color of the areas on the chart.

First, select the big bottom area of the chart. The Week1 Shade Area. Here it is grey. Go to formatting --> Fill --> Select No Fill.  Do the same in the border section of the chart.

Next, select the Down Series. Here it is blue. Go to fill and change the color to red.

Similarly, Select the UP series and change it's color to green. Increase the transparency of the colors a little bit.

Remove the gridlines. We don't need them. You can keep if you need it but I prefer a cleaner chart.

We do not need to see week1's series. Go to the formating of the week1 series and select no line. However, I want to see the markers of the week 1 line, so I select markers first. I do a little bit of formatting of the markers to suit my Excel dashboard.

I also need legends. I click on the Plus (+) sign of the chart and checked the legends. I remove unnecessary legends and let the needed once be there.

Finally, the chart looks like this.

You can download the template of this chart below.

Highlight When Line Drops or Peaks

So yeah guys, this how you can creatively compare two series in excel line chart. I hope it is useful for you. If you have any doubts regarding this chart or have any other excel/VBA related query, ask in the comments section below.

Related Articles:

How to Creatively Highlight Above and Below Average in Excel Line Chart | how do we make this chart that highlights the area when the line is above or below the average? Let's learn it step by step. We can simply insert an average line on the chart. It will look something like this.

How to Add Shade to Curve Line in Excel Chart | In google analytics and other online tools, you must have seen line charts. In these charts, you can see the shaded area under the curves. This shading looks cool, isn't it? The question is, can we do this in Excel? How we can shade the area under the curve chart?

How to Highlight Maximum and Minimum Data Points in Excel Chart| Knowing the highest and lowest value in a data set is essential in almost all kind of reports. But in line and column Excel charts, it often gets difficult to identify, which value is highest and which value is lowest.

How to Insert A Dynamic Vertical Marker Line in Excel Line Chart | We can draw a vertical line on the chart manually but that just not smart. We would like to add vertical lines dynamically to mark a certain data point, say the max value.

How to Create Milestone Chart in Excel | A milestone chart shows the date or time when a milestone achieved in a graphical way. This graph must be easy to read, Explanatory, and visually attractive.

Creative Column Chart that Includes Totals | To include the total of the clustered column in the chart and compare them with another group of the columns on the chart is not easy. Here, I have explained, how to smartly include totals in the clustered column chart.

4 Creative Target Vs Achievement Charts in Excel |Target vs Achievement charts is very basic requirement of any excel dashboard. In monthly and yearly reports, Target Vs Achievement charts are first charts the management refers too and a good target vs Achievement chart will surely grab the attention of management.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to Use The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to Use COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.