Have Excel formulas that return 0 make the result blank

Question

A recurring Excel problem I have is formulas such as INDEX array row column  that return 0 when there s no result  rather than returning blank   What is the best way to change the zero result to blank   Here are approaches that I have tried so far   1  Using division by zero  If INDEX returns 0  I cause an error that I then filter out    IFERROR 1 1 INDEX A B C        CONS  Makes the formula more messy and hides errors you may want to see   2  Using custom formatting  0 -0      CONS   1  can t simultaneously apply date format 2  It doesn t work with conditional formatting when it comes to checking for blank cells  there is still the value of zero  it s just not shown   3  UsingIF statements   IF  1 1 INDEX A B C   lt  gt     1 1 INDEX A B C         CONS  Messy repetition  Does anyone have any other better ideas

User · Answer

I figured it out  by concatenating an EMPTY string    INDEX tt Attributes MATCH  RowID  tt Attributes RowID  0  COLUMN tt Attributes Long Description      amp

User · Answer

I noticed this issue recently and it is frustrating that excel changes a blank string into a 0  I do not think that a formula should solve this issue because adding more logic to a complex formula may be cumbersome and might even end up breaking the original formula  I have two non formula options below  If you want to keep the formulas in the cells and have them return 0 instead of  quot  quot  use the following Click Path   File Options Advanced  Scroll to  quot Display options for this worksheet  quot   Deselect  quot Show a zero in cells that have zero value quot   I also want to give a simple manual solution if you want to change a value  as opposed to a formula  from 0 to a blank string  This solution is better than Find and Replace because it will not replace a number like 101 with 11   Select all of your data On the data tab  click the filter logo    Click on the pick list of the column with the undesired 0 s  to select only rows with the value  quot 0 quot   Make sure to only clear the contents of cells containing contents with the exact value 0      Select the data  which is only 0s   right click and select clear contents  The other data will remain if you used the filter properly and the back button is always there if something goes wrong  I understand option 2 is very manual and  quot unelegant quot  but it does successfully convert a 0 into a blank string and it may relieve a frustrated individual who does not want to use an if statement  I personally learned something exploring this  very dry  excel issue today and I am personally using these methods moving forward  A quick macro of option 2 could be a good option if this is a frequent task for an intermediate excel user

User · Answer

You can create your own user defined functions in a module within Excel such as  from memory  so may need some debugging  and the syntax may vary among Excel versions as well    Public Function ZeroToBlank  x As Integer  As String     If x   0 then         ZeroToBlank          Else         ZeroToBlank   CStr x      End If End Function   You can then simply insert  ZeroToBlank  Index  a b c   into your cell   There s a nice tutorial on just this subject here   The basic steps are    Open the VB editor within Excel by using Tools - gt  Macro - gt  Visual Basic Editor  Create a new module with Insert - gt  Module  Enter the above function into that module  In the cells where you want to call that function  enter the formula nbsp  nbsp  nbsp  nbsp  nbsp  ZeroToBlank   lt  lt whatever gt  gt  where  lt  lt whatever gt  gt  is the value you wish to use blank for if it s zero  Note that this function returns a string so  if you want it to look like a number  you may want to right justify the cells    Note that there may be minor variations depending on which version of Excel you have  My version of Excel is 2002 which admittedly is pretty old  but it still does everything I need of it

User · Answer

The question may be why would you want it to act different from how it does right now  Apart from writing your own enveloping function or an alternative function in VBA  which will probably cause calculation speed reduction in large files  there might not be a single solution to your different problems   Any follow up formula s would most probably fail over a blank thus cause an error that you would capture with IFERROR   or prevent by IF sourcecell lt  gt          if you would use the latter then testing for a zero is just the same amount of work and clutter  Checking for blank cells becomes checking for 0 valued cells   if this doenst work for you please explain more specific what the problem is    For esthetic purposes the custom formatting solution would be just fine   For charts there might be an issue  which would be solved by applying it in the original formula indeed

User · Answer

None of the above worked for me today  so I tried putting the 0 in quotes  as shown in the example below   Example   IF INDEX a b c   0      INDEX a b c

User · Answer

Perhaps the easiest way is to add the text formatting condition to the formula  with a   modifier  Thus    formula to grab values  becomes   text  formula to grab values        Hope that helps

User · Answer

if b2           b2    This worked for me

User · Answer

The normal way would be the IF statement  though simpler than your example    IF INDEX a b c  INDEX a b c        No need to do gyrations with the formula  since zero values trigger the false condition

User · Answer

Just append an empty string to the end   It forces Excel to see it for what it is   For example    IFERROR 1 1 INDEX A B C   amp

User · Answer

There is a very simple answer to this messy problem--the SUBSTITUTE function   In your example above    IF  1 1 INDEX A B C   lt  gt     1 1 INDEX A B C         Can be rewritten as follows    SUBSTITUTE  1 1 INDEX A B C

User · Answer

If you   re willing to cause all zeroes in the worksheet to disappear  go into    Excel Options        Advanced    page     Display options for this worksheet    section  and clear the    Show a zero in cells that have a zero value    checkbox     This is the navigation for Excel 2007  YMMV    Regarding your answer  2   you can save a couple of keystrokes by typing 0 -0         as far as I can tell  that   s equivalent to 0 -0       Conversely  if you want to be a little more general  you can use the format General -General     No  that doesn   t automagically handle dates  but  as Barry points out  if you   re expecting a date value  you can use a format like d-mmm-yyyy

User · Answer

I am not sure it works with all type of data but the only solution I found for the moment is to test if index returns blank    IF ISBLANK INDEX a b c      INDEX a b c     The formula   IF INDEX a b c  INDEX a b c        does not work with text

User · Answer

IF INDEX a b c   0      INDEX a b c   worked for me with a minor modification  Excluding the 0 and no spaces in between quotations   IF INDEX a b c         INDEX a b c

User · Answer

Use conditional formatting  Home tab  styles section  and apply highlight cells rule  putting 0 in the Format cells that are equal to box  but select custom format then the Number tab   Select Category custom and in the type box put   0 -0     Sounds complicated but is actually simple   This gives the advantage that the cell looks empty but 0 is still the underlying value  so any formulas you use against that cell selection will still see it as being numeric and saves on lots of messing around with chained IF statements

[excel] Have Excel formulas that return 0, make the result blank

Examples related to excel

Examples related to worksheet-function