How to Convert Hourly Excel Data into Daily Columns Automatically
Question details
The user needs to reshape a long list of hourly records into a grid format with dates as columns and hours as rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing large time-series datasets, specifically transforming approximately 2,160 hourly records into a structured daily table format.
- Observed behavior
- The user wants an automated method to restructure the data without manually copying and pasting values into the new layout.
Ensure your source data has clear, distinct columns for 'Date', 'Hour', and the 'Measurement Value'. Verify that there are no blank rows in your dataset to prevent Pivot Table errors.
Use a Pivot Table and Lookup Formula
This method uses a Pivot Table to restructure the dates and hours into a grid layout, followed by a lookup formula to securely map the actual measurement values.
While Pivot Tables are great for summarizing data, they aggregate values (like Sum or Count) by default. If you need to display exact, unaggregated measurements, using a Pivot Table just to build the row/column structure and then fetching the data with VLOOKUP or XLOOKUP is the most reliable automated approach.
In your source data sheet, insert a new column named 'Index'. Fill this column with a unique identifier combining the Date and Hour, or simply use a sequential ID for each record.
Select your entire source dataset, navigate to the 'Insert' tab on the top ribbon, and click 'PivotTable'. Choose to place it in a new worksheet.
In the PivotTable Fields pane, drag the 'Hour' field into the Rows area, the 'Date' field into the Columns area, and your 'Index' field into the Values area.
Highlight the resulting Pivot Table (the dates and hours grid), copy it (Ctrl+C), and paste it as values (Ctrl+Shift+V) into your final destination sheet.
In the first empty cell of your new grid, enter an XLOOKUP or VLOOKUP formula that references the specific Date and Hour to retrieve the matching measurement from your original dataset. Drag the formula across the columns and rows to fill the table.
Easily Manage Time-Series Data with WPS Spreadsheet
WPS Spreadsheet provides powerful Pivot Table features and advanced formulas like VLOOKUP and XLOOKUP to help you reshape hourly data into daily columns effortlessly.
- 1. Open data in WPS: Launch WPS Spreadsheet and open your dataset containing the hourly records.
- 2. Create Pivot Table: Highlight your data, navigate to the Insert tab, and click PivotTable to generate the grid layout.
- 3. Arrange fields: Drag 'Hour' into Rows and 'Date' into Columns to instantly build the required daily structure.
- 4. Fetch exact values: Use WPS Spreadsheet's built-in VLOOKUP or XLOOKUP formulas to seamlessly populate the exact measurements into your new grid.
Frequently Asked Questions
Can I reshape this data using Power Query instead of a Pivot Table?
Yes. If your spreadsheet software supports Power Query, select your data, go to Data > From Table/Range. Select the 'Date' column, choose 'Pivot Column' under the Transform tab, select your values column, and set the aggregate value function to 'Don't Aggregate'.
Why is my Pivot Table counting the values instead of showing the actual measurements?
Pivot Tables are designed to aggregate data. If your measurement column contains text or if the Pivot Table defaults to 'Count', it will show quantities instead of the actual data points. This is why it is recommended to use the Pivot Table only to generate the row and column layout, and then pull in the actual values using a lookup formula.
How do I extract the Date and Hour from a single DateTime column before pivoting?
You can separate them using basic formulas. To extract the date, use the INT() function (e.g., =INT(A2)). To extract the time, use the MOD() function (e.g., =MOD(A2,1)). Format the resulting cells as Date and Time respectively to view them correctly.
