Create a Drill Down Menu in Excel (1) - Cascading Drop Downs

Find me on Twitter here: https://twitter.com/MattPaul25

Part 2: http://www.youtube.com/watch?v=4k_8ZS6q6BI&feature=plcp

Here is the sample data: http://www.contextures.com/xlSampleData01.html

This is my idea of what a 'drill down menu' is. Some people may have a different interpretation as to what the term 'drill down' means. Honestly, it's somewhat vague. However - in my opinion drilling down on data, has a connotation of digging deeper / investigating / trying to come to a conclusion that requires further diagnoses. The tool I built works for any set of data (dates or not) and allows the user to do just that, come to a conclusion by displaying data in a way that a table cannot (at least not easily). The formulas in this are difficult, the concepts tedious, however i do have other versions that can simplify the hard parts - however are dirtier - and may require maintenance.

https://twitter.com/MattPaul25

---

Closed Caption:

hi welcome to how to create a drill-down
part 1 in this video I'm going to show
you basically how to create a UI that
drills down on a data set so here we
have our UI and here is our block of
data the data is made up of six
different fields so if we go back into
our drill down menu and select sales rep
you can see that we have basically a
pivot table to drill down menu is a lot
like a pivot table it can't do quite as
much to pivot table but what it can do
is look better than pivottable pivot
table can be kind of ugly and people are
familiar with them they might struggle
with how it works and stuff like that so
this is sort of a UI for someone who may
not be that great at excel so let's
let's go over what does so here we have
our sales data between the dates of
january first 2012 and September first
twenty-twelve Gil is currently selected
but i can go in here and go ahead and
select let's take a look at jones ok so
Jones has 13,000 897 sales and revenue
and if i want to see the breakdown per
item
obviously I could select item here but
also i can select region as well
well which region did he do the best and
as you can see here he did best in the
west so for instance if I some this and
I go ahead and highlight this will see
it
it sums up to 13 897 which one
coincidentally is the amount of total
sales so if i look at that and I go well
let's look at the west and I go to my
drop-down over here and select West West
gets highlighted and then I go into
items i want to see the items that Jones
sold in the West would go here and
here's the breakdown of items and the
cool thing about this is you can
actually do this in any order so if i
wanted to go to item here
here's my list of items all that were
sold and if i want to look at binders
and then see the sales reps that sold
them i can do that so you can really
organize it anyway you want the other
neat thing about this is that you can
actually change the values you're
looking at so currently work looking at
revenue but what if i wanted to see
units then i'm looking at units per
binder and then the amount of units each
person sold so anyway I hope you enjoy
this video this is going to be part of
you know i'm not sure if it's going to
be a two-part series or three part
series but i'm guessing it'll be a three
part in this video we're going to go
over the formatting just how to create
this how to create the dropdowns which
is data validation how to create this
sort of button look with the slight
color chains and then also we're going
to do something with our name for ages
so here's our data and the data is
measured by these named ranges that we
use in our formulas so if i click in
here you can see that whole column is
being accounted for by that range
the idea behind that is that this data
is growing most data does because it's
cyber being fed by some sort of data
feed or some sort of database you want
our ranges to grow as well so that that
way we don't have to keep maintaining
our formulas
alright let's get started ok so the
first thing that I did was I made this
whole thing white and I started it by
figuring out what my data was saying so
I needed to think about all the columns
that I wanted to sit switch through as
well as all of the columns I wanted to
someone and because we have columns that
are aggregate or numbers i wanted to
have first validation drop-down be
represented by essentially allow the
data validation to switch from units to
revenue
ok so that will be the first one this
one is going to be units and revenue and
what's the second one going to be well
because we have three other
stringfield's were
essentially going to have three three
layers of drill but that together and
then this cell is going to be my
selection cell so of the items that
appear here this will be the one that's
able to select it will be another bold
and you can actually use this really
cool thing called format painter use
that right there
ok so that's basically what its gonna
look like the other thing we're going to
have to do is color these I like to make
my drop towns have a sort of texture to
them like to have two colors that merge
it kind of some sort of a bubble effect
obviously you can do this any way you
want to go fill effects green and dark
green color of money
ok and then maybe for this one blue and
white looks good
ok I'll take this of format paint format
pain again and do the same with this
thing we're going to do is we're going
to have our date cells so we're going to
have a start start date go ahead and
write justify that an end date which i'm
also going to write justify and then i'm
going to square this end so that gives
the look of something needing to be
filled and I think that's pretty much
all you need that's the basic formatting
so i guess the next thing we need to do
is create our data validation
ok so now we need to create our data
validation so how you do that is you can
go bolts pay for data be for validation
and then be again for validation tab l4
list and then our source for the list
will be in our data tab
we're going to use region sales and item
click ok so this is our first one and
each one of these will have this exact
drop counts i'm just going to go ahead
and copy this and paste it three times
and all of and the data validation got a
copy as well so for this one data
validation again
we're actually going to go two units and
revenue because that is going to be our
drop-down that toggles between what were
aggravating or what were something
together
click OK this will allow struggle
between what kind of data were looking
at and then this will allow us to toggle
between basically what fields were
looking at these data validations will
be looking at our current things so
these will do a little bit later
ok so the next step would be to create
our ranges the name bridges are created
in a manager which is can be brought up
with ctrl f3 but unfortunately writing
formulas in this UI doesn't really work
out very well because you I doesn't give
you any formal and so it's it's really
no fun
so here we're going to use a match
function we're going to look up the
value 1 r lookup array is going to be in
this a through a calm and our match type
is going to be one
ok our match type is actually negative 1
looking for 1a through a negative 1 is
our match type basically like saying
find me the first cell that does not
have value 1 alright so what does that
do that brings us our final cell
reference which is all the way down here
to 661 and that is going to be part of
our reference so what we can do is if
we're talking about sales rep region or
item we would go to through the 661
however because those things very we
actually need to create another match
function and that is going to sit within
address function so we just made our one
of our real numbers we're going to put
to our column number is actually going
to be a match function and what are we
going to look up
well we're going to look up our region
look up raise going to be right here a
horizontal ray and our match is going
to be exact okay so this is my phone
number my real number
let's see what that does so i'm going to
close that out and okay so it says be 26
61 so be would be region and if we look
back here it's worth pointing out region
is perfect and 661 that's ok too so
we'll actually have to make another
dress
we're going to go eat ooh quote
ampersand quote through and percent as
we have to another one of those address
functions you address a match my roll
number which is made right there and
then my column number is going to be the
same exact formula that we did for the
first part of the reference
ok see if that works
ok so b2 through be 661 perfect
so if i go back here and i change this
to item d2 through d 661 and that's
exactly what we need
i'm actually going to go ahead and go
through these and make these absolute
references
ok then what i'm going to do is just
copy this a few times and . this at the
other and use so and this one is j15 so
I go in here and I change these to a
value
ok so perfect so we have b c and d are
all represented you put an indirect
function around it so indirect does it
takes a text and converts it into a
actual cell reference where in this case
a range reference so i'm going to put
indirect around it
direct ok so now i can copy this formula
and go into my name manager new going to
call it big range 1 and paste that
formalin and let's see what happens
perfect ok so that formula is creating
our range it should go all the way down
to 661 ok so I'm going to do the exact
same for the other one someone called
big range to and the Grange three
ok so now that all of my ranges are made
and if we look at them they should all
be referencing the correct reference i
can actually make my other ranges now so
my other ranges will be are some ranges
and the sum range will actually depend
on the item selected in this drop-down
so for instance if i go ahead and delete
these and i use my match formula address
the match forma my roll number is going
to be too and my column number is going
to be a match that we're going to look
up right in cell C 10 gonna make that an
absolute reference my lip array is going
to be here also an absolute reference
and it's going to be 0y close that off
at two so now we want F to through so I
always separate my text with an
ampersand with my formulas and then i'm
going to have something very similar
except I'm use my a through a function
right here so i'm going to go ahead and
copy this match paste it's and this is
actually convert my lookup value to a 1
my lookup array to
they threw a and make that a permanent
reference and then my match type to be a
negative one
ok so f2 through f 66 and i change that
two units you'll see that want to eat
you
366 so these ranges are actually both
these ranges are dynamic horizontally
and vertically because we have made with
the match function we have made them
they will switch between columns and be
able to grow as our data set expands
beyond 661 so I'm going to wrap this
with an indirect ind I tab and then
going to go here in depth to mode or
edit mode by pressing up to copy that
and my new range is going to be called
big sum range i like that and it should
give me my some range which is perfect
ok so this is then the first video of
how to create a drill down menu keep
following along to the second video i
know this was pretty tedious and trust
me it only gets more tedious but it'll
be worth it in the end it's a great you
why you can impress your boss with it
hopefully alright thanks for watching
again email me at xlsx geek and shemale
calm if you have any questions or better
yet leave a comment below thanks again