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 means we'll have a solid foundation to build our analysis on. 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 "Yes" 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
We have an onboarding tracker in Excel with columns for date, owner, and status—but some cells are blank. To make these standout for review by a colleague, we can use Copilot to implement conditional formatting. We'll open Copilot, enable Agent Mode, and run a prompt asking to highlight any missing or blank values in the table. Copilot analyzes the range and implements a conditional formatting rule. We can check the scope of the rule—which columns and rows it's targeting—to ensure Copilot picks the range that we expect. Now every blank cell stands out instantly. Next, we'll ask Copilot to highlight cells where Status is "Yes" in green.5. Example: Highlight duplicates and status flags
Again, Copilot implements a rule and we verify the scope. Now we can see at a glance which rows are flagged as complete. Always check the correct column, condition, and color were applied. If something looks off, we can iterate further with Copilot, or make small modifications ourselves. Copilot is powerful, but you're still the key decision-maker.6. 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.