Johann from Mexico City sends in a cool tip for creating charts that seem like they use conditional formatting to color the columns. Episode 800 will show you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, here we are. It's episode number 800. I have a tip today sent in by Johann.
Johann’s from Mexico City and he was watching podcast 776 where I showed how to create this chart where we could show the UNDERs, the AVERAGEs, and the OVERs, basically creating something sort of like conditional formatting, and Johann says, well, wait, there's a much better way to do this.
Let's do conditional formatting on every single month.
So, here we have our results for each of the months, and basically, you know, if we are over a 100%, we're in green, somewhere in between is yellow, and then the things under 50% are in red.
Well, that's a cool chart.
Let's take a look at how we create that.
First of all, you start with your data up here that has all of the, you know, scores for each month, and then we're going to build, in a hidden section of the worksheet, some formulas, and, you know, the first formula says, hey, if we’re > 1, if we are > a 100%, then bring that value down, otherwise bring a 0, and that's for the GREEN series, and then the RED series says, you know, if that value up in B2 is < 50%, bring the value down, otherwise a 0, and then the one in the middle, the YELLOW, just basically says, hey, we're going to take whatever is in B2 – whatever is in the RED, whatever's in the GREEN, that's going to give us everything in between.
So, when I copy this formula across, what we're going to end up with is basically 3 series now and it's going to indicate whether the data should be RED, YELLOW, or GREEN.
Now, I'm going to create the chart as a regular column chart and, initially, you know, well, first of all, the colors are all wrong.
So, I'm going to click here on this series.
I see that that series is YELLOW so I can go to my paint bucket icon and change that to YELLOW.
We'll go to the next series.
I'll choose series GREEN, and, again, the paint bucket to change that to GREEN, and then finally, series RED, and the paint bucket to change that to RED, but this is not the best result at all because we have, you know, the charts are kind of staggered.
So, I'm going to go in and format this series, and go back to the OPTIONS tab.
The OPTIONS tab, you know, is different depending on what your chart type is.
First thing I want to do is I want to set the overlap from 0 to a 100 and that's going to force the red, yellow, and green bars to appear directly on top of each other, and that works perfectly here because only one of them is going to appear for each month, and then maybe play with the gap width a little bit to make those columns a little bit wider, click OK, and there we end up with our result.
Basically looks like we've applied conditional formatting to each column but really, behind the scenes, we're just breaking this out into the 3 series.
The thing that makes it little bit tough is having to go in and format the series using the OPTIONS tab to change the gap width and the overlap.
The overlap’s the important one, to a 100%, to make sure that all of those columns appear directly on top of each other.
Thanks to you, Johann, for sending it in and thanks to you for stopping by.
We'll see you on Monday for episode number 801.
Well, here we are. It's episode number 800. I have a tip today sent in by Johann.
Johann’s from Mexico City and he was watching podcast 776 where I showed how to create this chart where we could show the UNDERs, the AVERAGEs, and the OVERs, basically creating something sort of like conditional formatting, and Johann says, well, wait, there's a much better way to do this.
Let's do conditional formatting on every single month.
So, here we have our results for each of the months, and basically, you know, if we are over a 100%, we're in green, somewhere in between is yellow, and then the things under 50% are in red.
Well, that's a cool chart.
Let's take a look at how we create that.
First of all, you start with your data up here that has all of the, you know, scores for each month, and then we're going to build, in a hidden section of the worksheet, some formulas, and, you know, the first formula says, hey, if we’re > 1, if we are > a 100%, then bring that value down, otherwise bring a 0, and that's for the GREEN series, and then the RED series says, you know, if that value up in B2 is < 50%, bring the value down, otherwise a 0, and then the one in the middle, the YELLOW, just basically says, hey, we're going to take whatever is in B2 – whatever is in the RED, whatever's in the GREEN, that's going to give us everything in between.
So, when I copy this formula across, what we're going to end up with is basically 3 series now and it's going to indicate whether the data should be RED, YELLOW, or GREEN.
Now, I'm going to create the chart as a regular column chart and, initially, you know, well, first of all, the colors are all wrong.
So, I'm going to click here on this series.
I see that that series is YELLOW so I can go to my paint bucket icon and change that to YELLOW.
We'll go to the next series.
I'll choose series GREEN, and, again, the paint bucket to change that to GREEN, and then finally, series RED, and the paint bucket to change that to RED, but this is not the best result at all because we have, you know, the charts are kind of staggered.
So, I'm going to go in and format this series, and go back to the OPTIONS tab.
The OPTIONS tab, you know, is different depending on what your chart type is.
First thing I want to do is I want to set the overlap from 0 to a 100 and that's going to force the red, yellow, and green bars to appear directly on top of each other, and that works perfectly here because only one of them is going to appear for each month, and then maybe play with the gap width a little bit to make those columns a little bit wider, click OK, and there we end up with our result.
Basically looks like we've applied conditional formatting to each column but really, behind the scenes, we're just breaking this out into the 3 series.
The thing that makes it little bit tough is having to go in and format the series using the OPTIONS tab to change the gap width and the overlap.
The overlap’s the important one, to a 100%, to make sure that all of those columns appear directly on top of each other.
Thanks to you, Johann, for sending it in and thanks to you for stopping by.
We'll see you on Monday for episode number 801.
