Now we're going to get a chance to create our very own PivotTable and we'll create it from scratch. Before we do, let's just take a quick look at our data. We have orders coming in from different areas of the country. The orders are coming in at different times of the year. We see we are selling different size products, 1 liter bottles, 2 liter bottles and so on, and we can see in Column E, how many bottles we're selling in each order. Now, if you have thousands of rows of data like this, it could be hard to understand what's going on. So the PivotTable will summarize it and make some sense of this.
PivotTables are something that we insert, so we go to the Insert tab and over here at the beginning of the Ribbon bar, click that PivotTable button and from the flyout, choose PivotTable. That brings up the Create PivotTable dialog box. So the first thing is that Excel is correctly guessing where our data are. And you can see the first row we have our Table/Range is correctly listed over here. Now, we want to put the table on a new worksheet. You don't have to, but it's generally a good idea. Now if you look down here in the lower left, we only have one tab in the worksheet anyway.
So let's leave New Worksheet selected and click OK. So now, you can see we still have our data. That's still here and the PivotTable is going to be on Sheet 1. You could rename that if you want. So over here on the left is where the PivotTable will go and over on the right, we have the panel that will let us construct the PivotTable. Also take a look at the Ribbon bar. We have a PivotTable Tools section and we're right now in the Options tab. So what we're going to do is we're going to use the field list here to construct the PivotTable. So let's start off with the row labels and what we want to do is let's take the region and just with your mouse, you see you get the four headed arrow, just drag down into this box here.
So immediately as we start creating the PivotTable, we have our row labels. Some of the other parts of that structures disappeared. Don't worry. It's going to come back in a moment. Second thing we want to do is figure out our column labels. So the column labels will be the size of the products. So take this Size and drag down here into the Column Labels. So now we have the basic structure of our PivotTable. So the row labels are here, column labels are here, and in the middle, this'll be the volume how many bottles are we selling. So let's go over and take Quantity and drag Quantity down here into the Values box.
So now we can see all right the Southeast region is buying over 40,000 or almost 41,000 bottles of the 1 liter size. We can also filter this by month, so let's take Month and drag that down here and into our Report Filter. So now we have that on the top and if you've done this, congratulations, you are now the proud parent of your very first PivotTable.
Author
Released
6/9/2010- Copying and pasting techniques
- Working with formulas and functions
- Dealing with formula errors
- Creating lookup tables
- Naming cell ranges
- Formatting data and worksheets
- Finding and replacing data
- Creating SmartArt diagrams
- Creating charts and PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
Duration
Views
Related Courses
-
Excel 2010: Tips, Tricks, and Shortcuts
with Dennis Taylor3h 43m Intermediate -
Excel 2010: Charts
with Dennis Taylor3h 38m Intermediate
-
Introduction
-
Welcome53s
-
-
1. Getting Started
-
Touring the interface3m 38s
-
2. Worksheet Basics
-
Creating a worksheet5m 23s
-
Targeting large data groups4m 26s
-
-
3. Excel Formula Basics
-
Entering data in a worksheet3m 22s
-
Adding numbers manually5m 1s
-
Adding a whole worksheet1m 48s
-
Using IF4m 49s
-
Using SUMIF and AVERAGEIF4m 15s
-
Naming and using cell ranges3m 45s
-
4. Essential Formatting
-
Adding pictures and shapes7m 19s
-
5. Advanced Formatting
-
Inserting SmartArt6m 54s
-
Applying built-in styles3m 16s
-
Creating and sharing styles5m 33s
-
Using templates4m 9s
-
-
6. Printing Preparation
-
Making the pieces fit4m 57s
-
Printing and PDFs4m 34s
-
-
7. Large Excel Projects
-
Finding and replacing data3m 12s
-
Managing worksheets7m 23s
-
-
8. Collaborating with Others
-
Setting workbook permissions6m 44s
-
Sharing a workbook1m 25s
-
Tracking changes3m 5s
-
Saving files in shared locations10m 29s
-
9. Exploring Excel's Database Features
-
Creating lookup tables6m 3s
-
10. Analyzing Data
-
Working with Goal Seek5m 29s
-
Using scenarios in formulas5m 28s
-
11. Advanced Analysis with PivotTables
-
Discovering PivotTables2m 22s
-
Creating a basic PivotTable2m 46s
-
Modifying a PivotTable6m 57s
-
-
12. Creating and Using Charts
-
Choosing chart types1m 55s
-
Inserting Sparklines3m 54s
-
Creating a column chart3m 23s
-
Modifying a column chart5m 47s
-
-
13. Creating and Using Macros
-
Understanding macros3m 5s
-
Editing a macro6m 50s
-
-
14. Customizing Excel
-
Customizing the Ribbon bar8m 44s
-
Setting Excel options8m 19s
-
Conclusion
-
Goodbye16s
-
- 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: Creating a basic PivotTable