Power Query Problems

How to Find Employee Work Dates Across Multiple Columns in Excel

Emma Brown Oct 21, 2026 868 views

Question details

The user needs to extract all dates a specific employee worked when their name is scattered across multiple role-based columns rather than a single name column.

How to Find Employee Work Dates Across Multiple Role Columns in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing employee schedules or timesheets where individuals take on different roles (e.g., supervisor, diver, tender) on different dates, making standard lookups difficult.
Observed behavior: Employee names appear in various column headers based on their daily role, requiring data restructuring to return a consolidated list of working dates per employee.

Before you start

Ensure your source data is formatted as an official Excel Table (press Ctrl+T) so that Power Query can easily import the data and refresh dynamically when new dates are added.

Solution 1Recommended

Unpivot Role Columns Using Power Query

The most robust method is to use Power Query to transform the scattered role columns into a single employee column, making it easy to filter and extract dates.

Traditional lookup functions like XLOOKUP struggle when the search value can be in one of many columns. Power Query solves this by unpivoting the dataset, turning your wide table into a long table where all employee names fall under a single column.

Import data into Power Query

Select any cell in your data table and go to the Data tab. Click 'From Table/Range' to open the Power Query Editor.

Select the role columns

Hold down the Ctrl key and click the column headers for all the different employee roles (e.g., Supervisor, Diver, Tender).

Unpivot the selected columns

Go to the Transform tab and click 'Unpivot Columns'. The editor will restructure the data, creating an 'Attribute' column (the roles) and a 'Value' column (the employee names).

Filter by employee name

Click the drop-down arrow on the new 'Value' (Employee Name) column header, uncheck 'Select All', and check the box next to the specific employee you want to track.

Load data back to Excel

Go to the Home tab and click 'Close & Load' to output the consolidated list of work dates to a new worksheet.

Dynamic Updates: Once set up, you only need to click 'Refresh All' on the Data tab whenever you add new schedule records to your original table.

Solution 2

Summarize Dates with a PivotTable and Slicers

If you need an interactive dashboard to quickly switch between employees and view their work dates, combine the unpivoted data with a PivotTable.

Efficient Data Processing with WPS Office

Easily Organize and Filter Work Dates with WPS Spreadsheet

WPS Spreadsheet offers intuitive data organization features, including PivotTables and advanced filtering, allowing you to quickly consolidate and analyze scattered employee schedules without complex configurations.

1. Open your schedule data: Launch WPS Spreadsheet and open your existing timesheet or schedule document.
2. Insert a PivotTable: Highlight your dataset, navigate to the Insert tab, and click PivotTable to place it on a new worksheet.
3. Organize your fields: Drag your date and employee fields into the Rows and Filters areas to organize the data logically.
4. Apply filters: Use the built-in drop-down filters on your PivotTable to quickly isolate work dates for specific employees.

Fully compatible with Microsoft Excel (.xlsx) formats and PivotTablesIntuitive user interface for fast data sorting and filteringLightweight application that handles large datasets smoothlyFree to download and use for daily office productivity

microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP instead of Power Query for this task?

XLOOKUP is designed to search a single column or row for a matching value. Since employee names are scattered across multiple role columns, XLOOKUP would require complex nested formulas or array operations. Power Query is much more efficient and scalable for this scenario.

How do I update my date list when the source schedule changes?

If you used Power Query to set up the unpivot, simply right-click anywhere in your output table and select 'Refresh', or go to the Data tab and click 'Refresh All'. The query will automatically pull in the new dates from the source table.

What does 'Unpivot Columns' actually do in Excel?

Unpivoting transforms data from a 'wide' format (many columns for different roles) into a 'long' format (fewer columns, more rows). It takes the headers and values and pairs them into two new columns, making it much easier to filter by a specific data point, like a name.

Can I filter out weekends or holidays in Power Query before extracting dates?

Yes. In the Power Query Editor, you can add a custom column to identify the day of the week, or merge your data with a separate holiday table. You can then apply a filter to exclude those specific dates before loading the final data back into your spreadsheet.

Excel & Data Problems