Chris from Long Island asks how to conditionally format the negative items in a chart. Episode 656 shows two methods for handling this.
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.
Today a question sent in by Chris.
Chris's from Long Island.
If you have a question for the podcast please feel free to leave us a voicemail or just drop me an email.
I'll say. hey, there's a new London Skype in number that old Skype in changed, so this new London number note that.
Chris said hey, I have a chart and I want to conditionally color the columns in the chart.
For example, if it's positive show it as green, if it's negative show it as red.
Well, the first way to do this is to take your chart, and we're going to choose that first series and hit Ctrl 1, which is basically "Format", and there's a great setting here on the "Patterns" tab that says "Invert if negative".
So I change the first color to green and then set "Invert if negative" and then what I get is green for positive and white for the negative.
A different way to go though is to kind of build, you know kind of rogue series out here.
So here's my original date.
"Date" and the "Change" and I have a formula here that just copies the date over and says hey, I only want the value if it's positive for green and the value if it's negative for the red.
So, you could do this with the IF function.
My favorite way to do it though is to take the =MAX(0,B2).
Now think about it. B2 is positive that's going to be larger than 0. We get the value for B2.
Otherwise, we're going to get the negative value.
So =MAX(0,B2) and then ofcourse to the red is the =MIN(B2,0) Just a fast way, faster than using the IF function.
Will copy that down to all of our cells and this time we're going to build a stacked column chart, a stacked column chart.
So we'll go into the "Chart Wizard" choose "Stacked Column" and click "Finish".
And you'll see that we have now, have two series red and green.
So I want to get rid of that legend first of all.
Click on the "Legend" and click "Delete".
Want to format the first series that way, It's green so "Format cells.
Choose green for the positive numbers okay, and then I want to go and select the negative series.
Now here's what's really strange this for some reason, I'm clicking around this chart today, and I can't manage to actually click on the negative bars.
That's coming up with category axis every time so I want to go to my "Chart" toolbar the "Chart" toolbar is floating here.
And we're going to open up that first drop down and choose Series "Red" now we can "Format" cells, Ctrl 1 and we'll make that series be red and basically what we've done here, is kind of fool people into thinking that we have a single series, some of the bars are red some of the bars are green.
When in reality behind the scenes, we've actually used formulas to break it out into 2 series. 1 series green, 1 series red.
Not necessarily the most elegant way to go, but there've been many charts where I end up creating kind of a rogue series in order to change the colors in some points and not other points.
Thanks to Chris for that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today a question sent in by Chris.
Chris's from Long Island.
If you have a question for the podcast please feel free to leave us a voicemail or just drop me an email.
I'll say. hey, there's a new London Skype in number that old Skype in changed, so this new London number note that.
Chris said hey, I have a chart and I want to conditionally color the columns in the chart.
For example, if it's positive show it as green, if it's negative show it as red.
Well, the first way to do this is to take your chart, and we're going to choose that first series and hit Ctrl 1, which is basically "Format", and there's a great setting here on the "Patterns" tab that says "Invert if negative".
So I change the first color to green and then set "Invert if negative" and then what I get is green for positive and white for the negative.
A different way to go though is to kind of build, you know kind of rogue series out here.
So here's my original date.
"Date" and the "Change" and I have a formula here that just copies the date over and says hey, I only want the value if it's positive for green and the value if it's negative for the red.
So, you could do this with the IF function.
My favorite way to do it though is to take the =MAX(0,B2).
Now think about it. B2 is positive that's going to be larger than 0. We get the value for B2.
Otherwise, we're going to get the negative value.
So =MAX(0,B2) and then ofcourse to the red is the =MIN(B2,0) Just a fast way, faster than using the IF function.
Will copy that down to all of our cells and this time we're going to build a stacked column chart, a stacked column chart.
So we'll go into the "Chart Wizard" choose "Stacked Column" and click "Finish".
And you'll see that we have now, have two series red and green.
So I want to get rid of that legend first of all.
Click on the "Legend" and click "Delete".
Want to format the first series that way, It's green so "Format cells.
Choose green for the positive numbers okay, and then I want to go and select the negative series.
Now here's what's really strange this for some reason, I'm clicking around this chart today, and I can't manage to actually click on the negative bars.
That's coming up with category axis every time so I want to go to my "Chart" toolbar the "Chart" toolbar is floating here.
And we're going to open up that first drop down and choose Series "Red" now we can "Format" cells, Ctrl 1 and we'll make that series be red and basically what we've done here, is kind of fool people into thinking that we have a single series, some of the bars are red some of the bars are green.
When in reality behind the scenes, we've actually used formulas to break it out into 2 series. 1 series green, 1 series red.
Not necessarily the most elegant way to go, but there've been many charts where I end up creating kind of a rogue series in order to change the colors in some points and not other points.
Thanks to Chris for that question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
