what would you do if you find Excel
filter is not working or your Excel
filter is disabled like this hello
everyone welcome to excel 20 tutorial in
this intermediate Excel tutor I'm going
to solve your filter not working issue
or your filter disabled issue okay let's
get started
[Music]
now there are two things you are most
likely to find the first one is your
filter is enabled just like this but
it's not working and the second problem
you will find is that your filter button
is disabled you cannot select it okay so
let's solve the first one filter button
is here everything is here but the
filter is not working okay take a look
at this data set here and I'm going to
click on filter and the filters are
added here now if I click on this drop
down and select
South and click ok
see the filter is working but this part
is not working okay and at the left side
you can see these row numbers are in
blue that means they are inside the
filtered selection but these are in
Black that means they are not inside
filter so it was working until this row
and after that it stopped working now
here we have a blank row and when we
added the filter we just click on the
header and select filter so Excel
automatically selected the range for us
and it has selected until it found a
blank row okay so if I clear
and remove the filter and if I select
this
range and you can see there is a blank
and if I go down and select this entire
range
and then click on filter
the filter is added and if I
select South and click OK and you can
see the entire range is working so this
is the first problem you may find out if
your filter is not working okay now
let's clear the filter and remove it
okay so the second problem will usually
have with the hidden rows okay so select
here and click on filter
and let's change this to Blue
and now let's hide this
and from if I select here and click on
this drop down and you can see we don't
have blue here okay cancel it if I
unhide this
and now we have blue here okay so filter
may not work if you have hidden rules in
you that's it
okay now let's change this to East
and the third issue you might have is
with merged cell
now let's insert in row here and now
let's merge this
and write something let's say I'm going
to write 1 or
2 okay now here it should be 1 and here
it should be equal so let's add some
random one here
and a few 2 here
okay now remove all those I don't need
this right now
so let's say this is our data and
highlight this
so these are our header and this one is
merged and centered so if I click here
and click on that and then click on
filter and here if I click on this drop
down it's not working
okay it will not work because we have a
merge cell in the header but if we
let's remove the filter again now if we
move this data and if we made these our
header if I click here filter
and the filter should work here we can
select one
all the row that has one is here if I
clear it and if I select here
2 you can see the filter is working so
you cannot use filter with more cell
okay now we have our merge column here
but it doesn't matter if you have Mars
to row it will still not work so try to
avoid Mouse itself inside your data
right okay now let's remove this now the
next problem you can encounter is the
formatting issue okay
now let's format this as debt and month
okay so if I click here it's the Apple
date here but here we have 8 October now
if I click on this drop down and click
on date filter and then click here and
then write
eight
height then 450.
be okay this tree is here
now if I click here
and go to date editor and write equal to
and then write 8
10
20 22 and click OK it's not working but
we have written the same date row C and
then select here date filter
equals eight
10.
if I click OK we cannot find this but
when we have written eight October like
this
it found our data because we use the
exact same formatting here okay if you
date is not formatted that way you
cannot find it so you must match the
formatting also if you are going to use
filter okay
clear it
remove it now let's go to the second
problem okay you filter is not activated
there are several reasons the first one
is if your data is partly in a table and
partly not this range is inside the
table
let's zoom in
and here you can see
this icon that means this port is inside
the table if we click here and you can
see we have our table design tab here
and this is our table names but if I
click here this port is not inside a
table so if your data is perfectly
inside table and partly not your filter
will not work so if I select the enter
range including the table and including
this range which is not inside the table
the filter button is disabled but when
we include this data inside the table
the filter button reappears okay
now another reason that your filter
button will be deactivated if you group
your push it so what I did here
press Ctrl and select the worksheet and
we have grouped these two worksheets
when we group our worksheet the filter
will not work and you will learn if your
worksheet is grouped or not you can see
the file name has group added to it okay
once I ungroup it and you can see the
group word is removed from here so if
you have grouped worksheet your filter
will be disabled now another reason your
filter button will be disabled if you
protect your worksheet let's add a
password
and you can see the filter button is now
disabled okay so there are three reason
for which your filter button might be
grayed out because the one you have
Delta in partly table and partly not and
the second reason for your filter button
to disable is if you group your
worksheet and the third reason will be
if you protect your worksheet with
password so these are the reason your
filter might not work in Microsoft Excel
and I have explained how you can solve
it this is what I wanted to show you
thank you thanks for watching see you in
the next tutorial if you can support the
channel through patreon and don't forget
to subscribe thank you thanks for
watching