Jul 20 2021 04:47 AM - edited Jul 20 2021 04:48 AM
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
Jul 20 2021 07:05 AM
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.
examble of One way, find more ways in the Link.
Hide or display all zero values on a worksheet
Click File > Options > Advanced.
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.
Nov 22 2021 06:21 AM
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.
Nov 22 2021 06:52 AM
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)
Nov 22 2021 09:19 AM
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)
Nov 22 2021 11:38 AM
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.
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
Jan 28 2022 11:18 AM
@NikolinoDE i have the same problem. file options returns to the same screen.
Jan 28 2022 11:21 AM
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.
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
I know I don't know anything (Socrates)
Jan 28 2022 11:31 AM
Jan 29 2022 02:52 PM
SolutionJan 29 2022 03:15 PM
@leobeeson thnk you so much! it worked - who knew!
thanks again
Feb 13 2022 02:33 PM
Apr 18 2022 11:20 AM
Jul 27 2022 12:56 PM
@Art_JB thanks! Amazing how useless Microsoft is at providing solutions to its own problems!
Sep 16 2022 07:18 AM
@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'
Oct 06 2022 03:46 AM
Nov 19 2022 09:57 AM
WORKED! THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!@leobeeson
Dec 23 2022 12:11 PM
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
Feb 14 2023 10:24 AM
@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.
Jan 29 2022 02:52 PM
Solution