Pivot Table Example: Reading Instrument Data

This guide explains how to read and interpret instrument readings using a pivot table, with a concrete example from a temperature data logger. You’ll learn what a pivot table does, how to structure your data, and how to summarize readings by time, sensor, or condition.

Formula

The average interval between readings is the total time divided by the number of readings:

Average interval (min) = Total time (min) ÷ Number of readings

Readings per hour = Number of readings ÷ (Total time in hours)

Variables: t = total time in minutes, n = number of readings. The result is in minutes per reading or readings per hour.

Worked Example

Suppose you logged temperature every 5 minutes for 24 hours. That’s 1440 minutes and 288 readings. Using the formula:

Average interval = 1440 ÷ 288 = 5.00 minutes

Readings per hour = 288 ÷ (1440/60) = 288 ÷ 24 = 12 readings per hour

This matches the default values in the calculator.

What Is a Pivot Table?

A pivot table is a data summarization tool that lets you reorganize and aggregate large datasets without changing the original data. You drag fields into rows, columns, and values to create a compact summary. For instrument readings, a pivot table helps you see patterns—like average temperature per day, maximum pressure per sensor, or count of alarms per shift—by grouping raw readings.

Instrument data often comes as a long list: timestamp, sensor ID, reading value, and maybe a status flag. A pivot table turns that list into a meaningful table, such as daily averages or hourly maximums.

Example Dataset: Temperature Logger

Imagine a temperature logger that records readings every 5 minutes from three sensors (A, B, C) over one day. The raw data has columns: Timestamp, Sensor, Temperature (°C). A sample of the first few rows might be:

• 2025-01-01 00:00, A, 22.1
• 2025-01-01 00:00, B, 22.3
• 2025-01-01 00:00, C, 22.0
• 2025-01-01 00:05, A, 22.2
• 2025-01-01 00:05, B, 22.4
• 2025-01-01 00:05, C, 22.1

With 288 readings per sensor per day, you have 864 rows total. A pivot table can summarize this into a daily average per sensor, or an hourly average for all sensors.

How to Build the Pivot Table

In a spreadsheet or BI tool, you typically:

1. Select the raw data range.
2. Insert a pivot table (Excel: Insert > PivotTable; Google Sheets: Data > Pivot table).
3. Drag “Sensor� to Rows.
4. Drag “Temperature� to Values, then set it to Average (or Max/Min).
5. Drag “Timestamp� to Columns or Rows if you want time grouping (e.g., by hour or day).

For time grouping, you may need to extract the hour or date from the timestamp using a formula like =HOUR(A2) or =TEXT(A2,”yyyy-mm-dd”) before pivoting.

Interpreting the Results

The pivot table output might look like this:

• Sensor A: Average 22.4°C, Max 23.1°C, Min 21.8°C
• Sensor B: Average 22.6°C, Max 23.3°C, Min 22.0°C
• Sensor C: Average 22.2°C, Max 22.9°C, Min 21.7°C

From this, you can see sensor B runs slightly warmer, and all sensors stay within a 1.6°C range. If you add time to rows, you can spot when temperatures spike or drop.

Common Mistakes and Misconceptions

One common error is placing the reading value in the Rows area instead of the Values area, which creates a long list of unique values rather than a summary. Another is forgetting to refresh the pivot table after adding new data—some tools don’t auto-update. Also, ensure the timestamp is recognized as a date/time, not text, or grouping by hour/day will fail.

Another misconception: a pivot table changes the original data. It doesn’t—it only creates a summary view. You can safely delete the pivot table without affecting the raw readings.

Choosing the Right Aggregation

What you put in the Values field depends on your question:

• Average: typical value over time.
• Maximum/Minimum: extremes, useful for alarm thresholds.
• Count: how many readings fall into a category (e.g., number of alarms).
• Standard deviation: variability of the readings.

For example, if you’re monitoring a freezer, you care about maximum temperature to ensure it never exceeds -18°C. If you’re checking sensor stability, standard deviation is more informative than average.

Frequently Asked Questions

Can I use a pivot table with non-numeric instrument readings?

Yes, but you can only count them or list unique values. For status codes like “OK� or “FAIL�, you can count how many of each occur per day.

How do I handle missing readings in a pivot table?

Missing values are simply ignored in calculations, but you can add a filter to exclude blank cells. If you need to know how many readings are missing, use a count of timestamps versus expected count.

What if my timestamps are in multiple columns (date and time separate)?

Combine them into one column using a formula like =DATE+TIME in Excel, or use the CONCATENATE function. Then make sure the combined column is formatted as a date/time.

Can I create a pivot table from live sensor data?

Yes, if your tool supports connections to a database or live feed. You may need to refresh the pivot table periodically to see new readings.

Why does my pivot table show “(blank)� in the row labels?

That indicates rows with empty cells in the field you dragged to Rows. Check for missing sensor IDs or timestamps in your raw data.

Is a pivot table the same as a chart?

No. A pivot table is a tabular summary; a pivot chart is a graphical representation of that summary. You can create a chart directly from a pivot table.

Related

• BMW i3 Sport i3s Electric Car: Full Guide
• Lookup and Sum Column: How To
• Air Jordan 11 Sizing & Comfort FAQ

in t | Total time (min) | min | 1440 | 0
in n | Number of readings | - | 288 | 1
out avg | Average interval (min) | min | t / n | 2
out per_hour | Readings per hour | - | n / (t / 60) | 2