(bright music)
- Hi there, if you are having a few
hassles when filtering, let's see
if I can give you a hand to get that filter working again.
(bright music)
Hey, I'm Sharyn from Excel at Work
where we offer straightforward nontechnical assistance
to help you become a confident
super-productive user of Microsoft Office.
In this video, I'm going to run through my
top Filter troubleshooting tips.
I've helped so many people become
super-proficient users of Excel
and I know that most times one of these tips
will get you going again quickly.
And, make sure you stick around for an additional tip
that explains something you really should keep
your eye on when you're filtering.
Something that could potentially
stuff up your filtering too.
Hey, and please dive into the comments below
and share which one of these tips helped you out the most.
Okay, let's see if I can find the problem
that is causing your filter not to work
as you're expecting it to work.
Now, first of all, the number one thing that I see people
get very, very wrong, when they are filtering,
if I just kinda come down and show you this data,
is where they forget to select everything that
needs to be included in the filter.
And, if you can see here, we've got
our row 37 and row 42 are blank.
And, if I just come up a little bit
and come to the top of the table here.
I'm just gonna turn Filter on. (mouse clicking)
And, then let's just go and quickly
do a filter maybe on the Region here.
(mouse clicking) And, if I just whip down
and maybe grab Central. (mouse clicking)
There you go, you can see that
the filter is actually only being produced
or actually taking in the data
that was in adjacent cells at the top of the data list.
It didn't actually include anything
under that first blank row, so that's
the number one thing I want to check, okay?
I want you to go and check to make sure
that you have actually include all of your
data or included all of your data.
If I now go and clear that, (mouse clicking)
and I'll just turn Filtering off actually.
And, I'm now going to just go to the top,
left-hand corner of the table and
make my way down to the bottom.
And, I'm just using Shift-click
on the very bottom, right-hand cell
(mouse clicking) to select everything.
I'm popping Filter back on. (mouse clicking)
And, now let's see what happens
(mouse clicking) if I go back to Region
and grab Central now. (mouse clicking)
Look at that.
Everything is now included, including those blank rows.
Just keep that as the number one thing to check, okay.
Because it seems to be the one that most people forget.
Right, on to number two. (mouse clicking)
As you can see, one or two of these columns have been merged
with a merged heading between column A and F here.
Now, and don't get me wrong, I love merged headings.
I think they're fantastic, however, they can cause
problems when you're filtering.
Because if I just click on the little dropdown here,
you're only going to see the Filter option
available (chuckles) for White Wine
not for the columns Dry and Medium.
What I would suggest is please unmerge
any merged columns that have got one heading
and give each column it's own heading.
This is what I would probably do here,
I'd probably remove the Filter.
Maybe move this up. (mouse clicking)
And move these two up. (mouse clicking)
And of course, there's no longer any reason
that we have that second row there.
By the way, try very hard to make sure that
your column headings are just on one row.
That will save you a lot of hassles, okay.
Now, if I go and select, again I'm just doing
that Shift-click down on the bottom corner there.
Just a nice quick way to do it.
And, turn Filter on. (mouse clicking)
And now, look I can Filter on Dry
and I can Filter on Medium.
That might be another one for you
to quickly go and check along with merged rows.
Okay, so that could cause you a bit of a problem as well.
Merged headings are fine, keep them out
of the data area though, but merged rows, check this out,
I'm just gonna go and pop some color
(mouse clicking) on these first,
three products in row six, seven, and eight.
Right, now that I've done that,
I'm just gonna whip over to here.
And of course I'm just gonna go
and do a filter based on the 15th of June.
If I just come in here and just
(keys tapping) type 15.
And there's 15th of June, lovely.
Click on OK.
And look, it's only got one product showing.
And, I'll just explain why because this can actually be
really scary because if you're filtering
and you're not picking up on the data
that's in there, that could just be yucky.
Let me just go and clear that filter.
(mouse clicking) And, there we go.
You can actually see that each one of those
that was actually only showing us one of those rows.
If I now go and Unmerge it at the very top here.
(mouse clicking) Now, look at this.
The row that it was showing us,
that data is actually only living
in row six and that's the reason why.
I would suggest, please make sure where
you don't have merged rows as well.
I would just copy that down.
Of course, as you know, you can actually
copy your cells rather than fill them.
And there you go.
Just keep an eye out for that as well, okay.
(mouse clicking)
And I'll just go and take that
color off so it doesn't confuse you as we keep on going.
I'm hoping by now it's actually fixed something.
If you need to keep going, we'll keep going.
Here's number three.
Number three is please make sure
that you don't have hidden rows, okay?
If I just come across here and maybe
if I just hide, (mouse clicking)
actually what I'll do (mouse clicking)
is I'll just call this something else.
I'll just call it Donkey. (keys tapping)
M-kay?
Then I'm just gonna hide that row.
I'll just go and hide it.
Okay, if I now go onto the Range and click.
You will see, there's no Donkey (chuckles).
If you've got a missing Donkey,
it could be that unfortunately you've got hidden rows.
If you're expecting the data to be in there,
just be really, really careful.
You might find that probably it'll be a good idea
to Unhide any rows and then, of course,
you will be able to see the information.
There it is, look.
If I now go and have a look, there's Donkey.
That could be another reason that
your data actually isn't showing.
Okay, here's one that I don't know how you'd actually
know this unless somebody told you this (chuckles).
But, if I just come across to, well
I'm just gonna come down.
You can see that we've got an error in this column here,
in the Medium White Wine column.
It's got a hashtag value and you're probably aware of the
fact that if you have a hashtag value,
normally what's happening is inside
of the formula, text or a cell
that's holding text is actually being referenced.
If I just come up and I'm gonna go up to Medium,
I'm gonna come down to Number Filters,
and I'm going to start using this Top 10 option.
And, this is the same if you're
using Above and Below Average as well.
What should happen is if I click on OK,
(mouse clicking)
I will get the Top 10 records at the very top,
were showing up at the top here.
Now, unfortunately it hasn't worked.
But, as soon as I do this, (mouse clicking)
if I just can show you.
If I scroll to the bottom of the list, look at that.
Can you see it's actually showing that I've got an error?
Now, watch this. (mouse clicking)
If I actually come and go down to where the error is,
(mouse clicking) I'll grab that
and I'm going to fix the error.
At the moment, it's actually going C10 multiplied by 10%.
(key tapping)
Let's say this formula's actually meant to be
A26 multiplied by 10%. (mouse clicking)
(keys tapping)
I'm just making this up (chuckles).
But all of a sudden we actually have
a figure in there instead of an error.
If I now come and use, actually I'll just clear that off
so you can see that the error's gone, yeah.
And, then what we'll do is we'll just
make our way back to that Top 10.
(mouse clicking) And okay, look at this.
That's something to be very weary
of and as I've said to you before, "It will work."
That kind of thing, the error, will start at the Top 10,
in the Above Average and the Below Average.
That may have helped you and sorted
your problem as well, I'm hoping.
The other thing, or another thing to check
is please make sure that you aren't looking at data
that actually already has filters on it.
(mouse clicking) If you're actually looking
at data that's already filtered or has filters on it,
you will see little filter symbols at the very top here.
Now, if the data's off the screen,
all the way off the screen, sometimes it's very hard
to actually see the filters that are on.
So that's why, and you might've seen me using this before,
that's why I like this button up here.
"Clear the filter and sort state
"for the current data range."
If I click on to that, it brings everything back
and of course if you don't see white,
oh sorry, white, if you don't see blue (chuckles)
numbers down the side here, then you know
that you are actually seeing everything.
And of course, if you did have a filter on
it would show you the filter number down here.
Not sure if you've ever noticed that before
but if I actually just go and show you.
(mouse clicking)
There you go, blue number, and it's actually
showing you the number of records found down here.
And there you go, you've got your little filter on here.
Doing a clear can sometimes get
you right back to the start again.
And, you can see all of your data
and that may help you as well.
I really hope that one of these tips
has fixed your problem straight away.
Just remember, check you have selected all of the data.
Keep your column headings to one
row and unmerge any merged cells.
Make sure your data doesn't have any errors in it.
That's always a gotcha.
And check other filters are turned off.
Now that you've got a good idea
of what to look for to get your filter back on track,
here's my additional super-helpful tip to you.
(air whooshing) Right, for the last
couple of gotchas that I wanna go through with you,
these are the things that you kind of find out
when you ask somebody else in the office and they go,
"Oh, yeah, I've done that before."
(chuckles) (gasps)
I just wanna show you, I'm just gonna
quickly put another sheet in here.
There's something called Grouping and you
might have done this before where you can
actually Group multiple sheets together.
And, so if I just go Select All Sheets
you'll see both of them are actually selected now.
And people do this so that you can print,
you know can print all the sheets at the same time.
You can update them at the same time.
But, that, if you've actually got Grouping on.
Can you see Group at the very top here?
And, your Filter will not be able to be selected.
That could be something to just watch out for.
Make sure if you've opened a shared file
that somebody else has been working in,
come down here, right-click, Ungroup.
And look, now your Filter will
actually be available for you to use.
If you're experiencing the old
grayed-out button, just check that for me okay?
Okay, now the last thing I want to share with you
is a bit quirky and certainly something that
I'm hoping will be helpful to you if you're finding
or having a bit of a difficulty in this area.
I'm just gonna change this figure here
(keys tapping) to 32,000.
(keys tapping) And, I'll come down a couple
(keys tapping) of rows and I'll do the same.
Put the same amount in there.
Now, as you can see 32,000. (mouse clicking)
If I come up here and I do a search.
(keys tapping) Nothing, absolutely nothing.
So you can go, okay, well let's go to Number Filter.
(keys tapping) Equals 32,000.
And again, nothing.
And you're like, "What?" (chuckles)
If I come into Number Filters Equals
and I actually type it with the formatting.
(keys tapping) Look at this.
Ta-da!
How 'bout that?
If you are having a bit of a problem in a certain area
and it does it the same with numbers or even with dates.
You can see that we've got the 15th, the 6th, 2019 in there.
If I come into here and I go,
(mouse clicking) Equals and I do
(keys tapping) my 15th of June,
get it!
'Cause it's actually formatted that way.
But, if I go into here and I go Equals
(mouse clicking)
(keys tapping) 15-06-19,
nothing.
Just keep an eye out for that, okay?
That can be a real gotcha and have
you kinda scratching your head going,
"What is going on here?"
I'll just clear that and I'm hoping that one
of those things that I've just shared
with you has got you back on track and back working again.
Let me know in the comments which one had helped you out.
And, if anything helped you out.
And, look, hey, if you are somebody
that's been using filtering for a while,
remember that a lot of people that
are watching this are just starting out.
They're just beginning with Excel, so if there's any tips
or hints that you'd like to give them
over and above what I've actually shown here,
I'd love for you to share with them.
Anything that you think's helpful,
pop it down below and help them out.
Hey, look I really hope this has been helpful.
Please let me know in the comments
below if it fixed your problem.
And, if you use Excel and want to stay connected,
just hit that Like button and please
make sure that you subscribe because
we're sharing new videos regularly.
And if you know someone who would benefit from this help,
please be sure to share this video with them.
Hey, thanks for watching and have a fabulous day.