New support for query tables in Excel Services improves workbook sharing across an organization

This week we’re highlighting some improvements in Excel Services, as part of the SharePoint 2013 release. This post is brought to you by Prash Shirolkar, a Program Manager in the Excel Team.

Take one last look: The error message below is now a thing of the past.

With the enhancements to Excel Services as part of the new Office, we now support query table refresh. Workbooks with query tables now can be seamlessly refreshed on the go from any browser! No need to convert your query table to a pivot table or download a workbook to desktop Excel client to refresh your query table. In other words, broad sharing of workbooks throughout an organization just got easier.
 

Let’s give it a whirl

In Desktop Excel (2007 or 2013), ensure you have a workbook with a query table and that it is connected to a data source. Right-click on the table, click Table, then click External Data Properties. If you don’t have one handy, then follow the steps here to create one.

Once you’re connected, follow these 5 steps:

  1. In the External Data Properties dialog ensure you have a connection for “Name.”

     

  2. Ensure your query table successfully refreshes. For Desktop Excel (2007, 2010, or 2013), select Refresh on the Data tab of the Ribbon.

  3. Ensure your Excel Services 2013 is configured for external data refresh depending on whether your query table uses embedded or external data.

  4. Publish your workbook with the Query Table to a SharePoint 2013 Document Library and click on the file to view your query table. You can follow the steps here to publish Excel workbooks to SharePoint.

  5. Select Refresh All Connections to see the latest data.

That’s it! Your query table is now refreshable in your Excel Services deployment.

More to come

As always, we’ll continue to work to improve Excel Services. One area we still do not support is refreshing a specific connection via “Refresh Selected.” This is something we are considering, but would love to hear from you in the comments if this is something you find important and use often.

Happy refreshing of your query tables! Keep us posted on additional features you’d like to see in future releases.

Prash Shirolkar, Excel Program Manager