3. Creating the pivot table

To create a pivot table from the dataset introduced previously here (and downloadable here), select the table (A1-H721), choose Insert in the main menu, and Pivot Table.









In the dialog box that shows up, you may choose the data (if it is not already done) and whether you want the pivot table to appear on a new worksheet or in the same one as the original table. If you choose to have it next to the original table, simply indicate which cell its top left corner will be located at.






The new menu PivotTable Fields that appears to the right of your screen will help you organize the pivot table. With the menu, you will be able to choose which values to display (counts, averages, minimum or maximum values, standard deviations, etc) and whether you want to do so for each category (or a selection of categories) named in the table (for instance per light condition, salinity condition, tank, date, etc).

First, select a dependent variable to display in the pivot table, here the standard length (SL (mm)) . To do so, click and hold SL (mm), then drag it in the box Σ VALUES in the lower right corner of the side menu.















In the worksheet, a pivot table made of 2 cells has appeared, showing Count of SL (mm) and 716. This count tells you how many cells in the SL (mm) column contain numerical data (it is indeed a count of entries). Note that from now on, the changes you will perform in the right side menu will show up “live” in the newly created pivot table.



If you wish to get averages (or any other statistic) instead of counts, go back to the side menu and click on  Count of SL (mm)  in the box Σ VALUES. Choose Value Field Settings... and select Average in the menu, then OK.






The pivot table now shows  Average of SL (mm) and its value based on all the data in the whole table.




Now, you may decide which category (or categories) you want to see the average of. Let’s assume that you are interested in getting the average of standard length for each tank. We go back to the side menu and drag and drop Tank nr. from the list of available fields down to the box called ROWS.




The pivot table now looks like this. The 9 tanks are displayed in rows with their labels (1-9) in the left column and the corresponding average in the right column, and the average of the whole table comes at the bottom as a “grand total” (and you can see that this grand total matches the average that was displayed in the pivot table just before we divided the table in rows).






We could have similarly dispatched the tanks horizontally by dragging and dropping Tank nr. from the list of available fields down to the box called COLUMNS.


These are the basics on how to create a simple pivot table. From there, things can get quite interesting/complicated, depending on whether you want to:

The following posts will show you how to go further with pivot table.

  Fant du det du lette etter? Did you find this helpful?
[Average: 0]