It is common where I work to obtain a CSV or Excel sheet that contains denormalized report data. Here is a simple example:

Category ID | Category Name | Detail one | Detail two
1           | Cat1          | 65         | Jane
1           | Cat1          | 13         | Jill
1           | Cat1          | 25         | Jack
2           | Cat2          | 60         | John
2           | Cat2          | 25         | Jeremy
2           | Cat2          | 84         | Jason

I want to know how to create a report from this kind of data with groupings and detail bands, similar to how one would build a report in SQL Server Reporting Services. I don't want to remove any information, I just want to format the data so that "categories" are only listed once, and all of the detail rows are listed under each category. Here is the example output:

Category ID | Category Name | Detail one | Detail two
1           | Cat1          | 103        |
            |               | 65         | Jane
            |               | 13         | Jill
            |               | 25         | Jack
2           | Cat2          | 169        |
            |               | 60         | John
            |               | 25         | Jeremy
            |               | 84         | Jason

I have added a sum field in the grouping row of column C, but this is not strictly required.

I have experimented with pivot tables and power query, but I cannot seem to figure out how to do grouping with these tools that do NOT aggregate all of the detail rows. As you can see, I am not trying to do a pivot of any kind -- I'm just trying to create category headers automatically based on the data in the table.

Is there any way to achieve the desired outcome by applying something to the entire source table at once?

  • One way is to use conditional formatting to blank (font color same as interior color) the cells in the first two columns if they are the same as the cell above. And you could use the Data --> Subtotal wizard to get the subtotals of each group. OH, and hide the Grand Total row if you don't need it. – Ron Rosenfeld Jul 13 '17 at 1:01

You can do what you want with a Pivot Table. Put all four fields in the Rows section, choose 'Show In Tabular Form' in Report Layout, and remove subtotals. Format to suit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.