Exploring Excel 2013 for BI Tip #7: Retaining Pivot Table Size After Data Refreshes

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.

Exploring Excel 2013 for BI Tip #1: Quick Explore

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!

Quick Explore

So, you have connected Excel to your SSAS cube.  You really wish you could cross drill easily in the product.  If you have used PerformancePoint Services you know the process.  Right click on the bar or cell and then choose the dimension to drill to using the information you clicked on as a starting point.  You can now do this in Excel 2013 using Quick Explore.  Here’s how to do it.

1. Click on a cell and hover over it.

2. Click the Quick Explore button a.k.a. magnifying glass and lightning bolt.  That will pop up the following dialog box.

In this box, you can see that the cell has focus on “Boston” based on context and is drilling into the Geography hierarchy of the Client dimension based on the selected filters and slice of data we are focused on.

3. Click the Drill To target.  Excel will create a new view of the Pivot Table with the Drill to on the row as shown here with the filter still in place.

4. Going back…  One of those frustrating things with this is how do I go back to my original view.  Ironically, you use Undo.  This will reset the view back to the previous.  So, if you choose to drill into this data again and again, you have to Undo that many operations to get back to the starting point.  Of course, can click the arrow by the arrow to see your “bread crumb” and pick where you want to go back to as well.

Until the next tip…