What are “Pivot Tables”

According to “Microsoft” Pivot Tables are “interactive tables where you can quickly combine and compare large amounts of data. These tables allow you to rotate rows and columns from the source data in different ways or to summarize and view details of key areas.”

Simply stated, I think a Pivot Table is a table where you can compare information by manipulating and filtering data.

Because of its unique features for comparing and moving data, we can easily analyze information in a fairly quick way.

 

Create a “Pivot Table”

To create a “Pivot Table” in “Excel 2010”, you place the mouse pointer in the table, choose the “Insert” tab in the “Ribbon”, click the “Pivot Table” button and in the drop-down menu that appears, again choose “Pivot Table”.

 

In the “Create Pivot Table” dialog that appears, the “Range” is already completed (1).

This is because we have first selected a cell in our table. If we had not done this, or if you still have to change the “Range”, click the “Folding” (2) button.

To create a “Pivot Table” from another file, check the “An external data source” box for the text.
More on this later but, let us first consider a simple “Pivot Table”.

If we wish to place this “Pivot Table” in a new “Worksheet” (3), which is checked by default, we click the OK button.

 

This opens a “Pivot Table” in a new “Worksheet”.
At the top of the “Ribbon”, we find two new tabs namely, “Options” and “Design”.
About the “Options” tab, I will expand further when I discuss a particular option.
The “Design” tab is quite simple, this too will be discussed later.

 

The first thing you do it to give the new “Worksheet” a more appropriate name by right-clicking it and selecting “Rename”. You can also double-click the sheet name. This keeps things a bit more manageable. “Pivot Table” seems like a logical name (1).

On the left we find the area where our “Pivot Table” will be located (2).

On the right we find on the top of the box the different “Pivot Table” Fields (3) and on the bottom, the different “Pivot Table” areas (4).

 

A field that is added to an area is included in the “Pivot Table”. To add a field to the “Pivot Table”, we click on checkmark near the field names in the field list.

When a check mark for the field name is clicked, “Excel” automatically puts the field in an area.

A word of explanation about the different areas:

• “Report Filter”: Table fields which are placed in this area can be used to filter “Pivot Tables”. In other words, you can    change this field so that only the data which meets certain conditions are shown.
• “Column Labels”: Table fields placed in this area are displayed as column headings.
• “Row Labels”: Table fields placed in this area are displayed as row titles.
• “Values”: Table fields placed in this area will be added by default.

 

Important:

To see the following areas in the field list, there must be a cell selected in the “Pivot Table Field List”.

 

In this first example I want to see sales, by product in the different “Regions”. So I click the “Region”, “Items”, and “Total” fields in the table field list.
“Excel” automatically places the “Region” and “Items” fields in the “Row Labels” area and the “Total” in the “Values” area.
Results are shown below. This is not bad, but it can be made clearer.

 

By dragging the “Items” field of the “Row Labels” area to the “Column Labels” area, the data in the table turns and everything looks much clearer. That is exactly the intent of a “Pivot Table”. Data can be moved and analyzed in a clear way .

 

For your information:

“Excel” will always place the fields that contain text in the “Row Labels” area while placing number fields in the “Values” area.
The fields in the “Values” area are used to perform calculations such as SUM or AVERAGE, etc. This does not mean that you cannot put any text in the “Values” area. “Excel” will calculate the number and display.

Featured Courses

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