How to Filter an Excel Table by Row with a Slicer
Question details
The user wants to know how to use an Excel slicer to filter table data specifically by row.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering data within an existing table using the visual Slicer tool.
- Observed behavior
- Slicers inherently filter table rows based on values selected from table columns, but cannot directly filter horizontal row labels unless the matrix is restructured.
Ensure your data is formatted as an official Table (Ctrl+T) or Pivot Table before attempting to insert a slicer, as slicers cannot be applied to standard, unformatted data ranges.
Use a Slicer to Filter Standard Table Rows
Apply a slicer to a vertically structured Excel table to easily filter rows based on column criteria.
Slicers are visual filters that allow you to quickly filter rows in a table. They work by looking at the column headers and filtering the rows underneath them. If you want to filter rows based on specific data, that data must be organized into columns first.
Click on any cell inside your formatted Excel table to make the Table Design tab active.
Navigate to the 'Insert' tab on the top ribbon and click on 'Slicer' in the Filters group.
In the 'Insert Slicers' dialog box, check the boxes for the column fields you want to use as filters, then click 'OK'.
A slicer window will appear on your sheet. Click on the values within the slicer to instantly filter the table rows dynamically.
Restructure Horizontal Matrix Data for Slicer Use
If your data has horizontal row labels instead of column headers, you must transpose it before a slicer can filter it properly.
Easily Filter Table Data with Slicers in WPS Office
WPS Spreadsheet provides a highly intuitive and visually appealing way to filter your data using Slicers. It offers a smooth experience for managing Pivot Tables and standard tables alike.
- 1. Open spreadsheet and create table: Open your spreadsheet in WPS Office, select your data range, and press Ctrl+T to convert the data into a Table.
- 2. Insert slicer: Go to the 'Insert' tab on the top menu and click on the 'Slicer' icon.
- 3. Apply row filters: Select the columns you wish to use as filters and click 'OK' to start visually filtering your rows.
Frequently Asked Questions
Why is the Slicer button grayed out in Excel?
The Slicer button is only active when you have selected a cell within a formatted Table or a Pivot Table. Also, ensure your file is saved in the .xlsx format, as older .xls formats do not support slicers.
Can I use one slicer to filter multiple tables?
Yes, but only if the tables are Pivot Tables connected to the same data source. You can link them by right-clicking the slicer, selecting 'Report Connections,' and checking the Pivot Tables you want to link.
How do I clear a slicer filter to show all rows?
To clear the filter and show all rows, click the 'Clear Filter' icon (a funnel with a red X) located in the top-right corner of the slicer window, or press Alt+C while the slicer is selected.
Can I filter a table by row if my data is horizontal?
Not directly. Slicers are designed to filter rows based on column values. You will need to transpose your data into a vertical layout using the Paste Special > Transpose feature before applying a slicer.
