Excel - Conditional Format Largest Column in Chart: Episode 1602
2,723 views · Published 20 September 2012 · 3:58 · Indexed 12 November 2026
Channel: MrExcel.com · 2012 · Education
Microsoft Excel Tutorial: How to Conditionally Format the Largest Column in a Chart. Welcome back to the MrExcel netcast. In this episode, we will be discussing how to conditionally format the largest column in a chart. This question was sent in by Mike, who wanted to highlight the worst offender in red and the second worst in yellow in his Pareto Chart. Unfortunately, charts do not have the capability for conditional formatting. So, in order to achieve this, we will have to cheat a little. I have created a workaround by using formulas to copy the data and then creating columns for red, amber, and black. The formula for the red column checks if the value in B4 is equal to the largest value in the range B4:B17. Similarly, the formula for the yellow column uses the LARGE function to check for the second largest value. To avoid having both red and yellow columns appear at the same time, we also need to make sure that the value in the black column is 0. This way, if there is a tie for the largest or second largest value, only one color will appear. Once we have our data set up, we can create a stacked column chart and format the colors accordingly. Although this may not be true conditional formatting, it is a useful workaround for achieving the desired result. The chart people have not yet built in the capability for conditional formatting, but with a little creativity, we can make it appear as though the chart is using conditional formatting. I want to thank Mike for sending in this question and I hope this solution helps others who may have a similar issue. For more tips and tricks on charts and graphs, be sure to check out the MrExcel library - Charts and Graphs Microsoft Excel 2010. Thank you for watching and don't forget to subscribe for more helpful Excel tutorials. See you next time for another netcast from MrExcel. 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/ Table of Contents: (00:00) Chart Conditional Formatting (00:18) Solution using formulas (01:09) Explanation of the formulas used (02:14) Creating a stacked column chart (03:15) Demonstration of the solution (03:45) Clicking Like really helps the algorithm 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. http://www.mrexcel.com/store/index.php?l=product_detail&p=183 ...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! http://www.mrexcel.com/learn2010/LE2010.html "The Learn Excel from MrExcel Podcast Series" MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today! Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153417/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920