Power Query Problems

How to Use an Excel Slicer to Filter Tasks by Person and Time

Kushani Nimanthika Oct 21, 2026 868 views

Question details

The user needs to transform a complex schedule into a normalized table format in order to filter tasks by person and time using an Excel slicer.

How to Use an Excel Slicer to Filter Tasks by Person and Time

Product: Excel
Device & OS: not provided
Scenario: Managing and analyzing a team schedule with multiple names, times, and task values that cannot be easily filtered in its current format.
Observed behavior: The current schedule format is a matrix that is not suitable for direct slicer filtering. It requires restructuring using Power Query before a PivotTable and slicer can be effectively applied.

Before you start

Ensure your schedule data is formatted as an official Excel Table (press Ctrl+T) and contains clear headers for names and times before importing it into Power Query.

Solution 1Recommended

Unpivot Data with Power Query and Apply a Task Slicer

Use Power Query to normalize your wide schedule data into a flat list, allowing you to easily summarize and filter it using a PivotTable and slicer.

To effectively filter tasks by person and time, a matrix-style schedule must first be flattened into three distinct columns: Name, Time, and Task. Power Query handles this transformation seamlessly using the 'Unpivot' feature.

Load data into Power Query

Select any cell inside your schedule table, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

Unpivot the time columns

In the Power Query window, click on the 'Name' column header to select it. Right-click the header and choose 'Unpivot Other Columns'. This instantly transforms your matrix into a flat list.

Rename the new columns

Double-click the newly generated 'Attribute' column header and rename it to 'Time'. Double-click the 'Value' column header and rename it to 'Task'.

Load the normalized data back to Excel

Click the 'Close & Load' button on the Home tab of the Power Query Editor. Your restructured data will appear in a new worksheet.

Create a PivotTable and insert a Slicer

Select the new data, go to the 'Insert' tab, and click 'PivotTable'. In the PivotTable Fields pane, drag 'Name' and 'Time' into the Rows area. Finally, go to the 'PivotTable Analyze' tab, click 'Insert Slicer', and check the box for 'Task' to start filtering.

Unpivot Data with Power Query and Apply a Task Slicer

Dynamic Updates: When you add new people or times to your original schedule, simply right-click the green PivotTable or the queried table and select 'Refresh'. The Power Query steps will run automatically and update your slicer.

Advanced Data Handling in WPS Spreadsheet

Filter and Analyze Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides robust data management capabilities, including powerful PivotTables and Slicers. You can easily flatten your schedules and apply dynamic filters without slowing down your workflow.

1. Open your workbook: Launch WPS Spreadsheet and open your schedule workbook.
2. Format your flat data: Ensure your schedule is organized with distinct columns for 'Name', 'Time', and 'Task'.
3. Insert a PivotTable: Highlight your data table, navigate to the 'Insert' tab, and select 'PivotTable'.
4. Arrange your rows: In the PivotTable field list, drag the 'Name' and 'Time' fields into the 'Rows' area to structure your schedule.
5. Add the Task Slicer: Click anywhere inside the PivotTable, go to the 'Options' tab on the ribbon, and click 'Insert Slicer'. Select 'Task' to instantly filter your schedule.

Free, lightweight, and fast office suiteFully compatible with Microsoft Excel (.xlsx) formats and PivotTablesIntuitive Slicer tools for creating interactive schedule dashboardsBuilt-in advanced data processing for managing complex records

microsoft office alternative - wps office

Frequently Asked Questions

What does 'Unpivot' mean in Power Query?

Unpivoting is the process of transforming data from a wide matrix format (where times might be spread across multiple columns) into a long, flat format consisting of discrete rows (e.g., Name, Time, and Task). This normalized structure is essential for summarizing and filtering data using PivotTables.

Why is the 'Insert Slicer' option grayed out in my spreadsheet?

The 'Insert Slicer' option is typically disabled if your file is saved in an older format like .xls, or if you haven't clicked inside a valid Table or PivotTable. Ensure your document is saved as a modern Excel Workbook (.xlsx) and that your active cell is inside the PivotTable.

Can I connect a single Task slicer to multiple PivotTables?

Yes. If you have multiple PivotTables built from the same data source, right-click the slicer, select 'Report Connections' (or 'PivotTable Connections'), and check the boxes for all the PivotTables you want to control. This is ideal for building unified, interactive dashboards.

Excel & Data Problems