Conditional formatting in a line chart lets you colour each data point by a rule, just like conditional formatting colours cells. In this tutorial you build a month-wise Service Level chart in Excel where every month that meets the target gets a green marker and every month below it gets a red one. The target sits in a single cell, so when you change the target or any month’s value, the colours update automatically. No VBA is needed – two helper columns with IF and NA() do all the work.
Watch the Video Tutorial
In the video PK adds a target cell and two support columns, builds a line chart with markers, styles the green and red points and adds arrow callout labels, then tests the chart with different targets.
What You Will Build
- A target cell (I1) that controls the colours of the whole chart.
- Two helper columns, Green and Red, that split the data by the target.
- A gold service level line that shows the trend across the year.
- Green and red circle markers for months at or above and below the target.
- Arrow callout labels: green above the point, red below it.
How Conditional Formatting in a Line Chart Works
Excel charts have no built-in conditional formatting. The trick is to plot the same data three times. The original series draws the line. Two extra series draw only markers: one holds the values that meet the target, the other holds the values that miss it. Every other cell in those columns contains #N/A, which a line chart does not plot. So each month ends up with exactly one coloured marker on top of the line, and because the helper columns are formulas, the colours follow the data.
Part 1 – Data, Target and Helper Columns
Step 1: Enter the data
Put the headers Month and Service level in A1:B1 and the twelve months in A2:B13. The sample values are 75%, 80%, 53%, 82%, 74%, 74%, 41%, 86%, 47%, 68%, 74% and 90%. Turn off gridlines in View › Gridlines for a cleaner sheet.
Step 2: Add the target
Type Target in H1 and 75% in I1. Every formula will point to this one cell, so it becomes the control for the whole chart.
Step 3: The Green column
Type Green in C1 and Red in D1. In C2 enter:
C2 =IF(B2>=$I$1,B2,NA())Press F4 after clicking I1 so it becomes $I$1 and stays fixed when you copy the formula down.
Step 4: The Red column
In D2 enter the opposite test:
D2 =IF(B2<$I$1,B2,NA())Select C2:D2, fill down to row 13 and format both columns as percentages.
How the formulas work
B2>=$I$1asks whether the month meets the target. If it does, the Green column shows the value; if not,NA()returns #N/A.B2<$I$1is the exact opposite, so the Red column gets the months that miss the target.- Every month is either green or red, never both and never neither. With 75% as the target, Jan (75%) is green because the rule is greater than or equal to, while May (74%) is red.
NA()is used instead of an empty text value because a line chart treats empty text as zero, which would draw extra markers at 0%.
Part 2 – Build the Line Chart
Step 5: Insert a line chart with markers
Select A1:D13 and go to Insert › Line › Line with Markers. Excel draws three lines: Service level, Green and Red. Resize the chart to a comfortable width.
Step 6: Format the Green series
Click one of the green points, right-click and choose Format Data Series › Fill & Line:
- Line: No line, so only markers remain.
- Marker options: Built-in, type Circle, size 8.
- Marker fill: Solid fill, green.
- Marker border: Solid line, green, width 3 pt, compound type Double.
Step 7: Format the Red series
Do the same for the Red series: No line, a built-in Circle marker, solid red fill and a solid red border, width 3 pt, compound type Double.
Step 8: Format the Service level line
Select the original Service level line. Set Marker to None, because the coloured series now provide the markers, and set the line to a solid Gold, Accent 4 with a width of 1.5 pt. The chart now shows a single gold line with a green or red dot on every month.
Part 3 – Labels and Finishing Touches
Step 9: Green labels above the points
Right-click a green point and choose Add Data Labels. Right-click the labels, choose Format Data Labels and set the position to Above. Right-click the labels again, choose Change Data Label Shapes and pick Down Arrow Callout. On the Home tab give them a green fill, a bold font and a white font colour. The callout now points down at its marker.
Step 10: Red labels below the points
Repeat for the red points, but set the label position to Below and choose the Up Arrow Callout shape, with a red fill, bold and white font. Red labels point up at the months that missed the target.
Step 11: Clean up
Select the legend and press Delete. Make the horizontal axis (months) and the vertical axis (percentages) black and bold, and give the chart a title. The finished chart in the screenshot uses the title Month Wise Service Level and shows the target above the chart.
Step 12: Test it
Change the target in I1 to 85% and most points turn red; change it to 65% and most turn green. Change a single month – for example Mar from 53% to 80% – and that one point switches colour. The chart is fully dynamic.
Optional Extras (Additions)
The finished screenshot also uses a smoothed line (Format Data Series › Line › Smoothed line) and a soft glow on the markers (Format › Shape Effects › Glow); these are not narrated in the video. The two ideas below are general Excel techniques added to this tutorial, not steps from the video:
- Show the target as a line. Add a column E with
=$I$1in every row, add it to the chart and format it as a thin dashed line. Readers then see exactly where the threshold sits. - Dynamic target text. Put
="Target-"&TEXT(I1,"0%")in a spare cell, insert a text box on the chart, type=in the formula bar and click that cell. The text updates with the target.
Common Errors and Fixes
These fixes are general Excel guidance added to this tutorial.
Markers appear at 0% on the chart
The helper formulas return empty text or 0 instead of #N/A. Use NA() as the false result so the chart skips those months.
Colours do not change when I edit the target
The target reference was not locked, so the copied formulas point to I2, I3 and so on. Use $I$1 and fill the formulas down again.
A month shows both a green and a red marker
Both formulas use the same comparison, for example >= twice. One column must use >= and the other <.
The gold line has markers of its own
The Service level series still uses automatic markers. Set its marker to None.
Tips
- Decide whether hitting the target exactly counts as a pass. This chart uses greater than or equal to, so 75% against a 75% target is green.
- Give the target cell a bright fill so users know they can change it.
- The same two-series idea works with column charts: format the Green and Red series as columns with 100% overlap.
- Hide the helper columns or move them out of sight once the chart is finished.
- Microsoft’s NA function reference explains how #N/A behaves in formulas and charts.
Want It Ready-Made?
If you want the finished, formatted workbook, get Conditional Formatting in a Line Chart on NextGenTemplates.com, or browse more Excel charts and visualization templates. To build target-versus-actual charts and dashboards step by step, join the course Excel Pivot Tables & Dashboards: Basic to Advanced.
Related Tutorials
- Highlight the Specific Data Point in a Line Chart Dynamically
- Highlight Top and Bottom N in Excel Chart
- Dynamic Conditional Formatting in Excel
- Infographics with Line Chart in Excel: Use Icons as Markers
- Conditional Formatting Tutorials
Frequently Asked Questions
Can you apply conditional formatting to a line chart in Excel?
Not directly. Excel has no conditional formatting for chart series, so you split the data into helper columns with IF formulas, one per colour, and format each helper series separately.
How do I change the colour of points below a target in an Excel chart?
Create a Red column that returns the value when it is below the target cell and NA() otherwise, add it to the chart as its own series with no line and red markers. Step 4 shows the formula.
Why do the helper formulas use NA()?
A line chart does not plot #N/A, so only the months that match the rule get a marker. Empty text or zero would be plotted at the bottom of the chart.
Does the chart update automatically?
Yes. The helper columns are formulas, so changing the target in I1 or any monthly value recolours the points instantly. No macro is needed.
Can I use more than two colours?
Yes. Add one helper column per band, for example red below 70%, amber from 70% to the target and green at or above the target, and make sure the conditions do not overlap.
About the Author
PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.
Conclusion
Conditional formatting in a line chart comes down to two IF formulas and a target cell: one helper series for values that meet the target, one for values that miss it, and #N/A everywhere else. Style the markers green and red, add arrow callouts and you have a trend chart that shows at a glance which months need attention.
