Working with PivotTables

Advanced Training for Microsoft Office Excel 2003

Creating and Using PivotTables

1	To begin creating a PivotTable, in the Limited worksheet, verify that cell A1 is selected, click the Data menu, and then click PivotTable and PivotChart Report.
2	On the first page of the PivotTable and PivotChart Wizard, verify that the Microsoft Office Excel list or database and PivotTable options are selected, and to move to the next page of the wizard, click Next.
3	On the second page of the wizard, in the Range box, verify that the data range of $A$1:$F$101 is displayed. To accept the range and move to the final page of the wizard, click Next.
4	On the final page of the wizard, verify that the New worksheet option is selected, and then to finish creating the PivotTable and close the wizard, click Finish.
5	To rename the new worksheet, double-click the Sheet1 sheet tab.
6	To name the worksheet, for the purposes of this exercise, press SPACEBAR to have the name typed for you, and then press ENTER.
7	To add the Month field to the PivotTable, in the PivotTable Field List dialog box, click Month and drag it to the Drop Row Fields Here box, as indicated.
8	To add the Week field to the PivotTable, in the PivotTable Field List dialog box, click Week and drag it to the right of Month, as indicated.
9	To add the Day field to the PivotTable, in the PivotTable Field List dialog box, click Day and drag it to the right of Week, as indicated.
10	To add the Hour field to the PivotTable, in the PivotTable Field List dialog box, click Hour and drag it to the Drop Column Fields Here box, as indicated.
11	To add the Sales field data to the PivotTable, in the PivotTable Field List dialog box, click Sales and drag it to the Drop Data Items Here box.
12	To close the PivotTable Field List dialog box, on the PivotTable toolbar, click the Hide Field List button, as indicated.
13	To select the PivotTable report, click cell D5.
14	To begin formatting the data list, on the PivotTable toolbar, click the Format Report button, as indicated.
15	To apply AutoFormat to the PivotTable, in the AutoFormat dialog box, click twice below the scroll box, click the Table 1 format, and then click OK.
16	To select a range of cells, click once on the Right scroll arrow on the horizontal scroll bar, click cell D5, hold down the SHIFT key, and then click cell N19.
17	To apply a new format to the selected cells, on the Formatting toolbar, click the Currency Style button, as indicated.
18	To save your changes, on the Standard toolbar, click the Save button.

Editing PivotTables

