Microsoft® Excel 2000 Step by Step

Author Catapult, Inc. Pages 448 Disk 1 Companion CD(s) Level Beg/Int Published 05/07/1999 ISBN 9781572319745 Price $34.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Related Series Related Books About the Author Support: Book & CD Rate this book

Chapter 8: Creating PivotTable Reports

• Creating a Dynamic Summary with a PivotTable
  • Open and save the file
  • Create a PivotTable report to summarize data
  • Group PivotTable reports by month
  • Format numbers in the PivotTable report
  • Make the PivotTable report more readable using AutoFormat
• Using Data from Other Programs
  • Import a text file
  • Create a PivotTable report from imported data
  • Create a Web Query
• Changing the Data Displayed
  • Add another field to the PivotTable report
  • Change the data grouping
  • Show data by category
  • PivotTable Report Data Orientation
  • Change the PivotTable report presentation
• Showing Cost by Category
  • Remove and add data from the PivotTable report
  • Change the summary function
• Refreshing PivotTable Report Data
  • Add new data
• Showing Details of a Calculation
  • Show underlying data
• Creating Page Reports for Distribution to Individuals
  • Create a page report for each employee
  • Finish the lesson

One key to staying ahead in business is to have the right tool for the right job. PivotTable reports make it easy for you to present the same data in a number of ways. By taking advantage of the flexibility PivotTable reports offer, you can accurately evaluate critical data, such as your company's current and past financial status or existing workload versus available resources. Being able to compare data is just one way that Microsoft Excel 2000 helps you to better manage your company's financial, sales, and operational issues.

In this lesson, you will create and work with PivotTable reports to present revenue data for Impact Public Relations. Because PivotTable reports combine the best of consolidation and subtotals, you can build on the skills you learned in Part 2. Using the PivotTable And PivotChart Wizard—completely redesigned for Microsoft Office 2000—you will create a PivotTable report and organize data stored in worksheets, workbooks, and external programs.

By consolidating data into groups, you can analyze revenue data on a monthly and quarterly basis. In addition, you can modify the layout of the PivotTable report to create a great-looking presentation and show various levels of detail without having to re-create the report. You will find it easy to refresh data to ensure that your report is accurate and up-to-date.

Creating a Dynamic Summary with a PivotTable

Impact Public Relations has three primary media campaigns: radio/TV, print, and Internet. Your business partner tracks revenue data by client representative, company, and media type to maintain a current snapshot of the company's financial condition. He recently updated the Revenue by Medium worksheet to reflect projected financial figures for each medium for the remainder of the year, and he has asked you to summarize the data. By using the PivotTable And PivotChart Wizard, you can easily create a financial comparison and provide detailed revenue information.

PivotTable reports are customizable tables that you can use to organize the fields in a list into new and useful combinations. Unlike a standard table, a PivotTable report lets you pivot rows into columns and columns into rows. This flexibility allows you to define your calculations and rearrange fields. When you pivot the data, Excel presents an entirely new view of the information. You don't have to create new rows and column headings, type the data again, and use the Subtotals command to summarize the results—the PivotTable report does that work for you. No matter how many times you rearrange the data, the original information in your worksheet remains unchanged.

This lesson demonstrates one of the many ways you can evaluate data in a PivotTable report. You can use a single source of data to create several different PivotTable reports. For example, you can evaluate revenue sales first by date, and then you can remove the date field and add the client field to evaluate revenue sales by client.

Open and save the file

1. On the Windows taskbar at the bottom of your screen, click the Start button.

   The Start menu appears.

2. On the Start menu, point to Programs, and then click Microsoft Excel.

   Microsoft Excel 2000 opens.

3. On the Standard toolbar, click the Open button.

   The Open dialog box appears.

4. In the Look In drop-down list, select your hard disk.

5. In the list of folders, double-click the Excel 2000 SBS Practice folder, and then double-click the Lesson08 folder.

6. Double-click the 08A.xls file.

   The 08A.xls workbook opens.

7. On the File menu, click Save As.

   The Save As dialog box appears.

8. In the Save In drop-down list, select your hard disk. Double-click the Excel 2000 SBS Practice folder, and then double-click the ExcelUser folder.

   The original filename, 08A.xls, is displayed in the File Name box.

9. In the File Name box, be sure that the original filename is selected, and then type Revenue_PivotTable_Report

10. Click Save.

Create a PivotTable report to summarize data

In this exercise, you create a PivotTable report to help your business partner assess Impact Public Relations' financial condition. You determine the monthly revenue totals for radio/TV, print, and Internet media. This information will enable you to reallocate resources to capture popular markets and alter future business development efforts to increase sales in fleeting markets.

