1 / 51

Project 5

Project 5. Jason C. H. Chen, Ph.D. Professor of Management Information Systems School of Business Administration Gonzaga University Spokane, WA 99258, USA Chen@gonzaga.edu. Creating Interactive Pivot Table Reports and Charts. Seven Steps for Developing Excel Workbooks.

dawn
Download Presentation

Project 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Project 5 Jason C. H. Chen, Ph.D. Professor of Management Information Systems School of Business Administration Gonzaga University Spokane, WA 99258, USA Chen@gonzaga.edu Creating Interactive Pivot Table Reports and Charts

  2. Seven Steps for Developing Excel Workbooks

  3. Add a field to a PivotTable list using Internet Explorer Add a PivotChart to a workbook Create PivotTable reports to analyze data Display PivotTable reports on the web Group data in a PivotTable report Import sales transaction data into Excel Modify fields in a PivotTable report Organize a PivotTable report using a page field Set up data for a PivotTable Excel Skills

  4. Key Terms • ASCII text • A format for storing letters, numbers, and symbols according to the American Standard for Information Interchange (ASCII). ASCII data can usually be shared among computers using different operating systems. • PivotChart • An interactive report that summarizes large amount of data in a graphical chart. • PivotTable • An interactive table that summarizes and analyzes data from lists.

  5. Key Terms • PivotTable list • A PivotTable report published on a Web page. The published PivotTable list and has much of the same interactive functionality in Internet Explorer as the PivotTable report in Excel. • PivotTable report • An interactive table that summarizes and analyzes data from existing lists, tables, and databases. • Transaction • A single business event, such as the sale of a product or depositing money into a bank account.

  6. Objectives • Import a delimited text file into Excel • Set up data for PivotTables • Create a Simple PivotTable • Group Data in the PivotTable • Organize a PivotTable using a page field • Modify a PivotTable report • Create a PivotChart Report

  7. Running Case • Selections, Inc. is a national department store chain with retail stores throughout the United States and Canada. • When the e-commerce site goes live, there will be thousands of transactions each day. They want to be sure that various departments will be able to quickly and easily analyze the transaction data using simple computer applications. • Summarize the data by store, and by product. • Post data analysis results to the company’s intranet.

  8. Project 4 Challenge • Examine the raw transaction data. If the data file has a consistent structure, you can import it into Excel and then analyze it. • Creating an Excel PivotTable to efficiently summarize the data. Add other statistical functions to further analyze the data. • Create a pivot chart to accompany the pivot table, and publish both the selections, Inc. intranet.

  9. Solution – Pivot Table

  10. Solution • Why • How • 9 Tasks • File you need • Orders.txt

  11. Why – Purposes of using PivotTable • you can quickly and efficiently summarize the data • you can add other statistical functions to further analyze the data • you can create a PivotChart to accompany the PivotTable and publish both to the intranet • Advantage of using PivotTable for data analysis is economy - it requires no formulas

  12. Task 1: Import an ASCII-Delimited text file into Excel • 0. You should have a text file: Orders.txt • 1. Click File, Open. The Open dialog box will appear • 2. In the Open dialog box, select Text Files in the Files of Type: listbox • 3. Select Floppy (A:) in the Look in: list (or the appropriate drive for the Orders.txt file) • 4. Highlight the Orders.txt file and click the Open button.

  13. Task 1: Import an ASCII-Delimited text file into Excel (cont.) 5. Make sure the Delimited option button under Original data type is selected and that the import will begin with row1. Click Next. 6. In Step 2 (of 3) of the import wizard, select Comma as the delimiter. Click the Next button.

  14. Task 1: Import an ASCII-Delimited text file into Excel 7. In Step 3 (of 3) of the import wizard, use the horizontal scrool bar to display the ZipCode data. Select the Text option button in the Column data format. 8. Apply the same way to display the OrderDate data. Select the Date option button in the Column data format box and MDY as the data type. 9. Click the Finish button. Excel will create a worksheet named Orders in the current workbook. 10. Click File, Save As and select Microsoft Excel Workbbok under the Save As tyep:listbox and click Save.

  15. Result from Task 1 Conditions of analyzing transaction data in a PivotTable: 1) each row must have a column label 2) each row must contain a unique record 3) avoid blank rows and columns 4) change any date values to date format

  16. Fields of the Data File Data file: Orders.xls

  17. 1. Type Total Price in cell O1 2. Type =m2*n2 as the formula in cell O2 3. Using the fill handle, copy the formula through row 37 4. Select columns M and O and change the format of the nonadjacent selection to currency, with two decimal places. 5. Highlight the range A1:O1 and change the font style of the selection to Bold, and add a lower border to the selection. 6. Select columns A through D (or A1:O1), and double click any column separator to automatically adjust all column widths to fit the longest entry. 7. Save the workbook. Task 2: Set up the data for the PivotTable report - new column O

  18. Features of PivotTable • Easy to change the view of the data • rotate rows and columns to see summaries • filter the data by displaying different pages • display the details for areas of interest • see any sales trends related to particular items • know if the sales of individual items vary according to the purchase date Help marketing department best represent the products Advertising and Marketing may be interested in ...

  19. Task 3: Create a Pivot Table 1. Select cell A1 2. Click Data, PivotTable and PivotChart Report. The PivotTable Wizard will appear. 3. In Step 1, verify that the data to be analyzed is an Excel list or database and that you want to create a PivotTable Report. Click the Next button.

  20. Task 3: Create a Pivot Table (cont.) 4. Step 2 of the PivotTable Wizard verifies the data range automatically. The range should be A1:O37 5. (a) Step 3 of the Wizard has you specify the location for the PivotTable Report. (b) To keep the report independent of the transaction data, select the option to put the PivotTable in a new worksheet. (c ) To set up the PivotTable, click Layout.

  21. Task 3: Create a Pivot Table (cont.) 6. The design grid will appear. Drag the Order Date field name to the Column area of the layout. 7. Drag the Product Description field name to the Row area of the layout. 8. Drag the Total Price field name and drag it to the Data area of the layout. The layout should now look like this. Click OK

  22. Task 3: Create a Pivot Table (cont.) • Step 3 of the Wizard will once again appear on the screen. • Click the Finish button. • The PivotTable shown on the next slide is added to the workbook. The PivotTable is in a new worksheet named Sheet1, and the PivotTable toolbar appears in an undocked state.

  23. Task 3: Result of Creating a Pivot Table The PivotTable is in a new worksheet named Sheet1, and the PivotTable toolbar appears in an undocked state. 11. Save the workbook.

  24. How do you display the PivotTable toolbar, once you close it?

  25. Task 4: Group data in the PivotTable Report 1. Right-click over the OrderDate button in cell B3, choose Group and Outline, and select Group 2. Select Months in the Grouping dialog box, and click OK.

  26. Task 4: Group data in the PivotTable Report (cont.) 3. Rename the Sheet1 worksheet tab to Item Description By Date, and place the insertion point in cell A5. 4. Save the workbook.

  27. Task 5: Organize a PivotTable using a Page Field 1. Click Data, PivotTable and PivotChart Report. 2. Click the Layout button in Step 3 of the Wizard. 3. Drag the Category field name to the Page area of the PivotTable layout. 4. Click OK. Step 3 of the Wizard will appear once again.

  28. Task 5: Organize a PivotTable using a Page Field (cont.) 4. (cont.) Step 3 of the Wizard will appear once again. Click Finish. Excel adds a new row to the PivotTable Report. It displays the Category field button in cell A1 of the PivotTable, and a drop-down list for product categories in cell B1. 5. Click the drop-down list button in cell B1 and select Appliances. 6. Click the OK. The PivotTable will change. The Appliances items are now displayed.

  29. Task 5: Organize a PivotTable using a Page Field (cont.) 6. (cont.) Click the OK. The PivotTable will change. The Appliances items are now displayed. 7. Using the same procedure, display other product categories. 8. Save the workbook.

  30. Pivot Chart

  31. Task 6: Add a PivotChart to the Current PivotTable Report 1. Open the existing Orders.xls workbook if it is not open and active. 2. Select cell A1 and click the Chart Wizard icon on the standard toolbar. 3. The chart displayed is added to the workbook. Notice the Category and OrderDate field buttons that appear in the chart area; these indicate that this is a PivotChart.

  32. Task 6: Add a PivotChart to the Current PivotTable Report (cont.) 4. Rename the Chart1 tab as Description by Months (chart) 5. Click the Category field button, and select Electronics. 6. Click OK. The chart is modified.

  33. Task 6: Add a PivotChart to the Current PivotTable Report (cont.) 7. Click the Category field button, and select All. 8. Click OK. The chart is modified. 9. Click the Item Description by Date worksheet tab.

  34. Task 6: Add a PivotChart to the Current PivotTable Report (cont.) 10. It shows how the PivotTable’s layout has also been modified. 11. Save the workbook.

  35. Task 7: Modify the PivotTable Layout using Drag and Drop 1. Click the Category field button and drag it into column A anywhere (left) inside the PivotTable Report. 2. Release the left mouse button. The report layout changes. Notice that the category descriptions now appear in column A and the product descriptions in column B.

  36. The complete report from the previous step.

  37. Task 7: Modify the PivotTable Layout using Drag and Drop (cont.) 1, 2. (cont.) Click the Category field button and drag it into column A anywhere (right) inside the PivotTable Report.

  38. Task 7: Modify the PivotTable Layout using Drag and Drop (cont.) 3. Right-click over the OrderDate field button, choose Group and Outline, and select Group. 4. Change the grouping to display years. Click OK. The complete report is shown next.

  39. Task 7: Modify the PivotTable Layout using Drag and Drop (cont.)

  40. Task 7: Modify the PivotTable Layout using Drag and Drop (cont.) 5. Right-click over the Category field button, choose Group and Outline, and select Hide Detail. As you can see that the report collapses.

  41. Displaying PivotTable Reports on the Web Task 8: Publish a PivotTable Report to the Web as an Interactive PivotTable List

  42. Task 8: Publish a PivotTable Report to the Web as an Interactive PivotTable List 2. Click the Save: drop-down list (you may select the floppy diskette if you wish). 3. Check the option to publish the current worksheet only, with interactivity. 4. Type Selections E Commerce data.htm as the name for the page. Click the Publish button. 1. Click File, Save As Web page

  43. Task 8: Publish a PivotTable Report to the Web as an Interactive PivotTable List (cont.) 5. The Publish as Web Page dialog box will appear. Choose the PivotTable in the list, and make sure the remaining setting match. When they do, click Publish. It will take Excel a moment to create the PivotTable list, and calculate the data summaries.

  44. Pivot Table List on the Web

  45. Field List Task 9: Add a Field to the PivotTable List using I.E. • Click the Field List button on the displayed Web page. The Field List dialog box will appear. • 2. Scroll the Field List to display the ProductID field name.

  46. Task 9: Add a Field to the PivotTable List using I.E. (cont.) 3. Click and drag the ProductID fiedl name between the Category and Product Description fields displayed in the browser. Notice the blue line indicating the position for the field you will add. 4. Release the left mouse button. The (ProductID) field is added to the PivotTable List (see next slide).

  47. Task 9: Add a Field to the PivotTable List using I.E. (cont.) 5. Close Internet Explorer. 6. Close the Order.xls workbook. Save any changes you have made.

  48. Question? • What happen if you • Select Entire Workbook, and • Unselect Add Interactivity

  49. Hands-On Exercise • You may try to publish a chart only on the Web with Interactivity.

  50. Pivot Table List

More Related