Exporting measurement data to Excel - good practice
Excel is still the most common place where monitoring data ends up - for summaries, charts and reports. But the value of a spreadsheet depends on how the data is arranged in it. A few simple rules save you hours of tidying up later and protect you from errors in analysis.
Long format or wide format
- Long format - one row = one measurement, with columns: date/time, station, indicator, value, unit. The most universal: easy to filter, sort and load into statistical tools.
- Wide format - one row = a station/date, with indicators in separate columns. Readable for a human and convenient for pivot tables, but harder for automatic analysis.
In practice it is worth keeping the data in long format as the “source of truth”, and building the wide format from it when needed.
What an export should contain
- the date and time of the measurement (preferably in an unambiguous format),
- the station / point and indicator (with unit),
- the value,
- useful metadata: who/how measured, notes, a quality flag.
Common pitfalls
- Dates and time zone - an ambiguous format (dd/mm vs mm/dd) and zone shifts are a classic source of errors. Keep one clear format.
- Decimal separator - in Polish Excel the default is a comma, while many tools expect a dot. Watch for consistency, especially when exporting to CSV.
- Units - record them explicitly; “42” without a unit is useless.
- CSV encoding - use UTF-8 so that special characters do not break.
- Blanks and “below threshold” - decide how you mark missing data and values below the limit of quantification.
In practice
The less manual handling there is at export, the fewer errors. LimnoLog exports data to Excel (along with metadata: station, session, indicator, unit), so you do not have to assemble the spreadsheet from scratch. On what to do next with such data, we write in the articles Time-series analysis and Preparing data for statistical analysis.
← Knowledge base