1. On the Revenue by Medium worksheet, click any data cell, and then on the Data menu, click PivotTable And PivotChart Report.

   The PivotTable And PivotChart Wizard — Step 1 of 3 dialog box appears.

   

2. Be sure that the Microsoft Excel List Or Database and the PivotTable options are selected, and then click Next.

   The PivotTable And PivotChart Wizard — Step 2 of 3 dialog box appears, showing the selected range, $A$1:$G$133, for the PivotTable report.

3. Click Next.

   The PivotTable And PivotChart Wizard — Step 3 of 3 dialog box appears, specifying where the PivotTable report is to be created.

4. Be sure that the New Worksheet option is selected, and then click Finish.

   The PivotTable And PivotChart Wizard dialog box closes. The PivotTable toolbar appears, and the PivotTable report is displayed on the new worksheet.

   Your screen should look similar to the following illustration.

   

5. On the PivotTable toolbar, drag the Date field button to the Drop Row Fields Here box.

6. Drag the Revenue Radio/TV field button—the first Revenue button—to the Drop Data Items Here box.

   A Total column appears.

7. Drag the two remaining Revenue buttons—the Revenue Print and Revenue Internet field buttons—to the data area of the Total column.

   Your worksheet should look similar to the following illustration.

   

Group PivotTable reports by month

In this exercise, you group dates by month to evaluate the financial strengths and weaknesses of Impact Public Relations. By combining day-to-day entries into a monthly group, you can easily evaluate monthly revenue totals for Impact Public Relations.

1. On the Sheet1 worksheet, in the Date column, right-click any cell. On the shortcut menu, point to Group And Outline, and then click Group.

   The Grouping dialog box appears.

   

2. In the By list, be sure that Months is selected, and click OK.

   Your worksheet should look similar to the following illustration.

   

Format numbers in the PivotTable report

In this exercise, you format the numbers to show that they represent dollars.

1. On Sheet1, click cell C4, which represents January's total revenue for the Radio/TV media campaigns.

2. On the PivotTable toolbar, click the Field Settings button.

   The PivotTable Field dialog box appears.

3. Click the Number button.

   The Format Cells dialog box appears.

4. In the Category list, select Currency, and click OK.

   The Format Cells dialog box closes.

5. In the PivotTable Field dialog box, click OK.

   The revenue totals for all of the Radio/TV media campaigns are displayed as dollar values.

6. Repeat steps 2 through 5 for both cells C5 and C6, which represent the Print and Internet media campaigns, respectively.

Make the PivotTable report more readable using AutoFormat

In this exercise, you use the AutoFormat feature to create a great-looking PivotTable report for an upcoming presentation.

1. On Sheet1, click anywhere inside the PivotTable report. On the PivotTable toolbar, click the Format Report button.

   The AutoFormat dialog box appears, displaying choices of presentation styles.

   

2. In the AutoFormat dialog box, click Report 2, and click OK.

   The AutoFormat dialog box closes, and the PivotTable report is reformatted.

   The PivotTable report changes from a nonindented, or cross-tabulated, format to an indented format.

   Your worksheet should look similar to the following illustration.

   

3. Right-click the Sheet1 sheet tab, and on the shortcut menu, click Rename. Type Revenue PivotTable Report and press Enter.

   Sheet1 is renamed.

4. Save your changes.

Using Data from Other Programs

You need to provide a client representative with information about Impact Public Relations' business development efforts. Your business partner uses a text file to track proposals that the company has submitted to clients. He also records the status of each proposal to track the number of projects contracted, which helps determine future staffing needs and expected revenue.

By importing a text file into an Excel worksheet, you can make the data more readable. The Text Import Wizard guides you through the process of selecting a delimiter and formatting the columns of data. A delimiter is a character—such as a space, tab, or comma—that separates fields of data in a database. For example, if you type a word in a text file, press Tab (the delimiter), and then type another word, the Text Import Wizard understands that the first word goes in the first field (usually column A) and the second word goes in the second field (usually column B). Each time it sees a tab in the text file, it assigns the next word in the text file to a new field or column on the worksheet. Once you have imported and formatted the material, you can create a PivotTable report to help summarize and evaluate the data.

Import a text file

In this exercise, you import a text file into Excel and create a PivotTable report to summarize the status of projects that Impact Public Relations has proposed.

1. Click the Revenue by Medium worksheet to make it active.

2. On the Data menu, point to Get External Data, and then click Import Text File.

   The Import Text File dialog box appears.

