How to Filter Excel Rows by Specific Name and Hide Other Rows
Question details
The user needs to filter an Excel dataset by a specific name in a column so that only the matching rows are displayed, and all other non-matching rows are hidden.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a large dataset containing multiple categories or names in a single column and needing to isolate data for just one specific item.
- Observed behavior
- Only the rows matching the selected name are visible on the spreadsheet, while all non-matching rows are effectively hidden from view.
Ensure your dataset has clear column headers and no entirely blank rows or columns within the data range to prevent the filter from breaking.
Use Excel AutoFilter to Display Specific Rows
The standard AutoFilter tool allows you to select one or multiple names from a dropdown list to instantly hide unselected rows.
Applying a basic filter is the fastest way to sift through thousands of rows of data. It temporarily hides rows that do not meet your exact selection criteria without altering the original dataset.
Click any single cell within your dataset, or click and drag to highlight the specific column headers you want to apply the filter to.
Navigate to the 'Data' tab on the Excel ribbon and click the 'Filter' button. Dropdown arrow icons will appear on each of your column headers.
Click the dropdown arrow on the column containing your target names (e.g., Vessel Name). Uncheck the '(Select All)' option, check the box next to the specific name you want to view, and click 'OK'.
Create a Slicer for Interactive Filtering
If your data is formatted as an Excel Table, you can insert a slicer for a more visual, one-click filtering experience.
Filter and Analyze Data Quickly in WPS Spreadsheet
WPS Spreadsheet provides powerful and intuitive data filtering tools, including standard filters, advanced filters, and slicers, allowing you to seamlessly manage large datasets and locate specific information in seconds.
- 1. Open Your File: Launch WPS Spreadsheet and open your existing workbook containing the dataset.
- 2. Select the Headers: Click the header row of your dataset or click any single cell within the contiguous data range.
- 3. Apply AutoFilter: Go to the 'Data' tab located on the top ribbon and click the 'AutoFilter' button.
- 4. Choose Your Criteria: Click the small arrow icon on the column header you wish to filter, select your specific target name, and click 'OK' to instantly hide all non-matching rows.
Frequently Asked Questions
Can I filter rows by multiple names at the same time?
Yes. When you open the filter dropdown on a column, simply check the boxes next to all the specific names you want to display. All rows containing any of those checked names will remain visible, while the rest are hidden.
Why is my Excel filter not hiding all the correct rows?
This usually happens if there is an entirely blank row in your dataset, causing Excel to think the data range ends early. To fix this, manually highlight your entire dataset (including rows below the blank one) before applying the filter.
How do I remove the filter and show all hidden rows again?
To clear the filter from a specific column, click the filter icon on that column header and select 'Clear Filter'. To remove all filters at once, navigate to the Data tab and click the 'Filter' button again to toggle the feature off completely.
Does hiding rows with a filter delete the data?
No, filtering only temporarily hides the rows that do not match your exact criteria. The underlying data remains completely intact and safe. It can be fully restored to view at any time by clearing the active filter.
