Many of you have most likely uploaded large workbooks and have seen the message.
“Couldn’t Open the Workbook
Sorry, we can’t open your workbook in Excel Online because it exceeds the file size limit.
You’ll need to open this in Excel.”
When searching online, you will see that there is a 10 MB limit for Excel workbooks in SharePoint Online.
File size limits for workbooks in SharePoint Online
If you want to work around this, you can use Power BI (free) license.
Make sure anyone who wants to access this large workbook from Excel Online, has this license checked.
In my test, I downloaded one of our larger Sample Workbooks.
Microsoft PowerPivot for Excel 2010 and PowerPivot in Excel 2013 Samples
I chose PowerPivotTutorialSample.xlsx
Looking at the workbook, it is 113,592 KB.
If I crack the workbook open (rename it from .xlsx to .zip). I can see the Data Model occupies the majority of the workbook.
File is 176KB and Data Model is 113,416 KB.
If I immediately open that workbook after uploading to SharePoint Online, I will get the error:
Here is how to workaround this limitation.
Upload your workbook to a SharePoint Online Report Library.
Go to Power BI: https://powerbi.microsoft.com and Sign In.
In the bottom left hand corner click the arrow to bring up Get Data.
Import of Connect to Data > Files.
SharePoint – Team Sites.
Enter Site URL of Connect to > type the URL > Connect
Choose the location
Choose the file
Connect, manage and view in Excel Power BI > Connect
It will process the file.
It will then connect and you can view the workbook.
You will now be able to open that file from SharePoint Online. Note, every user needs to have a Power BI free license.
If users do not have the free license they will get this error.
Another critical piece to is that the workbook (without the data model) cannot be larger than 10 MB. If it is, then you will see this error.