3. In the Look In drop-down list, select your hard disk, double-click the Excel 2000 SBS Practice folder, double-click the Lesson 08 folder, and then double-click the 08C.inc file.

   The Text Import Wizard — Step 1 of 3 dialog box appears.

   

4. In the Original Data Type area, be sure that the Delimited option is selected and that the number 1 is displayed in the Start Import At Row box. Then click Next.

   The Text Import Wizard — Step 2 of 3 dialog box appears.

5. In the Delimiters area, be sure that the Tab check box is selected, and then click Next.

   The Text Import Wizard — Step 3 of 3 dialog box appears.

6. In the Column Data Format area, be sure that the General option is selected. In the Data Preview box, be sure that the word General is displayed in all column headings. Then click Finish.

   The Import Data dialog box appears.

7. In the Import Data dialog box, select the New Worksheet option, and click OK.

   The text file data is imported into an Excel worksheet.

8. Right-click the new sheet tab, and on the shortcut menu, click Rename. Type Proposal Data and press Enter.

   Your worksheet should look similar to the following illustration.

   

Create a PivotTable report from imported data

In this exercise, you create a PivotTable report for Impact Public Relations' annual business development meeting by using the imported data to show the monthly and yearly proposal success rate totals.

1. On the Proposal Data worksheet, click any cell in the list, and then on the Data menu, click PivotTable And PivotChart Report.

   The PivotTable And PivotChart Wizard — Step 1 of 3 dialog box appears.

2. Be sure that the Microsoft Excel List Or Database and the PivotTable options are selected, and then click Next.

   The PivotTable And PivotChart Wizard — Step 2 of 3 dialog box appears, showing the selected range of data for the PivotTable report.

3. Click Next.

   The PivotTable And PivotChart Wizard — Step 3 of 3 dialog box appears, specifying where the PivotTable report is to be created.

4. Be sure that the New Worksheet option is selected, and then click Finish.

   The PivotTable And PivotChart Wizard dialog box closes. The PivotTable toolbar appears, and the PivotTable report is displayed on the worksheet.

5. Drag the Date field button to the Drop Row Fields Here box.

6. Drag the Status field button to the Drop Column Fields Here box.

7. Drag the Proposal field button to the Drop Data Items Here box.

   The PivotTable report displays date and status information for proposals that Impact Public Relations has submitted to its clients.

8. In the Date column, right-click any cell; on the shortcut menu, point to Group And Outline, and then click Group.

   The Grouping dialog box appears.

9. In the By list, be sure that Months is selected, and click OK.

   Your worksheet should look similar to the following illustration.

   

10. Right-click the new sheet tab, and on the shortcut menu, click Rename. Type Proposal PivotTable Report and press Enter.

Create a Web Query

In this exercise, you import table data directly from the Web.

1. Click any cell outside the PivotTable report.

2. On the Data menu, point to Get External Data, and then click New Web Query.

   The New Web Query dialog box appears.

3. In the Web site address box under step 1, type the Internet address of the table that you would like to import.

4. In step 2, select the Only The Tables option.

5. In step 3, select the None option, and click OK.

   The Returning External Data To Microsoft Excel dialog box appears.

6. In the dialog box, select the New Worksheet option, and click OK.

   The data you imported is displayed on a new worksheet.

7. Right-click the Sheet4 sheet tab, and on the shortcut menu, click Rename. Type Web Query and press Enter.

8. Save your changes.

Changing the Data Displayed

Pivoting data is a lot like mixing and matching outfits. You might have three shirts, a scarf, and two pairs of pants. You can put on a pair of pants and see how it looks with one shirt, and then keep on the pants and try another shirt, or even add a scarf. Each time you change one piece of clothing, your entire outfit takes on a new look. In the same way, you can pivot data sets in a PivotTable report to create unique and informative results.

Your business partner stops by your office and you show him the PivotTable report. He thinks it looks great and asks if there is any way to determine the client representative who sold the most advertising. You show him how easy it is to add data to a PivotTable report by dragging information from the PivotTable toolbar to the interactive drop zones.

In addition to being able to add information easily, you can transpose one or more fields and use new functions to highlight other trends in your data. Your business partner would also like to evaluate the company's financial performance over the first and second quarters of the year and asks you to group revenue totals accordingly.

Add another field to the PivotTable report

In this exercise, you add the Client Rep field button to the PivotTable report.

1. Scroll, if necessary, to click the Revenue PivotTable Report sheet tab, and then select the PivotTable report on that worksheet.

2. Drag the Client Rep field button from the PivotTable toolbar to the right of the Date column.

   A dark vertical line appears and the mouse pointer changes when the correct location is reached.

   Your worksheet should look similar to the following illustration.

   

