Change a Pivot Table to Tabular Form

You can switch a Pivot Table layout from the default Compact Form to a Tabular Form to view data in a traditional flat table layout.

The Tabular Form layout makes it easy to copy cells to other worksheets and export data to systems requiring data in a tabular structure.

The Compact Form Layout Versus Tabular Form Layout

When you create a Pivot Table in Excel, it is set out in a Compact Form Layout by default where all the row fields are in one column.

The Compact Form layout conserves space but can make it difficult to interpret data, copy it to other worksheets, or export it to systems requiring data in a table format.

The example Pivot Table below is in Compact Form Layout where the row fields of bank branch and account type are in column A under the generic name ‘Row Labels.’ 

The row fields are indented to distinguish between the bank branch and account type data.

Note: The example Pivot Table above shows a bank’s new deposits broken down by bank branch and account type.

When you switch the Pivot Table to the Tabular Form layout, it will look like the example below.

Notice that the ‘Branch’ and ‘AccType’ row fields are in separate columns making it easy to interpret that data, copy it to other worksheets, or export it to other systems.

In this tutorial, I will show you ways to change a Pivot Table to a Tabular Form layout from the default Compact Form layout.

Also read: How to Lock Pivot Table Column Width

Method #1: Use the Show in Tabular Form Option on the Report Layout Drop-Down List

You can change a Pivot Table to a Tabular Form Layout by selecting the Show in Tabular Form option on the Report Layout drop-down list located on the Layout group of the contextual Design tab.

Suppose you have the Pivot Table below in a Compact Form layout and want to change it to a Tabular Form layout.

Here’s how to do it:

1. Select any cell in the Pivot Table.
2. Click the Design tab, open the Report Layout drop-down list on the Layout group, and select the Show in Tabular Form option.

The Pivot Table is changed to Tabular Form layout as shown below.

You can select the Repeat All Item Labels option below the Show in Tabular Form option if you want the item labels in column A to be repeated in the Pivot Table as shown in the example below.

You can use the options on the Subtotals drop-down list on the Layout group of the Design tab to show/hide the subtotals or change their location. 

You can use the options on the Grand Totals drop-down list on the Layout group of the Design tab to show/hide the grand totals.

Also read: How to Lock a Pivot Table in Excel

How to Set Tabular Form as Default Layout

Rather than manually changing the layout to Tabular Form each time you create a Pivot Table, you can set Tabular Form as the default layout, ensuring all new Pivot Tables automatically use this format.

Here’s how you can do it:

1. Click the File button on the Ribbon to open the Backstage view window.
2. Click Options on the left sidebar of the Backstage view window.

The above step opens the Excel Options dialog box.

3. On the Excel Options dialog box, click Data on the left sidebar, and then click on the Edit Default Layout button on the right.

The above step opens the Edit Default Layout dialog box.

4. On the Edit Default Layout dialog box, open the Report Layout drop-down list, select the Show in Tabular Form option, and click OK.

3. Click OK on the Excel Options dialog box.

The above steps will ensure that all NEW Pivot Tables are by default set out in Tabular Form.

Also read: How to Clear Pivot Table Cache?

Method #2: Use VBA to Change All Existing PivotTables in a Workbook to Tabular Form

If your workbook contains multiple Pivot Tables in the default Compact Form layout, you can save time by using the VBA code below to switch all of them to the Tabular Form layout.

Sub SetAllPivotTablesToTabularForm()
Dim ws As Worksheet
Dim pt As PivotTable
Dim ptCount As Long
Dim updatedCount As Long
updatedCount = 0
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RowAxisLayout xlTabularRow
updatedCount = updatedCount + 1
Next pt
Next ws
If updatedCount > 0 Then
MsgBox updatedCount & " PivotTable(s) switched to Tabular Form layout.", vbInformation
Else
MsgBox "No PivotTables found in the workbook.", vbExclamation
End If
End Sub

When you run the code, it iterates through all sheets in the workbook checking for Pivot Tables. If it locates a Pivot Table it sets its layout to Tabular Form while tracking the number of updated Pivot Tables. 

If any Pivot Tables were updated, the code displays a message box indicating the total count of updated Pivot Tables. If no Pivot Tables were found the code informs the user accordingly.

I have shown you ways to change a Pivot Table to a Tabular Form layout. I hope you found the tutorial helpful. 

Other Excel Pivot Table articles you may also like:

• How to Change Data Source in Pivot Table
• How to Delete a Pivot Table in Excel?
• Cannot Group That Selection Error in Pivot Tables
• How to Group by Months in Excel Pivot Table?
• How to Add Calculated Field to Pivot Table?