Stop PivotTable Columns Resizing

A frustration with PivotTables in Excel is the automatic resizing of column widths that occurs when a PivotTable updates. This often happens when a PivotTable is refreshed or filtered.

Let’s look at how to stop PivotTable columns resizing for specific PivotTables, and how to change the default behaviour and stop the PivotTable column widths changing in future Excel workbooks.

PivotTable Column Widths Change on Update

In this example, we have a simple PivotTable with product sales and a Slicer that enables us to filter the PivotTable by product category.

In the following image, the PivotTable is filtered to show products in the ‘Food’ category only.

When the filter is changed to show products in the ‘Cakes & Pastries’ category, the first column expands to accommodate the longer product names such as ‘Chocolate Chip Muffin’.

This is due to a PivotTable option that forces columns to autofit on update.

Watch the video: Stop PivotTable Columns Resizing

Disable Autofit Column Widths for PivotTable

We will stop this PivotTable from automatically changing column widths on update by modifying the settings.

First, we need to resize the columns to the appropriate width.

In this example, the longest product name is ‘Chocolate Chip Muffin’, so we know that this column width is wide enough to cater for all product names. Therefore, we will adjust the setting while the PivotTable is filtered for ‘Cakes & Pastries’

Note: It is a good idea to clear the filter and list all product names. Then you can be certain of the required PivotTable column width.

Click in the PivotTable, then click PivotTable Analyze > Options.

Or, right-click the PivotTable and click PivotTable Options.

In the PivotTable Options window, click the Layout & Format tab, and uncheck the Autofit column widths on update box.

Click OK to close the window.

Now, when you filter the PivotTable, the width of the columns do not change.

Change the Default PivotTable Settings

You can change the default PivotTable options in Excel to stop new PivotTables resizing on update.

1. Click File > Options to open the Excel Options window.

2. Click the Data category down the left-hand menu.

3. Click the Edit Default Layout button beside the text reading Make changes to the default layout of PivotTables.

1. In the Edit Default Layout window, click the PivotTable Options button.

1. Uncheck the Autofit column widths on update box to stop PivotTable changing column width. Click OK to save and close each window.

The columns of new PivotTables will now no longer automatically resize.

These options can be modified for specific PivotTables in the future if columns automatically resizing is desired.

Stop PivotChart from Automatically Resizing

In addition to preventing PivotTables resizing, you can also stop PivotCharts from automatically changing width when PivotTables are updated.

Click on the chart area of the PivotChart, click Format, and click the Size and Properties icon in the corner of the Size group.

The Format Chart Area pane opens and you are taken directly to the Size & Properties category.

Expand the Properties section if needed and click the Move but don’t size with cells or Don’t move or size with cells option, depending on your needs.