Power Query Problems

How to Split a Master Timesheet by Employee using Power Query in Excel

Camila Milosovich Oct 15, 2026 870 views

Question details

The user needs to transform a master timesheet where multiple employee names are grouped in a single cell on separate lines into individual employee-level records.

How to Split a Master Timesheet by Employee in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing and analyzing employee timesheets where grouped data needs to be separated for individual tracking without creating numerous worksheets.
Observed behavior: Multiple employee names are combined in one cell separated by line breaks, making it difficult to analyze individual employee records effectively.

Before you start

Ensure your timesheet data is formatted as an Excel Table (Ctrl+T) so that Power Query can easily detect and load the data range.

Solution 1Recommended

Use Power Query to Split Column by Delimiter into Rows

Transform the grouped employee names into individual rows using Power Query's built-in delimiter splitting feature, allowing for seamless data refresh.

Power Query is an excellent tool for transforming messy, grouped data into flat tabular formats without requiring complex formulas or VBA.

By splitting the data into rows rather than columns, you can maintain a single cohesive dataset and easily filter by employee using data validation dropdowns or Pivot Tables.

Load Data to Power Query

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

Initiate Column Split

In the Power Query Editor, locate and right-click the header of the 'Team Member' column (or whichever column contains the combined names). Select 'Split Column' and then click 'By Delimiter'.

Configure Delimiter and Split into Rows

In the Split Column dialog box, set the delimiter to 'Custom'. Check the box for 'Split using special characters' and select 'Line Feed' (or carriage return, depending on the line break type). Crucially, under the 'Advanced options' section, select the radio button for 'Rows', then click 'OK'.

Load Transformed Data

Once the data is split into individual rows, click the 'Close & Load' button in the Home tab. This will return the separated data into a new Excel worksheet as an easily filterable table.

Use Power Query to Split Column by Delimiter into Rows

Dynamic Updates: Whenever the source master timesheet changes or new employees are added, simply navigate to the Data tab and click 'Refresh All' to update your separated employee records instantly.

Free Microsoft Office alternative

Manage Spreadsheet Data Effortlessly with WPS Office

While Power Query is specific to Microsoft Excel, WPS Office provides a lightweight, highly compatible, and completely free alternative for your daily spreadsheet processing needs. It handles Excel formats flawlessly and offers an intuitive interface for managing complex data.

1. Download and Install WPS Office: Visit the official WPS website to download and install the free WPS Office suite for your operating system.
2. Open your Excel Timesheet: Launch WPS Spreadsheet and open your existing .xlsx timesheet file to continue working without formatting issues.
3. Organize Data with Built-in Tools: Utilize robust built-in features under the Data tab, such as Pivot Tables and Advanced Filter, to neatly organize and track your employee records.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Rich data processing tools including advanced filters, Text to Columns, and Pivot TablesLightweight architecture requiring fewer system resourcesFree to use with a familiar, easy-to-navigate user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I automatically update my separated timesheet when new data is added?

Yes. Once you have set up the Power Query connection, any changes or additions made to the original input table can be updated in your output table by simply going to the Data tab and clicking 'Refresh All'.

How do I filter the output table by a specific employee without creating separate sheets?

You can set up a data validation dropdown list containing employee names above your output table, or simply use Slicers connected to a Pivot Table generated from your Power Query output to quickly filter data by individual employees.

What if the employee names are separated by commas instead of line breaks?

In the Power Query 'Split Column by Delimiter' settings, simply select 'Comma' as your delimiter instead of using the custom line feed option. Make sure you still select 'Split into Rows' in the advanced options section.

Does WPS Spreadsheet support Power Query?

WPS Spreadsheet currently provides standard, robust data processing tools like Text to Columns and highly capable Pivot Tables. However, automated ETL processes built explicitly on Microsoft's Power Query (M code) engine are a Microsoft-specific feature.

Excel & Data Problems