Look Who it Was Random video

Excel - How to Conditionally Format Negative Items in a Chart - Episode 656

679 views · Published 19 March 2009 · 3:03 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  How to Conditionally Format Negative Items in a Chart.

Welcome back to the MrExcel netcast! In today's episode, we'll be discussing a question sent in by Chris from Long Island. If you have a question for the podcast, please feel free to leave us a voicemail or send me an email. Also, note that there is a new London Skype in number, as the old one has changed.

Chris's question is about conditionally coloring columns in a chart. He wants to show positive values in green and negative values in red. One way to do this is by selecting the first series in the chart and hitting Ctrl 1 to open the "Format" menu. Then, under the "Patterns" tab, there is an option to "Invert if negative." By changing the first color to green and selecting this option, the chart will display green for positive values and white for negative values.

Another method is to create a "rogue" series using a formula. In this example, we have the "Date" and "Change" columns, and a formula that copies the date and only displays the value if it is positive for green or negative for red. This can be done using the IF function, but my preferred method is to use the =MAX(0,B2) formula. This will display the value for B2 if it is positive, and if it is negative, it will display 0. Similarly, the =MIN(B2,0) formula can be used to display the negative value in red. This method is faster than using the IF function.

To create a stacked column chart with the two series, we can use the "Chart Wizard" and select "Stacked Column." Then, we can format the first series to be green and the second series to be red. However, there may be an issue with clicking on the negative bars in the chart. In this case, we can use the "Chart" toolbar and select the "Series Red" option to format the negative series. This may seem like a workaround, but it allows us to create the desired color scheme without having to manually change the colors for each bar.

While this may not be the most elegant solution, it is a useful technique for creating charts with conditional colors. Thank you to Chris for the question and thank you for tuning in to another netcast from MrExcel. Don't forget to leave your questions in the comments below and we'll see you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Chris from Long Island asks how to conditionally format the negative items in a chart. Episode 656 shows two methods for handling this.

Table of Contents:
(00:00) Introduction and question from Chris
(00:20) New Skype in number for London
(00:32) Two ways to conditionally color chart columns
(01:03) Using formulas to create rogue series for color changes
(02:42) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Building rogue series
Chart conditional color
Chart toolbar
Creating rogue series
Fooling people with colors
Formatting series
Formula for conditional color
Invert if negative
MrExcel netcast
Removing legend
Stacked column chart
YouTube video

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/mrexcel-s-learn-excel-656-negative-charts.1152272/

 About MrExcel: Bill Jelen is an Excel MVP and the author of 70+ books on Excel. He has trained thousands of professionals worldwide.

More from this channel