Toggle Table Rankings On and Off with a Radio Button!
Excel Hack #94
For data reports, sometimes you want your data ranking in relation to each other visible. And sometimes, you don't want it visible.
Microsoft Excel's conditional formatting feature allows a quick way to rank data relative to one another visually, using in-cell bar charts, icons and heatmaps. However, for some use case or another, you might find that a heatmap is perfect for analysis, but too "busy" for a final presentation. Or perhaps you want to give your users the power to choose: do they want the raw numbers, or the visual story?
In the world of professional dashboards, interactivity is king. Static reports are like paper maps; interactive reports are like GPS. By adding a simple Radio Button (Option Button) to your sheet, you can "cloak" and "uncloak" your conditional formatting at will. It’s a sophisticated touch that makes your spreadsheet feel less like a grid of numbers and more like a custom-built application. Let’s wire up this switch!
We’re going to use a "ghost" cell that tells our Conditional Formatting whether to show up for work or stay home.
Step 1: Summon the Developer Tab
To get our radio button, we need the Developer Tab. If you don't see it:
Right-click any ribbon tab and select Customize the Ribbon.
Check the box for Developer in the right-hand list and click OK.
Step 2: Plant the Radio Buttons
Go to the Developer Tab > Insert > Option Button (Form Control).
Click and drag to draw two buttons near your data.
Right-click the first one, select Edit Text, and name it "Show Ranking."
Name the second one "Hide Ranking."
Step 3: Create the "Ghost" Link
Right-click your "Show Ranking" button and select Format Control.
Under the Control tab, click into the Cell link box.
Select an empty cell (let's use
$P$4).Do the same for the "Hide Ranking" button - link it to the same cell.
Now, when you click "Show," cell
P4will show 1. When you click "Hide," it will show 2. This is our engine!
Step 4: Program the Visual Logic - the default heatmap
Highlight the data range you want to format (e.g., your sales figures).
Go to Home > Conditional Formatting > New Rule.
Select "Use a formula to determine which cells to format."
Enter this formula:
=$P$4=1Click Format, go to the Fill or Icon tab, and choose your ranking style (like a green-to-red heatmap).
Click OK.
Step 5: Program the hidden rank state
Highlight the same data range as above
Go to Home > Conditional Formatting > New Rule.
Select "Use a formula to determine which cells to format."
Enter this formula:
=$P$4=2Click Format, go to the Fill tab and select white fill, and go to the Font tab and select black font color
Click OK.
