Instructor: All right, pro tip time.
In this demo, we're gonna talk
about how to create dynamic charts using dynamic arrays.
So jump back into your Excel workbook.
We're gonna make one final update
to our combining sort, filter and unique tab.
And essentially what we want to do
is visualize these average salaries by job title
using a bar chart.
But the catch is that we need that bar chart to grow
or shrink depending on the industry selected.
Whether you select an industry like manufacturing
with four job titles, or one like professional services
or technology with many more job titles.
So how do we do this?
Well, let's start by creating a chart
just like we normally would.
Here I've selected operations
so I'm only seeing three job titles.
Let's go ahead and select the titles and salaries
including the headers, and just insert a 2D bar chart.
Okay, so far so good.
We've got our salaries on the X axis.
We've got the three job titles visualized.
But obviously the problem is that
if we change this to something like technology,
we continue to just visualize those first three jobs.
Excel doesn't know that the source data has changed
and therefore isn't visualizing all
of the other job titles outside of those first three.
So how can we tell our chart
to accommodate this dynamic source data, this dynamic array?
Well, the first approach might be to just right click,
select the data, edit the series
and actually go to the cell reference itself
which you can see is fixed at J6 through J8.
And say, you know what?
Let's add a hash.
Tell Excel we're dealing
with spilled range dynamic array here and press OK.
Unfortunately, you can't reference spilled ranges here
in the edit series dialogue box, so that won't work.
But luckily we have another tool that we can use
which is the named range.
And essentially what we're gonna do here
is create two named ranges, one for job titles, one
for average salaries that do point to the dynamic arrays
and then reference those named ranges inside
of our chart to make the chart itself dynamic.
So it sounds complicated, but it's actually not that bad.
We're gonna start
by selecting the first job title here, I6.
I'm gonna go into formulas, open up my name manager,
create a new name here
and let's just name this named range titles.
Okay, and here's the catch.
Instead of just pointing to I6, which I had selected,
I'm gonna say, hey, Excel, here's a pound or a hash mark.
This is a spilled range.
This is a dynamic array.
I want this name titles to represent all
of the rows contained within that array
starting with I6.
Okay, press OK.
And we're good to go.
I'm gonna close that out.
Select J6.
Go back to define name.
Let's call this average salaries.
Add the hash, J6 hash, OK.
And the last step here is just
to swap out my source data references
with those two new names that I just created.
So right click, select data.
First, I'm gonna edit the legend, and if I scroll over,
you can see I still have J6 through J8 represented,
which is those first three salary values.
So instead of J6 through J8,
let's actually strip out everything
after the exclamation point with the sheet reference.
And this is where I can type that named range,
average salaries, AvgSalaries, Enter.
Same thing with my axis labels here, edit.
I'm gonna drag to scroll to the end,
delete everything after the exclamation point.
And this is where I want to drop in my series labels,
the titles.
Okay, and check that out.
That looks good.
Now I've got all
of my technology job titles represented here.
If I change this to something like media,
now there are only two.
Same thing with professional services.
Looks great.
So by using named ranges that point to the dynamic arrays,
we can reference them from our chart
and make the chart itself dynamic.
Now, of course, I've gotta take a minute
and clean this chart up a little bit.
Feel free to follow along if you'd like.
Otherwise, go ahead and skip this.
I'm gonna hold Alt and kind of snap this chart to the grid.
There we go.
Let's change this title
to something a little bit more meaningful,
like Average Salary by Job Title.
That looks good.
Make it bold.
Gonna right click the data series, and I'd like
to give it that same kind of navy blue here, no border.
Let's drop the gap width down a little bit here.
Really, this is just a matter of preference.
And then final change I'll make,
let's add some data labels as well.
Let's format those inside the end.
Make 'em bold, let's make 'em white text.
And now that we have data labels.
We really don't need this X axis anymore.
So I can delete that and boom, there you have it.
We've built a simple little dashboard here
showing average salaries
by different job titles based on a selected industry
all using a handful of dynamic array functions.
So hopefully that's helpful.
Great way to create dynamic charts using dynamic arrays
as your source data.