Didn't find your answer?

Latest Any Answers

After refreshing data, the existing pivot table will expand columns with (both text and numbers) results to the new data width. I then have to reset the column width back to my requirement.

How can I set column width in a pivot table so the widths do not change when new data is introduced. Also, can it be set so the data results justify, wrap and expand row height to show the whole result in the pivot cell?

Replies (4)

Depending on how complex is your pivot table; I usually find it useful to replicate the table in adjacent columns. It is best to use typed-in formulae (i.e '=+A2') to do this as if you use the arrow keys to reference the pivot table you get strange, pivot-based formulae which can cause problems.

Once you have the data alongside the table, making sure that it still reaches the bottom after refresh, you can easily format these new columns to how you like them and they will not change on refresh. Also you can copy and paste as value these columns elsewhere to manipulate the data if required, using standard Excel tools.

I find the GenerateGetPivotData functionality quite infuriating, which is why I was relieved to find a way of turning it off (ie so you can write formulas referencing the pivot table without getting an "=GETPIVOTDATA("YourPT")"):

Go (from within Excel) Tools>Customize and when the Customize Dialog opens select the "Commands" tab. Choose the Data category and navigate down the list of buttons until you come to the "Generate GetPivotData" one - you can drag this onto one of your toolbars. This then allows you to toggle On/Off ie On and you get GetPivotData() formulas, Off and you get normal refs (eg = A1 etc).