Effortlessly Create Dynamic Charts in Excel: New Feature Alert!

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..

test attribution text

Add Comment