cleaning data

Empty and repeated values

Fill the gaps in a column with the value above, empty the values that repeat the one above, fill empty cells with your own text, turn NA, null and the like into empty cells, and remove empty rows.

Data exported from reports and pivot tables often writes a value once and leaves the cells below it empty until it changes; other files write the same value on every row. Some say “nothing” with an empty cell, others with NA, null or a dash. These operations go from one way to the other.

sales.csv
region,store,month,sales
North,Leeds,Jan,18240
,Leeds,Feb,20195
,York,Jan,9630
,York,Feb,10120
,Hull,Jan,7410
South,Bristol,Jan,14320
,Bristol,Feb,15164
,Exeter,Jan,8870
,Exeter,Feb,9015
,Bath,Jan,6230
East,Norwich,Jan,11080
,Norwich,Feb,11960
,Ipswich,Jan,7790
,Ipswich,Feb,8140
West,Cardiff,Jan,12650
,Cardiff,Feb,13010
,Swansea,Jan,6980
,Swansea,Feb,7325
,Newport,Jan,5410
,Newport,Feb,5690
region · Row 1

Fill Down

Cells › Fill and Clear › Fill Down fills each empty cell with the nearest value above it in its column, the way a person reads a grouped report: a region column with North and then two empty cells becomes North, North, North.

A cell counts as empty when it has nothing in it, or only spaces. A missing value like NA is a value: if the gaps are written that way, run Cells › Fill and Clear › Empty Missing Values (NA, null, -…) first. With a selection, only the selected cells are filled, but the value can come from above the selection.

Clear Consecutive Repeats

Cells › Fill and Clear › Clear Consecutive Repeats is the opposite: a value equal to the one right above it is emptied, so each value is written only where it starts. It makes a sorted column read like a grouped report.

Only repeats one right after the other count: North, North, South, North becomes North, an empty cell, South, North. The last North stays, because the value above it is South. An empty cell in between also breaks the run. To find every value that appears more than once in a column, wherever it is, see Select Duplicate Values.

Fill Empty Cells

Cells › Fill and Clear › Fill Empty Cells… asks for a text and writes it in every empty cell: 0 in a column of amounts, unknown in a column of categories. Type the text and press Enter; with nothing typed, it does nothing. If the text needs quotes (it has a comma, say), they are added.

Empty Missing Values

Cells › Fill and Clear › Empty Missing Values (NA, null, -…) empties every value that means “nothing here”: NA, N/A, #N/A, null, NaN, none, nil, \N, -, --, ? and ., in capitals or not. It leaves one way of saying it, the one Excel, databases and most programs read as no value. The editor shows these values in grey, so you can see beforehand what will go (see Missing values and stray spaces).

Clear Cells

Cells › Fill and Clear › Clear Cells empties the cells, and leaves their delimiters, so the columns stay where they are. Like every operation, with only a cursor it takes the whole column: select the cells first if you want fewer. To remove the column itself, see Columns.

Remove Empty Rows

Rows › Remove Empty Rows removes the rows that have nothing in any cell: empty lines, lines with only spaces, and rows of only delimiters (,,,), which spreadsheets leave when rows are cleared but not deleted. A row with a missing value such as NA in it isn’t empty. The header row stays, and so does the line break at the end of the file. With two rows or more selected, it removes the empty ones among those only.

To see where the empty cells are, rather than whole rows, see Select Empty Cells.