Change the data grouping

In this exercise, you group Impact Public Relations' revenue totals by quarter to evaluate the company's financial performance over the first and second quarters of the year.

1. On the Revenue PivotTable Report worksheet, in the Date column, right-click any cell.

2. On the shortcut menu, point to Group And Outline, and then click Group.

   The Grouping dialog box appears.

3. In the By list, click to clear the Months option, select the Quarters option, and click OK.

   Your worksheet should look similar to the following illustration.

   

Show data by category

In this exercise, you show individual revenue totals for just Rita, the client representative with the top total sales.

1. On the Revenue PivotTable Report worksheet, in the Client Rep drop-down list, clear all check boxes except Rita.

   

2. Click OK.

   Your screen should look similar to the following illustration.

   

PivotTable Report Data Orientation

In your PivotTable report, the Date and Revenue data are displayed in rows; this presentation is called a row orientation. The Client Rep data are displayed in a page orientation, since the data for each client representative is summarized individually, as if it were on its own page. When data are displayed in a single column, this presentation is called a column orientation.

The presentation of your PivotTable report would be more understandable if the data for each quarter were displayed in separate columns and if the Client Rep data were displayed in rows. You also decide to hide the print and Internet revenues so that you can evaluate the client representatives' total earnings based solely on radio/TV advertising sales.

Change the PivotTable report presentation

In this exercise, you change the presentation of the Date data from row to column orientation.

1. On the Revenue PivotTable Report worksheet, in the Client Rep drop-down list, select the Dale and Steven check boxes, and click OK.

2. Drag the Revenue Print and Revenue Internet headings out of the PivotTable report until an X is added to the mouse pointer.

   The Revenue Print and Revenue Internet headings are removed from the PivotTable report.

3. Drag the Date field button from the PivotTable toolbar to the Revenue Radio/TV heading.

   The Date heading appears next to the Revenue Radio/TV heading.

   Your worksheet should look similar to the following illustration.

   

4. Save your changes.

Showing Cost by Category

During the annual meeting, your business partner plans to present data on year-to-date revenue totals by client representative and media. To assist him with his preparations, you create the summary in the existing PivotTable report by removing and adding data. Then your partner can import the PivotTable report into a Microsoft PowerPoint presentation.

The data for each medium is calculated by the Sum function because Excel automatically totals the range of cells you select for your PivotTable. The Sum function is just one of many functions you can use to calculate data. For your partner's presentation, you need to figure the average amount each client spent on radio/TV, print, and Internet advertising. For this task, you use the Average function, which calculates the average of a list of numbers. Determining sales averages is intended to show your company where clients spend their advertising dollars, which can help Impact Public Relations plan future business development efforts.

Remove and add data from the PivotTable report

In this exercise, you remove the Date information and add the Revenue Print and Revenue Internet data to the PivotTable report.

1. On the Revenue PivotTable Report worksheet, drag the Date field button out of the PivotTable report.

   An X is added to the mouse pointer.

2. Drag the Revenue Print and Revenue Internet field buttons from the PivotTable toolbar to the box in which the Revenue Radio/TV data is displayed.

   Your worksheet should look similar to the following illustration.

   

Change the summary function

In this exercise, you modify the PivotTable report to reflect the average amount of money each client spent on radio/TV, print, and Internet advertising.

1. On the Revenue PivotTable Report worksheet, drag the Client Rep button out of the PivotTable report.

   An X is added to the mouse pointer.

2. Drag the Company Name field button from the PivotTable toolbar to the cell labeled Total.

3. In the Revenue Radio/TV column, right-click any cell, and then on the shortcut menu, click Field Settings.

   The PivotTable Field dialog box appears.

4. In the Summarize By list, select Average, and click OK.

5. Repeat steps 3 and 4 for the Revenue Print and Revenue Internet data.

   Your worksheet now shows the average amount that each client spent on the three advertising media.

6. Save your changes.

Refreshing PivotTable Report Data

Because the data in your PivotTable report is linked to your worksheet, you can make changes to your worksheet data without having to create a new Pivot-Table report. Even if you move the worksheet to another workbook, you can continue to make changes. Your business partner realizes that his projected totals for May are off by $2,000, but your previous work won't be wasted because you can simply alter the Revenue by Medium worksheet data and automatically update the Revenue PivotTable Report worksheet.

Add new data

In this exercise, you update the Revenue by Medium worksheet and refresh the data in your PivotTable report.

1. On the Revenue By Medium worksheet, scroll downward to row 126.

   The date 5/1/99 is visible.

2. In cell 126D, type 17000 and then press Tab.

3. In cell 126E, type 12000 and then press Tab.

4. In cell 126F, type 27000 and press Enter.

5. Click the Revenue PivotTable Report worksheet tab.

6. On the PivotTable toolbar, click the Refresh Data button.

   The projected revenues for Radio/TV, Print, and Internet in row 26 are $18,125.00, $11,062.50, and $26,950.00, respectively.

7. Save your changes.

Showing Details of a Calculation

You receive a call from a manager at Downfall Coffee & Tea. She would like to know the minimum and maximum amounts her company has spent on radio and TV advertising. You can quickly provide her with this information by doing a drilldown. A drilldown creates a new worksheet that lists all the records used to calculate the PivotTable report data.

Show underlying data

In this exercise, you generate a detailed worksheet that extracts the requested information from your PivotTable report.

1. On the Revenue PivotTable Report worksheet, double-click cell A4 to show the revenue data details for A. Datum Corporation.

   The Show Detail dialog box appears.

2. In the Show Detail dialog box, select Total Revenue, and click OK.

   The Show Detail dialog box closes, and the total revenues for A. Datum Corporation are displayed on the worksheet.

   Your worksheet should look similar to the following illustration.

   

3. On the Edit menu, click Undo Show/Hide Detail.

   Your worksheet returns to its original state.

Creating Page Reports for Distribution to Individuals

In preparation for performance reviews, you compile a report of average sales for each of your three client representatives. Each report will provide you and the client representative with detailed information regarding the average sales for their prospective clients. These records will help your employees plan sales strategies for the next quarter so that they can meet the company's projected goals.

Create a page report for each employee

In this exercise, you create a page report for each employee based on the Revenue PivotTable report.

1. Click the Revenue PivotTable Report worksheet tab.

2. Drag the Client field button to the Drop Page Fields Here area.

   The client representative data is included in the PivotTable.

3. Right-click any cell in the PivotTable report, and on the shortcut menu, select Show Pages.

   The Show Pages dialog box appears.

4. In the Show Pages dialog box, click OK.

   Four new worksheets—which contain the revenue data for client representatives Dale, Rita, and Steven, as well as the projected revenues—are added to the workbook.

   Your workbook should look similar to the following illustration.

   

5. Save your changes.

Finish the lesson

1. To continue to the next lesson, close all open windows.

2. If you are finished using Microsoft Excel for now, on the File menu, click Exit.

Lesson 8 Quick Reference

To	Do this	Button
Create a PivotTable report	Select any cell in the data list. On the Data menu, click PivotTable And PivotChart Report. Follow the steps in the PivotTable And PivotChart Wizard, and arrange the data by dragging the field buttons to the appropriate areas in the layout diagram.	.
Group a PivotTable report by date	Right-click a date cell, and on the shortcut menu, point to Group And Outline, and then click Group. Select the date grouping that you want, and click OK.	.
Format PivotTable report numbers	On the PivotTable report, click any data cell. On the PivotTable toolbar, click the Field Settings button. In the PivotTable Field dialog box, click the Number button, and then select a number format for the data. Click OK twice.
Make a PivotTable report more readable using AutoFormat	On the PivotTable toolbar, click the Format Report button. In the AutoFormat dialog box, select a report, and click OK.
Import a text file	On the Data menu, point to Get External Data, and then click Import Text File. In the Import Text File dialog box, browse to find and select the text file you want to import. In the Text Import Wizard, select a delimiter, select the column formatting you want, and then click Finish.	.
Create a Web query	On the Data menu, point to Get External Data, and then click New Web Query. In the New Web Query dialog box, type the Internet address of the source Web page. Select the options you want. Click OK twice.	.
Add a new field	Drag a field button from the PivotTable toolbar to the PivotTable report.	.
Show data by categories	In a heading drop-down list, clear or select the check boxes for the categories you want to display. Click OK.	.
Change data orientation	On the PivotTable report, drag a heading to a new orientation position.	.
Remove or add data	To remove data, drag a field button out of the PivotTable report. To add data, drag a field button from the PivotTable toolbar to the report.	.
Change the summary function	Right-click any data cell, and in the shortcut menu, click Field Settings. In the PivotTable dialog box in the Summarize By list, select a new summary function. Click OK.	.
Refresh data	Click any cell in the PivotTable report. On the PivotTable toolbar, click the Refresh Data button.
Show underlying data	In the PivotTable report, double-click the data cell for which you want to view additional information. In the Show Detail dialog box, select the detail you want to show, and click OK.	.
Create a page report for a PivotTable report	Right-click any cell in the PivotTable report, and on the shortcut menu, click Show Pages. In the Show Pages dialog box, click OK.	.