cleaning data

Filtering rows

Bring together the rows you want to work on, by the values of one column or of several. The other rows aren’t removed: they wait in grey after the last row, and cleaning or sorting leave them alone.

A filter brings together the rows that interest you now: the rows of one country, the ones without a price, the amounts over a thousand. They come first, with their colours, and the other rows wait after the last one, in grey. Nothing changes in the file: every row is still where it was, saving saves them all, and the line numbers keep their values, so you see the gaps (2, 5, 7) and, beside the grey rows, where each of them is.

What you do then, you do to the rows that pass. That is what a filter is for: find the rows of a kind, fix them, and go on to the next.

The rows in grey

The grey rows are a picture of the rows the filter leaves out, lined up under the same columns: there to be seen, so that filtering never makes anything vanish, and out of reach. You can’t put the cursor in them, select them or search them, and no operation touches them. To work on one, change the filter so that it passes, or clear it: Clear filter, at the top of the grey rows, is one way.

A table of cities filtered to India: the header and the rows of Delhi, Mumbai and Kolkata with their line numbers 3, 8 and 16, the funnel on column 3 in the column bar, and below them “27 rows don’t pass the filter · Clear filter” followed by the other rows in grey, Tokyo, Shanghai, São Paulo, each with its line number

They are all there, however many the filter leaves out, each with its line number beside it. In a file with hundreds of thousands of them, they go by faster than the rest of the file as you scroll.

Filtering a column

In the bar above the rows, each column ends in three small dots, one over another: its menu. Choose Filter… there to open the filter of that column. (A right click on the column’s number opens the same menu.) Columns › Filter… opens the filter of the cursor’s column.

The filter window of the column country: the condition set to Any value, a box to search values, and the list of seven countries with how many rows have each, all ticked but France and Spain

The window has two ways of choosing rows, which you can use together:

  • A condition on the value: contains, doesn’t contain, is, isn’t, starts with, ends with, is empty, isn’t empty; for numbers, is greater than, is at least, is less than, is at most and is between; and for dates, is before, is after and is between dates. Text is compared without telling capitals apart. Numbers are read as the column writes them, so 1.234,50 is more than a thousand in a column of decimal commas, and a value that isn’t a number passes none of the conditions on numbers.
  • The list of the column’s values, each with how many rows have it, in order: as numbers in a column of numbers, as dates in one of dates, alphabetically otherwise. Untick the ones you don’t want, or untick (Select all) and tick the few you do. Type in the box over the list to find a value among many; (Select all found) then ticks or unticks the ones the search finds.

Matches the pattern takes a regular expression, for what the other conditions can’t say: ^\d{5}$ is a value of exactly five digits.

Conditions on dates

Is before, is after and is between dates compare days, however each date is written: 3/5/2026, 2026-05-03 and 3 May 2026 are the same day, and a time after the date doesn’t count. Is between dates takes both ends in. Type the date as the column writes its own, or as year-month-day (2026-05-03), which reads only one way. A value that isn’t a date passes none of these conditions.

Is 3/5/2026 the 3rd of May or the 5th of March? The column’s own dates usually say: one 25/5/2026 among them and the day comes first. When none of them says, the window tells you how it reads them, the day first in a file that writes decimals with a comma and the month first otherwise, with a link to read them the other way.

Press OK, or Enter. The column’s dots turn blue in the bar, with a funnel beside them, and the status bar says how many rows show: 412 of 1,978 rows.

Columns › Filter by This Value is the quick way to the most common filter: it keeps the rows that have, in the cursor’s column, the value the cursor is on.

Several columns

Each column has its own filter, and a row shows only if it passes them all: the rows of Spain whose price is over 100. The list of a column offers the values of the rows the other filters leave, so it never offers a value that would show nothing.

To change a column’s filter, open its window again; Clear in it, or Clear This Filter in the column’s menu, removes that column’s filter and leaves the others. Columns › Clear Filter removes them all, and so does the × after the count in the status bar; a click on the count opens a menu with it.

Working on the rows that show

With a filter on, the editor works on the rows you see:

  • Cleaning and sorting. Trimming, changing case, reformatting numbers or dates, filling, clearing, grouping similar values (the Cells menu): only the cells of the rows that show change, whatever is selected, Ctrl+A⌘A included. Sorting sorts those rows among themselves, in the places they have, and removing duplicates looks only at them.
  • Selecting. A column selected in the column bar, Selection › Select Cells, Ctrl+D⌘D and Find and Replace pass over the hidden rows.
  • Copying. A copy of a selection, or of a column, takes the rows that show.
  • Saving a part. Saving writes every row. To write only the rows that show to another file, see Exporting the rows that show.
  • Typing. The cursor goes from a row to the next one that passes, and nothing you type, delete or paste reaches a row in grey. Backspace at the start of a row doesn’t join it to a hidden row above it.
  • Rows. A row moves past the hidden ones (Alt+↑⌥↑, Alt+↓⌥↓) by changing places with the next row that shows, and deleting a row leaves the hidden ones around it as they are. Rows selected from their line numbers reach over the hidden rows between them: Del or a cut removes the ones that show, and the hidden ones stay.
  • The Column profile counts the rows that show.

What changes the shape of the table still takes every row, because it must: inserting, deleting, moving, splitting or joining columns, and numbering the rows. The Quality Check panel reads the whole file too, and its fixes are for the whole file.

A filter follows the text

The rows are checked again after every change. Fix the country of a row in a filter of Usa, and once you have moved on that row is no longer one of them: it goes down with the grey ones. The row with the cursor always stays while you are in it, so that nothing vanishes as you type.

The same goes for a row you write yourself. Enter at the end of a row opens a new line right after it; if what you write there passes the filter, it stays with the others, and if it doesn’t, it joins the grey rows when the cursor leaves it. Either way it is in the file.

Columns: Reapply Filter checks every row right now, the cursor’s too.

Keeping or removing the rows

To make the file what the filter shows, Rows › Remove Rows Hidden by Filter deletes the rows in grey and keeps the ones that pass. Rows › Remove Rows Shown by Filter does the opposite: it deletes the rows that pass and keeps the grey ones, which is the way to remove the rows that match a condition (the test orders, the rows without an id). Both remove the filter afterwards, since there is nothing left to hide, and both are one step of Ctrl+Z⌘Z. They are also in the menu of the row count in the status bar.

A filter belongs to its tab and lasts while the tab is open: it isn’t saved with the file, and it isn’t there after a reload.

Exporting the rows that show

Ctrl+S⌘S always saves the whole file, grey rows included. With a filter on, File › Save As… and the formats of File › Download as (File › Export as in the desktop app) have one more option, Rows: every row, or only the rows shown by the filter, with the header row. It works for every format: CSV, TSV, a spreadsheet, JSON, SQL, Markdown, HTML.

A file with only those rows is an export: the tab stays with its own file and all its rows, and the name it proposes ends in “(filtered)”, so that it doesn’t go over the original.

Large files

Rows are checked a part at a time in files of more than a million characters, so the editor stays free while it reads; the status bar says how far it has got.