Instructor: All right, next up
we're gonna talk about the transpose function.
And just like frequency, this isn't a new function,
but it's one that's become more user friendly
and more flexible with this new dynamic array functionality.
So transpose is pretty straightforward.
It just flips a vertical range of cells
to a horizontal range or vice versa.
In other words, it turns rows into columns
or columns into rows.
And from a syntax perspective, very simple,
just one single argument.
And that's the array of cells that you want to transpose.
So to see what this looks like visually,
consider this product and category-level data
in columns A through C.
we can use a transpose function
and reference that array of values from A1 through C9,
and it's gonna produce something that looks like this.
We're basically flipping that original array
by transforming each column into a row
and each row into a column.
And a nice little pro tip here.
Transpose is a great way to create pivot table-style views
where you can break down different values
by both row labels and column labels
to slice and dice your data for exploratory analysis.
And that's exactly what I'm about to show you
in this Excel demo.
So let's jump into our array formulas workbook
and practice applying this transpose function
to some of our Glassdoor data.
Alright, here we are in our workbook.
We've moved on to the Transpose tab.
We've got the exact same raw data
that we're getting used to working with at this point.
Got the industries, the job titles, the cities,
the salary, and the growth rates.
So you can see I've got a dropdown selected here
with a unique list of industries.
And basically what I wanna do
is create a view where I can list out
the unique job titles for the selected industry,
basically as row labels, here in column G,
and list out each of the cities,
essentially as column labels, here in row five.
And once I've done that,
I'd like to use an average IFS function
to compare average salaries
for every combination of job title and city.
And this is gonna be a really helpful exploratory tool
that users can use to start to tease out trends
and patterns in this dataset.
So let's go ahead and start in cell G6.
And what I'm gonna do is try to produce that list
of unique job titles based on the selected industry.
And every time we kind of create one of these
nested dynamic array formulas,
I like to start basically from the inside out.
So I know I need a filter function here
because I'm trying to return a filtered list of job titles.
Okay, I'm almost working from the core of the function
outward from there.
So I'm gonna filter my full list of job titles,
B3 through B842.
And the include argument is my filter criteria.
So which job titles do I want to include?
Well, the ones where my industry, which lives in column A,
is equal to the industry selected in my dropdown, H3.
And if I were to just close the parenthesis and enter that,
you'd see I'm getting health related job titles,
which looks good, but as you know by now,
I'm not quite done.
I need to wrap that all in a unique function.
There we go.
And then last but not least,
just from a user friendly standpoint let's also sort these.
And I don't need any extra parameters
'cause alphabetical would be great.
So there we go.
We've got our unique list of job titles
based on the dropdown.
That's step one.
Here's where transpose comes into play.
I'd like to break down the salaries
for each of these job titles by city.
And the clearest way that I can do that
is to add basically column headers for each city
here in row five.
Just like I created a unique sorted list of job titles,
I know I also want a unique sorted list of cities.
And in this case, I don't actually need to filter
because I want all of the possible cities
here in my column headers.
So I don't need the filter.
All I need is sort and unique.
So let's start there first.
Again, inside out, starting with the unique function.
I want a unique list of cities,
which come from C3 through C842.
There we go.
Kind of lists them out here in column H.
Looks like they already are sorted
but just to be sure we can wrap that in a sort.
Okay, but here's the kicker.
I don't want these to exist in one single column.
I wanna transpose, or pivot, them into one single row.
So I'm gonna take that sort and unique formula
that I just wrote, wrap that all in a transpose,
and that should do the trick.
So press Enter.
There we go.
Same set of cities, transposed into one row.
And let's go ahead just for styling.
Let's make these look like headers.
Maybe give 'em a little bit of a shaded fill.
And we've just built the structure, or the template,
for our pivot-style view.
The last step is to populate the values themselves, right?
The average salaries based on both the title and the city.
So starting here in cell H6,
we're gonna use an average IFS function
just like we've used before.
And the values that I want to average, the average range,
those are the salaries here in D3 through D842.
And now there are two different criteria
that I'll need to meet.
So criteria range one will be my job titles.
That's the first filter that I want to apply.
So criteria range one
is gonna be all my job titles in column B.
Comma over to the criteria one,
which is where my list of filtered job titles lives.
And because that's a spilled range,
I can reference that with G6 pound.
So at this point, we've told Excel
we want the average salaries from column D
where the job titles in column B
match up with the job titles in that spilled range
that I created.
And then by that same logic,
I'm gonna work in my second criteria.
This time, it's the city-based criteria.
So I'm gonna point to all my cities, C3 through C842.
Whoops. Let's go ahead and select that again.
Comma over to the criteria range.
And that's gonna be another spilled range,
but this time it's my horizontal range.
The beauty of these dynamic array functions
is that if I type H5 pound,
Excel knows that's a horizontal array
and it gives me that spilled range reference
without me having to edit anything.
And that should do the trick.
So if I go ahead and close that off,
press Enter, check that out.
We've got a whole list of average salaries
broken down by job titles and by cities.
Really, really powerful stuff.
And because we use those spilled references carefully,
that means that as we select larger ranges of data
like technology,
our formula applies down to the entire spilled range.
So it looks like we're working out just great.
It's dynamic, it's filterable, it's powerful.
I think the last thing that I might do here
is maybe wanna draw attention to,
let's say, the top five overall salaries in this range.
This is a great excuse to use a conditional formatting rule.
I know this is actually the largest set
of jobs available, down to row 17.
So I'm selecting the entire possible scope
of values that I wanna format.
Gonna head to Home, Conditional Formatting.
And let's use Top and Bottom Rules, Top 10.
And why don't we say, let's format the top five items
with the green fill and green text.
Press OK, and there we go.
So product manager roles in Boston,
New York City, San Fran, Seattle, and DC
are the top five roles based on this view.
If we go to operations, for instance,
now it's an operations manager role.
If we go into sales, as you can see,
really nice way to create this pivot-style flexible view
to help draw attention to key patterns and trends
in your raw data.