Conditional formatting: when the data talks back
1. Conditional formatting: when the data talks back
Welcome back! In this chapter, we'll be shifting from analysis workflows like lookups and charts to practical oversight: making sure our data is reliable before using it for advanced analytics.2. The big Excel challenge
Most Excel problems don't come from formulas—they come from not identifying and addressing data quality issues. Missing values, duplicates, and inconsistent entries can either quickly derail our analysis, or even worse, remain undetected and lead to misleading results. Catching them early saves you rebuilding the analysis later. Conditional formatting can play a big part in this, as it can highlight problematic data before it impacts our results.3. Conditional formatting to surface issues
Conditional formatting applies rules that highlight cells based on their value. Blanks can be highlighted in one color, duplicates in another, and specific status flags - like "Overdue" in a status column - can all be formatted differently. Instead of scrolling row by row looking for problems, we can see the issues at a glance. Copilot can suggest these formatting rules from a natural language prompt, so we don't have to build the formulas for each one manually. The workflow becomes: we describe what we want to be highlighted, and Copilot implements the rule and formatting. Let's see this in action!4. Example: Highlight missing values
Our project tracker has blanks scattered through it - a missing owner here, a missing date there. To make those stand out for anyone reviewing the sheet, we describe the rule we want in plain words.5. Example: Highlight missing values
Copilot applies the rule and reports what it did: thirteen blank cells across Projects A2 to F46. Read that scope line. It is far easier to catch a rule pointed at the wrong range now than after someone has acted on the colors.6. Example: Layering a second rule
Now we ask for a second rule on top: mark the rows where Status is Complete in green.7. Example: Layering a second rule
The rules layer rather than replace each other. Look at rows four and thirteen: both are green because they are complete, and both still carry red where data is missing. A finished project with gaps in its record is still visibly incomplete. Check the column and the condition, not just the color. A rule aimed at the wrong column still looks tidy while it colors the wrong cells, and nobody reviewing the sheet will question it.8. Let's practice!
Time to give this a try!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.