Have you ever gotten a Pivot table that looks great, the widths of cells just as you want, and then updated the table to add more data to it, and all your hard work formatting files were lost. This is due to one small tweak — a detail that you’ll learn about — this will no doubt be one of your tweaking options when creating a Pivot in the future.
So, if you need your columns to become static, here’s how you do it very easily in Excel. This is what I use all the time, as soon as I have a pivot table that looks the way I want it, and I don't want it to change! ".
First create a standard pivot table
- Click on any cell in the dataset that you want to use in your analysis.
- On the "Insert" tab, click "Pivot Table"
- The Create Pivot Table dialog box appears because Excel automatically selects the dataset for you.
- Click OK.
- A list of pivot table fields appears.
- Select the field you want to display in the row area
- Select the field you want to display in the "Columns" area
- Select the data field that Excel will analyze in the data area.
Now you have created a standard Pivot, and for most people this may lead to the completion of formatting settings. If you want more control over the widths of the columns, then get them right away, and then get access to the little setup magic that will keep your table neat and tidy when you update it.
- Select a cell in the table
- Click the Options tab in the ribbon.
- Select the Options tool in the pivot table group. Excel will display a group of pivot table options.
- Make sure the Layout & Format tab is displayed.
- Make sure that the width of the automatic column width is NOT checked during the update (we don’t want Excel to automatically adjust)
That is, your cell width will remain the same as you set it, before you specify the width of the AutoFit columns and will not move when you update or refresh the pivot table. Of course, if you want to cancel this static formatting, just make sure that the width of the autofit columns in the update is checked. This ensures that the cell width in your table will be automatically adjusted again as your data updates to fit more data into your dataset.
