Creating Charts and PivotTables

Advanced Training for Microsoft Excel 2002

Creating Dynamic Lists with PivotTables

1	To begin creating dynamic lists with PivotTables, click the Data menu, and click PivotTable and PivotChart Report.
2	Notice that the PivotTable and PivotChart Wizard is already set to analyze a Microsoft Excel list (selected in the top section) and create a PivotTable (selected in the bottom section), so to continue, click Next.
3	Notice that all data ($A$1:$F$1117) on the active sheet is selected by default for use in the PivotTable. To continue, click Next.
4	To create the PivotTable in a new worksheet, make sure that New worksheet is selected, and then click Finish.
5	Notice that a new worksheet, Sheet1 appears with empty fields where you will put data. To have monthly sales represented by rows, in the PivotTable Field List dialog box, click the Month field, press and hold down the left mouse button, drag it to the Drop Row Fields Here box, and release the mouse button.
6	To divide the monthly sales rows into weekly sales, in the PivotTable Field List dialog box, click the Week field, press and hold down the left mouse button, drag the mouse pointer to the right half of the Month field head, and release the mouse button.
7	To have days appear to the right of weeks in the rows area, in the PivotTable Field List dialog box, click the Day field, press and hold down the left mouse button, drag the mouse pointer to the right side of the Week field head, and release the mouse button.
8	To have columns show hourly sales data, in the PivotTable Field List dialog box, click the Hour field, press and hold down the left mouse button, drag the mouse pointer to the Drop Column Fields Here box, and release the mouse button.
9	To fill the PivotTable with sales data, in the PivotTable Field List dialog box, click the Sales field, press and hold down the left mouse button, drag the mouse pointer to the central Drop Data Items Here box, and release the mouse button.
10	To hide the PivotTable Field List dialog box, on the PivotTable toolbar, click the Hide Field List button (last button on the right).
11	To apply a predefined AutoFormat style to your PivotTable, on the PivotTable toolbar, click the Format Report button (first button to the right of the PivotTable menu).
12	In the AutoFormat dialog box, click twice below the scroll box in the vertical scroll bar, click the Table 1 format, and then click OK.
13	To select all cells that contain sales data in order to format them as currency, click cell D5, and then in the vertical scroll bar, click below the scroll box five times, and in the horizontal scroll bar, click once to the right of the scroll box.
14	Press and hold down the SHIFT key, click cell P119, and release the SHIFT key.
15	To apply the Currency style to the selected cells, on the Formatting toolbar, click the Currency Style button (the $ sign).

Editing PivotTables

1	To begin editing a PivotTable, click cell D5 in the PivotTable to select it, and then, on the PivotTable toolbar, click the Show Field List button (first from the right).
2	To create a drop-down list for filtering data by weekdays, in the PivotTable Field List dialog box, click the Weekday field, press and hold down the left mouse button, drag the mouse pointer to the Drop Page Fields Here box (at the top of the worksheet), and release the mouse button.
3	To filter the data so that only sales data for Mondays appears in the PivotTable, click the Weekday down arrow, click Mon, and then click OK.
4	To display the data from all weekdays again, click the Weekday down arrow, click (All), and then click OK.
5	To filter the PivotTable so that only data for sales during the first week of each month appears in the PivotTable, click the Week down arrow, click the check box next to Show All to uncheck all boxes, then click to insert a checkmark next to 1, and click OK.
6	To undo the filter and display data from all weeks,on the Standard toolbar, click the Undo button.
7	To change the layout of the PivotTable, on the PivotTable toolbar, click the PivotTable menu, and click Wizard.
8	In the PivotTable and PivotChart Wizard, click the Layout button.
9	To filter hours by pages rather than by columns, click the Hour field in the list on the right, press and hold down the left mouse button, drag it to the Page box (at far left), and release the mouse button.
10	To filter the Weekdays field by columns rather than pages, click the Weekday field on the left side of the diagram, press and hold down the left mouse button, drag it to the Column box, release the mouse button, and then click OK.
11	To view the changes reflected in the PivotTable, click Finish.
12	To move the Hour filter from the Page area back to the Column area, in the PivotTable Field List, click the Hour field, press and hold down the left mouse button, drag the Hour field to the right of the Weekday field head on the PivotTable, and release the mouse button.
13	To move the Weekday field back to the Page area, in the Pivot Table Field List, click the Weekday field, and then at the bottom of the list, click the Add To down arrow, click Page Area, and click Add To.
14	To revise the data that this PivotTable is based on, in the lower left corner of the workbook, click the AllData sheet tab, click cell F5 (which contains 201), type 2850, and then press ENTER.
15	To view the changes made in the source data list reflected in cell D8 of the PivotTable, click the PivotTable sheet tab, and on the PivotTable toolbar, click the Refresh Data button (the exclamation mark).
16	To hide the detail rows for January data and display only the January Total, in the Month column, double-click January.
17	To view the January detail rows again, double-click January again.
18	To display the Summary worksheet, click the Summary sheet tab.
19	To create a link between a cell in the PivotTable and a cell in the Summary worksheet, click cell C5, type =, and then click the PivotTable sheet tab.
20	To scroll to the bottom right corner of the worksheet, click once to the right of the scroll box in the horizontal scroll bar, and then click five times below the scroll box in the vertical scroll bar.
21	To link cell P119 from the PivotTable worksheet to cell C5 in the Summary worksheet, click cell P119.
22	Notice that =GETPIVOTDATA("Sales",PivotTable!$A$3) appears in the Formula Bar. To complete this link, press ENTER.
23	Notice that $330,896.00, the value from cell P119 of the PivotTable worksheet, appears in cell C5 of the Summary worksheet.To save your changes, on the Standard toolbar, click the Save button.

