The usefulness of PivotTables:

Apparently, there are many people who have difficulties in understanding pivot tables, so I will try to explain how they work in a simple manner.

Let me first answer the question “What do you do with a PivotTable.”
That’ a good question, and the answer is quite easy.
With a PivotTable, we can summarize, order, or analyze data in a table.
You can do this with filters, but the advantage of doing this with the help of a pivot table is that the column fields and the row fields can be exchanged (read speed), and thus you get a very different picture of the analysis.
The only thing you need to do is, to try to visualize what you want show in the PivotTable.
When you know that, you’re already halfway there.

Ok, we’ll go further.
What we need to create a PivotTable, is a data table.
This make sense. Right?
Otherwise, this data also cannot be rotated.
By “rotated” we mean, columns become rows and rows, columns.
Did you get it?
Of course you got it.

The first thing you do is select a cell in the table (1). Which cell does not matter, as long as it is a cell in the table.
The second thing we do is select the “Insert” tab (2).
You then click the “PivotTable” button in the ribbon (3).
Select the “PivotTable” option in the drop-down menu, again(4).

In the dialog box that appears, the range is already filled (1).
This is because we had placed the cursor in a cell in the table, before we added a PivotTable .
As an example, I create the PivotTable in a new worksheet (2). If you want to create it in the same worksheet, select this option.
Click the OK button.

In order to make everything a little clear, I give the worksheet with the PivotTable a meaningful name (1). PT1 seems clear.
PT stands for PivotTable. But had you well I think.

Right at the top of the PivotTable, we find a list of all the fields from our table (2).
At the bottom right corner, we have four areas: (3)
“Report Filter”, “Column Labels”, “Row Labels” and “Values”.

If you select a field in the field list, Excel will place it, automatically, in one of the areas.
A field that contains a number will be placed, automatically, in the “Values” area, and a field that contains text will be automatically placed in the “Row Labels” area.

I can imagine that you don’t want all the text fields that you want in your PivotTable, placed in the “Row Labels” area, so I suggest that you click and drag the fields to the area where you’d like to have them. Then, they will be right where you want them to be.
Later, you still have the option of dragging fields to a different area.

Let me start with a very simple example.
I drag the “Name” field to the “Row Labels” (1) area, the “Article” to the “Column Labels” (2) area, and the “total” field to the “Values” (3) area.
This will place all our names in different rows (A), and all our items in different columns (B).
All totals are placed in the middle of the PivotTable (C).
Automatically, a row and a column, i.e, “End Totals” is added to the PivotTable (d).

We can now swap the “Name” and “Item” fields with each other, and we immediately get a very different picture of our analysis in the PivotTable. To swap these fields, you only need to drag the fields from one area to another.

As you would have noticed, the columns are the “Names”, and the rows are the “Items”.
This is actually the intent of a PivotTable.

In the following tips about Pivot tables, I will discuss formatting numbers in a pivot table, how to change labels, filters, how you create groups, along with some other useful things about PivotTables.

FYI: Swotster.com thinks of its students, so you also remember swotster.com

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats