Share this content

Setting column width in pivot table

Setting column width in pivot table

Didn't find your answer?

Search AccountingWEB

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?

All suggestion gratefully received

Michael Ung

Replies (4)

Please login or register to join the discussion.

By Richard Willis
29th May 2007 10:21

Just an idea
Hi Michael

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.

Thanks (0)
By mung1
29th May 2007 18:07

Thank you
Many thanks for all your helpful comments. I shall give them a go.

Thanks (0)
By RichardSchollar
29th May 2007 11:51

Note for Richard
Hi Richard

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).

Hope this helps!


Thanks (0)
By Anonymous
29th May 2007 17:28

Thanks to Richard Schollar - that Pivot table issue has been annoying me for some time now - at last i can get the formulae done the easy way.

It makes one wonder why the toggle off is not the default position.

Also for Michael these are good ideas from the two Richards.

So its goodnight from me........and.....well you can guess the rest.

Thanks (0)
Share this content