The advantages of a “PivotTable”

One of the most powerful tools in Excel is the “PivotTable”.
In a “PivotTable”, we can arrange complicated data in a meaningful way.

A “Pivot table” is a summary report created dynamically from a database or a table.
This database or table, may be in an “Excel” workbook, or an external database file such as “Access”.
A “PivotTable” can answer questions such as,
Which store is the most profitable, and on which days?
What articles are best sold and in which stores?
What days of the week get the lowest turnover?
and so on.

Create “PivotTable”

We can create a “PivotTable” in two ways, the new method, and the old, as in previous versions of “Excel”.
To create a “PivotTable” in “Excel 2007” in the new way, you place the mouse pointer in the table, choose the “Insert” tab in the “Ribbon”, click “PivotTable” and in the drop-down menu and choose “PivotTable” again.

In the “Create PivotTable” dialog the range is already filled in.
This is because we selected a cel in the table, before we clicked the button “Pivot table”.
If you have not done this, or still have to adjust the range, click the “Collapse” button.
To create a PivotTable from another file, check the box for the text “Use an external data source”.
We will see more on this later, let us first finish a simple PivotTable.

If we wish to create the “PivotTable” in a new worksheet, this is checked by default, click the OK button.
This opens the PivotTable in a new worksheet.
At the top of the “Ribbon”, we find two new tabs, “Options” and “Design”.
I will explain the “Options” tab later.
The “Design” tab is quite simple.

On the left side of the “Worksheet” we find our “PivotTable”.
On the right side we find the list of the different “PivotTable” fields.
I give the new worksheet a more appropriate name, by right clicking the tab and selecting “Rename”.
This keeps things a bit more manageable.

Now to insert a field in our PivotTable, we only have to select the field names in our field list.
This places a check mark for the field name, Excel automatically puts them down in “Pivot table”.
A word of explanation about the different areas:

• “Report Filter”: Table fields which are placed in this area can be filtered. In other words, you can change the field so that only the data which meets certain conditions is 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 tittles.
• “Values”: Table fields placed in this area will be added by default.

Important:

To see the the different fields in the field list you must select a cell in the “PivotTable Field”.

In this first example I want to see sales by product in the different regions.
So I click the Region field, Product, and Sales in the table field list.
Excel automatically places the fields Region and Product in the”Row Labels” area and the “Sales” in the “Values”area.
Excel will always places the fields that contain text in the “Row Labels” area, and fields with a number, in the “Values” area.
The fields in the “Values” area are used for performing calculations such as sum or average, etc.
This does not mean that you can not put any text in the “Values” area.
Excel will calculate the number, and display.

To display the “Product” as a column instead of row, click the downward pointing arrow and choose “Move to column labels.”
This shows a consolidated view of our products by region.

And this is actually the intent of a PivotTable.
To place different fields in different positions to understand the data in your table.
With a few clicks, you can analyze the data from your table in the manner that best suits you.

I will explain you how to switch to the old way..
Place the cursor in the table, select the “Insert” tab in the ribbon, click the “PivotTable” button and choose “PivotTable”again from the drop-down menu.
Choose the “Options” tab, and click the “Options” button, on the left side.
In the “PivotTable Options”, choose the “Display” tab.
In the “Display” tab, click the check box for the text “Classic PivotTable layout” so this is checked.
Click OK.

Featured Courses

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