Use conditional formatting data bars to accentuate negative data

- [Instructor] In this worksheet we see some months in column A and profits in column B and we can certainly read the numbers, we know what they mean. We see some positives and some negatives there. Let's make these numbers stand out a bit more prominently by way of Conditional Formatting. Now, couple of options here as I select column B. This list will grow by the way, so we're selecting the entire column for the formatting that we want to apply. Then on the Home tab, Conditional Formatting, certainly one approach is to use color scales. So the one here that I'm pointing to, you can see immediately the higher values are dark green, the lowest values are bright red, and everything in between is a continuum of colors as we change between those two.

And nothing wrong with that and that could be okay. Icon sets bring out the data perhaps a bit more prominently. If we were to use arrows here that might help the numbers pop a little bit more readily as well. Notice here by the way that you might think at a glance well the negatives are all red down arrows. Well that's generally true, but as we look in row 14, if we were to make that choice and I will make it, in row 14 we see what's happening. So we can't always see here the negatives as prominently as we might want to.

So I'm going to undo that setting by pressing Ctrl + Z and with the column still selected, let's go back to Conditional Formatting and consider using Data Bars. Now gradient fill options I think you can see immediately what's happening here. I think this is a much better visual for the negatives to pop out. Notice how automatically, regardless of which color option you're making here, the negatives are red. So you probably wouldn't want to use the red data bar although there's nothing wrong with that. It's just a question of choice. I'm going to use solid fill here and perhaps this one here because we can see the numbers through the text.

But there are some adjustments we could consider making here with this option. The data's still selected. Let's go back to Conditional Formatting and manage the rule. There's only one rule here. And when you see this dialog box, you really don't see the rule at first. You can double-click here in the section called Data Bar or simply click Edit Rule. Double-click, this takes us into the dialog box. And notice there's some settings here for negative value in axis. One option, apply the same fill color as positive bar.

I don't think most people would want to do that. We want those negatives to stand out with a different color. You can move the vertical line that separates the positives and negatives by using Cell midpoint. We don't see that option just yet, so I'll click OK. And notice that the vertical bar as it separates the numbers is now as I finally click all these OKs, it's in the middle. That causes the bars to overlap the numbers more on the right side. That might not be the best choice. But that certainly is an option sometimes.

I'm going to undo that because I think maybe it's not the best option here for most people. Another concern about using Data Bars is the way these overlap the numbers on the right. And you can adjust that as well too. Going back to Conditional Formatting, Manage the Rule, double-click here again. This time we can control the value here under maximum. The type is automatic, applying this to a specific number. And you might want to experiment with this. I'm going to use 50 here and that's higher than the numbers that we're seeing on the screen there.

Higher than all of them. Let's click OK and watch the difference in the display here. And now you can see how the bars are not overlapping the numbers. So for an option that brings out the negative data, I think in a prominent way, this choice here under Conditional Formatting, again using Data Bars, remember automatically the colors appear as red but, and consider some of the choices you can make by managing those rules. And one other choice here too. I don't think it's a great one necessarily. But another rule that we could change here is the fact that we could have a border on each of the bars here.

And we're not seeing for the moment the preview for the negative value but it's going to be the same as well too. And that's another option to make these numbers pop out. So once again, Conditional Formatting gives us some tools to make certain data become a bit more prominent on our screens. It stands out, it makes the negatives more obvious than they would have been without this feature.