Spreadsheets

Sort and filter rows, save your own filter views, and control what goes into each cell.

A spreadsheet's Data menu sorts a selected range, filters its rows for everyone, and saves filter views that change only what you see. Data validation gives cells a dropdown, a checkbox or a number range, conditional formatting colours cells by their values, and tables name a range and its columns.

Get started free

Sort a range

Select a range and sort it from A to Z or from Z to A by the column of the active cell. Every selected column moves with its row, and a formula that moves to another row adjusts its relative references.

Sort order: Numbers sort by value and text without regard to case, with numbers inside text in order, so Item 2 comes before Item 10. Equal values keep their order, and blank cells go last.
A single column: Sorting one column on its own asks first, because the columns beside it will not move.
Size and Undo: One sort can cover up to 10,000 cells and is one step for Undo.

Filters for everyone, filter views for one person

A filter hides the rows of a range that do not match a condition on one column: is blank, is not blank, is equal to, text contains, is greater than or is less than. With First row is a header on, the header row stays in view, and the row numbers skip the hidden rows. The filter is part of the sheet: everyone who opens the spreadsheet sees it until someone chooses Clear filter.

A filter view filters a range, sorts it, or both, for one person. Each person saves their own named views, and opening one changes only what they see: the stored cells and other people's views stay as they are. Viewers and Commenters, who cannot change cells, can save filter views too, and the assistant can save one for you.

Dropdowns, checkboxes and number ranges

Data validation sets what a range of cells accepts. Blank cells are always allowed, a formula is accepted, and the rule also applies to pasted values.

Dropdown list: Enter the choices one per line. The cell shows an arrow, and double-clicking it opens the list.
Checkbox: The cell shows a box to tick, and stores the Checked value or the Unchecked value (TRUE and FALSE unless you change them) so formulas can use it.
Number range: A minimum, a maximum or both.
Reject input or Allow with a warning: Reject input refuses a value that breaks the rule and says so. Allow with a warning keeps the value and says it does not meet the rule.

Conditional formatting

A conditional formatting rule sets a text colour, a fill colour, bold text or a mix on the cells of a range whose value is greater than, less than or equal to a value, contains some text, is blank or is not blank. 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 applies.

Tables

Convert a range with a header row into a table, with a name for the table and one unique name for each column. The table gets header and banded-row shading. Tables cannot overlap.

Column names in formulas: =SUM(Sales[Revenue]) adds the Revenue column of the Sales table. Renaming the table or a column updates the formulas that use it.
New rows: Typing in the row just below a table adds that row to the table.
Convert to range: Turns the table back into ordinary cells and keeps their values and formatting.

Find and replace

Find searches the current sheet or all sheets, optionally matching case or the entire cell, and lists the matches; select one to go to its cell.

Replace all: Changes every match in one step that Undo reverses. Formulas are read again after the replacement, and validation rules still apply.
Viewer and Commenter access: Find works for everyone who can open the spreadsheet. With Viewer or Commenter access, sorting, filters, rules, tables and Replace all are shown as unavailable.

Sort and filter your first sheet.

Create a Magpie account, open Spreadsheets, and import a CSV file or select New Spreadsheet.

Create account