Tom from Cincinnati asks how to create a chart which can show either monthly or quarterly data. Episode 881 shows how to manually set up group and outline symbols to achieve the result.
Transcript of the video:
Hey, walk back to the MrExcel netcast, I'm Bill Jelen.
Cool question today, this comes from Tom in Cincinnati.
Tom says that he has a chart like this chart down here that shows months and quarters, but sometimes he wants it to show quarters, and sometimes he wants it to show this.
So, there's a trick in Excel that if you hide data in the worksheet, that data gets hidden from the chart.
And you probably have encountered the Group and Outline buttons when you've added subtotals before, but there's a way to do that manually.
Here's what I'm going to do is, I'm going to choose the first three months-- January, February, March-- and I'm going to go to Data, Group and Outline, and then say to Group that data.
And what it does is, it takes those three columns and puts it in a collapsible, basically, drop-down, so that way I could very quickly hide January, February, March, and only show Q1.
Now that I just did that, the fast way to go is to choose the next three months and press the F4 key, which is basically a redo; and then July, August, September, press F4; and then October, November, December, press f4; and I, very quickly, set up all of those groups.
Now, here's what happens, is when I press the #1 button, it will collapse down, as you see in the chart here, to showing just the four quarterly columns.
So, all of a sudden, my chart becomes a quarterly chart.
If I press the #2 button, it expands everything and I get to see the months.
So, a very quick way, without doing any VBA whatsoever, of creating a chart that can show either months or quarters-- depending on your choice up here in the horizontal Group and Outline buttons.
I want to thank Tom for that question, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Cool question today, this comes from Tom in Cincinnati.
Tom says that he has a chart like this chart down here that shows months and quarters, but sometimes he wants it to show quarters, and sometimes he wants it to show this.
So, there's a trick in Excel that if you hide data in the worksheet, that data gets hidden from the chart.
And you probably have encountered the Group and Outline buttons when you've added subtotals before, but there's a way to do that manually.
Here's what I'm going to do is, I'm going to choose the first three months-- January, February, March-- and I'm going to go to Data, Group and Outline, and then say to Group that data.
And what it does is, it takes those three columns and puts it in a collapsible, basically, drop-down, so that way I could very quickly hide January, February, March, and only show Q1.
Now that I just did that, the fast way to go is to choose the next three months and press the F4 key, which is basically a redo; and then July, August, September, press F4; and then October, November, December, press f4; and I, very quickly, set up all of those groups.
Now, here's what happens, is when I press the #1 button, it will collapse down, as you see in the chart here, to showing just the four quarterly columns.
So, all of a sudden, my chart becomes a quarterly chart.
If I press the #2 button, it expands everything and I get to see the months.
So, a very quick way, without doing any VBA whatsoever, of creating a chart that can show either months or quarters-- depending on your choice up here in the horizontal Group and Outline buttons.
I want to thank Tom for that question, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
