Today let's take a look
at an Excel chart trick. We'd like to hide one or
more series in a chart automatically depending
on another cell's value. We want to do this as dynamic as possible, so we want to avoid using manual filters or manually hiding any rows. We want the cell value to
basically drive this process. We're even going to take it a step further and automatically sort the
series based on visibility. Let's get to work. (upbeat music) My sample dataset here
consists of Company, Sales, and a column that decides which values I want to show in the chart. So let's say I'm running to a meeting and in that meeting we
only want to discuss these three companies. I put a one here and only
these three companies are going to show in that chart. If I want to include another
Company, so let's say this one, I just need to put a one and
that's going to automatically pop up in the chart without me having to manually do anything. Now, I'm going to take
this one step further and add this extra cool effect by having everything
automatically sorted in the chart. To get that set up, I'm going to use Excel's
new dynamic array functions that are available in
Excel for Office 365. A first step, whenever you create charts that have a different
setup to your source data is to create a data
preparation table in between. And that's what we're going to do here. Call this Data Prep and we'll set up the series that we need here. So basically we need these two series, the difference is this list here is going to be restricted to
wherever I have ones in here. I can use the filter function for that. So let's start with filter. The first argument is the array. So the array is what you want to get back. Well, I have a choice. I can get Company and Sales back, or I can just get Company back. Now, I'm going to do it in a way that I just get the Company back. For two reasons, actually. One reason is that your
Sales or your values might be somewhere else. So they might not be beside
your categories here, like in my example, so you might have other
columns in between. And the second reason is
I want to have each series separately so I can create
dynamic ranges for the chart. Okay, so this means that my array is just restricted to Company. It's the only thing I want to get back. Next argument is what do
I want to have included. Well, I want to have all the
rows included that have a one. So if this range equals a
one, it should show there. For if_empty, that's an optional argument. I'm going to skip that because I assume there is going to be at
least one one in here. Close bracket, press Enter. And that's my list that has one. So that's Inkly, Pet
Feed, Halotot, and Rehire. And I see it in the order that's here. Now remember, I said I want
to take this a step further and make this sorted. So here comes the tricky part. I want to sort this list
based on their Sales values. So first question is
what function do we use? Well, we know in the new
dynamic array functions we have two sort functions. One is sort and the other is sortby. Now, sortby is the function to use if you're sorting your end result based on a value that's
not in your end result. Well, in this part that I'm spilling, I'm not going to show the Sales. The Sales is going to be
separate in its own cells. It's not a part of the spilled range. So I have to use sortby. The array that I want to sort
is the result that I have, so it's basically this that
we see here, so that's fine. Now comes by_array. The by_array obviously will
have to be the same size as the first array, right? So I can't just say sortby
the entire Sales array because that's a different
size to the end size here. They need to be the same size. And of course they have
to be in the same order, which means I can use
the exact same formula, so I'm just going to copy and paste this, except instead of pointing
to the Company here, I need to point to the Sales here. So now I'm sorting this array based on their own Sales values. Now, the last argument is the sort_orders, if I want ascending or descending, I want to get them in descending order, so I'm going to put a minus
one here, closed bracket, and press Enter. Okay, so now I have the companies sorted based on their Sales value. And I'm only showing the
ones where I have a one. So let's test something. Let's put a one on WenCaL as well. It pops up in here because it has the second highest Sales value. First is Rehire, then WenCaL, and then it should be Halotot right here. Okay, so now that I have the companies, let's figure out the Sales. Well, this is easy, right? I can use XLOOKUP or I can use VLOOKUP. Now, I know there's a lot of
VLOOKUP fans in my audience, so I'm going to use VLOOKUP for this one, just for old time's sake. So the lookup value is this one, but I want it to spill as well. So I'm going to put the hash
sign to get the spilled range, the table array, well,
that's this range right here, and the column index is number two, because I want to get
the Sales value back, and the last one, let's not forget, we're going to go with false
because we want an exact match, closed bracket, press Enter, and I get my values spilled as well. Okay, so I'm just going to highlight this and add number formatting to this, to use 1,000 separator,
and zero decimal places. Okay, so let's just test something. If I put a one here, that pops up there. So notice something, 20,000
is repeated two times in here. I'm also going to put a one here that is automatically added as well. Okay, so now I have the
data preparation site done. Let's add in our chart. As a first step, I'm going
to highlight this area, go to Insert, and insert a column chart. Now, this chart is connected to this data. So, for example, if I take
away the one from here, we can see the chart area doesn't update. So it still includes this blank space. And if I add three more to here, we can see that we're missing
two entries from here, right? Because this range is not expanding. The range is fixed to
what I originally had. This is the part that we
want to make this dynamic. We want this chart range to be dynamic. One thing you need to keep in mind is that chart ranges can
either take direct references or they can take names. Since we can't currently
include the hash sign in the chart range, we have to indirectly use a name for that. But that's super easy to set up. So I'm just going to go
to Formulas, Name Manager, and I'm going to create two
names for each of my series. So let's go to New. The first name, let's call it Comp. And the reference is E4,
but we want it to spill, so it's E4 hash. And click on OK. So when I click here, we
can see it highlighted. Now, we need another
one, so let's go to New and call this Value. These are the Sales. This time we're referencing F4, 'cause that's where the Sales numbers are, and add the hash sign
to the end, click on OK. Let's double-check. That area gets highlighted. So I have two names, Comp and Value. So we're going to close. Now, let's go to the chart. Right mouse click, select Data, let's start with Sales,
so edit the series. For the series values, we need to remove everything that comes
after your sheet name. So everything after that exclamation mark. Delete, and replace with
the name we just input. If you forgot what that name was, click F3 and that brings up the names. So this is the Value, I'm
going to click OK and OK. Now, we need to repeat
that for the names as well, so for the Company names, click on Edit, remove everything after
the exclamation mark, type in Comp or select it
with F3, click on OK and OK. Now, my range has updated automatically. Now, let's quickly test. Let's remove these two. That updated, so notice I don't
have any blank spaces here. If I remove these two,
this updated as well. This range is updating and
this is using the names, which are using the dynamic ranges. So now you can go ahead and
format the chart as you like. We can add data labels to this. We can remove these. Give it a title and use this
as a part of your reports. Now, just to give you an idea of how quickly you can
turn this into a dashboard, let me just show you
another example of that I've already completed. It's the same setup we saw before, except I've created three teams here. So I've created a Red team, a
Yellow team and a Green team. And based on the allocation here, we are automatically
assigning the zeroes and ones. So in the example we did together, we manually put the ones, but you can of course also
drive that with a formula. Another thing I did here
is I turned this dataset into an Excel table, so that
if I add in new values here, my chart automatically gets updated. Here I have a dropdown that helps me see the values for the Red team. So in the Red team, I have
Dasring, Kind Ape, and Silvrr. I automatically get the ones in here, because I have a sum product
formula that helps with that. My data preparation is hidden right here, and this then feeds into the chart. So pretty much like the
example that we saw, except these are being driven by formula. Now, if I change this to the Yellow team, I just see the values of the Yellow team. And if I change this to All, I see everything because I
put a one for everything. And that's also driven by this formula that if this is All, it
should put a one everywhere, otherwise it should find
which team they belong to and then put a one accordingly. And just to quickly show you the advantage of using an Excel table here, let's quickly add a new Company. So I'll just put in Bold
and Bold has 20,000. It automatically gets a one here because I have All selected. So where did Bold pop up? It's right here. It just came in automatically there. Now, let's say I also put
in Bold in the Yellow team. So if I switch this
now to the Yellow team, Bold is already a part of it, right? So this is how you can combine
the power of Excel tables together with the power of dynamic arrays to create these automatic
dashboards really fast. That's how you can pimp your Excel charts with the power of dynamic arrays to get interactive Excel dashboards. If you like that, give it a thumbs up. If you'd like to learn more
about Excel dynamic arrays, I have a full course on it. I'm going to add the link to
the description of the video, check it out if you're interested. Otherwise I'm going to see
you in next week's video. (upbeat music)
