Instructor: Let's talk about how to use formatting
and specifically conditional formatting
when we're dealing with dynamic arrays.
So we're back in our Excel workbook,
we're still in the combining sort filter and unique tab.
Basically what we wanna do here
is bring this average salary data to life,
in this case using some sort of visual
or conditional format to really showcase
the relative differences in salaries
across the job titles displayed here.
And to show you how this works, let's actually reselect
so that we get a shorter list of job titles
like retail, right?
We've got four job titles here
and I don't want to overwrite
or format my existing salary values here in column J.
I'd like to preserve those
and basically just duplicate them here in column K.
And that's where I'm gonna apply my format.
And specifically, we're gonna use data bars here
to create that visual context.
So the simplest way to duplicate these values
isn't to write a new formula again.
It's simply to reference this range.
And you could type J6 hash
or you could make the full selection.
Excel should recognize it as a dynamic array.
Press enter.
And there we go.
So now we have our duplicated values
and these are the values that I want to apply
my data bars conditional format to.
So just like anytime we create a conditional format
we wanna select the range of cells impacted by the format.
In this case, these four cells from row 6 to 9,
gonna go to home, conditional formatting.
In this case, we want data bars.
And click the more rules option to open up
the dialogue box.
Here you can customize the color.
You know, let's go with a nice navy blue.
And this is the key,
because we already have our salary values represented
in column J,
we don't need to show the values again here.
That would be redundant.
So what we can do is check this box that says,
hey, we just wanna show the data bars themselves.
Press OK.
And there we go.
We're showing very clear differences in salary
between these four retail jobs.
But as you might expect, because we only selected
those four cells right off the bat, as we update this
to different ranges, anything with a larger number
of job titles means that our conditional format
drops off, right?
So anything beyond four job titles.
So you may be thinking, well, no problem.
Let's go ahead and just jump back
to our conditional formatting rules.
You can see clear as day, it's got a hard coded reference
to cells K6 through K9.
What if we just typed K6 hash to tell Excel
that we're dealing with the dynamic array here?
Press OK.
Seems to work fine, right?
The formatting did apply to the entire range of job titles.
And as you might expect
for anything with fewer job titles, it's all looking good.
So we might be able to call it a day here
and think our job's done.
But the important thing to remember is that if we go back
into that dialogue box, manage rules, check it out,
Excel actually converted that spilled reference
that I typed in to a fixed reference again.
Based on the current size of that dynamic array,
which extended from K6 through K17.
And if that is your largest range of possible values,
which in this case it is, you might be good to go.
But the problem here, let's press OK,
is that if a new job title appeared like web developer 2,
our formatting doesn't capture that.
In other words, Excel doesn't preserve
that spilled reference
and keep the formatting dynamic.
It will automatically convert it back to a fixed range
which kind of limits the flexibility here.
So let's press CTRL + Z to undo that.
Basically, at this point in time
this is just a limitation of Excel.
The dynamic array behavior hasn't been applied
to that conditional formatting dialogue box quite yet.
So the workaround, just like we showed here
is to identify the largest potential range of cells
that might take the formatting
and use that range as your reference
in your conditional formatting rule from the start.
It's a little bit of a workaround, but for now
that's the best way to make this work.
So there you go.
Basic example of applying conditional formatting
to a dynamic array in Excel.