After inserting a pivot table and relevant fields for analyses, you may require to change pivot table report layout as per your requirement.

A better layout can boost your analysis power. “Forget better, I want best.” So, what is the best layout? Wait a second, there cannot be any good or bad layout. It totally depends on your requirement whether the layout is good, better or the best…It is purely subjective. You need to use different types of layouts based on your data and analysis criteria as per your requirement as each layout has their own pros and cons.

Reports Layout in pivot table

There are three types of layouts in pivot table.

  1. Compact Layout
  2. Outline Layout
  3. Tabular Layout

Pivot table by defaults uses compact layout and there is no way out to change the default layout of the pivot table unless you use a VBA.

To change the Report layout

  1. Select any cell of the pivot table.
  2. Go to Design ribbon of Pivot table tool.
  3. Select the Report Layout
    change pivot table report layout
  4. Choose any one of the three layout.

If you want a file to try out different layouts of the pivot tables along with this article. Here is the Sample File: Pivot Report Layout option File

1. Compact Layout:

  • It is the default layout of the pivot table.
  • All the row labels items will be shown in one column. i.e. each of the row labels will be shown in individual rows.
  • Each of the rows field is indented to right to separate them from above row item. The indentation can be changed through pivot table options.
  • To change pivot table options Right click on any cell of pivot table. Select Pivot table Options. Go to Layout & Format and change indentation in layout option.
  • Pivot table options >> Layout & Format >> Layout >> Write value in When in compact Form indent row labels: __ Characters”
  • Note: You cannot repeat items label in compact layout.
  • The entire rows label item will be shown in one column only; still you can filter or sort by individual rows items.
  • Subtotal can be shown on the top or bottom of the rows in compact layout.
  • There is general heading for all row labels in compact layout i.e. “Row Labels”

2. Outline Layout:

  • All the row labels items will be shown in different column and on separate rows unlike compact layout.
  • This layout is useful when you are not concerned about total width of the pivot table.
  • Each of the row item labels have their name on heading row.
  • You can repeat items label in outline and tabular layout.
  • Subtotal can be shown on the top or bottom of the rows in compact layout.

3. Tabular Layout:

  • All the row labels items will be shown in different column and but on a single row.
  • Each of the row item labels have their name on heading row.
  • You can repeat items label in outline and tabular layout.
  • Subtotal can be shown only bottom of the rows.

FAQs

How to get pivot table multiple columns per row

Ans:

  1. Select any cell of the pivot table.
  2. Go to Design ribbon of Pivot table tool.
  3. Select the Report Layout
  4. Choose Outline layout or tabular layout.

Explanation:

Pivot tables default have compact layout type which is quite a bit difficult to understand and analysis. To have pivot table multiple columns per row, use tabular layout or outline layout.

How to get pivot table row labels side by side

Ans:

  1. Select any cell of the pivot table.
  2. Go to Design ribbon of Pivot table tool.
  3. Select the Report Layout
  4. Choose Outline layout or tabular layout.