Options won't open in files on Excel

[SummersFabricCrafts]

Hi,  I'm trying to hide zero values in excel spread sheet.

I've selected files>Options but when I do I get taken back to the same worksheet. Nothing is opening and I can't continue to advanced to hide the zero values.

Any advice please most welcome

Many Thanks in advance

[#2]

@SummersFabricCrafts 

You may have a personal preference to display zero values in a cell, or you may be using a spreadsheet that adheres to a set of format standards that requires you to hide zero values.

There are several ways to display or hide zero values.

Display or hide zero values

examble of One way, find more ways in the Link.

Hide or display all zero values on a worksheet

1. Click File > Options > Advanced.

2. Under Display options for this worksheet, select a worksheet, and then do one of the following:

   • To display zero (0) values in cells, check the Show a zero in cells that have zero value check box.

   • To display zero (0) values as blank cells, uncheck the Show a zero in cells that have zero value check box.

examble of second way:

Return a blank cell when the value is zero, you can use this formula.

=IF(A2-A3=0,””,A2-A3)

More ways you find in the Link: Display or hide zero values

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.

[#3]

@NikolinoDE 

I have the same problem, which your answer does not address.

the primary problem is that >file>options will not open, instead excel reverts to your document instead of the options window.

[#4]

@Paul1473paul 

Please include your Excel version, sample file, and a bit more context to help others answer your question.

Welcome to your Excel discussion space! 

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#5]

@NikolinoDE 

https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/722750

windows 10 21H1

Excel Version 2110 Build 16.0.14527.20270) 64-bit

Context: when using Excel the >file>options window will not appear when selected, the screen blinks then reverts to the excel document. This works as intended in Word (all part of the same Office suite)

[#6]

@Paul1473paul 

I can't tell you why the options don't appear for you either, despite researching, I couldn't find anything in the direction.

But to be on the safe side, do a Windows update to 21H2 beforehand, if the problem persists afterwards, perform an Office repair.

Repair an Office application

If the problem persists, please let us know, so that maybe someone else, who maybe knows a little more by then, can help.

Thank you for your patience and understanding

[#7]

@NikolinoDE i have the same problem. file options returns to the same screen. 

[#8]

@fsensoy 

With your permission, I recommend to provide more information.

You'll get a quicker and more accurate answer, and those who want to help don't have to invest time guessing the problem.

I recommend: Always inform about your Excel version, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.).

If possible, add a file (without sensitive data) and use this file to describe your project step by step, or add photos with the appropriate description.

Drag and drop here or browse files to attach

Maximum size: 71 MB • Maximum attachments allowed: 5

Don't forget that not every Excel user has a clue about every job and what you see he can't see.

In this link you will find some more information about it:

Welcome to your Excel discussion space!

Thank you for your understanding and patience

NikolinoDE

I know I don't know anything (Socrates)

[#9]

microsoft 365, windows 11 version 2112 build 14729.202060 automatic updates. storage hard drive and one drive. there is not much else to share. you go to file, options and no options come up, reverts back to the same sheet it was. i am attaching a video.

[#10]

it wont let me attch

[#11 · Accepted solution]

I had the same problem several people in this thread described. I managed to solve it the following way:
1) Close all open Excel files.
2) Go to `Start` and type `Excel` in the search box.
3) Click on the Excel icon to open the application.
4) When Excel opens and prompts you to select a file, don't open any file (nor create a new one).
5) Go to the bottom left and select `Options`.
6) The `Excel Options` windows will now pop up and you can modify the Excel options.
I hope this helps any other people facing the same issue in the future.

[#12]

@leobeeson thnk you so much! it worked - who knew! 

thanks again

[#13]

Thank you!! It worked! The old off-and-on-again is a staple for a reason! Thank you!!

[#14]

I too, was having a similar issue. Needed to get to File>Options>Formulas to turn on iterative calcs, but couldn't get to the Options window. Followed your directions and.....miraculously resolved this very frustrating issue. THANK YOU for your sage advice!

[#15]

@Art_JB  thanks!  Amazing how useless Microsoft is at providing solutions to its own problems!  

[#16]

@microsoftsucks66 It appears that he wants to help but he doesn't have enough time to actually understand the problem...so he probably has a few set responses that he pastes in... so much that he almost seems like a bot. Just sayin'

[#17]

This solved my problem you rock thank you

[#18]

WORKED! THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!@leobeeson 

[#19]

@leobeeson 

I am also having this issue, but with office 365 (using local install) it won't work either.... Selecting Options at the last step actually opens a blank spreadsheet... lol..

And yes, I am amazed by how unhelpful microsoft help is - - they can't even seem to grasp the problem let alone provide a solution.. but in their defence, it seems all the big tech players have the same challenges these days

[#20]

@leobeeson This worked for me except for one additional step. When I got to the last step and had the formula box open, the option to use iteration was gray-scaled out with a message that I could not change it because I didn't have an excel spreadsheet open.  So I went to my files menu and opened up the excel spreadsheet I wanted to apply this to (with this formula window still open) and then it allowed me to check the box to allow the iteration.