This document gives a basic understanding (beginner level) on how to create and use pivot tables in Excel.

This tutorial is based on a spreadsheet containing the following columns and corresponding fictitious data

Country,Salesperson,Order Amount,Order Amount, Order Date,Order id. User can enter own data to better understand the same. The spreadsheet would look as below. This spreadsheet is referred to as the practice session workbook in the tutorial.

When you scroll down to read further practice steps, the text in the layout area will disappear. That text will reappear when you click in the worksheet.

A new worksheet, "Sheet1," is inserted into the practice session workbook. The new worksheet contains three items: the PivotTable Field List , the layout area for the report, which contains separate outlined drop areas onto which you'll drag and drop fields, and the PivotTable toolbar. If you can't see the field list, which looks like this:

click in the outlined area. If you still don't see the field list, click the Show Field List button on the PivotTable toolbar. (The toolbar should appear either floating on the worksheet or docked to one side.)

Note     If the PivotTable toolbar is not visible, on the View menu, point to Toolbars , then click PivotTable .

If you wish, you can drag the field list and the toolbar to different locations as you complete the following exercises.

Exercise 2: Lay out the report

Now you'll lay out the report to find out how much each salesperson has sold. The report layout changes appearance as you drop fields into the layout.

drag the Salesperson field to the drop area labeled Drop Row Fields Here .

You can drag by selecting either the field name or the button in front of the field name. If you drop a field in the wrong drop area, just drag it to the correct drop area.

Note     Once field names have been dropped, they remain on the list but change to boldface.

When you drop a field in the drop area for data items, the colored outline disappears and you see the report, which displays a total for each salesperson.

The first rows of your PivotTable report should look like this:

To see the report without the empty drop area for page fields at the top of the worksheet:

Click anywhere outside the layout area. Click in cell C4, for example. The page fields area disappears, and so does the PivotTable Field List.

Exercise 3: Pivot the report

In this exercise you'll swing your data from a row orientation to a column orientation.

1. Click the Salesperson field heading (in cell A4).

2. Drag and drop the Salesperson field heading to cell B3, which is the cell just above "Total." When you drag, as long as you've got the gray box cursor and you point that to your destination cell, you're OK.

The report changes appearance to show the names of the salespeople in column, rather than row, orientation.

This particular data isn't very easy to read in column format, but column format could be very useful with other data. You need to know how to move a field from one orientation to another.

Next, move the Salesperson field back to a row orientation

Here's how     Click the Salesperson field heading, drag it to cell A4, and then drop it.

Exercise 4: Create page views

Now you'll try something new: creating page views. In this example, you'll display the order amounts for salespeople, with totals for different countries on different pages. You'll drag and drop a field to the drop area labeled Drop Page Fields Here.

Drag and drop the Country field from the PivotTable Field List to the Drop Page Fields Here area.

Note: If you can't see the field list, click in the layout area. If you still don't see the field list, click the Show Field List button on the PivotTable toolbar.

Now the report looks like this:

The name of the visible page appears in the cell beside the Country field. In this example, the visible page has data for all countries in the list.

To view a different page, click the arrow beside the page name, select a page, and click OK.

You can view three pages. The amount sold per country appears for each salesperson on that country's page, and totals for both countries together appear on the "(All)" page.

Exercise 5: Drag fields off the report

We told you that it's easy to change your mind about which information belongs in a report. Now you'll see how to remove fields. Before you start, notice that the fields now displayed in the report view are shown in bold letters on the PivotTable Field List . This helps you to keep track of which fields you've already dragged and dropped to the report.

1. Click the Country field heading on the worksheet (cell A1), then drag and drop it anywhere outside the outline. For example, drop it in cell D2.
   Note: A red "X" appears beneath the insertion point after the field is dragged outside the layout area. The X means that the field is removed from the report layout.

2. Click the Sum of Order Amount field heading on the worksheet (cell A3), then drag and drop it anywhere outside the outline.

3. Click the Salesperson field heading on the worksheet (cell A4), then drag and drop it anywhere outside the outline.

The outline looks as it did before you started. None of the names on the field list is in bold letters, because none of the fields in the list is used on the PivotTable layout area.

So if you feel like starting all over again, it's just this simple. You can have a clean slate in a couple of seconds.