1	To begin adding a field to the PivotTable, in the PivotTable worksheet, click cell D5, and then on the PivotTable toolbar, click the Show Field List button, as indicated.
2	To add the Weekday field to the PivotTable, in the PivotTable Field List dialog box, click Weekday and drag it to the Drop Page Fields Here box, as indicated.
3	To close the PivotTable Field List dialog box, on the PivotTable toolbar, click the Hide Field List button, as indicated.
4	To dock the PivotTable toolbar below the Formatting toolbar, click the title bar of the PivotTable toolbar and drag it to the indicated area.
5	To filter the PivotTable so that only the data representing sales on a particular day is displayed, click the Weekday list arrow, as indicated, click Mon, and click OK.
6	To remove the filter, click the Weekday list arrow, click (All), and then click OK.
7	To filter the PivotTable so that only the data representing sales in week 1 is displayed, click the Week list arrow, as indicated, clear the check box next to 2, and then click OK.
8	To display data from both weeks, click the Week list arrow, select the (Show All) check box, and then click OK.
9	To display the PivotTable and PivotChart Wizard, on the PivotTable toolbar, click PivotTable, and then click PivotTable Wizard.
10	To begin changing the layout of the PivotTable, in the PivotTable and PivotChart Wizard, click Layout.
11	To change the layout of the PivotTable, in the COLUMN box, click Hour and drag it to the PAGE box, as indicated.
12	To change Weekday into a column head and finish applying the changes, in the PAGE box, click Weekday and drag it to the COLUMN box, and then click OK.
13	To view the changes in the layout of the PivotTable, click Finish.
14	To change the layout of the PivotTable by using pivot, click the Hour field head, as indicated, and drag it to the Drop Column Fields Here box, as indicated.
15	To finish changing the layout of the PivotTable by using pivot, click the Weekday field head, and drag it to the Drop Page Fields Here box, as indicated.
16	Notice that the layout of the PivotTable is changed in response to the pivot. To display the Limited worksheet, click the Limited sheet tab.
17	To change the sales data, click cell F5, and for the purposes of this exercise, press SPACEBAR to have the data typed for you, and then press ENTER.
18	To display the PivotTable worksheet, click the PivotTable sheet tab.
19	To refresh the PivotTable data, on the PivotTable toolbar, click the Refresh External Data button, as indicated.
20	Notice that the sales data is updated. To hide the hourly sales details contained in the PivotTable, double-click cell A5.
21	Notice that the detail rows of the March section of the PivotTable are hidden, and only the totals for March are displayed. To show the PivotTable details again, double-click cell A5.
22	To move to the Link worksheet, click the Link sheet tab.
23	To begin creating a link to a PivotTable field, verify that cell C5 is selected, press SPACEBAR to have the text typed for you, and then click the PivotTable sheet tab.
24	To specify that cell C5 of the Link worksheet should be linked to cell N19 of the PivotTable worksheet, click once to the right of the scroll box on the horizontal scroll bar, and then click cell N19.
25	Notice that the desired formula is displayed on the formula bar, as indicated. To finish creating the link, press ENTER.
26	Notice that Excel switches back to the Link worksheet and displays the value $32,939 in cell C5. To save the changes made to the workbook, on the Standard toolbar, click the Save button.

Creating PivotTables from External Data

1	To begin importing a data list into Excel, click the Data menu, point to Import External Data, and then click Import Data.
2	To import data from a text file, in the left pane of the Select Data Source dialog box, click My Documents, double-click Garden Company, double-click Sales, click MarData, and then click Open.
3	On the first page of the Text Import Wizard, verify that the Delimited option is selected and the Start import at row box displays 1, and then to move to the next page of the wizard, click Next.
4	On the second page of the wizard, in the Delimiters area, verify that the Tab check box is selected, and then to move to the next page of the wizard, click Next.
5	On the final page of the wizard, in the Data preview area, verify that the data type of each column is set to General, and then to accept the values and data types assigned by the wizard, click Finish.
6	In the Import Data dialog box, verify that the Existing worksheet option is selected and =$A$1 is displayed in the Existing worksheet box. To paste the imported data into the active worksheet, click OK.
7	To close the External Data toolbar, click its Close button.
8	To begin creating a PivotTable, verify that cell A1 is selected, click the Data menu, and then click PivotTable and PivotChart Report.
9	On the first page of the PivotTable and PivotChart Wizard, verify that the Microsoft Office Excel list or database and PivotTable options are selected, and to move to the next page of the wizard, click Next.
10	On the second page of the wizard, in the Range box, verify that the data range of $A$1:$F$101 is displayed. To accept the range and move to the next page of the wizard, click Next.
11	On the final page of the wizard, verify that the New worksheet option is selected, and then to finish creating the PivotTable and close the wizard, click Finish.
12	To add the Week field to the new PivotTable, in the PivotTable Field List dialog box, click Week and drag it to the Drop Row Fields Here box.
13	To add the Day field to the PivotTable, in the PivotTable Field List dialog box, click Day and drag it to the right of Week, as indicated.
14	To add the Hour field to the PivotTable, in the PivotTable Field List dialog box, click Hour and drag it to the Drop Column Fields Here box.
15	To add the Sales field data to the PivotTable, in the PivotTable Field List dialog box, click Sales and drag it to the Drop Data Items Here box.
16	To save the changes made to the workbook, on the Standard toolbar, click the Save button.