Data Import & Export

How to Convert System User Lists to Employee Columns in Excel

Muhammad Talha Sep 30, 2026 869 views

Question details

The user needs to transform data where systems are column headers and employees are listed below them, converting it so that employees become the column headers with their assigned systems listed below.

How to Convert System User Lists into Employee-Based Columns in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Reorganizing system assignment datasets by transposing and unpivoting data to view assignments grouped per employee.
Observed behavior: The data is currently grouped by system in columns, but needs to be reshaped to be grouped by individual employees as column headers.

Before you start

Ensure you are using a version of Excel that supports dynamic array formulas (like Excel 365) if you plan to use the formula method, and verify your dataset has consistent column headers without merged cells.

Solution 1Recommended

Use Dynamic Array Formulas (Excel 365)

Best for Excel 365 users who want a live, formula-based transformation that updates automatically when new data is added.

By utilizing modern Excel functions like UNIQUE, REDUCE, and HSTACK, you can dynamically unpivot and transpose your data without manually copying and pasting.

Extract Unique Employee Names

In a new blank cell (for example, E1), enter the formula =UNIQUE(TOROW(A2:C4),TRUE) to extract a single row of all unique employee names from your data range.

Map Assigned Systems to Employees

In the cell directly below the first employee (e.g., E2), input the formula: =IFNA(DROP(REDUCE("",E1#,LAMBDA(a,i,HSTACK(a,TOCOL(IF($A$2:$C$4=i,$A$1:$C$1,NA()),3)))),,1),"")

Review the Transposed Output

Press Enter. The dynamic arrays will automatically populate the columns for each employee with the respective systems assigned to them, scaling automatically as data in A2:C4 changes.

Version Compatibility: This solution relies on dynamic array functions (UNIQUE, TOROW, REDUCE, HSTACK, TOCOL). If you are using Excel 2019 or older, these formulas will return a #NAME? error. Use the Power Query method instead.

Solution 2

Transform Data using Power Query

Ideal for larger datasets and earlier versions of Excel, offering a robust, formula-free data transformation process.

Efficient Data Management with WPS Office

Easily Transpose and Transform Data in WPS Spreadsheet

WPS Spreadsheet provides powerful data manipulation tools, including Paste Special (Transpose) and advanced array capabilities, allowing you to quickly reorganize system and employee data.

1. Open Your File: Launch WPS Spreadsheet and open the file containing your system and employee data.
2. Copy the Dataset: Select the entire data range you wish to convert, right-click, and select 'Copy' (or press Ctrl+C).
3. Use Paste Special: Right-click the destination cell where you want the new layout, select 'Paste Special', and check the 'Transpose' box to flip the axes of your data.
4. Apply Array Formulas (Optional): For automated lists, you can also use modern lookup and reference formulas available in WPS Spreadsheet to dynamically arrange the datasets.

Fully compatible with Microsoft Excel formats (.xlsx)Free and lightweight office suiteUser-friendly interface for complex data transformationSupports modern spreadsheet functions for data structuring

microsoft office alternative - wps office

Frequently Asked Questions

How do I transpose data without using formulas?

You can use the 'Paste Special' > 'Transpose' feature in both Excel and WPS Spreadsheet to quickly flip rows and columns. For structural data unpivoting, Power Query is the best visual, formula-free tool.

Why does the dynamic array formula return a #NAME? error?

This error typically occurs if you are using an older version of Excel that does not support newer functions like UNIQUE, REDUCE, or TOCOL. In this case, upgrade your software or use the Power Query method instead.

How do I update the Power Query result when new data is added?

Unlike dynamic formulas which update live, Power Query requires a manual refresh. Right-click anywhere inside the generated output table in your worksheet and select 'Refresh' to pull in the latest data from your source table.

Excel & Data Problems