Drilling around in your Excel PivotTables and PivotCharts

This week, I want to turn your attention to one of the truly fantastic Business Intelligence (BI) features that we added to the new Excel. It’s called, Quick Explore, and, as long as you are working in the volume licensed desktop version or in the Office 365 “E3″ and “E4,” or “M” packages, you may have seen its entry point when hovering over any of the dimensions or measures in a PivotTable or PivotChart. It represents one of the many BI elements that we focused on in Excel in order to elevate Excel to one of the preeminent analytic tools in the market today. Once you have a PivotTable or PivotChart on your spreadsheet, you can easily access the Quick Explore option by either hovering over a series member, or a category, and clicking on the magnifying glass or bringing up the context menu and selecting the option from there.

Or you can get to it from the PivotTable by clicking on any row member, column member, or value cell (you cannot get to it via subtotal or grand total value cells):

And, yes, you read that correctly. You can drill on a value cell. In the case of the value cell, we simply grab the associated member context and drill on that. This allows users to drill on a dimension value without having to find the corresponding row.

When you click on the icon, you are presented with the option to cross-drill, or explore, or “pivot,” against any of the other measures or dimensions for the series on which you clicked. For example, when I “Quick Explore” on the West region, I am presented with the ability to drill on the data associated with that member of the Region measure.

Clicking on the year member automatically adds it to my filter list and pivots my data accordingly.

And now that we’ve added the field list to the Office Web App experience, you can interact with the Quick Explore feature in nearly the same way as you would in the desktop version. If, for example, you don’t want to drill within the context of Region (as in this case), you can simply drag it to another part of the field well and continue your analysis in other meaningful ways. But unlike with the client experience, in the browser you can disable interactivity (either in embedding or in the SharePoint web part properties). Disabling interactivity will prevent users from filtering, sorting, and . . . exploring.

So there you have it — Quick Explore is a stellar feature and really makes Excel a terrific ad-hoc analytic tool in your BI toolset.