Back
Excel

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

Join the conversation

9 comments
  1. One of the main data flow we use with SharePoint (SPO in our case) is
    SharePoint list >> Excel table >> Excel Pivot table/graph >> Excel Services.

    This allows us to quickly visualize the content of a list right within SharePoint. Our only drawback at this point is that we need to manually open the workbook to refresh the data, which is not that optimal.

    Are we to understand that with this new update, that workflow in SP-2013 (or SPO-2013) will be fully supported without the need to open the client app?

    Tx!

    • Hi Frederic, it looks like you have a query table comprising external data from a SharePoint List. Refreshing such a Query Table is supported in SharePoint 2013, however “SharePoint Lists” themselves remain an unsupported feature in Excel Services 2013, hence you will need to open the workbook in client to refresh.

      That said, for SharePoint On-line (SPO) you still need to open workbook in desktop Excel client to refresh this Query Table, because external data access is not yet supported in SPO.

      • Thanks for the info. Well, truth being told I didn’t expect this to work quite yet. :) Still is my #1 requested feature for SPO tho. It would allow users to make their own, decently powerful, data visualisation solutions with very little expertise.

  2. I really love this feature I can write entire query to retrieve data on the fly via browser with excel services ..
    just absolute classic feature.
    please browse for
    Sharepoint
    Sarah

  3. My biggest request is importing an O365 SharePoint list into Excel’s Data Model (PowerPivot) via OData feed. If you try it in PowerPivot you get a 403 error. There’s a way to get it in via the Excel data import but it’s roundabout and leads to other problems.

  4. The link provided for creating a data source doesn’t work as it appears to be out dated as follows:

    Step 2 works as stated but step 3 says to “Select the connection that you want, and then click Open”. However that’s not the flow. The wizard first requires you to select “the kind of data source to connect to” followed by additional questions.
    I’m trying to use an existing Excel 2013 workbook as my data source (on my local drive just to keep things simple for learning.) Despite extensive web searching I can’t figure out what the proper connection type is (it appears to be Other/Advanced and one of the OLE DB providers) but which one and what the connection string needs to be has proven elusive.

    Providing this information would be greatly appreciated.

Comments are closed.