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.
AuthorOz du Soleil
- 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
Skill Level Intermediate
1. What Is Get & Transform?
2. Working with Queries
3. Working with Columns
4. Working with Formulas
5. Pivoting and Unpivoting Data
Group data2m 31s
7. Appending a Query
8. Merging Data with Joins
9. Putting It All Together and Applying Your Learning
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.