䉴A PivotTable report is an interactive, crosstabulated Excel report that summarizes and analyzes data—such as database records—from various sources, including ones that are external to Excel.
䉴A PivotTable report summarizes large amounts of data and is especially useful when you want
to analyze related totals—for example, when you want to compare first-quarter room sales in Miami to first-quarter room sales in San Diego, or to second quarter room sales, or to the total room sales. Use a PivotTable report to summarize, analyze, explore, and present summary data.
䉴A PivotTable report enables you to ask questions about large amounts of data in many ways. For
example, you can expand and collapse levels of data in a PivotTable report, and drill down to details from the summary data to focus on areas of particular interest.
䉴To create a PivotTable report, you must first define its source data, specify a location in the workbook, and lay out the fields. After that is accomplished, you can explore the data; change the form layout and field arrangement; change the layout of columns, rows, and subtotals; and change the format.
䉴You can also create a PivotChart, which provides a graphical representation of the data in a
PivotTable report.
To complete this workbook, you will need the following file: 䊏 e04_PivotTable
You will save your workbook as: 䊏 e04_PivotTable_Your_Name
1. Start Excel. Open the file e04_PivotTable, and then save it in your chapter folder as
e04_PivotTable_Your_Name
In this exercise, you will use data in a worksheet as the source data for the PivotTable report. Source data is the list or table that you use to create a PivotTable or PivotChart. In addition to using worksheet data, you can also use an external database or another PivotTable report.
When using worksheet data as your source data, the data should be in a list format, with column titles in the first row. Each cell’s data in the remaining rows should con-tain data appropriate to its column heading. Within the data that you want to analyze on your worksheet, be sure there are no blank rows or columns. When you create the PivotTable report, Excel will use your column titles as the field names for the PivotTable report.
2. Examine the data in the Bracelets and Earrings table. Notice that bracelets are identified by the letter B in the Style# column and earrings are identified by the letter E in the Style# column.
Create PivotTable dialog box Select a table or range option button
Table1 identified as
the range of data
A New Worksheet will be created for the PivotTable report
Figure 1 3. Click anywhere in the table data. On the Insert tab, in the Tables group, click PivotTable.
4. Notice that in the displayed Create PivotTable dialog box, the Select a table or range
option button is selected, and the Table/Range box displays the range of data from the Excel table identified as Table1. Notice also that the PivotTable report will be placed in a
New Worksheet. Compare your screen withFigure 1.
Each column of the source data becomes a field—fields summarize multiple rows of information from the source data. Names of the fields come from the column titles. Be sure that below a column title, the cells in each row contain similar items; that is, a column that contains numbers should not contain text or a date.
5. In the lower right corner of the Create PivotTable dialog box, click OK. Notice that on the
left side of the new worksheet, the layout area displays; on the right side of the table, the
PivotTable Field List displays, which shows the column titles from your source data.
The process for creating a PivotTable report involves selecting fields from the
PivotTable Field List and moving them to the layout area. There are several techniques to get the fields you want from the PivotTable Field List to the layout area.
The layout area must be active (a cell within the layout area is active) for the PivotTable Field List to display.
6. Consider the question How many of each gemstone item are in stock? Then, in the PivotTable
Field List, click the check box for the Gemstone field and the Quantity in Stock field.
Check boxes selected in field list
Summed data displays in layout area
Figure 2
You need not 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; then, you can move the field to another area if you want to do so. For example, you can change a field from a column area to a row area.
The data in the Gemstone field, which is text, automatically displays as rows on the left side of the report. The data in the Quantity in Stock field, which contains numbers, dis-plays in an area to the right.
7. Notice the heading over the Gemstone data indicates Row Labels and the heading over the
Quantity in Stock data indicates Sum of Quantity in Stock.
Here you can see, for example, that there are 117 Red Coral items in stock.
Sum of in the heading is added because Excel uses the Sum function to add up fields
that contain numbers.
You can select the check boxes in the field list in any order—Excel will automatically put them in the right place. That is, fields without numbers will be placed on the left, and fields with numbers will be placed on the right, regardless of the order in which you select them.
8. In cell A3, click the Row Labels arrow, and then notice that you can apply various filters
and sorts in the same manner you do with any other Excel table. Click Cancel to close the menu.
9. Consider the question How many of each gemstone item were sold? In the PivotTable Field
List, click to deselect the Quantity in Stock check box, and then select the Number Sold (May Regional Sales) check box. Notice that for each gemstone item, the total number sold
Totals for each gemstone display
Figure 3
10.In the PivotTable Field List, select the Metal check box. Notice that under each gemstone,
the total number sold in each metal category displays; for example, for Blue Topaz jewelry items, 203 were sold that were set in Gold.
11.In the PivotTable Field List, deselect Gemstone and Metal. Click the Metal check box, and
then click the Gemstone check box.
Categories collapsed
Figure 4
By collapsing categories, you can focus on specific information.
You need not be concerned about building a report incorrectly. You can see that Excel makes it easy to try things out, and to see how data looks in different areas of the report.
13.Save your workbook. Submit as directed, and then Close the workbook and Close
Excel.
䊏You have completed More Skills 13
12.In cell A4, to the left of Base, click the small minus sign to collapse the category. Then
