#Excel pivot table tabular format how to#
#Excel pivot table tabular format install#

You do NOT have to take any extra steps to format the numbers. The macro automatically formats the numbers in the values area of the pivot table when we add a field to the values area in the Pivot Table Fields List.

#Excel pivot table tabular format install#

If you don't want to mess with macros then you can download and install the add-in to get this feature. I have also added this feature to The PivotPal Add-in. I've created a macro that automatically applies number formatting when you add a field to the values area. So, how can we change this default number formatting to something besides General?

There are a few different ways to apply number formatting to the values area, and it can be time consuming process. This is a multi-step process that requires several clicks. As you can see in the screenshot below, the General format does not add commas/periods for thousands separators the number of decimal places is not fixed.Īfter adding a field to the values area, we then have to take extra steps to format the numbers. Unfortunately, the General number format makes numbers look UGLY.

This is the default format for all cells on the worksheet, and the pivot table does NOT change it. Typically when we add a field to the values area of a pivot table, the cells are formatted with the General number format. The Problem with Pivot Table Number Formats The 2nd video that explains the macro is available below. The PivotPal Add-in (paid) also has this feature and can be downloaded and installed. To do that, select the corresponding check boxes in the Layout Download The Field Settings dialog also allows you to specify whether to repeat item labels for an outer field, insert the blank line after each field item, or show items with no data. To display field items in compact form, select Show item labels in outline form and then click the Display labels from the next field in the same column (compact form) check box. To show field items in tabular or outline form, select the corresponding option in the Layout section.In the invoked Field Settings dialog, switch to the Layout & Print tab. To do that, select a row field and on the PivotTable Tools | Analyze tab, in the Active Field group, click the Field Settings button.

You can change the layout options for the specific row fields only. To invoke it, click the PivotTable Options button in the PivotTable group. The advanced layout options can be specified using the Layout & Format tab of the PivotTable Options dialog. To do that, on the PivotTable Tools | Layout tab, in the Layout group, click the Subtotals, Grand Totals or Blank Rows button, respectively, and select the desired action from the drop-down menu.

You can also specify whether to show/hide subtotals and grand totals in the PivotTable report, or insert the blank line after each item in the outer row fields. To disable repeating, select Do Not Repeat Item Labels. To do that, select the Repeat All Item Labels item in the Report Layout drop-down menu. For the outline and tabular forms, you can enable repeating item labels in the outer row fields.Tabular Form - the pivot table is shown in the table format. Outline Form - the report data is outlined. The report is compressed to prevent data from spreading horizontally off the screen. The invoked drop-down menu allows you to apply one of the following layout forms.Ĭompact Form - the default layout. To change the PivotTable layout, on the PivotTable Tools | Layout tab, in the Layout group, click the Report Layout button.

#Excel pivot table tabular format how to#

This topic describes how to specify the layout options for the entire PivotTable report or a specific row field only.