Exceljet

Quick, clean, and to the point

Pivot Table Example - Instrument readings

In this example, we're going to use a Pivot Table to analyze instrument measurements.

This data consists of Temperature readings taken in a green house over a period of days. We have Date, Time, temperature in Celsius, and temperature in Fahrenheit.

So, first, how many readings do we have?

You can see that we have over 9000 readings total.

How many reading per day is this?

If I add Date as a row label, we get a breakdown by day.

And you can see that on most days, we have 720 readings.

If you want to see a breakdown by hour, you could use time as a column label. You can see there are genrally 30 readings per hour, so one reading every 2 minutes.

Next, what are the maximum and minimum temperatures recorded each day?

We'll need two instances of a temperature field, and then we'll need to change the calculation for each.

This data would also work well as a Pivot Chart.

Next, what's the average temperature at each hour of the day?

For that, I can change the calculation for the minimum field, and then add Time as a column label.

Finally, one last question:

How can these temperatures be visualized with color?

To achieve this, we can use a Color Scale applied with conditional formatting.

The result is a colorful table clearly shows which times during the day have warmer and cooler temperatures.

Course 
Core Pivot

Related shortcuts

Author 
Dave Bruns