Oz du Soleil shows how to pivot data in Get & Transform in Excel 2016.
- [Instructor] Now I'm going to show you how to pivot data in Get & Transform, and if you want to follow along, pull up the exercise file called Pivoting Data. I'm in HR, and we had a volunteer week where employees were allowed to take time to go out and do good in the world, and now the CEO wants a summary of who actually did go out and do good. And look at this data. It's this tall list. And notice, on Monday, Cynthia has two entries, and she did two hours and three hours.
We'd like to see a five for Cynthia on Monday, not these two entries. It would be better also if we could have the days of the week going across the top instead of this tall column. Right now we're thinking about what kind of report would make sense for the CEO to look at. Let's do that. The data is already in the table and we're going to pivot this data in Get & Transform. Go to Data, From Table.
Here's our raw data, and let's think about what we want. We want the day going across the top, the reps along the side, and then the hours volunteered, those are going to be the values in our data. To pivot this, highlight the column that we want to have as column headers. Now we go to Transform, Pivot. It's telling us use the names in column Day to create new columns, exactly. What do we want as our values? Select those, Hours Volunteered, OK.
And that's exactly what the CEO wants to see. Let's close and load that to the workbook. Close and Load. Here's our data, let's look at it closer. And there, we see Cynthia did five hours on Monday, just one entry.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins