How to Automatically Transform Agent Hours into a Daily Excel Layout
Question details
Transforming source records containing agents, dates, and hours into a structured report with one row per agent and one column per day.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Converting vertical raw log data into a horizontal daily attendance or hours summary report.
- Observed behavior
- Raw data records are listed vertically and need to be pivoted horizontally into daily columns while supporting automatic data refreshes.
Ensure your raw data is formatted as an official Excel Table (press Ctrl+T) and has clear, distinct headers for Agent, Date, and Hours before applying any transformations.
Use Power Query to Automate the Daily Layout
Power Query is the most robust method for automating data transformations and pivoting rows into columns, especially for larger datasets.
By using the Pivot Column feature in Power Query, you can dynamically transform vertical dates into horizontal columns. This method ensures that your layout scales automatically as new dates are added to your source data.
Select any cell inside your source data table, go to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, click the header of the 'Date' column to highlight it.
Navigate to the Transform tab and click 'Pivot Column'. In the dialog box that appears, choose 'Hours' as the Values Column.
Click to expand the Advanced options in the Pivot Column dialog, ensure the Aggregate Value Function is set to 'Sum', and click OK.
Go to the Home tab and click 'Close & Load' to output the transformed daily layout to a new worksheet.

Create a PivotTable for a Quick Daily View
Best for users who prefer standard Excel features without utilizing the Power Query editor.
Easily Summarize Agent Hours with WPS Spreadsheet
WPS Spreadsheet provides highly intuitive PivotTable and data processing tools, allowing you to transform raw log data into professional daily reports effortlessly.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your raw agent log data.
- 2. Insert a PivotTable: Select your data range, click the Insert tab on the top menu, and choose 'PivotTable'.
- 3. Arrange the fields: In the side panel, drag the 'Agent' field to Rows, 'Date' to Columns, and 'Hours' to Values.
- 4. Refresh when needed: Whenever new data is added, simply click the Data tab and select 'Refresh All' to update your daily layout.

Frequently Asked Questions
How can I make my transformed daily layout update automatically?
If you are using Power Query, right-click the green output table and select 'Refresh'. For PivotTables, right-click anywhere inside the PivotTable grid and click 'Refresh' after adding new data to the source table.
Can I use dynamic formulas instead of Power Query to achieve this layout?
Yes. In modern versions of Excel, you can use the UNIQUE formula to generate a list of agents for the rows and dates for the columns. You can then use the SUMIFS function inside the grid to calculate the total hours for each intersecting agent and date.
Why are my PivotTable dates grouping by month instead of showing individual days?
Excel PivotTables often group dates automatically to simplify reports. To show daily columns, right-click any date header in your PivotTable, select 'Group', ensure only 'Days' is highlighted in the list, and click OK.




