Sign up for Office 365
Learn more about Office 365
My users are getting this error message when accessing certain Excel files. We are all using the Small Business P1 version. Other postings have suggested that this problem is caused by the file sizes exceeding the 10Mb limit imposed by Office 365 but this is not the case. I believe the issue lies with the way that Office 365 is handling the files when they contain macros or look up tables.
To test the problem I've created spreadsheets of various sizes, some with a simple look up table, and uploaded them to our Team Site with the following results -
1) A file of 13 MB won't open directly and the user gets a message that it’s exceeded the maximum size allowed (which is as expected).
2) A file of 9.1 MB without a look up table will open in the browser and data can be manipulated normally.
3) A file of 6.7 MB with a look up table WILL NOT open in the browser; instead the user gets the "Your session has exceeded its limits and has been closed" message.
4) A file of 6.5 MB with a look up table WILL open in the browser but as soon as the user attempts any manipulation of data the "Your session has exceeded its limits and has been closed" message appears. The file cannot then be reopened.
5) A file of 5.2 MB with a look up table behaves in the same way as #4 above.
In other words, I don’t think the issue is solely related to size, otherwise #3, 4, and 5 should have worked in the same way as #2.
None of the spreadsheets on our Team Site exceed 8 MB so we fully expect Office 365 to handle all these files without us having to buy even more client installations of Excel just so that our users can view data. After all, isn’t that what we’re already paying for in our monthly subscription?
Thanks for the feedback.
Microsoft have finally come clean and admitted that their advertised 10 MB Excel file limit in Office 365 is really only that if you don't want to do very much with the data in the file. If you're using, for example, a simple pivot table then MS Office will probably decide that you're exceeding the limit and produce the error message because the memory size required to process the activity will breach the 10 MB marker. Their solution? Either make do without the functionality you need or buy a client version of Excel to install on your desktop (in addition, of course, to the monthly fee you're already paying for the Web App version of Excel in Office 365....)
We too are getting the "Your session has exceeded its limits and has been closed" error with files much smaller than 10 MB, or even 3 MB.
The files will open in the Excel Web App, but when you try to sort the column of the table, or filter the pivot table or the table, using slicers or using the filters, then it crashes and gives that same error
I built some simple test files to see where it happened
Each file has a table with X rows (each is different) and 10 columns consisting or random data with random groupings
there are two with no pivot tables:
1) 3.6 MB - FAILS SORT TEST 40,000rows - has NO Pivots- - this consists only of a table of data - fails when you try to sort
2) 2.4 MB - PASSES SORT TEST 25,000rows has NO Pivots - only a table of data but you can sort it and filter it etc and it does not crash
and there are three which have pivot tables
1) 0.95 MB - PASSES ALL TESTS - 8,000rows - has pivots and passes both the sort the table test and the "clear the filters on the slicer and play around with the slicers" tests
2) 1.7 MB - PASSES MOSTLY - 15,000rows - this one passes all the tests except if I open it right after the next file has failed (#3) so there must be some sort of cache thing - quite strange. If I open it "fresh" it does not fail
3) 2.6 MB - FAILS - 25,000rows - this one fails both the SORT test and the PIVOT tests
The question is "why does this happen when the files are all much less than 10 MB"?
The files open up fine, but fail when you try to sort or use filters - it would seem to be something related to RAM usage or something like that. Is there an undocumented limit on that?
So how do we get around this? Can we pay for more "RAM" resources or whatever the limiting factor is?
I have tried assigning max storage and max resources to the site collection but this has no effect. I have tried it on 3 different browsers - all have the same problem. Tried it on different site collections - same problem.
Hello Peter and Sabre,
This is Justin with Microsoft Online SharePoint Support. Thanks for your questions.
This issue can be caused by both the file size and other factors of the Excel sheet. Per the Microsoft Office Web Apps Service Description, the max file size is 2 MB for SkyDrive, and 10 MB for SharePoint. This cannot be changed by the user or by Microsoft for SharePoint Online. I understand there is a discrepancy in that it may suggest that this can be changed in the service description, but it cannot. This is due to be changed in the future.
Since the Excel Web App is not a full desktop application, some of the features in spreadsheets may cause the files to not work. This may include lookup columns, macros, or anything special that might be embedded into the file. If you open the file in the client application, and remove some of these features, does the error still return? Please let us know.
Microsoft Office 365 SharePoint Support
Thanks for confirming that these problems are not related to the size of the files.
It turns out that there are numerous features one would expect to be able to use in a workbook that cannot be handled by Excel Web App. I've found a fuller description of those limitations here - pages.citebite.com/i1w5w8t7o8xns
It's extremely disappointing that Microsoft doesn't make explicit reference to these shortcomings when promoting Office 365. For our part we are now stuck with having to buy yet more Excel desktop licences to access quite basic spreadsheets.
I hope, as you say, that this is due to be changed in the future - the very near future would be good. I share Peter's frustration in that some very basic functionality of Excel crashes as the file gets larger than about 2 MB - We are on Office 365 Sharepoint online so the limit should be 10 MB.
For example, as I mention above, the SORT function, simply sorting a column, one of the most basic functions of any spreadsheet, does not work when the file size gets past about 4 MB. It will still open a larger file but when you try to sort it will give the "exceeded" message. Nowhere in any documentation does it speak of this basic function being restricted. And the PIVOT functionality works perfectly with small data sets, but crashes when the file gets above about 2 MB. So it isn't the functionality that is being restricted, but some aspect of memory or something similar which causes the misbehaviour.
I sent all the files above to your Microsoft support staff (Kayla) and she was able to replicate the errors and confirm what I wrote above.
The one interesting thing we found out is that all the files crash in "view mode" but are fine when you move to "edit mode".
In other words, when you double click on the file to open it you can interact with it but not enter data, this is called "view mode" apparently - and the files all crash in this mode. You get to "edit mode" by hitting the "edit in browser" button.
So why would that be?
Now this may be useful information for some (perhaps Peter - let us know if edit modes helps you?), but for me it is not useful, as I am trying to make these excel sheets look basically like an interactive web page, for my customers. It looks a lot slicker than having all of the excel stuff visible - and you can disable all of this with the settings.
It also brings up the point, if you can make it work in "edit mode" can you transfer those same limits to "view mode" please. At least that will help for starters, while you work on enabling the customer to expand these limits themselves.
Peter - I disagree - the problems ARE related to the size of the file. The functions I tested work perfectly with small files and do not work with "large" files. I thought that's what you noticed too ....
I agree with you that Microsoft needs to publish a very very detailed list of the limitations though. They have published some rather vague lists of differences, but not anything that was useful in identifying the problems we have been having.
I appreciate your concerns. As mentioned previously, the issue can be related to either the file size or certain functions. As a reminder, the Excel Web App is not a replacement for the desktop counterpart software, and as a result may include some limitations. Unfortunately as of right now, some of the features you desire may not be available in the Web App.
The development team is always working to improve the product, and perhaps in the future some of these functions may be available. To voice your opinions on this, please feel free to submit feedback at this link to them.
Sabre – well done on the discovery of different behaviour according to mode and, yes, I should have made my comment clearer that the file size is indeed relevant. I was just trying to draw the distinction from other postings where its suggested that file size is the only cause of the problem.
Justin - I can’t accept that what we are experiencing is merely a characteristic of Excel Web App. Surely it’s a good old fashioned fault if one file can execute a simple function which another can’t when both files are well within Office 365 limits?? I could perhaps understand it if BOTH failed but not if only one does.
If Microsoft are going to persist in the view that it’s behaving correctly can we now have a definitive list of exactly what limits and functions are involved please.
Hello again Peter,
For a more in depth overview of what the limits and functions are for the Excel Web App, please follow the article below for receiving answers to your question. This will be better suited to the product you are using. I will also include a help article that may have some valuable information.
I tested the "view mode" vs. "edit mode" thing further - and it doesn't solve the problem really, it just gives you slightly more headroom.
So for example, one of my files which crashes in "view mode" at 2.6 MB (25,000 rows), works in "edit mode" without crashing
However if I increase the size to 45,000 rows (4.5 MB) then it crashes in "edit mode" too.
Testing where this happens reveals that "edit mode" give you approximately 10% more headroom. So go figure. Still doesn't get anywhere near the quoted 10 MB. Also, when it fails, it fails harder - gives you the usual "exceeded" error and then another error:
"Either due to communication issues or a server problem, you need to be rejoined to your editing session. You may have lost changes that you made recently/"
This error doesn't crash gracefully either and keeps prompting you to hit ok infinitely until you go backpage or close the page.
So "edit mode" is no fix - not really all that useful.
And btw I concur with your lament. This product is still full of bugs.
Ok this is ridiculous. I just tested my pivot table test files on Micorosoft Skydrive and they work up to 5 MB. That's twice as big as works in Office 365. And they don't crash either - just fail to load above 5 MB, which is far less aggravating.
How is this a good idea, to make your paid flagship product, Office 365, less powerful than your free product, Skydrive. ????
Hi, I'm Microsoft - "Try our new Office 365! Just like our free Skydrive product, except 1/2 as powerful! And instead of that boring old "working as advertised" that we included with Skydrive, for your $25 per month we've added new surprise bugs and crashes!"
Sign me up! Oh wait - I'm already signed up. In that case - Microsoft - can you please make the Excel web app on Office 365 the same as the one you give out for free - or maybe, gasp, even better?
Peter - Your best bet here might to be to cancel your Office 365 subscription, and move to the free skydrive product. That is if you don't need the other features. At least you'd get files working up to 5 MB. Not sure if that would help you.
Justin - Could you find out why the two Excel webapps (seemingly the exact same visually) are different? And why Office 365 got the less powerful buggy one, or what limits are being placed on the Office 365 version (RAM?) that are not being placed on the skydrive version?