Okay, so here's an Excel chart. Do you notice
something cool about this? Something that's not standard behavior. Notice the bottom accents. We
have more categories in this view and less here. Standard Excel charts don't do this.
Here's how they behave. Assume you create your chart on this range. Now, we
switch our selection and unfortunately, we end up with all this white space. To get this to work,
you had to create dynamic ranges and use name manager, which does make you think twice if you
want to add this to your reports.
Now, you don't have to think twice anymore. It's going to do it
for you. You just need the right setup. So, the right start is to use formulas that automatically spill.
Basically, you just write a single formula and you get everything that you want. That's all you need
to do. Let's do this quick example together. Assume this is your source data. We have information about
Department, Name and Salary. We want to create a dynamic chart where we can easily switch our view
between Finance, Sales and Procurement department, and see the names and the salaries of the people.
My source data is formatted as an Excel table, and there is an advantage that comes with this, which
I'm going to show you after we create the chart. This table is called "TSalary." Now, let's go ahead
and prepare the data so that when someone selects Finance, I only see the names of the people that
work in the Finance department.
Which function is right for this? The FILTER function. Very powerful
formula and super easy to use. All we need is the array, this is what we want returned. So, we
want to get the name and the salary return, so I'm just going to select these two columns. What do
we want to include? Only the names that work in the department that's selected here. So, we need
to check whether Department column equals what we select in this cell, and that's pretty much it.
We can go ahead and close the bracket.
Now, if you want to account for cases where there is no match.
So, if I select Finance and there's no one working in the Finance department, and you don't want
to get an error, you can use that last "if_empty" argument. So here, I can put "No one works here," or
whatever you want returned. Now, immediately I get the name of the people that work in Finance. If
I switch my view to Sales, everyone that works in Sales pops up here. Now notice that the number
formatting doesn't come with, so we'll just have to apply the number formatting on this range. I'm
just going to use the shortcut key Control + 1 and add a thousand separator. Notice I've also selected
more cells than for which I have data for. That's simply because if I switch my view to Finance and
this range keeps expanding, maybe I will get more people working in the Finance department. Then the
formatting is automatically applied. Okay, so, so far, so good.
Now that we have our dynamic range, all we
have to do is go and insert the chart. Go to Insert, I'll insert a column chart and select our column
chart. Everyone is here. Now comes the beautiful part. When I switch my view from Finance to Sales,
look at this. It looks like a pivot chart. Excel is now able to automatically just pick out the used
range. When I switch now to Finance, look at this. No need to use workarounds anymore. Just create your
data preparation table using formulas that spill like this. Now, why is it great to have your source
formatted as a table? Well, check this out. If I have someone new working in the Finance department.
Okay, so now, my view is Finance, the last person here is Paul Hale, and I'm going to put a new person
here, call them Lily. Look what happens. My dynamic range automatically updates to include that, and
my chart automatically updates as well. By the way, if you want to get your results automatically
sorted, all you have to do is put the FILTER function inside the SORT function.
The array that
we want to sort is this, then we have to define the sort index, this is the column we want to sort by,
that's the second column. And then you can define the order, ascending or descending. Let's go with
descending. Close bracket, press Enter, and now we have everything sorted in descending order. Now,
let's take a look at another really cool example, especially when it comes to creating really nice
cool dashboard effects.
So, this is my source data, I have sales for each day. Currently, the data goes
till the end of January. This again is formatted as an Excel table and is called "TSales." I want
the user to be able to input a "From date" and a "To date", and my chart, in this case a line chart,
to automatically update to show the range that's selected. I'm going to need a data preparation
table for that. And because I want to take advantage of the new Dynamic Array functionality
in charts, I am going to use a formula that spills. Which formula do you think that is? I'm sure you
guessed it, the FILTER function. This time, though, we have two things to check for. But before we get
ahead of ourselves, we need the array. The array is what we want returned which, basically, is our table,
because we want both date and the sales value. Next up, what do we want to include? Well, we have two conditions. The first condition is that the date is greater than or equal to this date, and the
second condition is that it's less than or equal to this date, because is we want the date that's
typed included.
Here's what you need to do when you have more than one condition in your filter
argument, and that condition is an AND condition. You start off by opening your bracket, then you
start with the first condition. We need the date column from our source table and we're going to
check whether this is greater or equal to this date. Okay, then we close the bracket. Now what you
need is the asterisk sign because we're going to multiply this condition with the second condition,
which we're going to include in brackets as well. And I'm going to show you why in a second.
How do
we start the second condition? Well, we go back to our date column. This time, we're going to check if
it's less than or equal to this one. We're going to close the bracket, and in case they include some
dates that we don't have in our source data, we can put a simple message "NA", close the bracket, press
Enter, and we get our dates show up here. Although our dates aren't formatted properly. They
are formatted as numbers and we're going to update that. So. I'm just going to highlight a wider range.
Let's go and format this as a short date. For the amount as well, just going to select this range
here, and format this to have a thousand separator. Okay, so let's just double check this.
If I go with
the fifth, that's it. I get my dates, go from the third, fourth, and the fifth. Now, let's insert our
chart. Go to Insert, this time, I'm going to insert a line chart. I only get these three dates. But what
if I update this? What if I change this to the 13th? It updates automatically. If I go back to the
8th, only shows me what I need. Now, I did tell you that I'm going to explain this part. Why
are we multiplying? Well, let's take a closer look at what this formula is actually returning. So,
if I highlight this part and I press F9, which allows me to debug my code, take a look at what
it's returning. Lots of true and false values. Why is it doing that? Well, our first check was to see
whether this date is greater than or equal to this. These two dates aren't, that's why we get FALSE. All
the other dates are greater than this one, that's why we get TRUE.
Now, if we go here for our second
condition and we do the same thing, I'm going to press F9 here. Notice we have TRUE for these first
ones, why? Because these dates are less than or equal to the 8th of January, and then we have a lot
of FALSE values. Now, what happens when we multiply these two with one another? Well, let's take a look.
I'm going to press F9 here and we get a bunch of zero and ones. So, when you multiply FALSE with the
TRUE, you get zero. FALSE with the FALSE is zero. The only thing that returns a one is when you multiply
a TRUE with a TRUE condition, and then we get ones. Now our filter knows what to include. It's going to
include the ones only, the TRUE cases. So, it's going to skip these and only include these, in this case.
Now, I'm going to press Escape to leave the formula, so I don't hard code those values in the formula.
Now again, the advantage of using tables is that, my dynamic range here is automatically going to
update and include the new data.
So, if I come to the bottom of this and paste in my February data,
and I update my range here to go until the 10th of Februarym, all of this automatically updates.
My data preparation table as well as my chart. Now, obviously, if you don't want to show your data
here, you just want to show the chart, you are going to keep the data on a separate sheet or you'll be
putting it out of view. I find this to be a cool addition in Office 365. If you have Office 365
but you don't see it yet, you're going to probably see it soon. Unfortunately, it's not available in
the older Excel versions, so you're going to have to use those workarounds. Let me know what you think
about this.
If you have a use case for it, it would be great to hear about it. Just comment below, let
me know. I hope you enjoyed this update. Do like the video if you did and I'm going to see you in
the next one..
