Data in the spreadsheet: sorting, filters and tools

What it is

The Spreadsheet data menu (the funnel button on the toolbar; on a narrow screen, under More tools) changes the order and visibility of rows: sorting, filters, personal filter views, data cleaning, subtotals, groups, and freezing and hiding of rows and columns.

How to use it

1. Select a range, or stand in any cell: then the whole filled area of the sheet is taken.
2. Open Spreadsheet data. The Column A button chooses the column the menu works with. The Header row checkbox keeps the first row in place.
3. Sorting: Sort A: A → Z or Z → A. For several columns, use Sort range…: First by, then by, Add level, and then Sort.
4. Filter: Filter: A. By values: tick the values. There is a search, Select all, Clear selection, and a long list loads more with the More button. By condition: contains, does not contain, begins with, ends with, equal to, not equal to, greater than, greater than or equal to, less than, less than or equal to, between, not between, empty, not empty. By color: the fill or the text colour from this column. The text condition understands the words of a cell in any interface language: “contains январь” and “contains January”, “equal to ИСТИНА” and “equal to TRUE”, “1,5” and “1.5” select the same rows. So co-authors with different languages see the same thing under a shared filter. Click Apply. Clear filters removes all filters on the sheet.
5. Filter views: enter a name in Save current filters as and click Save. Everyone sees the list of views, but a view is switched on only for you: a click on its name switches it on, Turn off filter view switches it off, and × deletes it.
6. Split text into columns… works on one column: choose a Separator (Automatic, Comma, Semicolon, Tab, Vertical bar or Space), look at the preview, and click Divide. If there is data on the right, the window shows how many cells will be overwritten.
7. Remove duplicates…: tick Columns for comparison, then click Remove duplicates. Letter case and spaces at the edges are not taken into account. The first row of the repeats stays, and empty rows are not removed.
8. Trim spaces removes spaces at the edges of the text and double spaces inside it.
9. Subtotals…: the group column (With every change in), the Function (Sum, Count, Average, Max or Min), and the columns in Add totals by, then Add totals. The data is sorted by the group column. A total row appears after each group, and a grand total at the end. The groups are collapsed with the buttons to the left of the row numbers. Row labels are in the interface language of the person who added the totals: “ Total”, “ Count”, “ Average” and so on. At the end: Grand Total, Grand Count, Grand Average, Grand Max and Grand Min. A group without a value is labelled (blank).
10. Group rows 3–7, Group columns B–D and Ungroup work on the selection.
11. Freeze rows: N and Freeze columns: N freeze everything up to and including the active cell. Unfreeze returns the scrolling fully.
12. Hide rows and Hide columns work on the selection. Show all rows and columns brings them back.
13. Any action of the menu is undone with Ctrl+Z, except switching on a filter view: that is personal.

Limits

• The menu is available only to those who can edit the spreadsheet. In view mode it is absent.
• You can freeze up to 10 rows and up to 10 columns.
• No more than 50 filter views per sheet, and a view name can be up to 100 characters.
• Filter values are shown in portions of 200.
• Subtotals: up to 190 groups, and the sheet must not grow beyond 99 999 rows after rows are inserted.
• Groups can nest up to 8 levels. Groups for rows or columns: up to 500.
• Splitting text does not go beyond the 999th column. The preview shows the first 10 rows.
• Sorting, removing duplicates and totals do not work over merged cells.
• You cannot hide all the rows or all the columns of a sheet.

If something goes wrong

What you see on the screen	Why	What to do
Unmerge cells before sorting	The range has merged cells	Unmerge them, or select a range without them
Unmerge cells in the range	The same happens when removing duplicates	The same as above
Unmerge cells at the freeze boundary	A merge crosses the freeze line	Unmerge it, or freeze a different number of rows
Fill in the condition value	A filter condition has no value	Enter a value; for between, enter both
Leave one visible row and column	An attempt to hide everything	Hide less
Select one column of text	More than one column is selected for splitting	Select one column
Enter a title for the view, or No more than 50 views	An empty name, or the limit has been reached	Enter a name, or delete an extra view
Too many groups or rows for totals	There are more than 190 groups, or the sheet would grow beyond 99 999 rows	Reduce the range
Select a column with groups and at least one value column without merged cells	The totals columns are not selected, or merges get in the way	Tick a value column, and unmerge the cells
No more than eight nesting levels	A ninth level of groups	Ungroup the outer level
No extra spaces found, No duplicates found	There is nothing to change	No action needed