Look Who it Was Random video

Excel - Keeping Greenbar Formatting (Banded Rows) After a Filter - Episode 640

391 views · Published 26 March 2009 · 3:27 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Greenbar Formatting (also known as Banded Rows) After Applying a Filter in Excel. 

Welcome back to the MrExcel netcast! In today's episode, we have a question from Rod, a viewer from Cincinnati. Rod is using a trick from podcast 470, where Zack showed us how to create green bar formatting using conditional formatting. However, Rod ran into an issue when he turned on the auto filter and filtered for values greater than zero. This resulted in some hidden rows and the green bar formatting not working as expected.

After some thought, I remembered an interesting feature of the subtotal function. This function, usually added by the automatic subtotal command, has the ability to ignore rows that are hidden by the auto filter. So, I added a new column called "count" and used the subtotal function to count the rows, starting from cell C1. This way, even if some rows are hidden by the auto filter, the count will still be accurate.

Next, I applied Zack's trick of using conditional formatting to create the green bar effect. By selecting all the cells and using the formula =MOD(D2,2), we can get a remainder of either 0 or 1. Then, we can apply a format for when the remainder is equal to 1, giving us the alternating green bar effect. This works perfectly even when we filter for certain values, as the formatting is based on the count column, which is not affected by the auto filter.

So, thanks to Rod for sending in this great question and thanks to all of you for tuning in. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time on the MrExcel netcast!

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/


Rod from Cincinnati notes that the trick used in Podcast 470 to apply greenbar formatting fails when you use the AutoFilter to hide certain rows. There is an interesting workaround. Episode 640 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction
(00:10) Question from Rod
(00:20) Using green bar formatting
(00:30) Issue with auto filter
(01:10) Solution using subtotal function
(02:02) Applying conditional formatting
(02:41) Perfect green bar formatting
(03:00) Continued functionality with auto filter
(03:10) 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:
Auto filter
Conditional formatting
Count formula
Format conditional formatting
Green bar format
Green bar formatting
Hide rows
Mod function
MrExcel netcast transcript
Subtotal function


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

More from this channel