Spotlight visualization in Excel turns three plain KPI percentages into stage-style lights that glow red, amber or green on their own. In this first part you build RAG (Red, Amber and Green) stoplights for Service Level, Quality Score and Sales Conversion. The lights are ordinary Excel shapes, a simple IF formula decides the colour, and Name Manager plus a Linked Picture swap the right light into place. There is no VBA and no chart involved, so it works in any desktop version of Excel from 2010 onwards.
Watch the Video Tutorial
PK writes the colour formula, draws one spotlight from an oval and a trapezoid, makes red, yellow and green copies, and connects them with named ranges, INDIRECT and linked pictures.
What You Will Build
- A colour rule that returns Red below 50%, Yellow from 50% to 80% and Green above 80%.
- A 3-D spotlight base made from two ovals with a grey gradient, a bevel and a 3-D rotation.
- A light beam made from a flipped trapezoid with a fading, transparent gradient.
- Three versions of the light (red, yellow and green) parked in cells A1, B1 and C1 of a Support sheet.
- Dynamic linked pictures that show the matching light for each KPI, with live value and label text on top.
Step 1 – Set Up the KPI Data and the RAG Formula
The dashboard has three metrics. In the video they sit on Sheet1 like this:
- A1:A3 – the labels Service Level, Quality Score and Sales Conversion.
- B1:B3 – the percentages.
- C1:C3 – the colour name for each metric.
- F1:G3 – a small legend: Below 50% Red, 50% to 80% Yellow, Above 80% Green.
The colour formula
In C1 enter the formula below and fill it down to C3 (in the video it is shown in C2 with B2):
=IF(B1<50%,"Red",IF(AND(B1>=50%,B1<=80%),"Yellow","Green"))How the formula works
- The first IF checks whether the value is below 50%. If it is, the result is
Red. - Otherwise a second IF uses
ANDto test two conditions together: at least 50% and at most 80%. Both true givesYellow. - Anything left over (above 80%) falls through to
Green.
The three words matter. Later they are used as the names of the three lights, so spell them exactly Red, Yellow and Green.
While you build, PK fills B1:B3 with =RANDBETWEEN(20,99)% style test values so that every press of F9 produces new percentages and you can watch the colours change.
Finally, switch off gridlines in View → Gridlines so the shapes sit on a clean white canvas.
Step 2 – Draw the Spotlight Base
Two ovals, centred
Go to Insert → Shapes → Basic Shapes → Oval and draw an oval. Select it and press Ctrl+D to duplicate it. Select both ovals and use Shape Format → Align → Align Center, then Align Middle, so they sit exactly on top of each other. Now click one oval and drag a corner with Ctrl+Shift held down: it shrinks evenly towards its centre, giving you a large outer oval and a smaller inner one.
Colour the inner and outer oval
- Small oval – Shape Outline: No Outline. Shape Fill: yellow.
- Big oval – right-click, Format Shape → Fill → Gradient fill, pick the first preset gradient, then recolour the stops: white for the first, dark grey for the second and an even darker grey for the next. Set Line to No line.
Add depth with a bevel and 3-D rotation
Select both ovals, right-click and choose Group. Click once more to select only the big oval inside the group, then open 3-D Format in the Format Shape pane. Pick a top bevel (PK uses the convex style) and set Depth to 25 pt. Now click outside, reselect the whole group and open 3-D Rotation → Presets → Off Axis 1.
Step 3 – Create the Light Beam
Insert Basic Shapes → Trapezoid. Use Shape Format → Rotate → Flip Vertical so the narrow end points down, and place it over the base. Set Shape Outline to No Outline.
The beam effect comes from transparency. In Format Shape → Fill → Gradient fill use a Linear gradient at 90 degrees with four stops:
- Stop positions 0%, 33%, 66% and 100%.
- The same yellow colour on every stop.
- Transparency of 40%, 60%, 80% and 100% respectively.
Select the base group and the beam and group them into one final shape.
Make the red and green versions
Press Ctrl+D twice to get three spotlights. Keep the middle one yellow. In the first one, double-click the small oval inside the group and change its fill to red, then recolour the four beam stops to red with the same 40/60/80/100% transparency. Repeat with light green for the third light.
Step 4 – Park the Lights on a Support Sheet
Add a new worksheet and name it Support. Make the rows and columns large enough to hold one spotlight per cell. Cut the three spotlights (Ctrl+X) and paste them on the Support sheet so that:
- the red light sits in A1,
- the yellow light sits in B1,
- the green light sits in C1.
Select all three shapes, right-click, open Size and Properties → Properties and choose Don’t move or size with cells. Then copy column A and use Paste Special → Column widths on B and C so the three cells are the same size. Optionally add an outer Offset: Center shadow to the base (PK sets its size to about 105%) for a soft floor shadow. Turn off gridlines on this sheet too, otherwise they would show up inside the pictures.
Step 5 – Make the Spotlight Visualization in Excel Dynamic
Name the three lights
Open Formulas → Name Manager → New and create three names that point at the three cells:
Red =Support!$A$1
Yellow =Support!$B$1
Green =Support!$C$1These names are spelled exactly like the results of the IF formula.
Create one name per KPI with INDIRECT
Create three more names, each using INDIRECT on the colour cell of its metric. In the video PK types =indirect( in the Refers to box and then clicks the metric’s colour cell on Sheet1 (the narration names C1 for Service Level). The full reference is not readable on screen, but clicking a cell in that box inserts an absolute reference, so the three names end up like this:
Service_Level =INDIRECT(Sheet1!$C$1)
Quality_Score =INDIRECT(Sheet1!$C$2)
Sales_Conversion =INDIRECT(Sheet1!$C$3)If C1 contains the text Yellow, INDIRECT(Sheet1!$C$1) reads it as a reference to the name Yellow, which is Support!$B$1, the cell holding the yellow light. Change the value, the IF formula returns a different word, and INDIRECT points to a different cell.
Insert the linked pictures
Copy cell A1 on the Support sheet. Go to Sheet1 and choose Home → Paste → Other Paste Options → Linked Picture. You get a live picture of that cell, spotlight included. Press Ctrl+D twice for three pictures, then use Align Top and Distribute Horizontally to line them up.
Now select the first picture, click in the formula bar, type =Service_Level and press Enter. Do the same with =Quality_Score and =Sales_Conversion for the other two. Each picture now shows whichever light its name points to. Press F9 and watch the lights switch as the test values change.
Step 6 – Add Live Labels and Values
Insert a text box or a WordArt shape on the base of the first light. With the shape selected, type = in the formula bar and click the label cell (Service Level) so the text comes from the sheet. Make it white, bold and a little bigger, then copy it to the other two lights and link them to their own labels.
For the big percentage, copy a label shape and link it to the value cell instead, for example =$B$1. PK formats it with the Agency FB font at around 45 pt, bold, no outline and no shadow, then adds an Angle bevel and a 3-D rotation for the chrome look. Link the copies to B2 and B3.
Modern Excel Alternative
This section is an addition and is not shown in the video. In Excel 2019, 2021 and Microsoft 365 you can write the colour rule with IFS, which reads more easily than nested IF:
=IFS(B1<50%,"Red",B1<=80%,"Yellow",TRUE,"Green")IFS stops at the first true test, so the AND is no longer needed: any value that reaches the second test is already at least 50%.
Common Errors and Fixes
The picture shows the wrong light or an error message
The result of the IF formula must match a defined name exactly. A colour written as Amber, a trailing space or a typo means INDIRECT cannot find the name. Check the words in column C against the names in Name Manager.
The light is cut off inside the picture
The linked picture shows exactly one cell. If the spotlight is bigger than its cell on the Support sheet, increase the row height and column width until the whole shape fits.
Colours do not change
Make sure calculation is set to Automatic in Formulas → Calculation Options, and that each picture formula uses the KPI name (for example =Service_Level) rather than a fixed cell such as =Support!$A$1.
Tips
- Keep the thresholds in cells and point the IF formula at them, so you can change 50% and 80% without editing formulas.
- Use the same technique for any status picture: arrows, smileys or flags. Just name each cell after the status word.
- Microsoft’s guide to defining and using names in formulas covers Name Manager in detail.
Want It Ready-Made?
If you would rather start from the finished workbook, the Spotlight Visualization in Excel (Part-1) file is available on NextGenTemplates.com, and you can browse more Excel charts and visualization templates there. To learn charts, infographics and complete dashboards step by step, see the course Excel Pivot Tables and Dashboards.
Related Tutorials
- Highlight the Top Performer in an Excel Chart with a Spotlight (Part 2)
- Traffic Lights in MS Excel
- Stylish Traffic Lights in Excel
- Dynamic Graph with the Excel Camera Tool
- 3D KPI Indicator in Excel
Frequently Asked Questions
What is spotlight visualization in Excel?
It is a KPI display where each metric sits under a stage-style light that glows red, amber or green depending on its value. The lights are Excel shapes, and a linked picture shows the right one automatically.
Do I need VBA to change the spotlight colour?
No. An IF formula returns the colour name, INDIRECT turns that name into a reference to the matching light, and a linked picture displays it.
Why does the IF formula return a word instead of a colour?
The word is used as the name of a cell. Red, Yellow and Green are defined names that point to the three lights on the Support sheet, so the word decides which light the picture shows.
How do I change the RAG thresholds?
Edit the 50% and 80% limits in the colour formula in Step 1, or better, keep the limits in two cells and reference those cells in the formula.
Can I use more than three colours?
Yes. Draw another light, put it in the next cell on the Support sheet, give that cell a name, and extend the IF formula so it can return that name.
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
Spotlight visualization in Excel looks advanced, but it rests on a few simple pieces: an IF formula that returns Red, Yellow or Green, three shapes parked in named cells, INDIRECT to pick the right name, and a linked picture to display it. Build it once and you can drop eye-catching RAG lights into any dashboard or presentation. In Part 2 the same spotlight idea is used to highlight the top performer in a column chart.
