Today's trick from Lisa in Indianapolis is a faster way to filter a chart
You can filter a chart to just one customer, one sales rep, one anything.
You used to do this by filtering the original data set.
But, starting in Excel 2013, you can use the Funnel/Filter icon to the right of the chart.
Bonus tip today: Excel offers an odd Font dialog with things like:
ALL CAPS, Double Strikethrough, and more
You can filter a chart to just one customer, one sales rep, one anything.
You used to do this by filtering the original data set.
But, starting in Excel 2013, you can use the Funnel/Filter icon to the right of the chart.
Bonus tip today: Excel offers an odd Font dialog with things like:
ALL CAPS, Double Strikethrough, and more
Transcript of the video:
Learning Excel from MrExcel podcast episode 2092.
Chart Filter.
Hey, welcome back to the MrExcel net cast.
I am Bill Jelen.
Today's tip sent in by Lisa in Indianapolis.
I was doing a seminar and talking about one of my favourite old tricks for a while.
You create a chart for everybody, but then you can filter that down to just one person.
So we're going to insert a chart here, kind of like a, this chart, 2d column chart.
And of course it builds it wrong with the names going across the bottom.
I want those names in the legend.
So I switched row and column, which gives me the names down here in the legend.
Now, what I would normally do here is I would add the word name and A3 I would apply a filter.
And then the beautiful thing is once you hide all of the rows in the data set, except for one, if we would hide Betty, Claire, Dale on down through Gloria, then you'd have what the chart for just Andy.
And that's the way I was showing how to do the trick.
But Lisa said, Oh, no, wait, there's a faster way.
And, and of course there's a faster way.
At least if you have Excel 2013 or newer, so select the chart, you get these three icons out here, we'll open the funnel icon and uncheck, select all, choose just Andy.
All right.
And then come down here to the bottom and click apply.
And what you get now is a chart for just Andy or we'll choose Claire and chose Andy click apply, and I have a chart for just Clara, much faster than using the filter.
Plus we still get to see the values for everybody, not just the one person.
So this is a cool new addition to Excel, 2013 note.
That was a quick tip.
So again, a bonus tip here, and this one, this one is just a mystery to me.
I'm going to choose that chart title up there, the chart title, and I'm going to go to the home tab.
And under the home tab here in the phot group, I'm going to open the dialogue launcher.
All right.
And this, you can even see there in the tool tip, that's the regular font dialog, but because I'm there on that chart title, this is some bizarre, crazy version of a font dialog, which really reminds me of much more of word.
All right, because they have this, these cool things like all caps.
Let's take a look at that.
Right.
So it puts it in all caps or back here to home dialogue launcher.
And we'll see what small caps, small caps looks like.
All right.
So it's all in caps.
Put the first capital letter, the actual capital letter is higher than the others or home like this and equalize character height that makes those lowercase letters equally.
I, which it looks really bad in this font, but it's interesting that it's there even, you know, I'm a big fan of keeping my to do's to do lists in Excel.
They're not just through, but double strike-through.
I would, I would love to have double strike-through in regular Excel, but the only way that that's ever going to work at least it seems to me is you have to be in a chart.
All right.
So the way that I knew that this setting was here, right?
The way that I scored that setting is there is under the design tab.
There's a couple of, of these that appear in all caps like that one right there.
All right.
And there's nothing special about this gallery.
Everything in this gallery could have done in the Excel 2010 is just a trick of finding out where those settings were.
Right?
So the fact that that style three, there is given me all caps somehow out of out of title that isn't a proper case.
I knew that that's setting had to be somewhere in Excel, 2010, right.
They didn't add any functionality for that gallery and sure enough it's there, although what's weird, let's go back here to design and choose style to see there's also a way to make those axis values down there.
Those access labels be in all caps, right?
So there's a way to change that, just a cool, a bit of functionality.
I challenge you leave a note in the YouTube comments, where else you, you can find that this this weird version of the font the font dialog box with these very, they really feel like word where those settings, settings are.
All right.
So, Hey, that tip including the all caps is in the book power Excel with mr.
Excel, the 2017 edition click that on the top right-hand corner to buy the book, or, you know, Hey, you can just walk into your local Barnes and noble or up in Canada chapters, Indigo, and they are carrying the books.
So you can have it today.
All right.
So today's trick from Lisa in Indianapolis, faster way to filter a chart.
You could filter to just one customer, one sales rep, one, anything using the old auto filter but starting at Excel 2013, the funnel or filter icon to the right of the chart as a faster way to go.
And also we talked about that odd plot dialogue was things like all caps, double strike-through and more weight.
I want to thank you for stopping by.
We'll see you next time for another net cast from mr.
Excel,
Chart Filter.
Hey, welcome back to the MrExcel net cast.
I am Bill Jelen.
Today's tip sent in by Lisa in Indianapolis.
I was doing a seminar and talking about one of my favourite old tricks for a while.
You create a chart for everybody, but then you can filter that down to just one person.
So we're going to insert a chart here, kind of like a, this chart, 2d column chart.
And of course it builds it wrong with the names going across the bottom.
I want those names in the legend.
So I switched row and column, which gives me the names down here in the legend.
Now, what I would normally do here is I would add the word name and A3 I would apply a filter.
And then the beautiful thing is once you hide all of the rows in the data set, except for one, if we would hide Betty, Claire, Dale on down through Gloria, then you'd have what the chart for just Andy.
And that's the way I was showing how to do the trick.
But Lisa said, Oh, no, wait, there's a faster way.
And, and of course there's a faster way.
At least if you have Excel 2013 or newer, so select the chart, you get these three icons out here, we'll open the funnel icon and uncheck, select all, choose just Andy.
All right.
And then come down here to the bottom and click apply.
And what you get now is a chart for just Andy or we'll choose Claire and chose Andy click apply, and I have a chart for just Clara, much faster than using the filter.
Plus we still get to see the values for everybody, not just the one person.
So this is a cool new addition to Excel, 2013 note.
That was a quick tip.
So again, a bonus tip here, and this one, this one is just a mystery to me.
I'm going to choose that chart title up there, the chart title, and I'm going to go to the home tab.
And under the home tab here in the phot group, I'm going to open the dialogue launcher.
All right.
And this, you can even see there in the tool tip, that's the regular font dialog, but because I'm there on that chart title, this is some bizarre, crazy version of a font dialog, which really reminds me of much more of word.
All right, because they have this, these cool things like all caps.
Let's take a look at that.
Right.
So it puts it in all caps or back here to home dialogue launcher.
And we'll see what small caps, small caps looks like.
All right.
So it's all in caps.
Put the first capital letter, the actual capital letter is higher than the others or home like this and equalize character height that makes those lowercase letters equally.
I, which it looks really bad in this font, but it's interesting that it's there even, you know, I'm a big fan of keeping my to do's to do lists in Excel.
They're not just through, but double strike-through.
I would, I would love to have double strike-through in regular Excel, but the only way that that's ever going to work at least it seems to me is you have to be in a chart.
All right.
So the way that I knew that this setting was here, right?
The way that I scored that setting is there is under the design tab.
There's a couple of, of these that appear in all caps like that one right there.
All right.
And there's nothing special about this gallery.
Everything in this gallery could have done in the Excel 2010 is just a trick of finding out where those settings were.
Right?
So the fact that that style three, there is given me all caps somehow out of out of title that isn't a proper case.
I knew that that's setting had to be somewhere in Excel, 2010, right.
They didn't add any functionality for that gallery and sure enough it's there, although what's weird, let's go back here to design and choose style to see there's also a way to make those axis values down there.
Those access labels be in all caps, right?
So there's a way to change that, just a cool, a bit of functionality.
I challenge you leave a note in the YouTube comments, where else you, you can find that this this weird version of the font the font dialog box with these very, they really feel like word where those settings, settings are.
All right.
So, Hey, that tip including the all caps is in the book power Excel with mr.
Excel, the 2017 edition click that on the top right-hand corner to buy the book, or, you know, Hey, you can just walk into your local Barnes and noble or up in Canada chapters, Indigo, and they are carrying the books.
So you can have it today.
All right.
So today's trick from Lisa in Indianapolis, faster way to filter a chart.
You could filter to just one customer, one sales rep, one, anything using the old auto filter but starting at Excel 2013, the funnel or filter icon to the right of the chart as a faster way to go.
And also we talked about that odd plot dialogue was things like all caps, double strike-through and more weight.
I want to thank you for stopping by.
We'll see you next time for another net cast from mr.
Excel,
