Look Who it Was Random video

Excel - Create Dynamic Monthly Performance Charts in Excel with Conditional Formatting - Episode 800

1,142 views · Published 15 January 2009 · 3:28 · Indexed 10 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Create Dynamic Monthly Performance Charts in Excel with Conditional Formatting.

Welcome back to the MrExcel netcast! I'm Bill Jelen and today we have a special episode as we celebrate our 800th episode. I want to thank all of our viewers for their support and for sending in great tips and tricks. Speaking of which, today's tip comes from Johann in Mexico City. He saw episode 776 where I showed how to create a chart with conditional formatting and he has a better way to do it. Let's take a look!

Johann's idea is to use conditional formatting on each individual month in the chart. So, instead of just showing the overall results, we can now see the performance for each month. The chart will show green if the results are over 100%, yellow for results between 50% and 100%, and red for results under 50%. This is a great way to visualize the data and identify any areas that need improvement.

To create this chart, we start with our data and then use hidden formulas to determine the color for each month. The formula checks if the result is above or below a certain threshold and assigns the appropriate color. Then, we create a regular column chart and format each series to match the color assigned by the formula. The key is to adjust the overlap and gap width in the chart options to make sure the columns are stacked on top of each other.

This chart is a great way to showcase the performance for each month and easily identify any areas that need attention. Thank you, Johann, for this great tip and thank you to all of our viewers for tuning in. Don't forget to join us for our next episode on Monday, where we'll continue to share helpful Excel tips and tricks. See you then!

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/


Johann from Mexico City sends in a cool tip for creating charts that seem like they use conditional formatting to color the columns. Episode 800 will show you how.

Table of Contents:
(00:00) Introduction to episode 800 and tip from Johann
(00:21) Johann's suggestion for conditional formatting on every month
(00:38) Creating the chart with conditional formatting
(01:27) Using hidden formulas to create 3 series for the chart
(01:37) Formatting the chart to show red, yellow, and green bars
(02:42) Final result and explanation of the process
(03:05) 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:
Applying conditional formatting to columns in Excel
Chart formatting options in Excel
Chart with UNDERs, AVERAGEs, and OVERs
Conditional formatting chart
Creating a chart with conditional formatting
Creating a hidden section in Excel
Episode 800 of MrExcel netcast
Overlap and gap width in chart formatting
Tips for creating charts in Excel
Using formulas in Excel for conditional formatting


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152071/

More from this channel