In a pivot table Excel will place fields with numeric data in the Values section

Your worksheet has lots of data, but do you know what the numbers mean? Does your data answer all your questions? Pivot Table reports can help to analyze numerical data and answer questions about it.

In seconds you can ask questions and see the answers.

With Pivot Table reports, you can look at the same information in different ways with just a few mouse clicks.

Imagine an Excel worksheet of sales information on multiple parcels.   A Pivot Table report turns all that data into small, concise reports that tell you exactly what you need to know.

Before you start to work with a Pivot Table report, take a look at your Excel worksheet to make sure it is well prepared for the report.

When you create a Pivot Table report, each column of your source data becomes a field that you can use in the report. Fields summarize multiple rows of information from the source data.

The names of the fields for the report come from the column titles in your source data. Be sure that you have names for each column across the first row of the worksheet in the source data.

The remaining rows below the headings should contain similar items in the same column. For example, text should be in one column, numbers in another column, and dates in another column. In other words, a column that contains numbers should not also contain text, and so on.

Finally, there should be no empty columns within the data that you are using for the Pivot Table report. We also recommend that there be no empty rows; for example, blank rows that are used to separate one block of data from another should be removed.

When the data is ready, place the cursor anywhere in the data. That will include all the worksheet data in the report. Or select just the data you want to use in the report. Then, on the Insert tab, in the Tables group, click Pivot Table, and then click Pivot Table again. The Create Pivot Table dialog box opens.

Select a table or range is already selected for you. The Table/Range box shows the range of the selected data. New Worksheet is also selected for you as the place where the report will be placed (you can click Existing Worksheet if you don't want the report placed in a new worksheet).

This is what you see in the new worksheet after you close the Create Pivot Table dialog box.

On the left side is the layout area ready for the Pivot Table report, and on the right side is the Pivot Table Field List. This list shows the column titles from the source data. As mentioned earlier, each title is a field: parcel, Neighborhood, and so on.

You create a Pivot Table report by moving any of the fields to the layout area for the Pivot Table report. You do this either by selecting the check box next to the field name, or by right-clicking a field name and selecting a location to move the field to.

If you have worked with Pivot Table reports before, you may wonder if you can still drag fields to build a report. You can.

Tip If you click outside of the layout area (of a Pivot Table report), the Pivot Table Field List goes away. To get the field list back, click inside the Pivot Table layout area or report.

Now you are ready to build the Pivot Table report. The fields you select for the report depend on what you want to know.

Let's start with finding the average selling price by style. To get the answer, you need data about the style. So select the check box in the Pivot table Field List next to the Style field. You also need data about Sale Price, so select the check box next to the Sale Price field. Notice that you don't have to use all the fields on the field list to build a report.

When you select a field, Excel places it in a default area of the layout for you. You can move the field to another area if you want to. For example, when you select Style, it is moved to the Values section and displayed as.   Notice to the left of the Pivot Table Field List dialog box there is a partial table being constructed interactively.   At this point it displays one label - Sum of Style and one amount - 411.  

Clicking the arrow on the right side of that image causes a dialog box to appear with several choices including one to Move to Row Labels, which is where we want this field.

Upon selecting the Move to Row Labels option two things occur.   The label Style is moved to the Row Labels section of the dialog box and the partial table to the left has changed.   It now has a label Row Labels with numbers below it and a label Grand Total below that.   These numbers represent each of the unique values for Style found in the range of data selected for the pivot table.

Since we want to display the average Sale Price by Style, we select the Sale Price field.   It is moved into the Values section with the Sum notation and the pivot table now has values under a label "Sum of Sale Price".  

Sum is the default setting.   There are two approaches to changing that.   You can select value Field Settings from the dialog box that appears after clicking the arrow to the right of Sum of Sale Price in the Values section or in the ribbon at the top of the page.

Either of these approaches causes another dialog box to appear with a list of options.   Selecting Average and clicking OK causes the label in the pivot table to change from Sum of Sale Price to Average of Sale Price and the numbers under that label to change also reflecting the average by style.

Adjusting the Field Settings can change the pivot table to show the number of sales, the maximum sale price, the minimum sale price or the standard deviation.  

Another field can be added to the Column Labels section to show Average Sale Price by Style and some other field.   For example, you can add Bedrooms as a column and show the Average Sale Price by Style and Bedroom count, or change the Field Settings to Count and see how many sales occur in each combination of Style and Bedroom count.

The data in the pivot table can be formatted like any cell in a worksheet.   Highlight the cells to be formatted alike and right click the mouse.   Select Format Cells and adjust the format to personal preference.

Notice there are icons next to the row labels and column labels.   These allow you to adjust the pivot table by limiting the rows or columns that are shown.

This tutorial is an attempt to highlight some of the functionality available in Excel and provide you the user with some of the skills you will need to take IAAO's advanced mass appraisal courses.

Does a PivotTable have a values area?

Which fields will you add to the PivotTable areas?

How can you format the numeric data in a PivotTable?

Can we group numeric fields in row labels of a PivotTable?