Now let's take a look at how we can use icons and data bars with conditional formatting.
This is going to help us bring attention to specific areas of our dataset.
So let's start with icons.
Let's bring attention to people that earn more than 100,000
and those that earn below 30,000.
I'm going to highlight this area, go to conditional formatting. This time
Let's see the options we have under icon sets.
When I go over these with my mouse I can already see a preview in my cells.
Now Excel it's using its own logic to decide which icon to give this data.
Let's get to more rules and see what it has.
Here is checking if the number is in the top 67%.
It's going to get a green icon.
So it takes a look at each number in relation to what you selected.
Now you can change these percentages but in our example we're interested in looking at the full numbers.
We want people greater than 100,000 to have this green icon.
So we have to change this from percent to a number.
Now we can input our value here so that was a 100,000.
Now check this out.
If I put in 30,000 and then change this percent to number, it resets it.
Right.
So just change the type first and then go and input your value.
So that's 30,000.
Let's click away and that's going to be our new allocation.
The yellow one is going to be for people in between.
If I don't want to bring attention to the people in between and in this example I don't, I can click
on this down arrow and select no cell icon. And then click on OK.
And those are my icons.
This way I can quickly see I have two people earning less than 30,000.
If I change this one to 40,000.
My icon is gone
and if they go above 100,000 my icon comes back.
What if you just wanted to have the icons and not the numbers?
There is a setting for this, so let's go back to conditional formatting. This time I want to update
my rules.
I'm going to go to manage rules.
Click on edit rule and put a check mark beside show icon only. When I click on OK and apply.
See what happens. The numbers disappear, the icon stay.
Now in this case I do want to see my numbers.
But what if I wanted to have my numbers in their own separate cells and the icons in their own separate
cells?
Let's go back and show the numbers.
I'm going to click on OK and OK.
Now the way you can do this is to insert another column in between.
This is where the icons are going to sit.
Now to make sure that the icon is always looking at the same number, I'm gonna copy this, right mouse click,
go to paste special, and paste link.
These values are linked to this one.
The one I don't want to have the conditional formatting on, are these ones. I'm going to click on
conditional formatting and clear the rules from the selected cells. For these ones,
we can go back now
and, where was that setting?
Show icon only, click on OK, and OK. Now I have a dynamic report where my icons sit in their own separate
cells.
OK so let's just double check this one.
Let's change this value to 20,000 press enter and both are dynamic. Now let's take a look at using data bars.
To show the difference in salary from previous year to this year
we want to represent that using data bars. Data bars are similar to a bar chart inside cells.
All we have to do, highlight our range, go to conditional formatting.
This time let's go to Data bars.
When I hover over these, we can already see how they're going to look in the cell. The size of the
data bars are automatically adjusted. By default,
Excel takes a look at this data set, decides what is the biggest number, what's the smallest number
and it adjusts the size of these bars.
Let's go to more rules and see the other options we have.
We can select the color.
This is the color of your positive bars.
Let's go with a green color.
You can select the border if you want and you can also decide how negative values should be filled.
If you're okay with the default red you can go with that or you can change it here.
You can also decide on some Axis settings.
So the default is automatic.
It's not always in the middle.
It depends on your data set.
But if you're really want it in the middle of the cell, you can select cell midpoint. And you can also
decide on the color of your axis.
I'm going to go with white so that I don't see that axis going through here and then click on OK.
And let's just click on OK and we see our numbers and our bars in there.
Now with a quick look we can see this person seem to get a huge salary increase but that's not really
the case right.
Maybe they weren't there last year.
They have no previous year's salary. So let's actually update this formula.
If previous year's salary is zero we shouldn't see anything in the cell.
Otherwise it should do the calculation.
If previous your salary equals zero then let's just do nothing.
Otherwise it should run our calculation, close bracket,
press enter.
Now let's send this down and our data bars updates automatically.
So here this person really did receive a big salary increase.
Now again you have the option not to show the number or do the same trick with it before. Show the number
in their own cells and show the bars in own cells.
This time I just want to show the bars and no numbers here.
So I'm gonna go to manage rules.
Edit rule, put a checkmark besides show bar only. And click on OK and
OK. That's my report.
It's super easy to set up.
All you have to do is select and apply conditional formatting to this.