Professor Excel

Comments 20

  1. Stuart

    Hi Henri can you help me with what is I’m sure a simple problem.
    I have text in column A which is one of 4 options. In a related row I have times. I want a function to find the longest time in each text option. An example would be:

    A 01:30
    B O2:20
    A. 02:05
    C. 01:31


    Can you help?


  2. Dom

    Options 2 and 3 are seriously ugly solutions. By far the most elegant approach is to concatenate a null string to the lookup result, thus forcing the result to a string:


    • ExcelUser

      This is the best and most elegant answer I’ve ever seen to this annoying and ongoing problem.
      Kudos to you sir!

    • Henrik Schiffner

      Thanks, Dom, that’s a great solution. I’ve added it as #4.

  3. Ali

    Option 3: Show zeroes but don’t show empty return values
    sir this formula not working,
    show zeros but a same time show the empty return values. please fix this problem

  4. Topher

    How is Option 3 any different from just using the original formula?

    • Henrik Schiffner

      If the value in your original formula is blank, the original formula would (without the if-formula according to number 3) return 0. Using option 3 changes it to blank again.
      You can easily try it by just using a cell reference, for example writing =B1 in cell A1. If you leave B1 blank, A1 would show 0. Using the option 3 would show a blank cell A1.

  5. jay klein

    I have a sumif formula applied to a column that has blanks in it, and in some cases may have ALL blanks. I need to know that when I see a zero it is a ZERO, not a blank being displayed as a zero. I have tried everything.

  6. mll

    @Dom: your trick is great, as long as we only need to prevent zeroes to display.

    but if I need to do for example

    cases where VLOOKUP(A3,C:D,2,FALSE) = “” will display TRUE instead of the desired blank value

    If we do
    =””&VLOOKUP(A3,C:D,2,FALSE)<1, it will always display FALSE whatever the case.

    So finally we have no choice but to revert to the – ugly, I concur – suggestion of the article, which is

  7. Erik

    Wouldn´t ORIGINAL FORMULA&”” work as well?
    That displays the cell as empty if the value is 0

  8. Hi, I am using a simple if statement =IF(G2="L",1, ) and I dont want 0 as a false value. I am fine to get a blank cell when False. Please help.

    Hi, I am using a simple if statement =IF(G2=”L”,1, ) and I dont want 0 as a false value. I am fine to get a blank cell when False. Please help.

  9. Vester

    Option 2 worked for me. It’s so simple and useful. Thank you so much!!

  10. Rishie

    Hi Henrik!

    I have been using something like this: =If( [condition] , [result if true] , “”)

    This formula gives me blanks in case the condition is false and works good for me as it ‘shows’ cells as blanks. But in reality it has some special character which gets copied when I need to. How can I get rid of these special characters in the cell as I will need to keep it like this. Can we use any other option to make it ‘actual’ blank?


  11. Damir

    I have the same problem.
    When the “” result cell value is copied to another workbook as value it is not empty.
    The cell shows as populated and messes up calculations, for example the average of that column is #Value!
    It would be better to use some letter instead of “” as it would not interfere down the line.

  12. Alberto

    Hi Henrik,
    I’m building a chart and I don’t want empty cells to be plotted.

    Using the options for Hidden and Empty Cells in the “Select Data” chart functionality it works .. but only if the cell doesn’t contain a formula.

    I tried also using the methods you advise, but anyway the excel charting process plots a “zero” value, because (I guess) it actually finds a formula in the cell (thus the cell is not “empty” or “blank”)

    Do you have any tips or workarounds for this issue?


    • Henrik Schiffner

      Hi Alberto,
      The trick is to return the error value #N/A. You can you this like this =IF(B5=0,NA(),B5)
      Does that help?
      Best regards,

  13. beenarani

    =if(if(N5>J5,N5-J5,T5+0)=0,””,”=if(N5>J5,N5-J5,T5+0)”) this is formula i used, but when the result is not 0 then hpow to get the Correct result here?

    • Henrik Schiffner

      Hi beenarani,
      Have you tried removing the last two quotation marks and the equal sign?

Leave a comment

%d bloggers like this: