Sorting, filtering and data validation

Many spreadsheets are not calculations at all but lists: a shop's stock, a club's members, a class register, a list of tasks. Once a list grows past a few rows you need to put it in order, find the rows that matter and keep the entries consistent. Sorting, filtering and validation do exactly that. They are also where beginners most often damage their own data, for example by sorting one column on its own, so this lesson spends time on doing it safely.

Start with a tidy table

Sorting and filtering only work well when the list is laid out as a proper table. The rules are simple: put one heading row at the top; give every column one kind of information; put one record per row, such as one item of stock; leave no blank rows or blank columns in the middle; and keep other things, such as notes or totals, away from the table (a blank row and column away, or on another sheet). Avoid merged cells in a list because sorting cannot handle them.

Our example is the stock list of a small, fictional shop, Wanjiru's Stationery. Each row is one product, with its category, how many are in stock and the level at which it should be reordered.

Sorting without scrambling

Sorting puts the rows in an order: A to Z or Z to A for text, smallest to largest for numbers, oldest to newest for dates. The vital point is that sorting must move whole rows. If only one column moves, each item ends up beside the wrong quantity. Think of a row as a line of children holding hands: to rearrange them, you move the whole line, not one child.

To sort by two columns, add a second key: sort by Category first, then by Item within each category. Calc has a Then by section on the same tab and Excel has Add Level. For a quick sort you can also click a cell in a column and press the Sort Ascending or Sort Descending button on the toolbar. This works well when the table is tidy and has a heading row.

Filtering: showing only what you need

Filtering hides the rows that do not match your wish, without deleting them. The rows that remain are the answer to a question such as "Which items are in the Writing category?" The hidden rows are still there, and the row numbers on the left skip the missing ones, which is your clue that a filter is on. Calc calls this an AutoFilter; Excel calls it a Filter.

A column of numbers offers number filters too, such as Standard Filter in Calc or Number Filters in Excel. You can choose rules like "Quantity less than 10". Combine the idea with a formula and you can ask more complex questions, for instance a column that says =IF(C2<D2,"Reorder","OK") which flags rows where the stock has fallen below the reorder level. Then filter that column for Reorder.

Data validation: stopping mistakes at the door

Data validation sets rules for what may be typed into a cell. It is the reason online forms show a drop-down list for the country instead of letting you type anything. In a stock list, if people type "Writing", "writing", "Writting" and "Pens", filtering and sorting no longer work properly, because the spreadsheet sees four different categories. A drop-down list with only the allowed values removes the problem.

Validation can also limit numbers. Choose Whole Numbers for the Quantity column and set a minimum of 0 so that nobody records minus five pens. Choose Date to allow only dates, or Text length to limit codes to a certain size. The rule applies to what is typed in future, and values already in the cells are not checked automatically. Pasting can also bypass the rule, so it is a guard, not a lock.