Create Dynamic Excel Filter - Extract data as you type

In this video tutorial, learn how to create a dynamic Excel filter. It instantly extracts the records as you type in the search bar.

It is created using the Activex Control Combo Box in the developer tab in Excel. It allows you to filter the results as you type in the search box. A great way to show your data and make it interactive.

Step-by-step written tutorial and download file:
http://trumpexcel.com/2015/01/dynamic...

---

Closed Caption:

hello and welcome to the video back from
pics and I'm some advances and in this
video i will show you how to create a
dynamic excel filter
let me first show you what i mean by a
dynamic excel filter here i have the
data with product name sales rep name
and the geography and here i have a
filter a combo box and you can see there
is a drop down with the name of
countries in this column and as soon as
I select countries for example i select
india it would filter all the records
for India
similarly if i select any other country
it would filter regard for that country
now this is a regular filter but you
also have the functionality to type
within this combo box
so here if i type say you guess it would
instantly filter these records and it
does this while i'm typing so for
example here if i type india and i type
I it would give me all the countries
that have the alphabet
I in it so it would give me India China
Indonesia Philippines Singapore
similarly Phi types eh eh it would give
me all the countries that have the
alphabet in it
so this is a dynamic filter where you
get the records as you type in the
search box
now let me show you how to create this
from scratch so here i have the raw data
I have the exact role in a product name
sales rep name and geography and I would
create that but the first do that i
would need a unique list of these
countries because i also need to create
a drop down
so to do that I would select all these
countries
copy it create a new worksheet here and
I would taste the list of countries
so here i have all the countries let me
name this time as unique list
now to remove all the duplicates i would
simply go to data and here i have remove
duplicate option when i click on this it
would give me the column here I only
have one column which is column is why
would keep it
check my data does not have headers so
this is unchecked and i would click ok
and as soon as I do this you can see it
says 12 duplicate values found and
removed eight unique values remain so
these are the list of countries let me
again
give a header to it which is unique
country list now
one additional step i would do is i
would also create a named range for this
because i would use this named range to
create a drop down in the combo box so
to do that I would go to formulas and
here i have to define names group here
if you go to define name
I can create a named range I would name
it country list the scope for be
workbook and the range would be a to a
nine so in this case I have it in the
sheet unique list and the cell
referenced as a2 a nine if you have it
somewhere else
you can refer to that area and now when
i click OK a named range has been made
you can go back and check here in a
manager you can see that here i have
countries
i also have country list demo because it
was the name is that I have used in the
demo that I showed you
initial in this video now you can see if
you click on this it will show you the
necklace
this is the list I'll go back to the
dynamic filter list and the second step
would be to create that combo box so i
would go to the Developer tab if you do
not have the Developer tab you can
simply right click on any of these tabs
and go to customize the ribbon and here
you would see that this value this
checkbox is unchecked can simply check
this and you would have the Developer
tab there in your ribbon
now i would go to insert and with an
insert you have form controls and
activex controls here
I would select combo box which is this
one as soon as I click on it and go back
to the worksheet and click anywhere else
it would insert the
combobox now in this combo box when you
right-click you would see that there's a
properties option i would click on
properties and it opens this huge dialog
box there I would have to make three
changes i would first go and i would
have to link a cell so in this case I
would link cell
k - so I would manually type k to hear
then they would be list Phil range i
would type the name of the named range
that we created so it would be country
list and i would make one more change
here it says match entry which is match
entry complete
I would change this to match entry none
and I'll show you why i made these
changes and now when i close this my
combobox is set now if i go to the
design mode and I click on it I can type
in my combobox now see what happens as
soon as i type anything in this cell
it is reflected in the selkie to which
is the link set now my combobox is done
i would also have to set the data so in
this case i would have to create 3
helper columns so i would create helper
column one or maybe I can just call it
helper 1
I would create a helper column 2 and
help our country
now in this case the idea is that if I
type something here so for example here
if i type us then all the records for us
should be extracted
so I'm using these three hyper columns
and i would use formulas to do it
the first thing I would do is I would
manually have to either type the numbers
here or i can use the Rose formula and i
would use rose formula and I would lock
the first cell reference so that when it
goes down it would give me two and three
and so on
so here it gives me one and as I double
click it gives me all these numbers tell
20 the second header column would have a
formula
and here i would first search for this
text which is in cell links el que tu
and I've searched this text within the
geography sir
and now if it will find that value so
for example if i double click on this
you can see i'll also have to lock the
link so i would hit f4 now when I double
click on it you can see that it gives me
an error
whenever there is no match but whenever
there is a match where they were there
is us here
it gives me a number so i would use this
property and i would say is number so
that it returns true and false
so for example here it would give me a
true wherever there is us and false then
there is not i would then use an if
formula and i would say if it's a true
then give me this number and give me a
black
now if i apply this formula
overall you can see that this gives me
the number the row number whenever
there's a match so for example here if I
go back and I type
I you can see it gives me all these
numbers because now is there in India if
they're in China it's there in Indonesia
Philippines Singapore so wherever there
is this alphabet I it would give me the
number and now in the last hyper column
or helper column
I would use the formula small and i
would use this area and I would say give
me the first smallest number second
smallest number torch - number so that
all these numbers stack together here
so again I would use the Rose formula
and I would lock the first cell
reference so that when it goes down if
we look for the second
the smallest number and third smallest
number so i will also have to lock this
cell reference and now when i hit enter
it gives me one here and when I
double-click you can see it gives me all
these numbers which are stacked up
together
I do not want these errors so i would
use the formula if error if there is no
better give me this value else give me a
black and now when I double click here
you can see that it gives me all these
numbers which are stacked up together
and you can see it is very dynamic if i
type us
it gives me these numbers for 7 17 and
18 which other row numbers which has
this country's for 7 17 and 18 similarly
you can try this with other countries as
well if i type can actually give me 14
and 19 because these other two rows that
have this country
now I simply have to extract so for
example at me for demo purposes have us
here in the combo box and I have to
extract this data which means i have to
extract these real numbers
so to do that I would simply copy this
heading
I would protect here let me
shift the combo box
and now i would use the index formula
here
so index formula the area would be this
entire area
this entire range of cells I would press
f4 to lock it and the row number would
be this value and i would have to lock
it so that when i go to the right it
does not change so i would have to lock
g g 4 and the column number would be in
this case the column number would be one
because I need the product name so i
will use the columns formula here
I would again use the same technique and
I would lock
I for so that when i go to the right it
becomes 2 and n 3
now when i hit enter you can see it
gives me product for and as I go to the
right it gives me product for Rachel us
which is a product for it to us which is
this one fourth row if I go down
it gives me product seven and again if i
go to the right
it gives me the right right data now I
do get some air because then there is no
data here it as it goes down
I would have to use the if error formula
again and i would say there is no error
give me this value
let's give me a black and now when I
drag this
you can see that i have the data
extracted
now i can simply for cosmetic purposes
move this dialog box here so that this
link cell is hidden
I can maybe change the little bit and
align it to the middle so that the value
is not visible or you can simply go in
and just hide this by making it white or
go to custom number formatting and do
that and here I've set this
now if you hide all this data then you
would see that this has become a dynamic
filter you have a drop down here so i
would go to the Developer tab
get out of the design mode and now you
can see there is this entire drop-down
list if i select a donation I have the
results for Indonesia if i select any
other country i would have the results
for that country
and I would also have the ability to
write here so if i type china i would
have all the required for China if i
type canada and i would have all the
required for Canada
so this is how you can create a dynamic
filter now it is not necessary that your
data should recite in the same worksheet
your back and it could be anywhere in
some any any other worksheet and you can
create a filter as a summary sheet or as
a user interface and it is not even
necessary to have this functionality as
such you can also try some variations
for example in this case if i type in I
it will give me all the countries which
has the alphabet i but if you want only
the country that start with I which will
be india and indonesia then you can
configure that in a way where you can
use the hyper columns tweak the formula
so that it gives you that result
so you can try different variations but
overall this is how you can create a
dynamic excel filter
I hope you found this video useful thank
you and have a nice day