Carlos asks how to have the smallest number have the largest data bar in Microsoft Excel.
Microsoft Excel Conditional Formatting offers three data visualization tools. Two of them are easily reversible. But Data Bars don't have an easy way to reverse.
Nancy Faust takes us into the "Danger Zone" as I try using a devious number format to mask negative numbers as positive numbers.
Table of Contents
(0:00) Small Number Long Data Bar
(0:14) Reversing Color Bar in Excel
(0:24) Reversing Icon Sets in Excel
(0:42) Data Bar outside cell
(1:08) Negative Values Data Bar setting
(1:24) Show Bar Only in Data Bars
(1:34) Danger Zone: Devious Number Format
(2:14) Showing Negative numbers as positive in Excel
(2:45) Wrap-up
Microsoft Excel Conditional Formatting offers three data visualization tools. Two of them are easily reversible. But Data Bars don't have an easy way to reverse.
Nancy Faust takes us into the "Danger Zone" as I try using a devious number format to mask negative numbers as positive numbers.
Table of Contents
(0:00) Small Number Long Data Bar
(0:14) Reversing Color Bar in Excel
(0:24) Reversing Icon Sets in Excel
(0:42) Data Bar outside cell
(1:08) Negative Values Data Bar setting
(1:24) Show Bar Only in Data Bars
(1:34) Danger Zone: Devious Number Format
(2:14) Showing Negative numbers as positive in Excel
(2:45) Wrap-up
Transcript of the video:
Carlos makes me dive into the danger zone for this one.
Smallest number, longest bar.
So Carlos wants me to do a data bar where the smallest number is the longest.
And look, we have these three conditional formatting tools, and two of them have very easy ways to reverse.
The color scales, they actually offer the reverse right there.
So small numbers in red, large numbers in red.
For the icon sets, you have to go into Manage Rules, edit the rule, and right here, Reverse Icon Order will let you reverse that, okay?
But for Data Bars, yeah, there's nothing really built in for this.
Okay, so Carlos actually sent me a picture where the bar was outside the cell.
So this first one's going to work for him.
I'm going to come out here to column H, and I'm going to make those numbers be negative. And copy it down.
Conditional formatting, Data Bars, and choose a color here.
Now, that's not how we want this to look.
But we're going to go into Conditional Formatting, Manage Rules, Edit the Rule.
And down here, there's something called Negative Values and Axis.
And we're going to say that we want the axis settings to show negative bars in the same direction as positive.
And of course, they're changing the color. Ask for green bars, I'm getting red.
Click okay, and let's Show Bar Only. Click okay.
So now we have the smallest number with the longest bar.
All right, how did I do this here?
Yeah, I hate to even bring this one up because clearly we are going into the DANGER ZONE. I'm going to take those positive numbers.
I'm going to multiply them, Alt+E, S, M to multiply by the -1 that's on the clipboard.
Here, we'll do the Conditional Formatting just like I did before, the Data Bars.
And then we'll go in and Manage Rules, Edit the Rule, Negative Value and Axis set to none.
Click okay this time, I'll show the numbers. Click Okay, click Okay.
And then Ctrl+1 to format those cells.
And a custom number format that says, for positive numbers, we want no negative sign.
And for negative numbers, we want no negative sign. So zero, semicolon, zero.
Boy, this is really dangerous because someone in the future is going to think that those numbers should add up to something like 65, but in fact, it's negative 65.
So if you ever do any kind of math here, I can see where it would be a bad error, but it gives us the look that Carlos was looking for. All right, there you go.
A couple of ways to reverse the Data Bars basically by converting them to negative.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Smallest number, longest bar.
So Carlos wants me to do a data bar where the smallest number is the longest.
And look, we have these three conditional formatting tools, and two of them have very easy ways to reverse.
The color scales, they actually offer the reverse right there.
So small numbers in red, large numbers in red.
For the icon sets, you have to go into Manage Rules, edit the rule, and right here, Reverse Icon Order will let you reverse that, okay?
But for Data Bars, yeah, there's nothing really built in for this.
Okay, so Carlos actually sent me a picture where the bar was outside the cell.
So this first one's going to work for him.
I'm going to come out here to column H, and I'm going to make those numbers be negative. And copy it down.
Conditional formatting, Data Bars, and choose a color here.
Now, that's not how we want this to look.
But we're going to go into Conditional Formatting, Manage Rules, Edit the Rule.
And down here, there's something called Negative Values and Axis.
And we're going to say that we want the axis settings to show negative bars in the same direction as positive.
And of course, they're changing the color. Ask for green bars, I'm getting red.
Click okay, and let's Show Bar Only. Click okay.
So now we have the smallest number with the longest bar.
All right, how did I do this here?
Yeah, I hate to even bring this one up because clearly we are going into the DANGER ZONE. I'm going to take those positive numbers.
I'm going to multiply them, Alt+E, S, M to multiply by the -1 that's on the clipboard.
Here, we'll do the Conditional Formatting just like I did before, the Data Bars.
And then we'll go in and Manage Rules, Edit the Rule, Negative Value and Axis set to none.
Click okay this time, I'll show the numbers. Click Okay, click Okay.
And then Ctrl+1 to format those cells.
And a custom number format that says, for positive numbers, we want no negative sign.
And for negative numbers, we want no negative sign. So zero, semicolon, zero.
Boy, this is really dangerous because someone in the future is going to think that those numbers should add up to something like 65, but in fact, it's negative 65.
So if you ever do any kind of math here, I can see where it would be a bad error, but it gives us the look that Carlos was looking for. All right, there you go.
A couple of ways to reverse the Data Bars basically by converting them to negative.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
