How to Show Matching and Nonmatching Excel Rows Without Filtering
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.

- 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 applying new sorting rules, ensure your dataset does not contain completely empty rows or columns, as this might disrupt the sorting process.
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.
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.
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.
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.

Apply Conditional Formatting to Highlight Matches
Use color coding to visually distinguish matching rows from nonmatching ones without altering the row order or hiding any data.
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. Open Your Data: Launch WPS Office and open your spreadsheet document.
- 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. Sort the Data: Go to the 'Data' tab, click 'Sort', and group the specific column containing your matching criteria.
- 4. Restore Original Layout: When finished, simply sort the Index Column from smallest to largest to revert to the initial view.

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'.




