How to Use Conditional Formatting in Excel

How to Use Conditional Formatting in Excel

1. Start with a completed summary table

• In the video, the starting point is a table showing average customer review by technician and service type
• This gives you a clean range of numbers that can be visually enhanced with Conditional Formatting
• Select the full data range you want to format before applying any rule

2. Create a heat map with color scales

• Highlight the numeric range in your table
• Go to Home → Conditional Formatting → Color Scales
• Choose a color scale where:
  • Green represents higher values
  • Red represents lower values
• This works well when higher numbers are better, such as customer review scores
• If you are analyzing a metric where lower values are better, choose a reversed color logic instead

3. Test different visual styles

• After selecting your range, go to Home → Conditional Formatting
• Explore other built-in options such as:
  • Color Scales
  • Icon Sets
  • Data Bars
• In the video, icon sets are used to show how arrows and symbols can quickly signal high and low values
• Data bars are also shown as another way to compare relative size within a range

4. Customize the formatting style based on the metric

• Not every metric should use the same colors
• For example:
  • High customer reviews should usually appear green
  • High complaint rates or high costs might need to appear red
• The main idea is to match the formatting style to the meaning of the metric

5. Highlight cells below a specific threshold

• To find values below a cutoff, highlight the range
• Go to Home → Conditional Formatting → Highlight Cells Rules → Less Than
• Enter a number such as 4
• Excel will highlight every cell below that threshold
• In the video, this is shown as a better option than a color scale when you want to isolate all values below one exact benchmark

6. Compare threshold-based rules against heat maps

• Color scales are useful for showing relative highs and lows
• But they are less precise when you need to answer a question like:
  • Which values are below 4?
• In those cases, a direct highlight rule such as Less Than, Greater Than, or Between is much easier to read

7. Use other built-in cell rules

• Conditional Formatting also lets you highlight cells that are:
  • Greater Than
  • Between
  • Equal To
  • Not Equal To
• These options are useful when you want to flag only a specific band of values instead of the full range

8. Highlight an entire row based on one cell’s value

• In the video, the next example highlights a full service row when Days Since Last Service is above a certain threshold
• Start by selecting the entire table or row range you want to format
• Then go to Home → Conditional Formatting → New Rule
• Choose Use a formula to determine which cells to format
• This lets one column control the color of the whole row

9. Use a formula-based rule carefully

• In the video, the rule is built from the first row of the selected range
• This is important because Excel applies the formula relative to the top-left cell in the selected range
• If the wrong starting reference is used, Excel may highlight the wrong rows
• The example shows how the first attempt highlighted too much, then the rule was corrected through Manage Rules

10. Fix and manage existing rules

• If your formatting is not behaving correctly, go to:
  • Home → Conditional Formatting → Manage Rules
• This is where you can:
  • Review the current rule
  • Edit the formula
  • Change the applied range
  • Update the formatting style
• In the video, this step is used to correct a row-highlighting rule that was starting from the wrong place

11. Link the rule to a target cell

• Instead of hard-coding a value like 365, you can place the target in a worksheet cell
• Then update the Conditional Formatting rule so it references that cell
• This makes the logic dynamic
• In the video, the threshold is changed by editing the target cell, and the formatting updates automatically

12. Use locked references for target cells

• When a rule depends on a target cell, that reference should stay fixed
• In the video, this is explained as a locked reference
• This prevents the rule from shifting incorrectly across the selected range
• This is especially useful when building interactive worksheets with adjustable thresholds

13. Apply the same idea to other service metrics

• The video shows that the same process can be reused for many HVAC-related fields, such as:
  • Customer rating
  • Response minutes
  • Delay thresholds
  • Service recency
• Once you understand the logic, you can build rules around almost any operational metric in your data

14. Choose the best Conditional Formatting method for the question

• Use Color Scales when you want a quick visual heat map
• Use Icon Sets when you want directional signals
• Use Data Bars when you want relative size comparisons
• Use Highlight Cells Rules when you need exact thresholds
• Use New Rule with a formula when you want to highlight entire rows or build more advanced logic

15. Treat Conditional Formatting as a visual decision tool

• The main takeaway from the video is that Conditional Formatting helps transform raw service data into visual signals
• It makes patterns easier to spot without changing the underlying data
• Once the rules are in place, your table becomes much easier to scan for performance issues, delays, risks, and outliers