Pivot Table Row Labels In the Same Line

It is a common issue for users to place multiple pivot table row labels in the same line. You may need to summarize data in multiple levels of detail while rows labels are side by side. In this post I’m going to show you how to do it.

Lets assume you have a table of data that shows the  distribution of complaints for your car deliveries to your dealers. Our tables has dealer info, error code and description and occurance (table has 1000 rows).

Now we need to make a table that summarizes errors occurance by dealer. But we want both error code and error description visble in our new table. Lets see how to do it.

First make a pivot table with required fields. Arrange the fields as shown in left picture. Your initial table will look like right picture.

  

Now click on “Error Code” and access field settings.

First check “None” option in “Subtotals & Filters” tab to disable totals after every row. Then navigate to “Layout & Print” tab and click on “Show item in tabular form” option.

 

Do this procedure also for “Dealer” field and your table will look like this:

If you also want dealer names to repeat on each row, reopen “Dealer field settings and check “Repear item labels” option in “Layout & Print” tab.

This is how you arrange pivot table row labels in the same line.

You may also like...

19 Responses

  1. Rehan says:

    You buddy are a star.

  2. Marie says:

    This cured my problem. Thank you!!!

  3. Kris L says:

    Exactly what I was looking for, thank you.

  4. Dione says:

    Thanks a million! exactly what I needed to know!!!

  5. Anna says:

    Wow. Mind Blown.

  6. Jooly says:

    OH wow. So many years I have struggled with this. Thank you.

  7. Kevin says:

    Thanks! This has saved me so much time! The only thing I am still having trouble with is that I don’t see a “Repear item labels” option. I only have “Insert blank line after each item label” and “Show items with no data”.

  8. Taz says:

    Fantastic result! Though I couldn’t find the “Repeat item labels” option. Not a biggie.

  9. Kevin Shorthouse says:

    You good Sir are a God send! I’ve wasted way too many hours doing this manually. Thanks a million!!!!

  10. Cris says:

    Thank youuu!! Exactly what I was looking for..

  11. Rhymingoz says:

    sensational. Thankyou. Have always tried to “Merge” Row labels with no luck. This worked a treat

  12. Crap@Excel says:

    Mega, nice one

  13. Anup Shah says:

    Hi, There is also one more simple way, Right Click on Pivot Table >>Select Pivot Table Options>> Go to Display>> Click on Classic Pivot Table Layout (enable dragging of fields in the grid)>>Ok.

    It would give same result.

  14. Richard says:

    Thank you!!!

  15. Silviu says:

    Finally a smart replay without Classic table view. You buddy are fantastic.
    Thank you! 🙂

  16. Monika says:

    Does anyone know if there is a way to make these settings the default, so I do not have to repeat these steps for every table and every variable? Thanks!

  17. Mikel says:

    Thanks!!!

  18. Rita says:

    Great, Thanks! That’s what I was looking for!

    But now I have an additional issue. Some of the descriptions appears in several rows.

    Based on the above example, it appears as following:

    E010 Sound Problem (row1)
    Something else related to E010 description (row2)
    E011 Damage Delivery (row1)
    E012 Late Delivery (row1)

    Thus, in the above case, the E010 has two rows instead of one. How can I merge the two rows into one in the source data?

    Thanks in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *