Microsoft Excel - 6 Amazing Uses of Conditional Formatting (Tricks you've 'likely' never seen), by BCTI on YouTube. 36 min 33 s.
- Video length
- 36 min 33 s
- Time to do it
- 15 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Open your workbook and pick an empty cell to hold the checkbox result, for example F1. Leave it blank for now.
Insert a checkbox. In Microsoft 365, go to Insert > Checkbox. Otherwise enable the Developer tab and use Insert > Form Controls > Check Box.
Right-click the checkbox and choose Format Control. On the Control tab, set Cell link to F1 and click OK.
Select the cells you want to highlight, then go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format and enter a formula that points at the linked cell, such as =$F$1.
Click Format, pick a fill colour on the Fill tab, then click OK twice to close both boxes.
Tick and untick the checkbox. The highlight should appear and disappear as the linked cell changes.
For adjustable settings, add a dropdown in a spare cell using Data > Data Validation > List, with entries like Min, Max and Average.
Edit the rule formula so it reads the dropdown cell, for example =A2>=LARGE($A$2:$A$50,3) to mark the top three values.
Click OK, then change the dropdown and confirm the highlighted cells update.
Tips and common mistakes
Use dollar signs in the formula, like $F$1, so the rule keeps pointing at the same cell across the whole range.
If nothing happens when you tick the box, check the cell link and make sure the formula returns TRUE or FALSE.
Open Conditional Formatting > Manage Rules to edit or delete rules later instead of starting over.
Keep the linked cell and dropdown away from the data range so your rule does not highlight them.
Questions
Why does my checkbox not change the formatting?
The checkbox is probably not linked to a cell, or the formula points at the wrong cell. Right-click the checkbox, open Format Control and confirm the cell link matches your rule.
Can I use one checkbox for several rules?
Yes. Point each rule at the same linked cell, so ticking the box switches all of them on at once.
Does this work on Excel for Mac?
Yes. Form Control checkboxes and conditional formatting rules work on Mac, though the Developer tab may need to be enabled in Preferences first.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
