We're back for another informative blog in our series, Excel at Excel with Steve Hughes! In this post, he will cover how to retain the size of your PivotTable after refreshing your data.
Let’s start with the fact that these tips are the 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.
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.
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.
Learn more about Excel in our 4-day online class is designed to get you up-to-speed using tools easily accessible to Power Users: Microsoft Excel and Power BI Desktop.
