Others

How to Show Matching and Nonmatching Excel Rows Without Filtering

John WilsonJohn Wilson Nov 2, 2026 869 views

Question details

The user wants to view matching rows grouped together while keeping nonmatching rows visible below them, avoiding the standard filter behavior that completely hides unmatched records.

How to Show Matching and Nonmatching Excel Rows Without Filtering
Product
Excel
Device & OS
not provided
Scenario
Data analysis and comparison where the user needs to evaluate matching records without losing context of the nonmatching data.
Observed behavior
Excel's standard filter automatically hides rows that do not meet the selected criteria, preventing the display of matching and nonmatching rows simultaneously.
Before you start

Before applying new sorting rules, ensure your dataset does not contain completely empty rows or columns, as this might disrupt the sorting process.

Solution 1Recommended

Use a Helper Column to Sort and Restore Data

Add a temporary numbering column to preserve your original data order, then sort by your desired criteria to group matching rows together.

Sorting allows you to group similar items at the top of your sheet without hiding the rest of the data. To ensure you can easily revert to your original layout, an index or "helper" column is required.

1
Insert a Helper Column

Right-click the first column of your dataset and insert a new column. Enter consecutive numbers (1, 2, 3...) from the top row to the bottom row to establish an original order index.

2
Sort by the Matching Criteria

Highlight your entire dataset, including the new helper column. Go to the 'Data' tab, click 'Sort', and choose the column containing the values you want to match. This will group matching rows together while keeping nonmatching rows visible below.

3
Restore the Original Order

Once you have finished reviewing the grouped data, open the 'Sort' dialog again. Choose to sort by the Helper Column in ascending order. This will instantly return all rows to their original positions.

Use a Helper Column to Sort and Restore Data
Pro Tip: You can quickly fill the helper column with consecutive numbers by typing '1' and '2' in the first two cells, selecting both, and double-clicking the fill handle in the bottom-right corner of the selection.
Efficient Data Management with WPS

Group and Analyze Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers highly intuitive sorting capabilities and advanced conditional formatting tools, making it exceptionally easy to analyze matching and nonmatching rows without hiding critical context.

  1. 1. Open Your Data: Launch WPS Office and open your spreadsheet document.
  2. 2. Add an Index Column: Insert a new column next to your data and fill it with sequential numbers to track your original row order.
  3. 3. Sort the Data: Go to the 'Data' tab, click 'Sort', and group the specific column containing your matching criteria.
  4. 4. Restore Original Layout: When finished, simply sort the Index Column from smallest to largest to revert to the initial view.
Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Advanced built-in sorting options to instantly group data by text, color, or multiple criteria.Rich conditional formatting templates to visually identify key data clusters.Free, lightweight, and features a familiar user interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel's standard filter completely hide rows?

The standard filter is specifically designed to isolate data that meets defined criteria by hiding the rest. To view both matching and nonmatching data simultaneously, sorting or visual highlighters (like conditional formatting) must be used instead.

Can I use an advanced filter to show nonmatching rows?

Advanced filters can extract unique records or filter complex logic in place, but they will still hide rows that don't fit the criteria. However, you can use the 'Copy to another location' feature in Advanced Filter to extract matches while leaving the original dataset untouched.

How do I safely remove the helper column once I am done?

After you have sorted your data back to its original order using the consecutive numbers, simply right-click the letter heading of the helper column at the very top of the sheet and select 'Delete'.