[Excel] Excel resets the column width of the pivot table!How to fix with a width that is easy to see

Does the column width change every time I update the pivot table? !!

Have you ever used a pivot table in your daily work? Pivot tables are a useful feature for aggregating and analyzing data, and have the advantage of being intuitive with the mouse, without the need for cumbersome formulas or special functions.

It's a pivot table that's easy and convenient to use, but have you ever been frustrated because the column width you adjusted changed when you updated the data?

For example, suppose you have the following pivot table (1).

Try it with the following pivot table (①)

To make the entire pivot table easier to see, try widening the column width (②).

Try widening the column width to make it easier to see the whole (②)

Then, if you modify the data in the table that is the basis of the pivot table and click [Pivot table analysis] (③) → [Update] (④) to update the pivot table, the column width will be restored. Masu (⑤).

If you update the pivot table by clicking [Pivot Table Analysis] (③) → [Update] (④), the column width will be restored (⑤).

It's frustrating that even if you adjust the column width to make the table look good, it will be reset every time you press the [Refresh] button. So, this time, I will explain how to set the column width so that it will not be reset every time the pivot table is updated.

In the previous article, I explained "How to create a pivot table" and "How to update a pivot table", so please read it before practicing this trick.

Prevent the column width from being reset every time you update the pivot table

Let's use the "PivotTable" example above to set the column width not to be reset every time we update the pivottable.

With the cells in the pivot table selected (1), select the [Pivot Table Analysis] tab (2) → [Pivot Table] (3) → [Options] (4).

With the cell selected (1), select the [Pivot Table Analysis] tab (2) → [Pivot Table] (3) → [Option] (4).

This will bring up the PivotTable Options dialog box. Make sure that the [Layout and Format] tab (⑤) is displayed, click [Automatically adjust column width when updating], and turn off the check mark (⑥).

After changing the settings, click [OK] (⑦) to close the dialog box and complete.

On the [Layout and Format] tab (⑤), turn off the check mark (⑥) of [Automatically adjust column width when updating], and then click [OK] (⑦) to close the dialog box.

In the next section, let's see if the column width is no longer reset when we actually update the pivot table.

Let's see if the column width is no longer reset

Let's try using the same example as at the beginning. First, try widening the column width of the pivot table (①).

Try widening the column width of the pivot table (①)

Next, change the data in the table that is the basis of the pivot table. In this state, the changes have not been reflected yet, so click the [PivotTable Analysis] tab (②) → [Update] (③) to update the pivot table.

Click the [Pivot Table Analysis] tab (②) → [Update] (③) to update the data in the original table.

Even after clicking the [Update] button, the column width does not change (④). The data in cell "G6" has been updated, and it has been changed from "5000" to "10000" (⑤).

The column width does not change even if the data is updated (④), but the data in cell "G6" has been changed from "5000" to "10000" (⑤).

Now the column width of the pivot table doesn't change automatically. The column width remains fixed not only when you update the data, but also when you change the items that make up the pivot table. You can change the column width manually.

Can be set so that the column width is not reset at the time of update

This time, I explained how to set the column width so that it will not be reset every time the pivot table is updated. By default, updating the pivot table automatically adjusts the column width according to the data length entered in the cell, but you can change it if you find it inconvenient. Let's change the settings as needed. Please remember.