Ute from Germany sends in a better way to show negative points in a column chart in a red color. His cool trick works for Excel 97-2003. Episode 674 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.
Now back in episode 656 I went through this horrible process to create a chart where the negative bars were red and positive bars were green and I did that by creating two different series.
One for green one for red.
I got a note from one of the German MVPs Newt Simon and Newt says. hey, you completely missed it There is an easier way to do this and he's right.
Let's create a quick chart here, and we'll create a column chart click "Finish".
A couple things we want to clean up, get rid of the legend, get rid of that title and the first thing we want to do is we want to go and format those bars.
So I select the Series.
Hit "Ctrl+1" Fast way to do "format cells" and I'm going to go into "Fill Effects" and go back to the "Pattern" tab where I can now specify two colors.
I'll specify red as the "Foreground" and green as the "Background".
Click "OK" Now because I've been allowed to apply two colors when I click "Invert if negative", instead of always using white for the negative bars. I actually get the red and the green.
Now you'll see that there is the pattern showing through there.
I'm sure you won't be able see it on the iPod screen, but we're getting little dots.
Let's just go back in "Format cells" and just apply a plain green.
Click "OK" It still remembers that other color from the pattern tab.
Now unfortunately this is something that you could do if you're in excel 97 through excel 2003, since they took the patterns out of Excel 2007, we don't have any good way to control the color of the negative bars.
Want to thank Newt for sending in that great tip much better way to create the bars especially if you're in the older versions of excel.
Want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Now back in episode 656 I went through this horrible process to create a chart where the negative bars were red and positive bars were green and I did that by creating two different series.
One for green one for red.
I got a note from one of the German MVPs Newt Simon and Newt says. hey, you completely missed it There is an easier way to do this and he's right.
Let's create a quick chart here, and we'll create a column chart click "Finish".
A couple things we want to clean up, get rid of the legend, get rid of that title and the first thing we want to do is we want to go and format those bars.
So I select the Series.
Hit "Ctrl+1" Fast way to do "format cells" and I'm going to go into "Fill Effects" and go back to the "Pattern" tab where I can now specify two colors.
I'll specify red as the "Foreground" and green as the "Background".
Click "OK" Now because I've been allowed to apply two colors when I click "Invert if negative", instead of always using white for the negative bars. I actually get the red and the green.
Now you'll see that there is the pattern showing through there.
I'm sure you won't be able see it on the iPod screen, but we're getting little dots.
Let's just go back in "Format cells" and just apply a plain green.
Click "OK" It still remembers that other color from the pattern tab.
Now unfortunately this is something that you could do if you're in excel 97 through excel 2003, since they took the patterns out of Excel 2007, we don't have any good way to control the color of the negative bars.
Want to thank Newt for sending in that great tip much better way to create the bars especially if you're in the older versions of excel.
Want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
