Sort, filter and check spreadsheet data

These commands are in the Data and Format menus of a spreadsheet, and the ones that act on a selection are also on its right-click menu. With Viewer or Commenter access, the commands that change the spreadsheet are shown as unavailable; Find still works.

Sort

Select a range and choose Data → Sort A–Z or Sort Z–A. The column of the active cell is the sort key, and every selected column moves with its row. Equal values keep their order and blanks go last. A formula that moves to another row adjusts its relative references.

If you select only one column, Magpie asks before sorting it, because the columns beside it will not move. Select the whole table first when the columns belong together. One sort can cover up to 10,000 cells and is one step for Undo.

Filter

Select a range and choose Data → Filter. Choose a Column and a Condition (is not blank, is blank, is equal to, contains, is greater than or is less than) and a Value, then select Apply filter. Leave First row is a header on to keep the header row showing. Rows that do not match are hidden, and the row numbers skip them.

The filter is part of the sheet: everyone who opens the spreadsheet sees it. Clear filter shows every row again. Deleting the column a filter uses clears the filter.

Filter views

A filter view filters or sorts rows for you alone. Choose Data → Filter views, give the view a Name, select the range to use, choose a filter condition, a sort column and direction, or both, and select Save filter view. Opening a view changes only what you see: other people's view and the stored cells do not change. Viewers and Commenters can save their own views. Close active view shows the sheet again; a saved view can be changed or deleted in the same drawer.

Dropdowns, checkboxes and number rules

Select cells and choose Data → Data validation. Choose a Rule:

• Dropdown list: enter the values, one per line, up to 100. Double-click a cell to choose a value.
• Checkbox: the cell stores the Checked value and Unchecked value, TRUE and FALSE unless you change them, so formulas can use them. Double-click the cell to tick or untick it.
• Number range: a Minimum, a Maximum or both.

Under Invalid values, choose Reject input or Allow with a warning, then select Save rule. Blank cells are always allowed, and a formula is accepted in a validated cell. Rules also apply to pasted values. A sheet can have up to 200 rules.

Conditional formatting

Select cells and choose Format → Conditional formatting. Choose a condition (greater than, less than, equal to, contains, blank or not blank) and a value, then a Text color, a Fill color, Bold text or a mix, and select Save rule. The formatting follows the calculated values and changes as they change; the cells' own values and styles stay as they are. When two matching rules set the same thing, the later rule wins. A sheet can have up to 200 rules.

Tables

Select a range with a header row and at least one row of data, and choose Data → Tables. Enter a Table name and one unique name per column, then select Convert selection to table. The table gets header and banded-row shading, and its columns can be used in formulas by name: =SUM(Sales[Revenue]). Typing in the row just below a table adds the row to it. Tables cannot overlap. Renaming a table or column updates the formulas that use it; deleting it makes them show #REF!. Convert to range removes the table and keeps its values and formatting.

Find and replace

Choose Edit → Find and replace, or press Ctrl/⌘ F. Enter the text, choose Current sheet or All sheets, and tick Match case or Match entire cell if needed, then select Find. Select a result to go to its cell. A search lists up to 500 matches; narrow it if it says the list was cut short.

Enter text in Replace with and select Replace all to change every match in one step that Undo reverses. Formulas are read again after the replacement, and validation rules still apply. Viewers and Commenters cannot replace.

If a sort, filter or rule does not do what this page describes, Contact support.