Two simple tools that answer many questions
Before reaching for formulas or summary tables, it is worth knowing how far sorting and filtering will take you. Sorting changes the order of rows. Filtering hides rows that do not match a condition. Between them, they answer questions such as “Which orders were largest?”, “What did this customer buy?” or “Which entries are missing a date?” with no calculation at all.
Both work best on a tidy table with a header row, one record per row and no empty rows or columns in the middle.
Sorting
Sorting arranges rows according to the values in one or more columns.
- Numbers can be sorted smallest to largest or the reverse.
- Text can be sorted alphabetically, from A to Z or Z to A.
- Dates sort in time order, provided they are stored as real dates. Dates stored as text sort alphabetically, which gives a confusing result.
Sort the whole table, not one column
The classic mistake is sorting a single column while the others stay put. The values in that column move, but the rest of each record does not, so names end up beside the wrong amounts. The damage can be hard to notice and hard to reverse after saving. Always select the entire table, or just click a cell inside it so the program detects the full range, and confirm that the option for a header row is set correctly. Afterward, spot-check a couple of rows against the original.
Sorting by more than one column
You can sort by a main column first, then by a second to settle ties. A hypothetical example: sort a list of orders by Region, and within each region by Amount from largest to smallest. Each region’s biggest orders then appear at the top of its block. The order of the sort levels matters, so put the main grouping first.
Custom order
Sometimes alphabetical order is not what you want, such as months (January, February, and so on) or priority levels (High, Medium, Low). Many programs allow a custom list. Another approach is to add a helper column with numbers, such as 1, 2, 3, and sort by that.
Filtering
Filtering shows only the rows meeting your conditions, and temporarily hides the rest. The hidden rows are not deleted. Clearing the filter brings them back.
Typical conditions include:
- Equals a value, such as Region equal to East.
- Text contains a word or begins with a letter.
- Number comparisons, such as greater than 100 or between 50 and 200.
- Date ranges, such as within a certain month.
- Blanks or non-blanks, which is useful for finding missing entries.
- Several columns at once, combining conditions, such as East and also Pens.
Applying filters to more than one column narrows things further, since a row must satisfy all of them to remain visible. Take care with the idea of “and” versus “or”. Within one column you can usually pick several values to include, which acts like “or”. Across columns, the filters combine like “and”.
A small hypothetical example
Suppose a list has six sales rows with amounts of 10, 20, 15, 5, 5 and 10. Filtering for amounts greater than 9 leaves four rows (10, 20, 15 and 10). Sorting those four from largest to smallest puts 20 first, then 15, then the two 10s. You have answered “which sales were above 9, biggest first?” with no formulas.
Filtering is also a quick way to inspect a column’s distinct values. The drop-down list for a column shows each variant, which makes inconsistent spellings easy to see. That makes it a handy first step in data cleaning.
Protect the original data
Sorting and filtering change what you see, and sorting changes the stored order. To explore safely:
- Work on a copy when the order matters or you are not sure what you will do.
- Add an ID or row-number column before sorting, with values typed in as plain numbers rather than formulas. You can sort by it later to restore the original order.
- Use filter views if your tool has them. In shared online files, a standard filter changes the display for everyone looking at the file, which can confuse colleagues. Personal or temporary filter views let you look at the data your way without changing what others see. If your program lacks them, make your own copy.
- Avoid deleting visible rows in a filtered list unless you are sure you understand what will be removed. Some operations apply only to visible rows and some to all.
- Clear filters when done, so the next person does not think data is missing.
Beware of misleading totals
Filtered and hidden rows interact with formulas in different ways. A plain sum may still include hidden rows, while certain functions designed for subtotals count only the visible ones. If a total beside a filtered list does not match what you see, this is a likely reason; it is one of the typical spreadsheet errors. Check by comparing against a manual total of the visible rows.
From finding rows to summarizing them
Sorting and filtering are about locating records. When you want totals by group, such as the amount per region, pivot tables do the grouping for you. When you need a calculated answer under a condition, basic formulas such as conditional sums and counts are a good fit. For feedback collected in a form, such as responses with comments, filtering by answer is a fast way to read customer feedback for a given group.
Habits worth building
- Keep headers in the first row and make them distinct.
- Keep each column to one type of data.
- Save before major sorts.
- Make a note of which filters are on.
- Compare the number of rows shown with what you expect.
With these habits, sorting and filtering become reliable ways to ask questions of your data, and you can do so with little risk to the original.