Excel 365 Sheet Views are not working

New Contributor

I have 7 different Sheet views in a shared document, they were working fine until about a month ago.

 

Since then I have tried everything, I deleted them, created them again and they don't work.

 

When I create them they look fine but once my collaborators enter to work on the spreadsheet all the views start showing the default view. The sheet has the black border and the eye in the tab.

 

How can I solve this

20 Replies

@PFonseca Hi, I was wondering if you figured out how to fix this? I am having the same problem. Thank you

@Rolke I haven't found a solution, the team is just using the default view. 

@PFonseca 

 

Some XLS files cannot be viewed in OWA

The problem can be observed as soon as the following constellation occurs (note the AND links):

    The old XLS file type (the problem does not exist with the new XLSX format).

        AND

    View> Break preview is activated in one of the data sheets.

        AND

    SharePoint or OWA is used for viewing.

A solution as far as I could research on the internet has not yet been found.

The possible workarounds can be seen below.

 

Workaround 1:

Convert existing files to the new XLSX format

To do this: Open the XLS file and then "Save as", file type "Excel workbook (* .xlsx)".

Save new files only in the new (XLSX) format (because only this can be edited in the browser via OWA).

 

Workaround 2:

In the existing XLS files, switch from "View"> "Break preview" to "Normal" e view.

Or toggle in the status bar of Excel

This has to be repeated in a file for all sheets that have activated the "Break preview" view.

Then save (and upload) the file.

 

Link: XLS files and Office Web Apps

https://social.technet.microsoft.com/Forums/azure/en-US/95c1e237-bcf0-4c15-ae2e-d96a019289df/xls-fil...

 

Please do not forget, I am a layman when it comes to Excel Web, all information without guarantee ... because I know that I know nothing
 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE I am having the same issue with these views not working after a day or two, even if I leave the file open, and I am using XLSX format. Filters usually disappear overnight. I leave the file open all the time and sometimes when I come back, the filters are simply not showing and the sheet views stop working. Even if I turn filters back on, all the sheet views no longer function. This is extremely annoying. I wonder if it has something to do with the fact that some people are opening the file through Teams. I have tried to break it by opening in Teams myself, but it doesn't seem to have an immediate effect. My guess is that a series of things together is causing this but I can't figure out what. Probably the solution would be to move this to a SharePoint list.   

@Dom_Correa 

IF EXCEL DOES NOT ALLOW FILTERS OR SORTING

OFFICE

 

The great strength of Excel is the management of an almost infinite amount of data. When you work with a table, it becomes larger and more complex over time. When sorting or filtering is no longer possible, you often face a major problem. Not being able to bring the cells into the required order makes your work unnecessarily difficult. It's good that there are quick solutions!

 

In such a case, the Sort and Filter button in Excel is grayed out and cannot be selected when certain cells are selected. The way via right mouse button on the cells to be sorted> Sort does not work either. The cause here is usually the application of a table template to some cells, which is more or less confusing to Excel.

 

The first solution is to remove the table template: highlight the affected cells and remove any formatting, coloring and backgrounds. This allows you to analyze step-by-step what exactly is responsible for the error, but it is also time-consuming.

 

The faster way: Select the complete table by clicking on the small triangle between the row and column names. Then right-click in the selection and click Copy. Then create a new, empty worksheet and click in the first cell.

 

By pressing the right mouse button and inserting content, you now get a variety of possible insert types. Select values ​​and source formatting here. The table looks like before, only suddenly the filter and sort button can be used again!

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE thanks for the response. I am able to get filters working again, the issue is the custom sheet views (View Tab >> Sheet View area) stop working. The table filters do get turned off as well, but clicking filter turns that back on. Even so, while the custom sheet views are present, they do nothing. This spreadsheet is reviewed with many people on a call each day and it would be nice to be able to simple click on a sheet view to get the table in order for the review. Instead I have to go into Sort & Filter >> Custom sort, filter a column here and there, etc. I was excited when I saw you could save a sheet view, but after some period of time it simply stops working. Frustrating. 

@Dom_Correa 

Sheet Views in Excel

(Please click on the heading for detailed information.)

I am using sheet views. That's what is breaking. They work for a while, then, typically overnight when the spreadsheet is left open, filtering gets turned off, and none of the sheet views work, even after turning filtering back on. This is the issue I am facing.

@NikolinoDE Hi, I know you are trying to help here but please be mindful of the thread's topic before replying. The issue the OP was asking about is "Excel 365 Sheet Views are not working" and your reply is out of context of the original issue.  

@nagaden 

Of course, it is clear to me that Excel is constantly evolving.

 

I also realize that I am not the best person to give a 100% answer (I pointed this out at the beginning).

I also realize that it took this user, months to get a response.

 It is also clear to me that if I give an answer in an area that I have very little idea, I have to be very careful with my wording and pass on the information as far as possible from links.

 

