Excel Conditional Formatting Tutorial | Beginner to Advanced, by Data Universe on YouTube. 23 min 32 s.
- Video length
- 23 min 32 s
- Time to do it
- 25 minutes
- Difficulty
- Easy
- Steps
- 10
What you need
Steps
Select the cells you want to format, such as B2:B50, then open Home > Conditional Formatting on the ribbon. Every rule you add applies only to the selected range.
For a Greater Than rule, click Highlight Cells Rules > Greater Than, type your number, pick a format like Light Red Fill, then click OK. Less Than works the same way.
Use Equal To for exact matches such as a name or status. Use Between and enter a low and a high value to shade a band, for example 50 to 100.
To flag words, choose Highlight Cells Rules > Text That Contains, type the text and pick a fill. For dates, use A Date Occurring and select options like Last month or Next week.
Select the column, then click Highlight Cells Rules > Duplicate Values to shade repeated entries. Pick Unique in the dropdown instead if you want to spot one-off values.
Open Top/Bottom Rules > Top 10 Items and change 10 to 5 for a top five. Top 10 Percent ranks by share, and Above Average or Below Average compare values to the column average.
Data Bars draw a bar inside each cell, so choose Gradient Fill or Solid Fill. Color Scales shade cells from low to high and give you a quick heat map.
Icon Sets add traffic lights, arrows, shapes or ratings. Check the thresholds under Manage Rules so the icons match your own numbers.
To remove rules, select the cells and use Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Clear Rules from Entire Sheet wipes everything at once.
For whole-row highlighting, select the full table, choose New Rule > Use a formula and enter =$B2
Tips and common mistakes
Lock the column with a dollar sign and leave the row relative so each row is tested against its own values.
Rules run from top to bottom, so tick Stop If True on any rule that should win.
Check the Applies to box in Manage Rules if a rule shades the wrong cells.
Formatting updates on its own when you change the numbers, so you rarely need to redo a rule.
Questions
Why is my conditional formatting not working?
Check the Applies to range and the rule order in Manage Rules. Numbers stored as text will also fail, so convert them to real numbers first.
How do I copy a rule to other cells?
Use the Format Painter on a formatted cell, or copy it and choose Paste Special > Formats.
Can I highlight a whole row based on one cell?
Yes. Select the full row range and use a formula rule with a locked column, such as =$B2
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.
