Capstone Project Wiki

Pivot tables are used in excel to summarize data. If there is a list of data containing multiple fields, and you want to display it with one of the fields as a row label, and another of the fields as a column label, you need a pivot table. This is a basic set of instructions for creating a pivot table from data contained in a CSV file. These instructions are based on Excel for Windows; you will need to check the help for instructions to do it under MacOS.

1. Open the csv file in Excel
2. Save the file as an xlsx file. This is necessary if you want to use advanced pivot table features.
3. Click somewhere in the data list in the file. From the Insert menu, select Table. In the popup window, make sure the proper data is selected, and the “My table has headers” box is checked. Click OK.
4. From the Insert menu, select Pivot Table. In the popup window, make sure you have the “Select a table or range” option button checked, and the table should be Table1 (the newly created table from the previous step. Make sure the “New Worksheet” option button is selected. Make sure the “Add the data to the Data Model” checkbox is checked. Click OK.
5. The new sheet in which the Pivot Table is created will become the active sheet. On the right hand side of the sheet is a window showing the pivot table fields. Click on the name of the field that should be the row labels (not the check box) and drag it to the Rows window. Do the same for the field that should be the column labels.
6. If the data to be placed in the Values area is numeric, click on the name of the field and drag it to the Values window. You may wish to change the formula in the value field from Sum of Values (default) to something else (Count, Maximum, Minimum, Average, etc.)
7. If the data to be placed in the Values area is text (like NetID), you will need to create a new Measure for that data, as follows:
   1. Right-click on Table 1 in the field list, and select Add Measure
   2. When the Measure window pops up, type in a measure name (List of NetIDs in this example; you can make it be whatever you want). Enter the following formula in the Formula window:

      =ConcatenateX(Table1, Table1[NetID], ", ")

      . Note that Table1 is the name of the data table from which the pivot table is created, and NetID is the name of the text field you wish to display in the values area of the pivot table. Change the name of the data table and/or the text field as needed for your situation.

   3. Click OK on the Measure window
   4. Now, go to the Pivot Table Fields list, and you should have a new field to choose from. Select the checkbox for the field, and it will go into the Values area.
   5. Turn off the row and column totals by selecting Pivot Table, Options, then the Totals & Filters tab, and clearing the Show grand totals for rows and Show grand totals for columns checkboxes. Then click OK

That should do it. You now have a pivot table. You can copy and paste the data anywhere it is needed.