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!
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!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a question sent in by Rod.
Rod's from Cincinnati.
He's using the trick all the way back from podcast 470, where Zack showed us how to basically, create green bar formatting.
Using conditional formatting.
Rod says this works great until I turn on the auto filter and filter to some certain value for example, in his case, he was saying, "I only want where things are greater than zero." We have a few items that are not offered in certain locations and so when I filter those out, what we're getting is like here.
There was a zero row between Row 9 and 11 that has been hidden and now, the green bar formatting isn't working perfectly because I'm having some rows, that are now adjacent to each other.
And so, I thought about this, and there's an interesting feature with the subtotal function.
The subtotal function normally is added by the automatic subtotal command.
But it has the amazing ability that it will ignore rows that are hidden by the Auto filter.
So, I came over here in Column D and added a new column called count and basically what the count does is, It says, hey! We're going to count everything from cell C, Dollar sign, 1.
That we always start at one down to this column.
But we're going to use the subtotal function, to count.
So, that's subtotal open parenthesis, 2 and basically it just puts in the numbers, one, two, three, four, five.
But you'll notice that if I turn on the auto filter.
Data, filter, auto filter and then ask for just where the cost is greater than zero.
Click [ ok ].
The data is now remembered.
So, here between row 9 and 11 where there is a row hidden.
We do successfully go from 8 to 9.
Now, it's just a matter of using Zack's trick.
So, let's do data, filter, show all, to bring everything back.
We use Zack's trick to apply conditional formatting.
I'll select all of those cells.
I'll notice that A2 is the active cell.
Will use format, conditional formatting and say the formula is equal mod of in this case D2.
I want to make sure we always point to D.
So, I put a dollar sign before that D2, comma, 2.
The mod function basically divides that number 1, 2, 3, 4 by the number 2 and gives us the remainder.
So, we're either going to get a 0 or 1.
I'll ask for where it's equal to 1 and then apply a format.
Maybe a green, click [ ok ], click [ ok ] and now we have perfect green bar.
But if we go through and ask for just certain records.
Let me just ask for the 200th because it's easier.
It will continue to format the green bar, perfectly because it's based on this Column D.
And of course we get hide Column D And it still continues to work on the fact that it's hidden by format, column hide.
Doesn't affect the subtotal function.
It Just affects the rows hidden by the auto-filter.
So, Rod great question.
Thanks for sending it in and thanks to you for stopping by, we'll see you next time for another netcast for MrExcel.
I'm Bill Jelen.
Today, we have a question sent in by Rod.
Rod's from Cincinnati.
He's using the trick all the way back from podcast 470, where Zack showed us how to basically, create green bar formatting.
Using conditional formatting.
Rod says this works great until I turn on the auto filter and filter to some certain value for example, in his case, he was saying, "I only want where things are greater than zero." We have a few items that are not offered in certain locations and so when I filter those out, what we're getting is like here.
There was a zero row between Row 9 and 11 that has been hidden and now, the green bar formatting isn't working perfectly because I'm having some rows, that are now adjacent to each other.
And so, I thought about this, and there's an interesting feature with the subtotal function.
The subtotal function normally is added by the automatic subtotal command.
But it has the amazing ability that it will ignore rows that are hidden by the Auto filter.
So, I came over here in Column D and added a new column called count and basically what the count does is, It says, hey! We're going to count everything from cell C, Dollar sign, 1.
That we always start at one down to this column.
But we're going to use the subtotal function, to count.
So, that's subtotal open parenthesis, 2 and basically it just puts in the numbers, one, two, three, four, five.
But you'll notice that if I turn on the auto filter.
Data, filter, auto filter and then ask for just where the cost is greater than zero.
Click [ ok ].
The data is now remembered.
So, here between row 9 and 11 where there is a row hidden.
We do successfully go from 8 to 9.
Now, it's just a matter of using Zack's trick.
So, let's do data, filter, show all, to bring everything back.
We use Zack's trick to apply conditional formatting.
I'll select all of those cells.
I'll notice that A2 is the active cell.
Will use format, conditional formatting and say the formula is equal mod of in this case D2.
I want to make sure we always point to D.
So, I put a dollar sign before that D2, comma, 2.
The mod function basically divides that number 1, 2, 3, 4 by the number 2 and gives us the remainder.
So, we're either going to get a 0 or 1.
I'll ask for where it's equal to 1 and then apply a format.
Maybe a green, click [ ok ], click [ ok ] and now we have perfect green bar.
But if we go through and ask for just certain records.
Let me just ask for the 200th because it's easier.
It will continue to format the green bar, perfectly because it's based on this Column D.
And of course we get hide Column D And it still continues to work on the fact that it's hidden by format, column hide.
Doesn't affect the subtotal function.
It Just affects the rows hidden by the auto-filter.
So, Rod great question.
Thanks for sending it in and thanks to you for stopping by, we'll see you next time for another netcast for MrExcel.
