5 Sorting and Filtering Data

Prepare the data

Before working with records, arrange the data as a clear table. Give every column a distinct header, keep one record in each row, and use each column for one type of information. Avoid blank rows or columns that break up the dataset, and store dates and numbers consistently as dates and numbers rather than as text.

Keep all fields belonging to a record together. Identify the header row so it is not treated as a record, and select the complete data range when sorting. Sorting only one column can associate values with the wrong records.

Sort complete records

Sorting rearranges records into an order without changing their underlying values. A Sort key is the column that determines the order. Common orders include text from A to Z or Z to A, numbers from smallest to largest or largest to smallest, and dates from oldest to newest or newest to oldest.

For example, sorting a sales table by Date from newest to oldest puts the latest transactions first. A Multi-level sort uses a second key to order records tied on the first key. Sorting by Region from A to Z and then Sales from largest to smallest groups records by region and ranks sales within each group.

Select the full data range, choose the sort column and direction, and add secondary sort keys when useful. Spreadsheet commands and menus vary, but the key principle is to sort whole records rather than isolated fields.

Apply and combine filter criteria

A Filter displays records that match chosen criteria and hides the others from view; hidden records are not deleted. You can select values in a column or use conditions, such as Sales greater than $100$, Region equal to East, or Name containing Lee. Filters can be applied to multiple columns at the same time.

For example, to inspect East-region records with sales above $100$, set Region to equal East and Sales to greater than $100$. Criteria applied in separate columns typically combine with AND, so a record must satisfy both conditions. Selecting several allowed values within one column typically combines with OR, so a record can match any selected value. Some spreadsheet tools also offer explicit AND and OR options for custom conditions. Check the criteria shown in the filter menu to confirm how they are combined.

Choose search or filtering

To locate a particular value, use the filter menu's search box or the spreadsheet's Find command. Search helps locate matching text or numbers. A filter is more useful when you want a subset of records to remain visible while you inspect the table. For example, search for a customer name to locate it, or filter Status to show all records marked Open.

Inspect and clear results

After applying a filter, review the visible records and the active criteria. If expected records are missing, check for misspellings, extra spaces, inconsistent categories, or numbers and dates stored as text.

Clear or remove filters when finished to show all records again. Some spreadsheet programs provide Filter views, which let you save or use a particular set of criteria without changing what collaborators see.