How to Total Hours by Cost Code from Multiple Excel Columns
Question details
The user needs to consolidate and total hours based on cost codes that are spread across multiple column pairs (e.g., CC1/Hrs1, CC2/Hrs2) into a single summary list.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Consolidating wide-format time-card data into a unified summary report to calculate total hours per cost code.
- Observed behavior
- Cost codes and hours are stored in multiple columns in a single row, making it difficult to calculate an aggregated total without creating fragmented PivotTables.
Ensure your time-card data has clear column headers for each cost-code and hours pair (e.g., CC1, Hrs1, CC2, Hrs2) and format your data range as a Table to make data processing easier.
Use Power Query to Unpivot Data and Create a PivotTable
This is the most reliable method. By unpivoting the wide data into a normalized layout, you can generate a single PivotTable to calculate total hours per cost code.
A wide data layout with multiple columns per record is difficult to aggregate. Power Query allows you to transform this data into a normalized table structure (one row per entry), which is the ideal format for creating an accurate PivotTable.
Select your data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Hold the Ctrl key and select all the repeated cost-code and hours columns. Right-click the column headers and choose 'Unpivot Columns'. This will reshape the data into an Attribute and a Value column.
Rename the newly created columns to 'Cost Code' and 'Hours'. Go to the Home tab and click 'Close & Load' to place the normalized data into a new worksheet.
Select the new table, go to the Insert tab, and click 'PivotTable'. In the PivotTable Field List, drag 'Cost Code' to the Rows area and 'Hours' to the Values area. Ensure the Values calculation is set to 'Sum'.
Summarize Complex Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful data management tools, including advanced PivotTables and flexible data formatting, helping you quickly organize and total hours from complex time-card layouts.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open your existing time-card file containing the wide data layout.
- 2. Normalize the Data: Reorganize the cost-code and hours pairs into a vertical list format using standard copy-paste or data consolidation tools.
- 3. Insert a PivotTable: Select your standardized table, navigate to the Insert tab, and click PivotTable.
- 4. Generate the Summary: Drag the 'Cost Code' field into the Rows section and the 'Hours' field into the Values section to instantly view the aggregated totals.
Frequently Asked Questions
Can I total hours across multiple columns without reshaping the data?
Yes, but it is highly inefficient and prone to errors. You could manually create multiple PivotTables for each pair of columns and combine the results, or use complex SUMIFS formulas. However, normalizing the data (unpivoting) is the recommended best practice for accuracy.
Why do I need to unpivot my data before creating a PivotTable?
PivotTables are designed to summarize data that is arranged in a normalized structure (one row per record). Unpivoting converts your wide layout into a vertical list, allowing the PivotTable to correctly group identical cost codes and sum their hours.
How do I ensure my PivotTable sums the hours instead of counting them?
When you drag the 'Hours' field into the Values area of the PivotTable Field List, right-click the field, select 'Value Field Settings', and change the summary calculation from 'Count' to 'Sum'.
