As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013. Much of the content will be a result of my daily interactions with business users and other BI devs. In order to not forget what I learn or discover, I write it down … here. I hope you too will discover something new you can use. Enjoy!
Retaining the Pivot Table Size after a Data Refresh
Let’s start with the fact that these tips are a result of my work with Excel to support BI tasks. This turns out to be one of those things that power users of Excel probably already know. I had to find this out and thus I am sharing it here.
The Problem
When I was working on the dashboard for Modern Apps Live! I had some nicely formatted tables. However, I had two pivot tables stacked as seen in the picture below on the right.
Whenever I did a data refresh the columns would automatically resize to the “one inch” width. Making the Poll Questions in the second table unreadable.
The Solution
I went looking for how to fix this. I searched for solutions online and I poked around to see if I could fix this problem. After all, it was very important to be able to refresh the data on the dashboard.
I was able to locate the solution here. As it turns out, you need to unselect the Autofit column widths on update property on the PivotTable. You can get to those options in a couple of ways in Excel 2013. First you can right click on the table to get the short cut menu and select PivotTable Options.
Or you can select Options on the far left of the ANALYZE tab on the PIVOTTABLE TOOLS ribbon.
Once you are in PivotTable Options, you go to the Layout & Format tab and deselect the Autofit option as mentioned above.
Voila! Problem resolved, now my dashboard does not need the columns resized in the pivot tables each time it is refreshed.
Data on Wheels - Kristyna Ferris & Steve Hughes 