This instruction will walk through a simple example showing how to quickly plot temperature and activity data. The same steps can be followed to plot any parameters of interest. In the example there are 9 subjects group housed into two cages. This data can easily be plotted to show data by cage, or by subject.

For a Video on setting up Pivot Tables and Charts click this link.

Walkthrough Tutorial

1. Open an Excel file from a Ponemah export that has pivot table format enabled and view either the Derivations or Data Reduction Tab. These tabs are specifically output in a format to work optimally with Pivot Tables.

The data will look like the example.

2. Select the Insert tab at the top and then select Pivot Table. The selection should automatically choose the entire table. Click OK.

3. A new sheet will be created and the PivotTable Fields box will be showing on the right side of the screen.

4. Select the following from the list: ParameterName, ParmValue, and RealTime

5. The fields selected will be automatically placed in the boxes below. Click and drag to re-arrange them as needed. As example RealTime will be placed in the Rows box, ParameterName will be in the Columns box and ParmValue will be in the Values box.

a. With this setup all of the parameters will initially be selected. To select ONLY the parameters of interest, click the arrow in the table next to Column Labels to remove parameters that are not needed. The example is showing total activity and temperature mean.

b. In the Pivot Table Fields area on the right select the Sum of ParmValue and then click Value Field Settings. This will allow changing the value from Sum to Average. The pivot table has now been configured to display Temp and Activity averages.

6. Adding a Pivot Chart - Select the Insert tab at the top and choose PivotChart. This will place a chart in the Excel tab that is open and automatically graph the data. Multiple graph options can be selected, and in the first example a Stacked Line Graph is used to show variation of both Temp and Activity on the same graph. On the right side of the screen under Format Chart Area there are many adjustments that can be made to customize the graph features.

7. Select Insert at the top of the screen and select Slicer. This is a feature to allow filtering of the data quickly and easily by any field type chosen. The example shows filter by cage, to plot all data for only Cage 1, Cage 2, or both.

8. If both cages are desired to be plotted side by side on the same graph for one parameter, Temperature as example, go back to the PivotChartFields and add Cage as a Column(Legend)

NOTE: For this modification, right click on the graph and choose Change Chart Type to select a Line graph instead of a Stacked Line. Then select Column Labels and choose the Cage field to make sure both are selected.

9. Alternatively, to quickly switch from plotting the cage average to see individual subjects, remove the Cage column and replace with SubjectID as a column.

A slicer can be added for SubjectID to select specific subjects.

10. This is a tech note to assist getting started using Pivot Tables with Ponemah data but it is by no means a comprehensive instruction. There are many parameters that can be graphed as well as various ways to group the data.

Additional References

Setting up Excel Pivot Table and Pivot Chart from a Ponemah data Excel file export

VIDEO: Creating Charts and Tables from Ponemah Pivot Compatible Tables

How to import Experimental Protocol Header settings from a file