Mike has a Chart and wants to use Conditional Formatting to make the Data Present more clearly. Today, in Episode #1602, Bill "MrExcel" Jelen addresses this Conditional Formatting for Chart Graphic Presentation.
Using Excel 2010, it's possible to create breathtaking charts, graphs, and other data visualizations -- and communicate even the most complex data more effectively than ever before. For more information about Charting in Microsoft Excel, be sure to check out Bill's book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen. Charts and Graphs: Microsoft Excel 2010
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Using Excel 2010, it's possible to create breathtaking charts, graphs, and other data visualizations -- and communicate even the most complex data more effectively than ever before. For more information about Charting in Microsoft Excel, be sure to check out Bill's book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen. Charts and Graphs: Microsoft Excel 2010
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast – Chart Conditional Format Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Today's question is sent in by Mike.
Mike has - let's call it a Pareto Chart – he has things that have gone wrong and the number of times each has gone wrong and in the chart, he wants to highlight the worst offender in red and the second worst in yellow.
So here I have ran between here on my chart so every time we press F9, we get a different worst and second worst.
Sometimes we even get a tie.
There we go - two items tied for red.
I've seen the tie in the yellows - there we go - tie in the yellows.
Okay, so Mike wanted to know – “how can I conditionally format a bar and say if it's equal to the max and another bar to say if it's equal to the second largest?” Well, charts simply don't do conditional formatting and the way that I created this is I cheated.
I'm going to move the chart and out here I built formulas so these formulas copy the items over and then I have columns for red, amber and black.
Alright.
And the red formula saying, “Hey, look at B4 and see if it's equal to the largest value in B4 to B17” and I lock that down with dollar signs.
If it is, then give me B4, otherwise, give me 0.
So, in this particular dataset - these values - 198 is the largest value and that's the only one that shows up in the red column, and then for the amber or yellow, to figure out the second largest value, instead of using max, I had to use the LARGE function, so, =LARGE($B$4:$B$17,2) will give me the second largest value in this column.
And so check to see if B4 is equal to the second largest value.
Also, I don't want red and yellow appearing at the same time so that would create a really tall bar that was double high, so make sure that E4 is 0.
That way, in case there is a tie in the reds - like here, where this is the max and this is the second largest - so they're both showing up as red.
We have no yellows in that case.
And then the formula for the black column is just whatever the B4 value is minus red, minus amber will give us the black.
So what typically will happen is we'll have one red, one yellow and then everything else is black and then from there, a simple matter to create a stacked column chart, so insert, column, stacked - make sure to choose stacked.
We nicely have a legend here so I'm going to choose the series called “black”, come back to the format tab, shape fill, make that be black.
Choose the series called “amber”.
Again, on the format tab, shape fill, choose yellow and on the series called “red”, format, shape fill, choose red.
Now, get rid of our legend there and I also right click format data series and make the gap width narrower - that makes those columns wider.
Alright, so there you go.
Everytime I press F9 now, we'll get the largest type.
So, is it really conditional formatting?
No.
No, it's not.
We have to cheat.
It's a horrible cheat but people are kind of used to conditional formatting now because we can do it in the spreadsheet so much so it's a natural question to say – “Hey, well how do we do that in a chart?” The chart people haven’t built conditional formatting in, but it's simple enough to use some formulas that are in the spreadsheet to kind of make it appear as though the chart is using conditional formatting Well, hey, I want to thank Mike for that question, I want to thank you for stopping by.
Hey, for more on charts and graphs, check out the MrExcel library - Charts and Graphs Microsoft Excel 2010.
See you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast – Chart Conditional Format Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Today's question is sent in by Mike.
Mike has - let's call it a Pareto Chart – he has things that have gone wrong and the number of times each has gone wrong and in the chart, he wants to highlight the worst offender in red and the second worst in yellow.
So here I have ran between here on my chart so every time we press F9, we get a different worst and second worst.
Sometimes we even get a tie.
There we go - two items tied for red.
I've seen the tie in the yellows - there we go - tie in the yellows.
Okay, so Mike wanted to know – “how can I conditionally format a bar and say if it's equal to the max and another bar to say if it's equal to the second largest?” Well, charts simply don't do conditional formatting and the way that I created this is I cheated.
I'm going to move the chart and out here I built formulas so these formulas copy the items over and then I have columns for red, amber and black.
Alright.
And the red formula saying, “Hey, look at B4 and see if it's equal to the largest value in B4 to B17” and I lock that down with dollar signs.
If it is, then give me B4, otherwise, give me 0.
So, in this particular dataset - these values - 198 is the largest value and that's the only one that shows up in the red column, and then for the amber or yellow, to figure out the second largest value, instead of using max, I had to use the LARGE function, so, =LARGE($B$4:$B$17,2) will give me the second largest value in this column.
And so check to see if B4 is equal to the second largest value.
Also, I don't want red and yellow appearing at the same time so that would create a really tall bar that was double high, so make sure that E4 is 0.
That way, in case there is a tie in the reds - like here, where this is the max and this is the second largest - so they're both showing up as red.
We have no yellows in that case.
And then the formula for the black column is just whatever the B4 value is minus red, minus amber will give us the black.
So what typically will happen is we'll have one red, one yellow and then everything else is black and then from there, a simple matter to create a stacked column chart, so insert, column, stacked - make sure to choose stacked.
We nicely have a legend here so I'm going to choose the series called “black”, come back to the format tab, shape fill, make that be black.
Choose the series called “amber”.
Again, on the format tab, shape fill, choose yellow and on the series called “red”, format, shape fill, choose red.
Now, get rid of our legend there and I also right click format data series and make the gap width narrower - that makes those columns wider.
Alright, so there you go.
Everytime I press F9 now, we'll get the largest type.
So, is it really conditional formatting?
No.
No, it's not.
We have to cheat.
It's a horrible cheat but people are kind of used to conditional formatting now because we can do it in the spreadsheet so much so it's a natural question to say – “Hey, well how do we do that in a chart?” The chart people haven’t built conditional formatting in, but it's simple enough to use some formulas that are in the spreadsheet to kind of make it appear as though the chart is using conditional formatting Well, hey, I want to thank Mike for that question, I want to thank you for stopping by.
Hey, for more on charts and graphs, check out the MrExcel library - Charts and Graphs Microsoft Excel 2010.
See you next time for another netcast from MrExcel.
