The Excel workbook is included with our video training.

Abstract 

In this video, we look at how 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.

Transcript 

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

This data consists of Temperature readings taken in a greenhouse 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 generally 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 each 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 that clearly shows which times during the day have warmer and cooler temperatures.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.