RAG Analysis Chart in Excel: Red, Amber & Green Background

A RAG analysis chart in Excel draws your monthly results as a line on top of red, amber and green bands, so you can see at a glance which months were poor, average or good. In this tutorial you build one for a monthly service level. The coloured background is a stacked column chart driven by a small scale table, and the result is a smoothed line with 3D ball markers. Change one number in the scale table – for example make Poor 30% instead of 50% – and the bands resize on their own. No VBA, no conditional formatting, just a combo chart and three helper columns.

RAG analysis chart in Excel: monthly service level line with 3D ball markers over red Poor, amber Average and green Good background bands — The finished RAG chart: March and July fall in the red Poor band, October reaches the green Good band.

Table of Contents

Watch the Video Tutorial

In the video PK first shows how the bands move when the scale changes, then builds the chart from a blank sheet: helper columns, stacked columns, the line series, gradient fills and the 3D markers.

What You Will Build

A scale table where you set the size of the Poor, Average and Good zones.
Three helper columns that turn the scale into stacked columns for every month.
A combo chart: stacked columns for the background and a line with markers for the service level.
Red, amber and green fills with gradients and no gaps, so the background looks like smooth bands.
A smoothed black line with white 3D ball markers and optional data labels.

The Data and the Scale Table

Data for the RAG chart: Month and Service Level in A1:B13, Poor, Average and Good helper columns C:E and the Scale table in A17:B20 — Month and Service Level in A:B, the three helper columns in C:E, and the scale table in A17:B20.

The sheet has two small tables:

A1:B13 – Month (Jan to Dec) and Service Level (for example Jan 80%, Mar 20%, Oct 91%).
A17:B20 – the scale: Scale and Color Range, with Poor 30%, Average 30% and Good 40%.

The scale values are the heights of each band, not the limits. With 30%, 30% and 40%, Poor covers 0 to 30%, Average 30 to 60% and Good 60 to 100%. In the video’s first example the scale is 50%, 30% and 20%, so Poor runs up to 50%, Average from 50 to 80% and Good above 80%. Because the service level can never be more than 100%, the three numbers must always add up to 100%.

Part 1 – Build the Helper Columns

Step 1: Add the Poor, Average and Good headers

Copy A18:A20 (Poor, Average, Good), click C1 and use Paste Special › Values with Transpose ticked. The three names now sit across C1:E1.

Step 2: Link each column to the scale

Enter these formulas in row 2. Press F4 after picking each cell so the reference is absolute:

C2  =$B$18
D2  =$B$19
E2  =$B$20

Select C2:E2 and fill down to row 13. Every month now has the same three numbers – 30%, 30% and 40%.

How the helper columns work

Each month gets one stacked column made of three blocks: Poor at the bottom, Average in the middle and Good on top.
Because every month uses the same values, the columns line up into three continuous horizontal bands.
The $ signs lock the formulas onto the scale table. Change B18, B19 or B20 and all twelve rows – and the bands in the chart – update instantly.

Part 2 – Create the RAG Analysis Chart in Excel

Step 3: Insert a stacked column chart

Select A1:E13 and go to Insert › Column Chart › Stacked Column. At this point the service level is just another block in each stack.

Step 4: Turn Service Level into a line

Right-click any column and choose Change Series Chart Type. In the Combo window set Service Level to Line with Markers and leave Poor, Average and Good as Stacked Column. Click OK.

Step 5: Fix the vertical axis at 0 to 100%

Right-click the vertical axis, choose Format Axis and set Minimum = 0 and Maximum = 1 (which is 100%). The bands now fill exactly the full height. Then select the axis and press Delete – the coloured bands do that job. Remove the gridlines and the legend from the Chart Elements (+) button.

Step 6: Remove the gaps between columns

Right-click a column, open Format Data Series and change Gap Width from 150% to 0%. The twelve columns join into solid bands.

Part 3 – Colour the Red, Amber and Green Bands

Step 7: Red gradient for Poor

Select the Poor series and go to Format Data Series › Fill & Line › Gradient fill. Set the type to Linear, remove the extra stops so only two remain, make the first stop red and the second a lighter red. Choose the Linear Up direction (angle 270°) so the dark red is at the bottom.

Step 8: Solid amber for Average

Select the Average series and give it a Solid fill in amber (orange-yellow).

