Sorting, Filtering, and Data Validation

What do sorting and filtering do?

Warning: Sorting only one column instead of the whole table disconnects values from their records. Always select the full table, or use a formal table, before sorting.

Example

Goal: Inspect the largest South-region orders in July.

1. Apply a filter: Region = South
2. Apply a filter: Date between 1 July and 31 July
3. Sort the visible rows by Revenue, largest to smallest

Multi-level sorting: Sort by Region (A–Z), then by Revenue (largest first) to see the top orders within each region.

Data validation

Validation rules restrict what can be entered into a cell.

How it works

• Use filters to investigate a subset, then clear them before calculating whole-dataset totals.
• SUM includes hidden rows. SUBTOTAL and AGGREGATE (Excel) can ignore filtered-out rows:
  • =SUBTOTAL(9, F2:F100) sums only visible rows.
• Always check whether hidden rows affect the formula you use.

Common mistakes

• Treating a filtered view as the entire dataset.
• Forgetting a filter is still active when sharing the file.
• Assuming validation rules prove existing data is correct. Validation only applies to new entries. Imported or pasted records may still contain invalid values.
• Sorting a range with blank rows, which splits the table into separate blocks.