Finding the true story in a spreadsheet

Every winter, thousands of people in Aotearoa sit by a window for one hour and count birds for the New Zealand Garden Bird Survey. Scientists at Manaaki Whenua – Landcare Research add all those counts together. Over many years, the data shows which birds are becoming more common in our gardens and which are declining. One person's count is small. Thousands of fair counts tell a big story.

Collect it fairly

Data is only useful if it is collected the same way each time. The bird survey asks everyone to record the highest number of each kind of bird they see at one time. That way nobody counts the same sparrow twice. Before you collect data, agree on the place, the time, the length of the count and exactly what counts.

Rows and columns

A spreadsheet stores data in a grid. Each row is one observation, such as one count at one place. Each column is one kind of information, such as date, place, bird and number. - Cell: one box in the grid, like B4. - Formula: an instruction that calculates, such as =SUM(D2:D40) to add up a column. - COUNTIF: counts how many cells match something, such as every row that says 'tūī'.

Clean before you count

Real data is messy. One person types 'tui', another 'Tūī', another 'TUI', and the spreadsheet thinks they are three different birds. Someone types 400 instead of 4. A row gets entered twice. Cleaning means fixing these problems carefully and keeping a log of every change, so you never hide or invent anything.

Pick the right chart

Charts that trick

A chart can mislead without a single wrong number. If the vertical axis starts at 90 instead of 0, a tiny difference looks huge. A 3D pie makes the front slice look bigger. Always check the axis, the scale and the title. Then write two sentences: what the data shows, and what it cannot tell you.