Simply put, there are two ways to turn Conditional formatting On and Off. The manual way will take you to the Home/Conditional Formatting/Manage Rules… window where you can delete the rules you want. But then there is an elegant way of doing this, that makes you look like an Excel Guru and that’s the one we will be learning here. We will achieve this with the use of Conditional Formatting. It’s funny and slightly “cannibalistic” since we will use Conditional Formatting to turn Conditional Formatting on and off, but it’s a very efficient and elegant way of doing this!
So here it is, the whole article will get you there in three stages. If you are already familiar with some just jump ahead to the part that interests you. The three parts will be:
Here the original rule is the one we wish to turn on and off. In our case, let’s take a look at the following example in Excel. Note the Data Validation dropdown in cell B1. It can quite as easily be a simple cell where you manually put in “Yes” and “No”. Now on to our rule no. 1.
Our goal is simple, to highlight the top 10 values. So we select the cells and go to Home/Conditional Formatting/Top Bottom Rules/Top 10 Items
In the dialog box that follows, you can change the number of Top items and the format of the cell, but I will just go with the defaults.
So now we have our first rule. It must be said, you could do as many rules as you like, and you will still be able to turn them all on or off with one click.
Now here we will do a rule based on the value of the Data Validation Dropdown in cell B1 as visible on the first picture in this post. We will now create a conditional formatting rule based on a formula. We will do this like this. !!First we select the same range of cells as we did for the first rule!!. Then we select Home/Conditional Formatting/New Rule…
…and in the dialog box we will write
=IF($B$1=”Yes”,False,True)
In the “Format values where this formula is true:” input box and set no specific format at the bottom. It’s important that we set no specific format, since this rule will only work as a switch.
Now this has created the second rule for us, but at this point it has no effect on the previous rules. To achieve this, we must go to Home/Conditional Formatting/Manage Rules… In the dialog box we get, there are two sections demanding our attention. You can see them outlined and numbered in the following picture.
Our first goal is for the Formula rule, to be the first of the bunch… You can achieve this by selecting it and clicking the up-arrow button in section marked with 1. As for the second goal, this will be the magic one. You check the Stop If True Checkbox. And this checkbox says, if the first rule gives true (in other words, if we select “No” from the dropdown in B2), then no other rule will be checked and applied. This also explains, that there can be many more rules bellow, and they all would not be applied in quite the same manner. This truly is a big step to eternal happiness!
Check out our YouTube channel and subscribe for more amazing Excel tricks!
Follow us on LinkedIn.
Check out our brand new R Academy!
Highlighting Weekends can be hard in Excel. So just for fun, ...
Ever wondered if your could use conditional colouring on Excel ...
This is a follow up post on the final result of last week’s ...
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Simple and elegant — very cool!
Reblogged this on Argee's Office Help.
hi
conditional formatting button disabled please help me?
I’m guessing the file format is incorrect, or the file is read only or Excel is in compatibility mode… Try to save the file as “Any name”.xlsx (so to the new excel format) and it should work.
Thanks a lot.
Thought, the formula wouldn’t it be =$B$2=”No”
Thanks a lot for the good trick.
Yes, you’re right. The reason I went for the “Hot-Dog” formula, was to emphasize that the formula must return a TRUE or FALSE. The condition itself would suffice.
I appreciate the comment.
Excellent, thanks for the knowledge
I like tricky, gadgety do-dads – and this fits the bill!
Wow, fantastic blog layout! How long have you been blogging for?
you make blogging look easy. The overall look of your web site is fantastic, let
alone the content!
Hi,
this is actually a WordPress Chunk Theme which I customized a bit… This blog was “born” on May 13th 2014, so it’s in it’s infancy 🙂 Just wanted to share this Excel Tricks and knowledge with those that are interested in Excel, so I started this blog. I am glad you like it 🙂
Howdy, I do think your blog could possibly be having internet browser compatibility problems.
When I take a look at your site in Safari, it looks fine however when opening in Internet Explorer, it
has some overlapping issues. I merely wanted to give you a quick heads up!
Aside from that, wonderful blog!
Thanks, I will check on that. Which version of IE?
After exploring a handful of the blog posts on your site, I honestly like your technique of blogging.
I book-marked it to my bookmark website list and will be checking back
in the near future. Please check out my website too and tell me wwhat you think.
I’m not that much of a online reader to be honest but your sites really nice, keep it up!
I’ll go ahead and bookmark your website to come back in the future.
Many thanks
Wow, that’s what I was exploring for, what a data! present here at this web site, thanks admin of this web page.
Is it possible to conditionally format maximums in EACH individual row all at ONCE? How?
Thanks, regards.
Sure thing! This is the formula
=A1=MAX($A1:$E1)
and here is a sample file
https://excelunplugged.com/wp-content/uploads/2017/05/Maximums.xlsx
Cheers