1. Engineering data analysis workflow in Excel

Sensor logs rarely arrive in a form that Excel can analyze directly. A typical data acquisition system exports a flat text or CSV file with a timestamp column, one or more measurement channels, and possibly a status or error flag. Before any formula can produce a meaningful result, that raw stream must be imported with correct type handling, restructured into a stable tabular layout, and mapped to an analysis plan that determines which formulas belong in which columns. This chapter establishes that workflow as a repeatable discipline rather than an ad-hoc sequence of copy-paste operations.

From Sensor Log to Structured Table

The first decision is how to bring the file in. For CSV and delimited text, Data > Get Data > From Text/CSV (Power Query) is preferable to double-clicking the file, because it records every transformation as a repeatable step. When the import dialog appears, verify three things before loading: the delimiter, the data type of the timestamp column, and the decimal/thousands separators. A European-locale logger may emit 1.234,56 where the workbook expects 1,234.56; if the locale is misread, every numeric channel silently becomes text and downstream formulas return #VALUE!.

Timestamps deserve special attention. Excel stores dates as serial numbers, so a log recorded at 10 Hz for eight hours produces 288,000 rows whose timestamps differ by 0.0000116 day. If the timestamp column is imported as text, sorting and interval calculations fail. Convert it explicitly in Power Query using Change Type > Date/Time, and confirm that the first and last rows show the expected start and end times. For logs that span midnight or cross time zones, record the offset in a separate column rather than embedding it in the timestamp string.

Once loaded, the data should be shaped into a tidy structure: one row per observation, one column per variable, and a single header row with no merged cells, blank rows, or subtotal lines. A common defect in exported logs is a repeated header every few thousand rows, or a units row beneath the header. Both break structured references and pivot tables. Remove them during import, and keep units in the column name itself, for example Pressure_kPa or Temp_C, so that formulas and charts remain self-documenting.

Planning the Formula Layer Before Writing Formulas

The most expensive mistake in formula-driven analysis is writing formulas before deciding what the columns mean. A workable plan separates the sheet into four logical zones: raw data (the imported table, never edited), derived columns (calculated fields appended to the table), summary calculations (aggregates, statistics, and engineering metrics), and presentation (charts and report cells). Keeping these zones distinct prevents circular references and makes it obvious which cells are inputs and which are outputs.

Derived columns should be added inside the table so they inherit row context. For a vibration log with an acceleration channel sampled at a fixed interval, a derived velocity column might integrate acceleration using the trapezoidal rule:

=IF(ROW()=2, 0,
   PREVIOUS_VELOCITY + (Acceleration_g*9.80665 + PREVIOUS_ACCEL*9.80665)/2 * dt)

In practice, with the table named VibLog and a sampling interval stored in a named cell dt, the formula in the second data row references the row above through structured references, and the first row is seeded with zero. The point is not the specific integral but the discipline: every derived column has a documented input, a stated assumption (here, zero initial velocity), and a unit conversion applied explicitly rather than hidden in a magic number.

Summary calculations belong outside the table, in a clearly labeled block. Engineering metrics such as RMS, peak-to-peak, crest factor, and mean are computed with SUBTOTAL or AGGREGATE so that filtered views remain consistent, and with AVERAGEIFS/STDEV.S when the metric must be conditioned on a status flag or operating regime. Naming these result cells (RMS_g, CrestFactor) lets later formulas read like engineering statements rather than cell addresses.

Validation Before Analysis

Before trusting any formula, run a short validation pass on the imported table. Check that the row count matches the logger's export count, that the first and last timestamps bracket the intended test window, and that each numeric channel returns a number from ISNUMBER rather than ISTEXT. A quick COUNT versus COUNTA comparison on a channel column reveals hidden text entries. If the sampling interval is nominally constant, compute the difference between consecutive timestamps and confirm that the maximum deviation is within the logger's stated jitter; a sudden gap usually indicates dropped packets or a midnight rollover that was parsed incorrectly.

A Minimal Worked Example

Consider a pressure log sampled at 1 Hz for one hour, exported as pressure_log.csv with columns Timestamp, Pressure_bar, and Status. After import, the table PLog holds 3,600 rows. A derived column converts bar to kPa (=[@Pressure_bar]*100), a second derived column computes the first difference to detect step changes, and a summary block reports mean, standard deviation, and maximum rate of change using AGGREGATE(1,6,PLog[Pressure_kPa]) and MAX(PLog[Delta_kPa]). If the mean is implausibly far from the expected operating pressure, the first place to look is the locale setting during import, not the formula.

The workflow described here is deliberately front-loaded: most of the effort goes into import correctness and structural discipline, and comparatively little into the formulas themselves. That ratio is intentional. In engineering data analysis, a formula is only as trustworthy as the table it reads from, and a table is only as trustworthy as the import that produced it. Establishing this sequence once, and reusing it through Power Query and named ranges, turns each new sensor log into a repeatable analysis rather than a fresh manual exercise.