The real power of a PivotTable comes out when you want to rearrange your data dynamically. A task that would take several minutes if done by hand takes just a few seconds when you summarize your data in a PivotTable.
- [Instructor] Creating a pivot table allows you to reorganize your data on the fly. In the previous movie, I showed you how to create a pivot table, and in this movie, I will show you how to rearrange those contents by pivoting it's fields. My sample file is the pivotPT workbook, and you can find it in the chapter nine folder of the exercise files collection. In this workbook, I have a pivot table on sheet one and there you can see that I have a cross tabular arrangement. Where I have state names in the row labels area and product categories in the column labels area.
And at the intersection, for example here, for B6 which is Alaska and batteries, shows the total value of sales that were made from Alaska for batteries. Let's say that I want to change the arrangement of these fields. For example, if I want to go by State and then by product category. It makes more sense to put this sort of list in the rows area, so I will drag the product category field from the columns area to the rows area below customer state.
And now you can see the arrangement. I have Alabama and then the three categories from which there are orders, the two categories for Alaska, and the six categories for California. If I want to change the order of these two fields, I can drag customer state below product category. And then I get the category, batteries at the top in this case, and then each of the states from which there were orders for batteries. And of course if I want, I can also put customer state in the columns area.
I prefer not to do that though, and the reason is I only have six categories, but I have 50 States plus the District of Columbia, So I would have 51 columns and only 6 rows, and I prefer to read a lot of data vertically in a list so I'll put customer State back into rows, and product category back under columns. Of course if you want to remove a field, all you need to do it drag it from it's area, in this case rows, and I'll drag it back to the field name list, and I'm down to only the total values for the categories.
One final thing, if you don't see the pivot table fields task pane, for example, if it were hidden, or if you has clicked a cell that was not part of the pivot table. First thing you should do is click a cell within the body of the pivot table. Now if the pivot table fields task pane doesn't come back, for example, if it's been closed. Like I did there. You can go to the pivot table analyze contextual tab on the ribbon, and then click the field list button to bring it back.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- 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.