PivotTables are powerful and flexible Excel analysis tools. A PivotTable lets you rearrange, sort, and filter a data set on the fly so you can analyze it from several different perspectives with a minimum of effort.
- [Instructor] Pivot tables are a powerful and flexible Excel analysis tools. A pivot table lets you rearrange, sort, and filter a data set on the fly so you can analyze it from several different perspectives with a minimum of effort. In this movie, I will show you how to create a pivot table from an Excel table that is stored in your workbook. My sample file is the Create Pivot workbook. You can find it in the chapter 9 folder of your exercise files collection. This workbook currently contains a single worksheet and on it I have an Excel table with order data.
And you can see that I have 10 columns of data, order numbers, date of purchase, and so on, all the way over to the total for a particular line. While this data set is detailed and complete, it's difficult to summarize it in an Excel table. It makes more sense to create a pivot table. To do that, I will make sure that any cell within the body of the table is selected, go to the Insert tab of the ribbon, and then click Pivot Table.
Doing so displays the Create Pivot Table dialog box and it identifies the Orders5 table, which is the table that I had created here, so the data range is correct. And then I want to put the pivot table on a new worksheet so I will leave that radio button selected. I could, if I wanted to, put it on an existing worksheet and select the top left corner cell for it, but I'll just stay with a new worksheet. I'll click OK, and there is my pivot table on a new worksheet as well as the Pivot Table Fields task pane on the right.
I will increase the zoom level of the pivot table a bit so you can see its contents more easily. Let's summarize what we have. On the left is a blank pivot table, there's no data. And on the right we have the name of the fields, order number, date of purchase, stock keeping unit, quantity, and so on. And we also have four areas. We have Columns, which provides column headers. Rows, the rows headers. Values, which provides the values for the center, or data area, of the pivot table.
And finally, Filters, which don't change the arrangement of the pivot table, but allow you to limit the data that's displayed. I will start by displaying some fields inside of the pivot table to create an original arrangement. So I will go up to the field list here and I'll scroll down. I'll put the total in the Values area, and you can see that I dragged it from the field list to the Values area, and I get the sum of my total. Let's say that I want to have the product category for the columns, so I'll click product category and bring it down here.
There we go for that, and then let's say I want to break it down by state. So I will scroll up and have customer state. I'll put that in the Rows area. And what I see is that my data is broken down by state and then on the column side I have different categories, batteries, grid tie inverters, landscape lighting, and so on. So as you can see, the data that I had in this huge table has now been summarized effectively using a pivot table.
- 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.