(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.)
Issue: Excel automatically makes column widths too wide
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.
Trick: How to change the default column width when refreshing a pivot table
- Right-click in the pivot table
- Select Pivot Table Options
- In 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.