My principle, everyone has the right to an answer, even if it is not 100%.

Better a vague answer than none at all.

My principle, rather to show a community member that you are here, even if you are not 100% the right one, than that the user feels left alone in the vast universe of Microsoft.

My principle is that what you feel is not a fact, but if you ignore it, it becomes a fact.

 

To end, if you ask me or ask me not to answer something that is not 1000% correct, then I can only answer ... NO, I will answer even if im not 100% sure.

 

At the same time you would have to delete 80% of the messages because they are not 100% correct.

I haven't seen in any chat forum until now that the Pareto principle has been applied.

 

Anyway that's just my humble opinion, but who am I that I have the infallible and am not wrong?

 

 

Thanks for your time and understanding.

Nikolino

@PFonseca I know it's an old post but I was struggling with the same case. Out of the blue, I thought, what if I create at least one Sheet View opening the EXCEL file via EXCEL WEB APP. This did the trick. I created one Custom Sheet View online. Then I tested going back and forth from the DEFAULT to the CUSTOM view. Everything was working like a charm. Then I created from my EXCEL Desktop APP a 2nd CUSTOM view and start going back and forth from the DEFAULT to CUSTOM1 and then CUSTOM2 and then randomly from one to the other. The response was as should.

Hope that helps.

I have a very similar issue. Excel workbook with queries and pivot tables and slicers to create customized reports uses could view and edit in their individual sheet view. This spreadsheet worked during the fall of 2020 but the identical spreadsheet now constantly refreshes the sheet view based on the last user's filter modification. Basically all users have to work with the same (default) sheet view, instead of their personalized sheet view. This eliminates to possibility of multiple users using the spreadsheet independently.

@NikolinoDE your answer was to copy and paste from the Microsoft support page

 

The frustration isn't because your answer is right or wrong, its because what you posted is literally useless. The person who asked the question and anyone coming here with the same issue hoping to find an answer obviously already knows what Sheet Views in Excel is and would've already read that page.

@markbolvin 

What I have posted is nothing different from what Microsoft has on its pages. If that is useless, then my link and infos are also useless (as you saw it - which I did not hide – it is exactly the same information).

If you didn't like it, please ignore it, comments of this kind do not help our mutual help here in the forum.

I can understand that the frustration can be very high when a problem arises that usually appears at the worst time. Nevertheless, I can't and don't want to understand why a person can be rude and take the help offered to him (even if it may be so "useless" for him) as annoying.

I am not a Microsoft employee and have no direct or indirect business dealings with Microsoft. I don't want to distinguish myself in any way or benefit. Am a simple user, nothing else.

I try to give my help here in the forum (as far as this is possible and does not add any harm) because I have also received help here in this wonderful forum and also get it whenever I need it in Excel.

In this forum, a user gets help from other users in advance. If a user thinks he needs professional help, he can also contact Microsoft Support directly. With the appropriate account and investment, of course.

Here you have received a link from me that maybe can shows for the user the way to this info or that this might also help.

As I said at my previous posts "Please do not forget, I am a layman when it comes to Excel Web, all information without guarantee ... because I know that I know nothing".

 

If you find my comments annoying, please do not pay attention to them and if you want to, do not pay attention to me either.

I will do the same by trying do not pay attention any future questions from you.

 

Life is short, but we always have time for politeness, because politeness is a virtue ... a virtue where we don't need talent, just politeness.

 

It is a shame that these days are not used to see "in us" before we look around and above us.

 

I also wish you reflective days with health and joy.

 

 

Thank you for your patience and time.

 

NikolinoDE

I know I don't know anything (Socrates)

This is a buggy, half-baked feature released by MS - ridiculous. If you set up a sheet view and then exit it (go to default view), Excel still thinks there is a filter enabled. This prevents the user from moving around columns or doing anything that is prohibited in filtered views. There is no way to clear the filter from the "Sort & Filter" menu - "Clear" shows up as an option, but when the user clicks it, it does not clear the filter. Bottom line, Excel thinks there is a filter set when there is nothing being filtered. The only workaround is to exit and open Excel again. Crazy this is such a major bug so long after release! Apparently MS does not care to address this.

@nirad1595 

If I may recommend, report your concern/problem in a description below these pages.

Think this will help you and maybe others more if this is a common topic.

How do I give feedback on Microsoft Office?

Contact Office Support

 

I know that it is annoying when you have a problem and instead of an answer you get more questions. But please consider the possibilities in Excel are very diverse, as are the possibilities of the causes.

 

I hope that your issue will be resolved quickly and that you will have a lot of fun with Excel.

 

Thanks for your comments, they might help others as well.

 

NikolinoDE

I know I don't know anything (Socrates)

same issue for me
same issue for me.... did you solve it?
Same issue her for me - Excel views are simply not working any more. Very frustrating. The old style views were fine, but now they have "modernised them they seem to have broken them