Hi, this is Wayne again with a topic “Excel Conditional Formatting Advanced Technique”.
In this short excel tutorial, I want to show you an advanced technique of how to do conditional, formatting and if you haven’t already watched my Excel conditional, formatting in-depth tutorial. You should probably watch that in addition to this one, in that in-depth tutorial, I showed many of the options that we have when we’re using conditional formatting. I went through all of these, but there’s one advanced technique that I’d like to share in this short tutorial, and that is instead of applying conditional formatting just to one column like I’ve done in these cases right, this column is formatted based on the higher the number. The more green the cell is, the cells in this column are formatted in a different way. The higher the number the bigger this bar is, and then I have these stoplight symbols here in column, L, but instead of just dealing with one column.
What if you want an entire row to be highlighted based upon a certain number, so, for example, in this spreadsheet, I would like to highlight the rows of the products etc. That sold the most units, and so it deals with column E. But because I want to highlight the entire row, I’m gon na have to select all of my data in this spreadsheet. So I’m going to click here in the upper left on column a and I’m holding the click, and then I’m dragging across to highlight all of the data there.
We go a through Q and now I’m gon na browse back over here to the left. My data is still selected, but I just used this bar to move back to the left side of my spreadsheet. Now I’m gon na go into conditional formatting and I’m looking for an option that highlights not cells but rows and the places that you’re gon na have to go for. This is actually down here and it’s in new rule.
So I click there on new rule and there’s a bunch of Advanced Options here that you can choose from, but I just want to create a formula to determine which cells to format. Now the question is: what is my formula format values where this formula is true, so I’m going to click in this box to type a formula – and this is going to be an absolute for me and this is kind of confusing. But that’s all you need to know is that it’s an absolute formula, therefore you’re gon na have to use a dollar sign. So I just typed in that box and equals sign so equals, and then here comes dollar sign to make it an absolute formula.
And then I need to select a column, and this is a little bit tricky which column do I pick? Well, it’s the one that I’m gon na base the formatting on and that’s column e. Remember. I want to base it on which products are sold the most. So I type in E now what about the row? So E is the column the row, and this is kind of tricky.
I don’t know what I should pick here. Would it be e that certainly could work, but there’s a downside to doing it that way. Instead, I’m gon na go with whatever is here in the upper left. This is the active cell right now, a 1 is the active cell, and so that one is what I’m gon na put here in my formula so e1, if e1 is greater than so.
I type in the greater than symbol 1000. Then I want some formatting to happen and just as an example, I’ll click here on format and I’ll pick a border style. How about an outline? I can also click a fill color, maybe dark green there’s, also more colors. I could pick here and there are some fill effects and you can see what some of those different options are. I’M just gon na leave it, as is click, OK and then, if I click OK here, take a look at what happens. I clicked OK and Excel. Has analyzed the entire column E and it’s found the values that are higher than a thousand and if they are higher than a thousand, it’s highlighted the entire column. So the nice thing about this, it just makes it easier to hone in on all of the data that meets certain criteria.
It can be easy, though, to forget which column it’s based on. If you do forget, you can go here to conditional formatting, manage rules and look, there’s the rule and I can click on it. I can delete the rule. If I don’t want it anymore, I can edit the rule, and there is something I would like to edit about.
It – notice that right now it’s set for values that are greater than a thousand, and so what about the number 1000 itself? Of course, that would not be included, so I should put greater than or equal to a thousand or just as good and maybe better because it’s more concise would be to say greater than 999, so that would include a thousand and up, and then I can just Click OK and apply, and then okay and that’s now changed. It was a very subtle change and it probably didn’t really affect my conditional formatting in a real way, but that might come up. So that is something to watch for thanks for watching this short tutorial on this advanced conditional, formatting technique. I hope that you found this tutorial to be helpful.
If you did, please click the like button below and consider connecting with me on my social media web sites like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe, please click The bell, next to the subscribe button that way you’ll be notified whenever I post another video and watch for another video from me at least every Monday. If you’d like to support my youtube channel, consider becoming a patron of mine through my patreon account and you’ll see links to that in the description below .