Skip to main content
Skip to main content
Microsoft 365

Faster OLAP PivotTables in Excel 2016

If you work with connections to OLAP servers, your PivotTables are now faster. The June 2016 update contains query and cache improvements to this powerful feature. You could benefit from these improvements, whether you use PivotTables to answer one-off questions or build complicated workbooks with dozens of PivotTables.

These updates are available to Excel 2016 users as part of an Office 365 subscription. If you are already an Office 365 subscriber, find out how to get these latest updates.

You’ll also need a workbook that has PivotTables connected to either the Data Model or to an OLAP server.

About the improvements

We have made significant improvements in three major areas while querying OLAP servers.

  • Improved query efficiency—Excel will query for subtotals only if they’re required to render the PivotTable results. This means you wait less for the OLAP server to finish processing the query and spend less time waiting for the results to transfer over your network connection.
  • Reduced the number of queries—Excel is smarter when refreshing your data. It now uses the LAST_SCHEMA_UPDATE property of a connection to limit schema requests to those times when it has, in fact, changed.
  • Smarter caches—When the schema is retrieved, it is now shared across all of the PivotTables on that connection, further reducing the number of queries.

Getting the most from the improvements

No two workbooks are alike; some have received a bigger benefit than others.

It doesn’t matter if they’re connected to a tabular or multi-dimensional model. All of your PivotTables connected to Microsoft SQL Server Analysis Services, third-party OLAP providers or the Power Pivot Data Model will likely give you fresh data, faster.

Additionally, your pivots with subtotals and grand totals disabled, could be much faster to work with. Get insights more quickly when refreshing, expanding, collapsing and drilling into your data. The bigger the pivot you’re working with, the bigger the potential improvement.

To disable subtotals and grand totals, follow these simple steps:

  1. Under the PivotTable Tools section, select the Design tab.
    Faster OLAP PivotTables in Excel 2016 1
  2. Under the Subtotals button, select the Do Not Show Subtotals option.
    Faster OLAP PivotTables in Excel 2016 2
  3. Under the Grand Totals button, select the Off for Rows and Columns option.
    Faster OLAP PivotTables in Excel 2016 3

Try them yourself!

Try these new improvements and see how they can help you improve your analysis performance. Do you have ideas on other improvements that you’d like to see in Excel? Visit the Excel UserVoice and tell us about them.

—Alexander Lahuerta, program manager for the Excel team

You may also like these articles

Image for: View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more.

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…