When you hide a row, Excel will remove that series from a chart. In Episode 609, I create a hideous chart, but then use an AutoFilter to create a cool ad-hoc charting tool that allows a sales manager to see results for any one sales rep.
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.
Last monday I talked about taking one chart and breaking it into four charts to make it easier.
This trick is a little bit related.
We have a whole series of sales reps here and to try and put all of these people on one chart would just be horrendous, but there's an amazing feature in excel that if you have data that is in a chart and you hide some of the rows. That data is by default removed from the chart.
So I'm going to take these sales reps. I'm going to create just a horrendous looking chart. I'm Gonna use insert chart and create a column chart.
Click finish.
and this is completely unreadable.
In fact they even have the data reverse.
I'm going to go into "Chart" "Source Data" and on, say that the data is in rows.
and now I've created just a hideous chart that absolutely no one could ever ever hope to understand.
I am going to take that chart I'm going to move it up above my data and then down here in the data set I'm going to choose "Data" "Filter" "Auto Filter" and basically we'll instruct the manager or whoever is going to be using this chart that they're supposed to go to the "Name" field and choose one specific name from the list.
As soon as they choose that name from the list, It hides all the other rows and our chart snaps into something that looks good.
I guess we can take a legend of at this point i'm and then basically you have a nice little adhoc reporting tool here.
Choose the next rep and it will show you just the results for that rep.
Great little trick basically it by hiding charts this is actually a default setting on the chart that says that any hidden rows are removed.
You can turn that off but in this case that default is a really excellent way to create a cool-looking adhoc reporting tool with the chart.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Last monday I talked about taking one chart and breaking it into four charts to make it easier.
This trick is a little bit related.
We have a whole series of sales reps here and to try and put all of these people on one chart would just be horrendous, but there's an amazing feature in excel that if you have data that is in a chart and you hide some of the rows. That data is by default removed from the chart.
So I'm going to take these sales reps. I'm going to create just a horrendous looking chart. I'm Gonna use insert chart and create a column chart.
Click finish.
and this is completely unreadable.
In fact they even have the data reverse.
I'm going to go into "Chart" "Source Data" and on, say that the data is in rows.
and now I've created just a hideous chart that absolutely no one could ever ever hope to understand.
I am going to take that chart I'm going to move it up above my data and then down here in the data set I'm going to choose "Data" "Filter" "Auto Filter" and basically we'll instruct the manager or whoever is going to be using this chart that they're supposed to go to the "Name" field and choose one specific name from the list.
As soon as they choose that name from the list, It hides all the other rows and our chart snaps into something that looks good.
I guess we can take a legend of at this point i'm and then basically you have a nice little adhoc reporting tool here.
Choose the next rep and it will show you just the results for that rep.
Great little trick basically it by hiding charts this is actually a default setting on the chart that says that any hidden rows are removed.
You can turn that off but in this case that default is a really excellent way to create a cool-looking adhoc reporting tool with the chart.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
