Tip:
Highlight text to annotate it
X
Hi and welcome back to our Microsoft Excel Tutorials.
In our last video we learned how to use the filter command and in this video we’re taking it another step and learn how combine the filter tool with the sorting tool for conditional formatting.
So first off we want to learn what is conditional formatting?
In the Home tab you will see a button for Conditional Formatting.
I’m going to apply conditional formatting to the salary column as an example.
I want to select all the values in the salary column, and I’m using a tool I built into this worksheet called a Name Range box.
I’m clicking the name range box right here on the left and I’m selecting the salary field.
When I click that you will see all of my values in the salary field have become selected.
If your curious about the box I set up for this worksheet, make sure to look at my video on “Naming Ranges.”
All of my salary values are selected and I’m going to go to the conditional formatting option and there are a whole lot of options to choose from,
but I’m going to click on Highlighted Cell’s and you will see some more options to choose from.
At the top of those options are Greater Than and Less Than options.
What I want to do is highlight anyone who has a salary of less than thirty thousand in red.
Anyone who has a salary over seventy thousand I want to highlight in green.
I've lost my selection and I'm going to click on the drop down and select Salary. This box is really useful for large ranges or ranges that are hard to remember.
Again you can find out more about that on our videos on 'Named Ranges"
I’m going to reselect my salary, click on conditional formatting, go to highlighted cells and click on greater than.
I’m going to type in seventy thousand because I want it so the value in the cell in this range is greater than seventy thousand.
Then I go over to the drop down box and I want to fill it in with a green fill with a dark green text.
I select the fill I want for my greater than range, click on “OK” and when I scroll down you will see it has selectively highlighted the fields that meet our criteria.
The nice thing about conditional formatting instead of doing this manually is if you change a value, the conditional formatting will automatically be applied.
I applied the rule to all of the values in the salary column.
If I change this field from thirty five thousand to ninety thousand, the ninety thousand meets our criteria and when I click enter you will see the formatting is applied to that field.
Now I want to create the second rule for people who make under thirty thousand.
I go back to the Name range box, click on salary.
My salary values are highlighted once again, I go back to Conditional formatting, select Highlighted cells and I’m going to click on the Less Than option.
I’m typing in thirty thousand because I want this rule to be applied to numbers less than thirty thousand and I want this rule to be filled with a light red fill and a dark red text.
There a number of different options to chose from here, and there is also custom format you can choose from to custom build your own color combos, but right now I’m going to make it simple.
I click “OK” and now you will see it has highlighted everyone who meets those criteria.
Again you will see if I change a value from thirty one thousand to twenty thousand the conditional formatting is automatically applied.
Now I’m going to teach you how to tie in a simple conditional formatting tool with the sorting and filtering tools.
Click on your header row, like you have for the sorting and filtering tools and go to the Data Tab at the top.
Click on filter and you will see the drop down arrows appear on your header row.
I click on the drop down menu for salary and you will see a lot of options such as sort by color as well as filter by color.
You will see the values that I’ve placed in for conditional formatting has already been placed in here.
There are the two background colors and the two foreground colors.
Let’s say I want to see people who meet the first rule I created, people who make more than seventy thousand.
I click on either one of the green colors and now I’m only seeing people who meet the criteria of our first rule.
If I go back up to the salary drop down menu and click on one of the red colors you will see the list changes to only people who meet the criteria of the second rule, people who make less than thirty thousand a year.
You can also change the conditional formatting rule.
Select the name range you applied the value to, go back to the home tab, select conditional formatting and click on “Manage Rules.”
A box will appear showing you both of the rules I applied to the salary selection.
If you click the drop down box next to Current Selection at the top, and change it to The Worksheet, it will show you all of the rules you have applied to the entire worksheet.
In this box you can see our two rules, which can be clicked on to select them.
I’m going to click on our first rule, less than thirty thousand, and click on “Edit Rule,” and I’m going to change the cell value to twenty-three thousand.
You can also update the formatting of the cell.
If you click on format, you will see all of the different options you have to change the format of that rule. You can even apply borders and fill effects.
I’m going to make a slight change to our fill for rule number one, and press “OK” and “OK” again and you will see the changes we made have been applied to the items in our column.
Since I applied the filter before I changed the formatting rule, the filter is showing me things I don’t want.
Go to the Data tab and click on Reapply next to the filter button and you will see the rules reapply.
Now you will see there is no information on the list.
The reason all of the information disappeared is I changed the background color from the light red to a light orange and the filter I set up for the rule was specifically for the light red color.
Now since the color is changed the filter isn’t finding any information. I reselect the color and you will see the entries meet the criteria.
This information can be applied to the sorting tools.
I clicked on the clear button so my list goes back to normal
I click the drop down for salary, and I can click on sort by color.
I sorted it by the green, and the green items have been sorted up towards the top.
So that’s a little bit about how to work with sorts and filters and the custom formatting tool.
Remember your sort and filter tools rae located under Data and the sort and filter area. You're Conditional Formatting is all in the Home tab, in the Conditional Formatting button.