You can consume a SharePoint Online list in Excel Online and On Premise if you call the ListData.svc:
Excel Online – Consuming SharePoint Lists
Excel Services – Consuming a SharePoint List using the ListData.svc
Since Excel Services inception (SharePoint 2007) users have been attempting to consume a SharePoint List in Excel Services. Unfortunately this is not supported. I will explain why and I will also provide a workaround using PowerPivot.
If you are unsure whether or not you are using a SharePoint list as a data source, you can verify this by opening the workbook in Excel > Data > Connections > Properties > Definition tab. If the connection contains Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="":ApplicationName=Excel;Version 22.214.171.124, you are trying to consume a SharePoint List (also see screenshot).
This connection is created by (the below example is using SharePoint 2013) List > Export to Excel:
You may see the following error in the ULS log when interacting with a workbook in Excel Services that has an Excel data connection to a SharePoint list:
Excel Calculation Services process tried to access a null virtual address for which it does not have access to.
Below is Microsoft's documentation regarding Unsupported Features in Excel Services.
Unsupported Features in Excel Services
When you have PowerPivot for SharePoint installed you get the option to “Export as Data Feed” from your SharePoint lists.
When using PowerPivot, there are 2 connections. The first one is the Excel connection and the second is the PowerPivot Connection.
Much like the previous screenshot, you can view the Excel connection by opening the workbook in Excel > Data > Connections > Properties > Definition tab
You can view the PowerPivot connection via Excel > PowerPivot tab > Manage
Choose Existing Connections
Click Edit > Advanced > and you will be able to see List.atomsvc in Connection String.
Once the data is in the PowerPivot Add-in then you can create a Pivottable off that set of data.
Once your Pivottable is finished you now can publish this workbook back to SharePoint in the PowerPivot Gallery.
Once the workbook is in the Gallery you can click on Manage data refresh to setup a daily refresh schedule.
To test the connection go to the manage data refresh page and choose "Enable" & "Also refresh as soon as possible".
Important Note 1: I have seen cases where the refresh runs and eventually times out. If you see this, you may need to grant the account running SQL Server Analysis Services (POWERPIVOT) Read permissions to the SharePoint list you are attempting to refresh.
After this runs, you will see fresh data.
Important Note 2: The Scheduled Data Refresh is designed to run once a day during after business hours when server is experiencing the least amount of stress. Therefore you will only see refreshed data once a day (versus real-time like when using Excel Services directly).
Using SharePoint lists as data sources with Excel Services (SharePoint Server 2010)
Using a SharePoint list as a data source
Using SharePoint List Data in PowerPivot Whitepaper