Join Dennis Taylor for an in-depth discussion in this video Use conditional formatting data bars to accentuate negative data, part of Excel Tips Weekly.
- [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.
Author
Updated
2/23/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 24m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Use conditional formatting data bars to accentuate negative data