Creating PivotTables from External Data

1	To import a data list from a text file into an Excel workbook, click the Data menu, point to Import External Data, and then click Import Data....
2	To locate your data source, in the Select Data Source dialog box, in the Places bar at left, click My Documents.
3	Double-click the Garden Company folder, double-click the DataSources folder, and then double-click JanuarySalesRecord.
4	Notice that the Text Import Wizard has determined that your data is delimited, which means characters such as commas or tabs separate each field. This is correct, so click Next.
5	Notice that in the Delimiters section, the Tab check box is checked. To see how your imported data will appear if you select a different delimiter, click Comma, and then click Tab to remove the check mark next to it.
6	Notice that the Data Preview pane no longer displays data in columns. To view the data in columns again, click Comma to remove the check mark, click Tab to insert a checkmark, and then click Next.
7	This page of the wizard lets you select each column and set the data format. To accept the General data format for all columns, click Finish.
8	To paste the imported data into the existing worksheet beginning at cell A1, in the Import Data dialog box, click OK.
9	To hide the External Data toolbar, click its Close button.
10	To create a PivotTable from the imported data in this worksheet, click the Data menu, and click PivotTable and PivotChart Report.
11	Since you want to create a PivotTable based on the current Excel list, on the first page of the PivotTable and PivotChart Wizard, the default selections are fine, so click Next.
12	Notice that all the data from the active worksheet is selected by default and entered into the Range box. Click Next.
13	To create the PivotTable in a new worksheet, click Finish.
14	To begin organizing the placement of data in the PivotTable, in the PivotTable Field List, click the Week field, press and hold down the left mouse button, drag it to the Drop Row Fields Here box on the left side of the worksheet, and release the mouse button.
15	To further organize rows by weekdays, in the PivotTable Field List, click the Weekday field, press and hold down the left mouse button, drag it to the right half of the Week field head, and release the mouse button.
16	To label the columns with hours, in the PivotTable Field List, click the Hour field, press and hold down the left mouse button, drag it to the Drop Column Fields Here box, and release the mouse button.
17	To fill the body of the PivotTable with data from the Sales field, click the Sales field, press and hold down the left mouse button, drag it to the Drop Data Items Here box, and release the mouse button.

Creating a Dynamic Chart Using PivotCharts

1	To begin creating a PivotChart based on the data in this worksheet, click the Data menu, and click PivotTable and PivotChart Report.
2	On the first page of the PivotTable and PivotChart Wizard, the default selection of Microsoft Excel list or database is fine. To change the type of report to a PivotChart, click PivotChart report (with PivotTable report) at the bottom, and then click Next.
3	Notice that all the data from the Excel worksheet is selected by default and entered into the Range box. Click Next.
4	To create the PivotChart and the associated PivotTable in separate new worksheets, make sure that New worksheet is selected, and click Finish.
5	To begin organizing the placement of data in the PivotChart, in the PivotTable Field List, click the Hour field, press and hold down the left mouse button, drag it to the Drop Category Fields Here box at the bottom of the worksheet, and release the mouse button.
6	To have the Month field appear as a filter at the top of the page, click the Month field, press and hold down the left mouse button, drag it to the Drop Page Fields Here box at the top of the worksheet, and release the mouse button.
7	To place the Weekday field as an additional page filter, click the Weekday field, press and hold down the left mouse button drag the mouse pointer to the top of the worksheet, just to the right of the Month field head, and then release the mouse button.
8	To fill the body of the PivotChart with data from the Sales field, on the PivotTable Field List, click the Sales field, press and hold down the left mouse button, drag it to the Drop Data Items Here box (the body of the PivotChart), and release the mouse button.
9	To close the PivotTable Field List, click its Close button in the upper-right corner of the dialog box.
10	To revise data that this PivotChart is based on, at the bottom of the workbook, click the AllData sheet tab.
11	Click cell F2 to select it, and type 25000.
12	To view this change reflected in the first bar of the PivotChart, click the Chart1 sheet tab, and then on the PivotTable toolbar, click the Refresh Data button (the exclamation mark).
13	To save this chart as a custom chart type so that you can create similar charts in the future, click the Chart menu, and click Chart Type.
14	In the Chart Type dialog box, click the Custom Types tab.
15	In the Select from section at bottom left, click the User-defined option.
16	To add this chart as a new custom chart type, click Add.
17	To name the new chart type, in the Add Custom Chart Type dialog box, in the Name box, type Sales PivotChart.
18	Click in the Description box, type sales per hour, and then click OK.
19	Notice that Sales PivotChart now appears in the Chart Type box and its description appears below the Sample box. To view other chart types, click the Standard Types tab.
20	To change the current chart to an Area chart type, in the Chart type list, click Area, and then click OK.