Sort and Filter Data Safely in Microsoft Excel

Quick answer: Sort the complete records with Data → Sort, then use filter controls to hide nonmatching rows. For AND/OR criteria across columns, use a separate Advanced Filter criteria block.

Version and platform: Excel 2024 for Windows; ordinary sort and AutoFilter also documented for Microsoft 365, but Advanced Filter steps here use the Excel 2024 source scope. Use a disposable workbook for the exercise.

Use sorting when you need to reorder records and filtering when you need to temporarily hide records that do not match. Excel supports both on ordinary ranges and on Excel Tables.

Practice data

Enter the following at A1:E7 in a blank worksheet, including the single heading row.

Order	Region	Product	Amount	Status
1001	East	Cable	120	Open
1002	West	Adapter	85	Closed
1003	East	Stand	160	Open
1004	North	Cable	95	Open
1005	West	Hub	140	Closed
1006	North	Adapter	70	Open

Sort without separating records

1. Select the full five-column block, including the header row.
2. Choose Data → Sort. If Excel prompts about a partial selection, expand it so the whole record moves together.
3. Confirm that the data has headers.
4. Sort first by Region A to Z, then add Amount Largest to Smallest.
5. Check that order numbers, products, amounts and statuses still stay on their original rows.

Microsoft also documents direct sort controls on formatted Excel Tables, where filter buttons are placed in the header row automatically.

Filter the same list

1. Click inside the range and choose Data → Filter, or format the data as a table.
2. Open the Region header menu and show only East.
3. Add a second filter on Status for Open.
4. Clear the filter when you need all records again.

Excel documents text, number and date filters and also filtering by font color, cell color or icon. Filters are additive: each additional column filter narrows the visible subset.

Common mistakes

• Sorting only one column: this can detach a value from the rest of its record.
• Mixed types in one column: Excel may offer filter choices based on the dominant data type.
• Confusing hidden with deleted: filtering hides rows; it does not remove them.
• Assuming formulas only see visible rows: use functions designed for filtered lists when that distinction matters.

Reconcile the order and filtered records

Enter the fixture at A1:E7, with exactly one header row. After sorting Region ascending and Amount descending within each Region, the order IDs must read 1003, 1001, 1004, 1006, 1005, 1002. Region is the primary key; the amounts do not sort globally. For example, West’s 140 stays after North’s 70 because Region takes precedence.

The East-plus-Open filter should show two orders, 1001 and 1003, totaling 280 by inspection. To test a numeric criterion, clear the Region and Status filters, open Amount, and choose Number Filters → Greater Than, with 100. The visible orders should be 1001, 1003 and 1005, totaling 420. This is AutoFilter’s comparison dialog, not the separate Advanced Filter criteria-range workflow.

Clear filters before editing the fixture. Change order 1002’s amount from 85 to 180, reapply the numeric filter, and expect four records totaling 600. Re-sort if you want the updated West order to place 1002 before 1005. Save a separate XLSX practice copy; after reopening, clear filters and verify all six records still exist. Sorting is a reorder, not a reliable permanent record of the original input order, so keep the Order field.

Use a criteria range for AND/OR conditions

For this Advanced Filter exercise use Excel 2024 for Windows, the edition covered by the cited advanced-criteria source. Restore order 1002 to 85 and clear ordinary filters. In G1:H3 enter:

Region	Amount
="=East"	>130
="=West"	>100

The formula-like text entries display =East and =West, which request exact equality. Conditions on one row are AND; separate rows are OR. Select a cell in A1:E7, choose Data → Advanced, choose Filter the list, in-place, set List range to $A$1:$E$7 and Criteria range to $G$1:$H$3, then confirm. Only 1003 and 1005 should remain, totaling 300. Change H2 to >160 and run Advanced again: only 1005 qualifies because 160 is not greater than 160. An extra empty criteria row can admit unintended records, so select exactly the three-row criteria block. Clear the filter to restore all six source records.

Official references

• Filter data in a range or table in Excel
• Sort data in a range or table in Excel
• Filter by using advanced criteria

Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.