Patrick wrote in to address my answer from podcast 641. In that podcast, I used a SUMIF to create a pie chart of hours spent by department. Patrick notes that early in the month, many of the departments have 0 values and would like those excluded from the chart. In Episode 643, I set up a series of formulas to sort the data and then a named range to exclude the zeroes from the chart.
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:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Let's go back to Patrick's question from Tuesday.
Patrick send a new note and said hey that worked out great, creating the SUMIF function over here to figure out the total for department.
He says but you know at the beginning of the month. I made I'm done work for all the departments, I end up with all these departments that have zeros, and it's just showing up in the pie chart.
They're stacked up sometimes, they're actually on top of each other. Is there any way to get the zero departments out of the Pie Chart.
Well, there's two ways to do this, the first is to turn on AutoFilter, Data, Filter, AutoFilter and we'll go in and say we want a custom filter basically, where the data does not equal zero and when you hide something because of an AutoFilter what happens is it gets hidden from the chart.
Now, the problem with this is it's actually destroying some of our data over here on the left hand side.
So, there's a much more difficult way to do this automatically, where we will never have to go through an AutoFilter.
Basically, I said we're gonna come over here to column F to the left of our data and add a Rank function in.
Basically, I say hey, I want the rank of this value in H4, amongst all the values from H4 to H10.
Now, sometimes we're going to end up with ties and so, when we end up with a tie in the later functions.
We're also going to add a count of how many times this particular number of hours has already appeared before me.
What that does, is even though there's a tie here with two departments both at 0 hours.
It's showing up as a Rank of 6 and a Rank of 7.
It's important that we have every value represented.
Now, I came over here to the right hand side and put in the numbers from 1 through 7 because I had 7 departments and then used.
Basically, a VLOOKUP, said hey, go find whichever value is ranked number 1 today.
So, that ends up the TRF department with 3.3.
Grab their total also using a VLOOKUP.
So, now I have an interesting table over here on the right hand side.
That basically, sorts the data, that way the largest values are at the top and their hours and I have several values that are 0 at the end.
Now, this is where it gets kind of tricky, I came over here to Column N, and created COUNTIF function, said hey I want to count every cell that's greater than 0.
So, that way I know that, I only have in this case 5 cells today and you'll see that this would change.
If I would add a new value in, now I have six values that are non-zero.
I wanted to Insert, name Define and set up a Dynamic Range.
We've talked about this podcast before basically it says, Hey, we're gonna start from this cell right here, cell K2, go down zero rows and right 0 rows, but then how many rows do I want to include in the range.
Well, hey I wanna include whatever the answer is over here in N3 plus one for the headings.
And then finally, make it be two columns wide.
I then went in basically created a chart instead of specifying that source data I then created the chart based on MyData, find that creates a Dynamic Chart.
So, if we have several departments, that are 0 over here and they automatically fall out of the chart as the month goes on and we get more data.
It automatically gets added back into the chart.
So, you know this takes the good five, six, seven minutes to get it set up once, but once you have it set up then it'll work perfectly for the rest of the time you won't have to go in and reapply the AutoFilter, to get rid of the non-zero values every single day.
A couple of ways to go AutoFilters certainly easier to do but if you're going to have to produce this report every day, probably worth the time to add the extra columns, the Rank, the VLOOKUPS and then the COUNTIF.
Finally, the dynamic range name to make sure that it only charts the values that are non-zero.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Let's go back to Patrick's question from Tuesday.
Patrick send a new note and said hey that worked out great, creating the SUMIF function over here to figure out the total for department.
He says but you know at the beginning of the month. I made I'm done work for all the departments, I end up with all these departments that have zeros, and it's just showing up in the pie chart.
They're stacked up sometimes, they're actually on top of each other. Is there any way to get the zero departments out of the Pie Chart.
Well, there's two ways to do this, the first is to turn on AutoFilter, Data, Filter, AutoFilter and we'll go in and say we want a custom filter basically, where the data does not equal zero and when you hide something because of an AutoFilter what happens is it gets hidden from the chart.
Now, the problem with this is it's actually destroying some of our data over here on the left hand side.
So, there's a much more difficult way to do this automatically, where we will never have to go through an AutoFilter.
Basically, I said we're gonna come over here to column F to the left of our data and add a Rank function in.
Basically, I say hey, I want the rank of this value in H4, amongst all the values from H4 to H10.
Now, sometimes we're going to end up with ties and so, when we end up with a tie in the later functions.
We're also going to add a count of how many times this particular number of hours has already appeared before me.
What that does, is even though there's a tie here with two departments both at 0 hours.
It's showing up as a Rank of 6 and a Rank of 7.
It's important that we have every value represented.
Now, I came over here to the right hand side and put in the numbers from 1 through 7 because I had 7 departments and then used.
Basically, a VLOOKUP, said hey, go find whichever value is ranked number 1 today.
So, that ends up the TRF department with 3.3.
Grab their total also using a VLOOKUP.
So, now I have an interesting table over here on the right hand side.
That basically, sorts the data, that way the largest values are at the top and their hours and I have several values that are 0 at the end.
Now, this is where it gets kind of tricky, I came over here to Column N, and created COUNTIF function, said hey I want to count every cell that's greater than 0.
So, that way I know that, I only have in this case 5 cells today and you'll see that this would change.
If I would add a new value in, now I have six values that are non-zero.
I wanted to Insert, name Define and set up a Dynamic Range.
We've talked about this podcast before basically it says, Hey, we're gonna start from this cell right here, cell K2, go down zero rows and right 0 rows, but then how many rows do I want to include in the range.
Well, hey I wanna include whatever the answer is over here in N3 plus one for the headings.
And then finally, make it be two columns wide.
I then went in basically created a chart instead of specifying that source data I then created the chart based on MyData, find that creates a Dynamic Chart.
So, if we have several departments, that are 0 over here and they automatically fall out of the chart as the month goes on and we get more data.
It automatically gets added back into the chart.
So, you know this takes the good five, six, seven minutes to get it set up once, but once you have it set up then it'll work perfectly for the rest of the time you won't have to go in and reapply the AutoFilter, to get rid of the non-zero values every single day.
A couple of ways to go AutoFilters certainly easier to do but if you're going to have to produce this report every day, probably worth the time to add the extra columns, the Rank, the VLOOKUPS and then the COUNTIF.
Finally, the dynamic range name to make sure that it only charts the values that are non-zero.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
