Sort, filter and clean up sheet data

Sort rows

Sorting keeps each row's cells together, so a customer's name never ends up beside someone else's phone number.

• Sort A → Z and Sort Z → A sort by the column you're in. With one cell selected, Memlow sorts every row under the header row. With a range selected, it sorts just those rows.
• Sort range… opens a panel for more control. Set the Range, pick a column under Sort by and choose A → Z or Z → A. Add another sort column adds a Then by column to break ties, for example by class and then by name. Choose Sort.

Empty cells always go last.

Filter rows

A filter hides the rows you don't need to see right now, such as every order that isn't paid yet.

1. Select the data, or one cell in it, and choose Data, then Add a filter.
2. Each column's header now has a filter button. Choose it for the column you want to filter.
3. Untick the values you want to hide under Values to show, or set a Condition. Search values helps in a long list.
4. Choose Apply.

The filter button's menu can sort that column too. To show every row again, choose Data, then Remove the filter.

A filter is part of the sheet: in a shared sheet, everyone sees the rows it hides.

Save filter views

A filter view is a filter with a name that you open only for yourself. Opening one changes only what you see, and everyone else sees the sheet as it is. That makes it the better choice in a shared sheet, for example "This month's orders" for one person and "Unpaid" for another.

1. Choose Data, then Filter views.
2. Choose Create new filter view.
3. Give it a Name, check the Range, and set its filters under Filter by column.

The line above the grid says "Filter view:" and the view's name while it's open. Choose Close view to go back to the whole sheet, or Delete view in the panel to remove it. Your views are saved with the sheet, so you can open them again whenever you like. Filter views open in the full-screen sheet, not in a small sheet inside a note.

Remove duplicates

Use it for a list where the same row was typed or imported twice, such as a customer list or attendance.

1. Select the rows, including the header row if there is one.
2. Choose Data, then Remove duplicates.
3. Leave Data has a header row on if your first row holds the column names.
4. Under Columns to compare, tick the columns that make a row a duplicate. Untick a column like "Date" if the same customer on two days isn't a duplicate for you.
5. Choose Remove duplicates.

A row counts as a duplicate when the ticked columns hold the same values as a row above it. Upper and lower case don't matter. The first one stays, the copies go, and the rows that stay move up. Memlow says how many it removed and how many are left, or "No duplicates found."

If you change your mind, undo it straight away with Undo.

Split text into columns

When one column holds "Ayesha Khan, 0300 1234567" and you want the name and number apart:

1. Select the cells in that column.
2. Choose Data, then Split text to columns.
3. Under Separator, leave Detect, or pick Comma, Semicolon, Period, Space, Tab or Custom and type what to split at.
4. Check the Preview, then choose Split.

The parts go into the columns after it. Memlow asks before it writes over cells that already have something in them. Formulas aren't split.

Trim extra spaces

Text pasted from a message or a website often has spaces at the start or end, which can make two names that look the same count as different. Select the cells and choose Data, then Trim whitespace. Memlow takes spaces off both ends and turns runs of spaces inside into one. It says how many cells it trimmed, or "No extra spaces found". Formulas aren't changed.

Protect cells in a shared sheet

Protected ranges let the owner of a shared sheet decide who may change some cells, for example the totals row of a shop's sales sheet or the prices column.

1. Choose Data, then Protected ranges.
2. Choose Protect a range and type or select the Range.
3. Under Who can edit, choose one:
   • Only the owner: everyone else can read these cells but not change them.
   • The owner and chosen people: pick the people who may change them.
   • Anyone, after a warning: people are asked before they change these cells.

Only the owner can add, change or remove protection (Remove protection). Protected ranges take effect once the sheet is shared. Until then nobody else can open it anyway.

Someone who isn't allowed to change a protected cell sees "This range is protected. Ask the owner to change it." With a warning range, they see Change a protected range? and can choose Change anyway.

For more on what can go in a cell, see Smart cells, links and cell notes. For freezing, hiding and merging, see Freeze, merge, hide and resize.

Questions and answers