Step 9: Green gradient for Good

Select the Good series, choose Gradient fill again and use a light green for the first stop and a darker green for the second.

Part 4 – Style the Service Level Line

Step 10: Make the line dark and smooth

Right-click the line, open Format Data Series › Fill & Line › Line and choose a Solid line in black (or another dark colour) with a width of about 2 pt. At the bottom of the Line section tick Smoothed line.

Step 11: White circle markers

Switch to Marker › Marker Options, choose Built-in, type circle and size 8 or 9. Under Fill choose Solid fill in white, and set the marker border to No line.

Step 12: Turn the markers into 3D balls

With the line selected, go to Format › Shape Effects › Bevel and pick Circle. The flat white dots now look like shiny 3D balls.

Step 13: Finishing touches

Make the month labels on the horizontal axis bold and black.
Optionally right-click the line, choose Add Data Labels, set Label Position to Above and make them black and bold.
Add a chart title such as Line Chart with RAG Background.

The finished chart in the image also shows Poor, Average and Good labels on the left of the bands. The video does not show how they were added; a simple way is to insert three text boxes inside the chart area. (Addition.)

Test That It Is Dynamic

With the scale at 30%, 30% and 40%, type 50% as January’s service level: the marker lands in the amber band, because 30 to 60% is Average. Type 10% or 15% and it drops into the red Poor band; type 80% and it climbs into green. Now change the scale to 50%, 30% and 20% – the red band grows to cover everything up to 50% and green shrinks to the top 20%. As long as the three scale values add up to 100%, the chart stays correct.

Common Errors and Fixes

The bands do not reach the top of the chart. The axis maximum is still on Auto, or the scale does not add up to 100%. Set Maximum to 1 and check that B18:B20 sums to 100%.
The line is drawn as a fourth stacked block. Service Level is still a Stacked Column. Change its series chart type to Line with Markers (Step 4).
Thin white lines between the columns. Gap Width is not 0%. Set it to 0 for any of the three column series. (Addition: general Excel guidance.)
Bands do not change when you edit the scale. The helper formulas are typed values or relative references. Re-enter them as =$B$18, =$B$19 and =$B$20 and fill down.

Tips

Use the same chart for any 0 to 100% KPI – attendance, on-time delivery, quality pass rate or customer satisfaction. (Addition.)
For KPIs that can go above 100%, raise the axis maximum and make the scale add up to that new maximum. (Addition.)
Save the finished chart as a template (right-click the chart › Save as Template) so you can reuse the styling. (Addition.)
New to chart formatting? Microsoft covers the basics in Create a chart from start to finish.

Want It Ready-Made?

If you would rather start from the finished file, the RAG (Red, Amber and Green) Analysis Chart in Excel template on NextGenTemplates.com is the workbook from this tutorial, and you can browse all Excel charts and visualization templates. To learn combo charts, dashboard charts and infographics step by step, join the video course Excel Pivot Tables and Dashboards.

Frequently Asked Questions

What is a RAG analysis chart?

RAG stands for Red, Amber and Green. A RAG analysis chart shows results against coloured zones – red for poor, amber for average and green for good – so you can see the status of each period without reading the numbers.

How do I change the red, amber and green limits?

Edit the Color Range values in the scale table. Each value is the height of its band, so Poor 30%, Average 30% and Good 40% means red up to 30%, amber from 30 to 60% and green above 60%. Keep the total at 100%.

Why are the background bands made with a stacked column chart?

Stacked columns with a gap width of 0 form solid horizontal bands whose heights come from cells. That makes the background dynamic, which a fixed picture or shape behind the chart cannot do.

How do I get 3D ball markers on a line chart?

Use white circle markers of size 8 or 9 with no border, then apply Format, Shape Effects, Bevel, Circle to the line series. Step 12 shows the details.

Which Excel versions can build this chart?

The combo chart, gradient fills and bevel effects are available in Excel 2013, 2016, 2019, 2021 and Microsoft 365. Excel 2010 can build it too, but the menus look different.

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, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

A RAG analysis chart in Excel needs only three helper columns linked to a scale table and a combo chart: stacked columns with no gaps paint the red, amber and green bands, and a smoothed line with 3D markers shows the trend on top. Change the scale and the zones move; change the data and the line follows. Use it in your business presentations to show at a glance which months landed in the red, amber or green zone.