A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.
Create a PivotTable
Select the cells you want to create a PivotTable from.
Note: Your data shouldn't have any empty rows or columns. It must have only a single-row heading.
Select Insert > PivotTable.
Under Choose the data that you want to analyze, select Select a table or range.
In Table/Range, verify the cell range.
Under Choose where you want the PivotTable report to be placed, select Newworksheet to place the PivotTable in a new worksheet.
Building out your PivotTable
To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.
Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values.
To move a field from one area to another, drag the field to the target area.
Typically, the data you use as the source for PivotTable is something that's organized like a list.
Perhaps like what we're seeing here on the screen.
If you're trying to analyze this information, you certainly have some tools available on the Data tab.
Now most of them don't let you to analyze the data so much, but at least allow you to view it in different ways.
But we need to talk a little bit more about what a PivotTable is, how we create it, and also about our source data.
You can't just say, "I've got a bunch of data, therefore I've heard so much about this PivotTable feature, I'll just see what a PivotTable can do with it."
The data that's going to be the source for your PivotTable has to be organized as a list. Now possibly you've converted it into a table.
That's not a pre-requisite, although it could be helpful.
The list that we're looking at here must not have any empty rows within it, nor empty columns.
Your headings should be in a single row up top.
If you have any interim subtotals in here, get rid of them.
Get rid of those empty rows. Click on any cell within here.
On the Insert tab, the left most button, many times you'll simply click that button and then click OK.
The default location for a PivotTable will be on a new worksheet.
And that's what we're going to do the first time around.
Usually you want to give a quick look glance here at the Table/Range.
Has Excel figured out the extent of your data? It certainly has here.
It's saying I see data from cell A1 in the upper left hand corner down to cell H910 in the lower right. We'll click OK.
We're about to see a new worksheet with a PivotTable placeholder ready for us to create a PivotTable.
Placeholder over on the left hand side.
On the right side, you'll see a dialog box, PivotTable Fields.
Now it might be docked or anchored on the right side, and that's fine.
You can leave it there. If you wish you can click an drag it's title bar.
Drag it out here. You can possibly resize it.
Now you don't really have any rational for doing this just yet, but as you work with PivotTable, sometimes it's handy to have this closer to the data.
But you can move this around a little bit if you wish.
If you want it to go back to its docked position, simply double click its title bar.
And for a while I'll just leave it there.
There we are. We see in the top portion here, all of the field names that we saw earlier.
Now all I have to do to get this PivotTable is first click Salesperson.
And two things are going to happen on the screen.
Far left, we see an alphabetical list of all our sales people.
Down below the PivotTable field list here, there's a section called Rows, sometimes these are referred to as Row Labels.
And over in the PivotTable, which for the moment has no numbers, we see that term Row Labels as well. I'm now going to click the box for Number of Items.
We're tracking number of items sold. That's a numeric field.
It will automatically appear in the PivotTable. We see it off to the left.
And also below here, in a section called the Values Area.
The wording might seem a little strange, but here are those totals.
Remember the 7997 that we saw, and you can see who our best sales person is.
It's Sam Ramey, right here.
Third thing we need to do is a drag.
Simply drag whatever field we want into the Columns area.
And so if we want to see the products, we'll drag Product into the Columns area, and there's our PivotTable.
If we go back to the actual worksheet we started with here, the creating worksheet, we see off to the right, ultimately the same numbers.
Of course you're not going to remember them all, but take Dotty Kirsten here, 209, 118, jump back over.
Here we are 209, 118. And all the other numbers are accurate too.
We've got a PivotTable.
Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.
Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.
Get unlimited access to over 4,000 video courses.
Receive personal recommendations based on your LinkedIn profile.
Stream courses from your computer or mobile device.
Take courses for every level – beginner to advanced.
Practice while you learn with quizzes, exercise files, and coding windows.
Choose a plan for yourself or your entire team.