Skip to content

External data available in Excel services


If you want to work with table data based on an external data connection in Excel Services, you can’t use an external data range (ordinary excel table). You must create a PivotTable and make it look like an ordinary table by flatten the hierarchies into a table.

Create a connection to a data source, get data and place it in a pivot table.

Place all your "columns" in Row Labels area.


Set the options so that the pivot table don’t show subtotals and grand totals.


Select the option to show the pivot table in Tabular Form. You’ll find it on the ribbon under PivotTable Tools tab and then Design tab.


To remove the +/- signs before each level click the Buttons button under Show/Hide group in PivotTable Tools, Options on the ribbon.


If you publish this to SharePoint using Excel Services to show it you can now filter, refresh and all the things you can do with a pivot table in Excel Services. With the look of an ordinary table.

From → Excel, Patrik, Sharepoint

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: