Use Pivottable Report To Make External. In the Data group click the top section of the Change Data Source command. Drag and drop fields from the PivotTable Field List onto the Row Fields Column Fields and Data Items sections of the PivotTable.
Under Choose where you want the PivotTable report to be placed select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear. In TableRange verify the cell range. To do this in Excel 2016 and Excel 2013 go to the Insert tab Charts group click the arrow below the PivotChart button and then click PivotChart PivotTable.
Select the Excel file with your data.
All seemed to be fine in that I was able to go to DataConnectionsAdd and create an external data connection to this DataDump table file that resides on Sharepoint. Select Browse for More. Click INSERT Recommended PivotTables. Under Choose where you want the PivotTable report to be placed select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear.