How to change the layout of the Excel Pivot Tables?


Pivot Tables are powerful mechanism to analyze data. And as you may know, it’s really popular among business professionals that use excel for their day-to-day data analysis needs. With that context, Here’s the blog post on Pivot Tables Layout:


I got a question from my client who had a specific need on how she wanted her Pivot Tables to look. Here’s how the Pivot Table should look like:

excel pivot table power pivotSolution:

I am using Excel 2010 Pro Plus to demo the solution, please use this solution to adapt to use it with other versions of excel.

Without formatting and changing the layout, the excel pivot table looked like this:

step 1 excel pivot table from power pivot

Now, let’s work step by step to meet the requirement:

Step 1: click somewhere on the Pivot Table and from the Toolbar, Switch to the Design Tab under PivotTable Tools

Pivot Table Tools Design ExcelStep 2: Now here, Go to Report Layout > Show in Tabular Form

Pivot Table Layout Tabular Form

Step 3: As you might have noticed the “hierarchical” structure is now broken up into multiple structure getting us closer to meet the requirement.

half way pivot table layout

Step 4: In our requirement, you can see that it does not give the user to see the “+” or “-” (expand, collapse) buttons. So, let’s hide these buttons from the report from Step #3

Right click somewhere on the pivot table > PivotTable Options > Display tab > uncheck the box that says “Show expand/collapse buttons“:

pivot table hide expand collapse button

Step 5: so now it looks as follows:

excel pivot table power pivot

So as you can see, it meets our requirement now.

To recap, Here’s what we had to do:

#1: Change the Layout of the Pivot Table

#2: We saw how to hide the expand/collapse buttons too.


That’s about it for the post! Thanks for reading.

your comments are most welcome!


0 thoughts on “How to change the layout of the Excel Pivot Tables?

What do you think? Leave a comment below.