Electronic Spreadsheet (Excel)

Electronic Spreadsheet (Excel) A pivot table is an interactive table that quickly summarizes large amount of data from a data source. It is a valuable tool to reveal the information hidden in the details. As the name suggests, pivot table allows you to rotate its rows and columns to see different comparisons of the source data. A pivot table can contain row fields and column fields. Numeric data appear as data fields. The following is an example of a pivot table displaying average salary for each department by gender: Avg salary in each dept, broken down by gender Original data (Portion) Pivot table Avg salary for each dept Figure 4-1: A pivot table 1. Creating a Pivot Table We will use the ee2-0701.xls workbook that you can download from our class web. (1) Open the workbook, save it as salary-pivot. (2) Click any cell within the data range (A9:G120), and choose Insert – PivotTable. You will see a dialog window entitled “Create PivotTable”. [This bullet is based on Excel 2013] [The following steps are Excel 2010/2003] (It should be easy to figure them out with 2013 version) (3) We will use the default settings. So simply click Next. The “Wizard – Step 2 of 3” opens. Please note that the range of the data list is automatically selected (because you clicked at the range in “(2)”). Click Next. The “Wizard – Step 3 of 3” opens (as follows). 1 Figure 4-2: Specifying the location of the pivot table (in the existing worksheet) (4) We want the pivot table to appear right beside the data source. So select the radio button Existing worksheet. After that, the box for the starting cell of the pivot table will allow you to enter a cell address. Click at cell J10. Click Finish. Excel creates a shell for a pivot table (as follows): Figure 4-3: Shell for pivot table (5) In this step we want to make the departments the row headers for the pivot table. Click Department in the PivotTable Field List box. Make sure the Row Area is selected in the bottom of the PivotTable Field List box, and click Add To button. Figure 4-4: Select Department for the rows of the pivot table 2 (6) Now we want to make the salary amount the row headers for the pivot table. We can repeat the above step for the field Salary except that this time we will select the Data Area (Or, we can drag the Salary field to the Drop Data Items Here area of the pivot table shell to accomplish the task). The result: Figure 4-5: Pivot table – Count by departments (7) The summary calculation is a count by default. We can change it to a sum by doubleclicking at Count of Salary in the above pivot table, and the PivotTable Field dialog box will appear, where we can change from Count to Sum (complete with OK): (7) Select to change to Sum Figure 4-6: Pivot table – Change from Count to Sum (8) Click and select Currency Figure 4-7: Pivot table – Sum by departments (8) We can format the numbers to be currency by clicking at the Number button in Figure 4-6, and choose Currency with appropriated decimal places for the Sum of Salary. 3 2. Expanding a Pivot Table (1) Open the salary-pivot workbook, if necessary. If you do not see the PivotTable Field List box, right-click within the pivot table and then select Show field List. (2) We want to add “position” as a sublevel of rows: click Position in the PivotTable Field List box, select Row Area, and click Add To button. Excel adds position data to the pivot table. Figure 4-8: Positions added as second level rows (Portion of table) (3) We want to add “shift” as columns: click Shift in the PivotTable Field List box, select ________ (what?) Area, and click Add To button. Excel adds shift data to the pivot table. Figure 4-9: Shifts added as columns (Portion of table) Note the PivotTable Field List now has several highlighted fields (in bold) – what are them? 4 3. Removing, Hiding, and Showing Data (1) If we do not need the Shift field in the pivot table, we can drag it upward off the pivot table. (Result in Figure 4-10). (2) If we want to hide the data for Engineering, R and D, and Warehouse departments, we can uncheck these fields from the drop-down list of fields, and click OK. The results are as indicated in Figure 4-10. Field removed from the pivot table (2-1) Click to display the drop-down field list (2-2) Uncheck to hide the fields in the pivot table Figure 4-10: Removing and hiding fields from pivot table 4. Refreshing a Pivot Table If the source data is changed, the pivot table will reflect the change. But that requires that we refresh the pivot table: Right-click inside the pivot table, and choose Refresh Data. (Try it!) 5. Rotating Pivot Table Fields The fields in a pivot table can be re-organized by simply dragging the respective field button around according to the user’s needs. Figure 4-11 on next page shows several “re-incarnation” of our pivot table, and how they can be achieved by moving the fields. (To make the list shorter, the departments of Art and Admin are also hidden). 5 a. “Original”: Then, drag Position upward to the right of Sum of Salary:  b b. Department down the column and Position across the row (portion): Then, drag Position to under Department, and then Department up one cell:  c c. Position down the column and Department across the row (portion): Finally, drag Department to on top of Position, guess what?  d. Back to the original!! 6