Power Query Problems

How to Create a Cross-Reference List of Clients and Employees in Excel

Bushra Parveen Sep 28, 2026 869 views

Question details

The user needs to convert a matrix of client and employee assignments into a structured cross-reference list in a second table without manual data entry.

How to Create an Excel Cross-Reference List of Clients and Employees

Product: Excel
Device & OS: not provided
Scenario: Reorganizing employee assignments that are spread across multiple columns for each client row into a clean, easy-to-read cross-reference list.
Observed behavior: Employee data is stored horizontally across columns next to client rows, making it difficult to generate a direct mapping list or PivotTable.

Before you start

Ensure your source data is formatted as an official Excel Table (select your data and press Ctrl+T). This ensures that any new clients or employees added later will automatically flow into your queries or formulas.

Solution 1Recommended

Use Power Query to Unpivot and Structure Data

Power Query is the most robust and recommended method for reshaping matrix data into structured relational lists without writing complex formulas.

By unpivoting the employee columns, you flatten the data into a straight list. Grouping and indexing then allow you to reconstruct the data exactly how you need it.

Load Data into Power Query

Click anywhere inside your source data table, navigate to the Data tab on the ribbon, and select 'From Table/Range' to open the Power Query Editor.

Unpivot Employee Columns

Right-click the header of your Client column and select 'Unpivot Other Columns'. This transforms all the employee columns into two new columns: 'Attribute' (former column headers) and 'Value' (employee names).

Group and Add an Index

Remove the 'Attribute' column. Next, group the rows by the Client column and add a custom Index Column. This assigns a sequence number to each employee under a specific client.

Pivot the Output

Select the newly created Index column, go to the Transform tab, and click 'Pivot Column'. Choose your employee names column for the Values and under Advanced Options, select 'Don't Aggregate'.

Load the Results

Click 'Close & Load' on the Home tab. The formatted cross-reference list will be generated on a new worksheet. When source data changes, simply right-click this new table and select 'Refresh'.

Use Power Query to Unpivot and Structure Data

Automatic Updates: Because Power Query connects directly to your source table, any future modifications to client or employee assignments can be updated with a single click.

Solution 2

Use Dynamic Array Formulas

If you are using modern spreadsheet versions, you can use built-in array formulas to dynamically reshape the data without using Power Query.

Organize Data Efficiently with WPS Office

Create Cross-Reference Lists Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful data transformation tools, including PivotTables and dynamic array functions, making it incredibly easy to cross-reference and reshape complex datasets without coding.

1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your client and employee assignments.
2. Format as a Table: Select your entire data range and press Ctrl+L. This formats it as a dynamic table, allowing formulas to automatically adjust as data grows.
3. Apply Dynamic Arrays: Navigate to a new sheet and utilize powerful built-in array formulas to map employees to clients instantly.
4. Summarize with PivotTables: Alternatively, go to the Insert tab, select PivotTable, and drag your fields to quickly cross-reference clients and their assigned staff.

Seamlessly handles Microsoft Excel formats (.xlsx)Advanced PivotTable features to quickly summarize client-employee dataSupports hundreds of modern functions for automatic data generationFree and lightweight alternative for complex data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Do I need to manually update the list when new employees are assigned?

No. If you use Power Query or dynamic array formulas linked to an Excel Table, the list is tied to the source data. For Power Query, you only need to right-click the final table and select 'Refresh' to display the new assignments.

Why are blank cells appearing in my unpivoted employee list?

When unpivoting a grid, empty cells in the original matrix might carry over as null or blank values. You can easily fix this by filtering out the empty cells in the 'Value' column within the Power Query Editor before applying the final Pivot step.

Can I use older versions of Excel for the formula method?

Functions like TOCOL, REDUCE, and HSTACK are only available in newer versions (like Microsoft 365) or updated spreadsheet software like WPS Office. If you are using an older version, Power Query is the recommended and universally compatible approach.

Excel & Data Problems