Forum Discussion
Having a hard time keeping the data in one line with a pivot table
Hey folks, I am trying to get a pivot table where all the information I need is in one row, but no matter what I do each column is in a different row. Here's the data I need to make into one line f...
Not sure follow below can help:
- Prepare Your Source Data
Make sure your data includes a unique identifier for each group you want to collapse into one row. In your case, that could be a combination of:
- Co Code
- Batch ID
- File #
- Batch Description
- Pay #
- Temp Cost Number
You can create a helper column that concatenates these fields to form a unique key.
- Insert Pivot Table
- Go to Insert > Pivot Table.
- Use your helper column (or the combination of fields above) in the Rows area.
- Drag all the numeric fields (e.g., Reg Hours, Temp Rate, SF1 Hours 3 Amount, etc.) into the Values area.
- Set each value field to Sum or Max, depending on what makes sense for your data.
- Avoid Row Splitting
If you accidentally drag fields like SF1 Hours 3 Code into Rows or Columns, Excel will split the data into multiple rows. Instead:
- Keep all code fields (e.g., SF1 Hours 3 Code) in the Values area.
- Use Max or First as the aggregation function to show the code once per row.