Conditional Formatting is a simple way that you can format your data based on conditions. This opens up a wide range of beneficial use cases, for example:<\/p>\n\n\n\n
- \n
- Find areas to focus attention<\/strong> \u2013 Highlight in red any Sales data that falls below a target<\/li>\n\n\n\n
- Project or task tracking<\/strong> \u2013 Highlight in green any project or task that is complete<\/li>\n\n\n\n
- Find data anomalies or issues <\/strong>\u2013 Highlight in red any blank or 0 cells where there shouldn\u2019t be<\/li>\n<\/ul>\n\n\n\n
- \n
- <\/li>\n<\/ul>\n\n\n\n
Example – Project Task Tracking<\/span><\/h3>\n\n\n\n
Say we have a basic project tracking worksheet, like this:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture1.png\")
<\/figure><\/div>\n\n\nDownload the file here:<\/p>\n\n\n\n
Conditional-Formatting-post-sample-data<\/a>Download<\/a><\/div>\n\n\n\n<\/p>\n\n\n\n
Say we\u2019re the project manager for all of these projects, and we want to do the following:<\/p>\n\n\n\n
- \n
- Highlight in green the whole row for any project that is 100% complete<\/li>\n\n\n\n
- Highlight in red any project that is past due <\/em>but not<\/u><\/strong> 100% complete<\/li>\n<\/ol>\n\n\n\n
But first, let\u2019s cover the basics:<\/p>\n\n\n\n
Creating a Conditional Formatting Rule<\/u><\/strong><\/h3>\n\n\n\n
As a prerequisite, let\u2019s change our values in the \u201c% Complete\u201d to \u201cNumber\u201d formatting. To do this, highlight the range F2:F9<\/em>, click the formatting drop-down on the Home tab and select \u201cNumber\u201d:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture2.png\")
<\/figure><\/div>\n\n\nThis is great, but it will add two decimals to our percentages that we don\u2019t want. To remove these, leave the range highlighted and click on \u201cDecrease Decimal\u201d twice:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture3.png\")
<\/figure><\/div>\n\n\nSo now our data will look like before, but this column is all in \u201cNumber\u201d format now. This is needed to make sure the conditional formatting rules work right.<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture4.png\")
<\/figure><\/div>\n\n\nOk now let\u2019s just create a basic Conditional Formatting rule:<\/p>\n\n\n\n
Highlight your data (or Ctrl + A) and click on \u201cConditional Formatting\u201d -> \u201cHighlight Cells Rules\u201d -> \u201cEqual To\u2026\u201d:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture5.png\")
<\/figure><\/div>\n\n\nA dialog box will appear:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture6.png\")
<\/figure><\/div>\n\n\nEnter 100<\/em> <\/strong>in the box and select \u201cGreen Fill with Dark Green Text\u201d from the drop-down. Click \u201cOK\u201d:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture7.png\")
<\/figure><\/div>\n\n\nHere\u2019s what we get:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture8.png\")
<\/figure><\/div>\n\n\nPretty cool, right?<\/p>\n\n\n\n
This works fine for many use cases, but say we want to highlight the whole row<\/em>, not just the one cell where the value appears. This will make the row stand out much more.<\/p>\n\n\n\n
Clearing Conditional Formatting Rules<\/u><\/strong><\/h3>\n\n\n\n
To start over, let\u2019s go ahead and clear the formatting rule we created.<\/p>\n\n\n\n
Go to \u201cConditional Formatting\u201d -> \u201cClear Rules\u201d -> \u201cClear Rules from Entire Sheet\u201d:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture9.png\")
<\/figure><\/div>\n\n\nThis will remove the conditional formatting rule we created, so we\u2019re starting over.<\/p>\n\n\n\n
Highlighting the whole row based on a cell value<\/u><\/strong><\/h3>\n\n\n\n
Creating a New Rule<\/h4>\n\n\n\n
Now, let\u2019s do this again. This time, we\u2019re going to highlight the data and go to \u201cConditional Formatting\u201d -> \u201cNew Rule\u2026\u201d. This will allow us to create a more custom rule, rather than the simple default one we did above.<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture10.png\")
<\/figure><\/div>\n\n\nIn the dialog box that opens, select \u201cUse a formula to determine which cells to format\u201d:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture11.png\")
<\/figure><\/div>\n\n\nIn the formula bar, enter the following:<\/p>\n\n\n\n
=$F1=100<\/em><\/p>\n\n\n\n
Another way to do this step-by-step is:<\/p>\n\n\n\n
- \n
- Enter \u201c=\u201d in the formula bar<\/li>\n<\/ol>\n\n\n\n
![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture12.png\")
<\/figure><\/div>\n\n\n2. Click on the F2 cell<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture13.png\")
<\/figure><\/div>\n\n\n3. Hit F4<\/em> on your keyboard twice. This adds the $<\/em> before the F, which “locks” the column (more explanation below).<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture14.png\")
<\/figure><\/div>\n\n\n4. Enter \u201c=100\u201d<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture15.png\")
<\/figure><\/div>\n\n\nHow the Formula Works<\/h4>\n\n\n\n
This formula compares each cell in the F column to the value 100, but since the formatting rule applies to the entire range A2-F9, it will highlight the entire row, not just the F column.<\/p>\n\n\n\n
The secret here is to use the $ next to F: this \u201clocks\u201d the column to F for the purpose of doing the comparison. So, if the value 100 appears in any other column, it will not be highlighted because I\u2019m only comparing column F.<\/p>\n\n\n\n
That\u2019s it for the formula.<\/p>\n\n\n\n
Finishing Up<\/h4>\n\n\n\n
Now, let\u2019s set the formatting: click on \u201cFormat\u2026\u201d<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture16.png\")
<\/figure><\/div>\n\n\nNavigate to the \u201cFill\u201d tab and select your favorite shade of green (or any color you like), and click \u201cOK\u201d.<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Picture17.png\")
<\/figure><\/div>\n\n\nPretty cool!<\/p>\n\n\n\n
Highlighting the whole row based on two conditions<\/em><\/u><\/strong><\/h3>\n\n\n\n
Now that we\u2019ve learned how to highlight the whole row based on a condition, let\u2019s try the second use case we mentioned in the beginning:<\/p>\n\n\n\n
- \n
Highlight in green the whole row for any project that is 100% complete<\/s><\/li>\n\n\n\n- Highlight in red any project that is past due <\/em>but not<\/u><\/strong> 100% complete<\/li>\n<\/ol>\n\n\n\n
Breaking this down, we see that this has two <\/em>conditions that we need to apply, whereas our first use case only had one.<\/p>\n\n\n\n
There\u2019s several ways to tackle this, but I\u2019m going to use the AND() function.<\/p>\n\n\n\n
Setting up the <\/strong>rule<\/h4>\n\n\n\n
First, we follow the same steps as before:<\/p>\n\n\n\n
- \n
- Highlight the data and go to \u201cConditional Formatting\u201d -> \u201cNew Rule\u2026\u201d.<\/li>\n<\/ol>\n\n\n\n
![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/11\/Picture18.png\")
<\/figure><\/div>\n\n\n2. In the dialog box that opens, select \u201cUse a formula to determine which cells to format\u201d:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/11\/Picture19.png\")
<\/figure><\/div>\n\n\nHere is where we enter the formula:<\/p>\n\n\n\n
=AND($F2<100,$E2<TODAY())<\/em><\/p>\n\n\n\n
Click on \u201cFormat\u2026\u201d<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/11\/image.png\")
<\/figure><\/div>\n\n\nNavigate to the \u201cFill\u201d tab and now let\u2019s pick a shade of red (since we\u2019re highlighting projects that are past due<\/em>), and click \u201cOK\u201d.<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/11\/image-1.png\")
<\/figure><\/div>\n\n\nAs you can see, the first row is a project that is past due (was due on 10\/1\/23 but today is 11\/4\/23) and is NOT 100% complete. Nice!<\/p>\n\n\n\n
Understanding the Formula<\/h4>\n\n\n\n
Let\u2019s break down the formula to make sure we understand:<\/p>\n\n\n\n
=AND($F2<100,$E2<TODAY())<\/em><\/p>\n\n\n\n
The AND() function takes two or more arguments (inputs), evaluates each one to TRUE or FALSE, and it will return TRUE only if both <\/em>conditions are true.<\/p>\n\n\n\n
So in this case, the full row will only be highlighted if BOTH the below conditions are true:<\/p>\n\n\n\n
- \n
- The numeric value in column F is less than 100 (this is the $F2<100<\/em> portion)<\/li>\n\n\n\n
- The date value in column E is before today\u2019s date (this is the $E2<TODAY()<\/em> portion)<\/li>\n<\/ol>\n\n\n\n
Together, these two conditions give us what we want \u2013 only highlight the row in red when BOTH the project is less than 100% complete AND the due date has already past.<\/p>\n\n\n\n
Bonus: Filter by Color<\/u><\/strong><\/h3>\n\n\n\n
The conditional color highlighting we just learned is great, but what if we have a lot of data that we don\u2019t want to scroll through to find every instance of green or red?<\/p>\n\n\n\n
Here\u2019s where filtering by color<\/em> comes in.<\/p>\n\n\n\n
To filter by color,<\/p>\n\n\n\n
- \n
- Click in any column of the first (\u201cheader\u201d) row of the data<\/li>\n<\/ol>\n\n\n\n
![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/11\/image-2.png\")
<\/figure><\/div>\n\n\n2. Go to the \u201cData\u201d tab in the toolbar and click on \u201cFilter\u201d<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/11\/image-3.png\")
<\/figure><\/div>\n\n\n3. In any column (since the whole row is highlighted), click on the drop-down that appeared, hover over \u201cFilter by color\u201d and select which color you\u2019d like to filter by.<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/11\/image-4.png\")
<\/figure><\/div>\n\n\nNow you\u2019ll only see rows that are the color you selected, for example red:<\/p>\n\n\n
\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/11\/image-5.png\")
<\/figure><\/div>\n\n\nThat\u2019s it for today, let me know what you think in the comments below!<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey everyone! Today I\u2019m going to talk about a simple but very powerful tool in Excel: Conditional Formatting. What is Conditional Formatting? Conditional Formatting is a simple way that you can format your data based on conditions. This opens up a wide range of beneficial use cases, for example: Example – Project Task Tracking Say… Read More »Unlock the Power of Excel Conditional Formatting<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_themeisle_gutenberg_block_has_review":false,"_ti_tpc_template_sync":false,"_ti_tpc_template_id":"","footnotes":""},"categories":[1],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/posts\/261"}],"collection":[{"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/comments?post=261"}],"version-history":[{"count":3,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/posts\/261\/revisions"}],"predecessor-version":[{"id":294,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/posts\/261\/revisions\/294"}],"wp:attachment":[{"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/media?parent=261"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/categories?post=261"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/tags?post=261"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- The date value in column E is before today\u2019s date (this is the $E2<TODAY()<\/em> portion)<\/li>\n<\/ol>\n\n\n\n
- The numeric value in column F is less than 100 (this is the $F2<100<\/em> portion)<\/li>\n\n\n\n
- Highlight in red any project that is past due <\/em>but not<\/u><\/strong> 100% complete<\/li>\n<\/ol>\n\n\n\n
- Enter \u201c=\u201d in the formula bar<\/li>\n<\/ol>\n\n\n
- Project or task tracking<\/strong> \u2013 Highlight in green any project or task that is complete<\/li>\n\n\n\n