- 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. In this movie, I will show you how to pivot your data. My PivotTable is set up very simply: I have the years as my row headers and quarters as my column headers, and I have room revenue as the data for the body of the PivotTable. If I want to pivot the PivotTable, I need to change the order of the fields in the PivotTable Builder.
So I'll go over and activate that. The PivotTable's organization is based on years as the row headers and quarters as the column headers. If I wanted to subdivide my data by year and then by quarter, I could drag Quarter down below Rows, and you see that I have more of a tabular format. I can also drag Year to the Columns header, and I have essentially transposed what I had before. This organization, to me at least, reading horizontally, focuses on quarters and see how they compare year on year, whereas the previous organization, emphasizes years.
If I want to change the organization, I can drag Quarter back to the Field List, and then add Month below Year, so I'll drag the Month field below Year, and now I have my tabular data again, and it's a summary of data across both room types by month for the years, with subtotals and a grand total at the bottom. I can also then add Room Type to the Columns area to see how revenue breaks down across the Cambridge and Piccadilly room types, which are the only two room types listed in my original data set.
But one thing to note is that some organizations don't make a lot of sense. So let's say that I have this organization with year and month providing my row headers and room type providing column headers, and I drag Quarter to the Columns area and put it above Room Type. You can see that I have my data broken down by quarter, but the design is extremely inefficient, or rather the arrangement is extremely inefficient, so I have a lot of white space, because April, May, June, and the other months, except for January, February, and March, aren't in quarter number 1.
Then we have quarter number 2, quarter number 3, and behind the PivotTable Builder is quarter number 4. So if I wanted to include Quarter, then I would need to find another way to do it. So I'll drag Quarter back to the Field List. One thing to note is that if the PivotTable Builder dialog box doesn't appear, say, for example, if I close it, you can redisplay it by going to the PivotTable Analyze contextual tab that appears when any PivotTable cell is selected, and click the Field List button.
Doing so brings the PivotTable Builder back, and you can work with your PivotTable normally.
Author
Updated
2/18/2016Released
8/7/2015- Customizing the Ribbon
- Formatting worksheets, cells, and cell data
- Sorting and filtering data
- Working with formulas
- Detecting formula errors
- Creating charts
- Importing data
- Inserting objects and graphics
- Using PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
Duration
Views
Q: This course was updated on 02/18/2016. What changed?
A: We updated one tutorial, "Managing objects using the Selection pane." The new Selection pane, released in a January 2016 Office update, allows Excel for Mac users to more easily rearrange worksheet and slideshow objects.
Related Courses
-
Introduction
-
Welcome59s
-
-
1. Getting Started with Excel
-
Getting help in Excel3m 11s
-
2. Managing Workbooks
-
Setting workbook properties2m 45s
-
3. Working with Worksheets, Cells, and Cell Data
-
Creating named ranges5m 58s
-
Creating an Excel table5m 9s
-
4. Sorting, Filtering, and Managing Worksheets
-
Sorting worksheet data3m 6s
-
Creating a custom sort order4m 49s
-
Filtering worksheet data3m 55s
-
Managing worksheets5m 4s
-
-
5. Summarizing Data Using Formulas and Functions
-
Adding a formula to a cell3m 56s
-
6. Analyzing Data and Formulas
-
Rounding cell values4m 14s
-
Managing scenarios7m 1s
-
7. Formatting Worksheet Elements
-
Managing text alignment4m 46s
-
Copying cell formats3m 29s
-
Managing cell styles4m 10s
-
Managing Office themes5m 45s
-
8. Working with Charts
-
Creating pie charts2m 25s
-
Creating line charts3m 11s
-
Creating XY (scatter) charts2m 38s
-
Creating stock charts2m 42s
-
Adding trendlines to charts3m 31s
-
Creating sparkline charts4m 17s
-
9. Working with External Data
-
Using hyperlinks4m 18s
-
10. Working with Objects
-
Adding and adjusting images4m 58s
-
Manipulating text boxes3m 20s
-
Creating SmartArt graphics4m 19s
-
Creating WordArt2m 55s
-
11. Exploring PivotTables
-
Applying a PivotTable style2m 26s
-
12. Reviewing and Sharing Your Spreadsheets
-
Checking spelling2m 55s
-
Managing workbook comments3m 29s
-
Exporting to other formats2m 48s
-
Protecting a workbook3m 23s
-
-
13. Automating Workbooks Using Macros
-
Running an existing macro4m 31s
-
Recording a macro2m 46s
-
Adding comments to a macro2m 23s
-
-
Conclusion
-
Next steps1m 8s
-
- 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.
CancelTake 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.
Share this video
Embed this video
Video: Pivoting a PivotTable report