Sunday, October 21, 2012

Quick Trick: Resizing column widths in pivot tables

AppId is over the quota
AppId is over the quota

(Who could be better than a Business Intelligence analyst at teaching us a thing or two about Excel? We asked our own number-crunching wizard Stacey Armstrong to share some Excel tricks she's learned along the way..)

I often work with pivot tables that have URLs as rows of data, and the URLs can be quite long. In the past, whenever I changed the data and refreshed the pivot table, Excel would automatically resize the column width, making the column as wide as the longest URL. This pushed all my other data to the right and out of view. To see it, I had to scroll--an extra eye-wearying, time-consuming step. 

Right-click in the pivot table 

resizing pivot tables

Select Pivot Table OptionsIn the Pivot Table Options dialogue box, click the Layout and Format tab, and then uncheck the box Autofit column widths on update.

Now the column width doesn't change each time the pivot is refreshed, but now you need to define how wide the column with the URLs should be.

Go back to your pivot and select the column with the long URLs. Right-click, and enter a value for the column width. 

Now when you refresh the pivot table, the column doesn't resize. Instead its width corresonds to the value you entered.


View the original article here

No comments:

Post a Comment