Free Microsoft Excel 2013 Quick Reference

formula to make cell blank instead of zero

I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.

This is the formula I have at the moment:
=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")

1 0.18 1 0.82

Post your answer or comment

comments powered by Disqus
Hi, I have the same problem with blanks and ceros in cells. I want to make
calculations from blank cells then I used =If (B2="","",B2-1), the problem is
that when I make a graphic with my new values, cero appears in the graphic.

"Frank Kabel" wrote:

> Hi
> =IF(B1="","",B1)
> "Db1712" wrote:
> >
> > I have two cells linked, is there a formatting to return a blank
> > instead of zero when the cell its linked to is empty
> >
> >
> > --
> > Db1712
> > ------------------------------------------------------------------------
> > Db1712's Profile:
> > View this thread:
> >
> >

Hello all,
I have 5 sheets. I have used the following formula to show Blank instead of Zero in Sheet2.
Where, in Sheet1, A5 is a value entered and A7=A5+A6

But with this formula I get the value '0.00' in Cell B3 in Sheet2.

Could you help me as to how to get Blank cell instead of Zero in Sheet2 in Cell B3

Thank you all for your kind help.

Excel 2003

How can I modify the following formula to show a blank instead of #NA when no match is found?

I want to pull info from my master over to other sheets, if nothing is in the cell I want it to return a blank cell instead of zero so this is the formula I am using. The only problem is my formulas start row 12, rows 15-28 either won't pull info over or if blank it puts in a zero. 29-560 works fine then in row 561-596 nothing, zeros, or #NA.

=IF(LEN(VLOOKUP($A12,'FD RUN LIST - MASTER'!$A$12:$N$596,$B$17))=0,"",(VLOOKUP($A12,'FD RUN LIST - MASTER'!$A$12:$N$596,$B$17,FALSE)))

Is there another formula I should use so I get a blank instead of zero?

Hi there

I have the following macro that make cells blank if for example AK3>=AL3.

    Dim myRange As Range 
    Set myRange = Range("AK3:AL3" & Range("AL" & Rows.Count).End(xlUp).Row) 
    For i = 1 To myRange.Rows.Count 
        If myRange(i, 1) >= myRange(i, 2) Then 
            myRange(i, 1) = "" 
            myRange(i, 2) = "" 
        End If 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I change my macro to shift the next cells up if the cells were made blank? I have another problem in that my macro runs forever. How do I get it to stop? The macro can stop once it finds the first blank cell after a cell with data?

I have an excel question.
In the attached file, I have a formula to return the abs value of H8-G8.
I would like to not see the 0 when there is no data in H8 or G8.

Then I have a formula in K7 that I believe I have set up correctly?
I want it to show the Green check mark when the value of I7 is < 0.135,
and a Red X when the value of I7 is > 0.135, but I want it to show nothing
when there is not a value in cell I7.

Please help!

I have the following sheet:
1 0 8 16
2 0 9 16
3 0 9 16
4 0 9 16
5 0 8 17
6 0 8 17
7 0 8 17
8 0 7 18
9 0 7 18
10 0 7 18
11 0 7 18
12 0 8 17
13 0 8 17
14 0 8 17
15 0 8 17
16 0 7 18
17 0 7 18
18 0 7 18
19 0 7 18
20 0 7 18
21 0 8 18
22 0 10 17
23 0 10 17
24 0 10 17
25 0 10 17
26 0 8 18
27 0 7 19
28 0 7 19
29 0 8 19
30 0 0 0
31 0 0 0
AVG. #DIV/0! 8.00 17.45

My formula is: =(SUM(B3:B33)/COUNTIF(B3:B33,">0")) How can I change the formula to show 0.00 instead of #DIV/)!? I must use the countif function because each month is a different number of days, the workbook doesn't belong to me so I can't shorten the rows.

hai folks,

I am using Macros to "delete a column and insert a new column by copying the same formulas(sum) and number formats".While doing this, the formula is calculating the sum of blank cells and returning zero(which i do not want to display).if i use the spaces instead of zero to display,it is erasing the formula.can u plz help me out with this.

When i used tools->options->view: unchecked the zeros values ...i cannot really enter a zero value for summing,as well the previous cell which contain zero is also removed.

I have a setup that (simplified slightly) looks like:

A1: 20
B1: =if(A1Options
checked, so that blank values are not plotted as zeros.) However, it is
graphing my "not quite blank" cells, generated by the above formula, as
zeros. It does skip truly blank values (if I delete the formula altogether),
but doesn't when I try to force blank values using a formula like this.

Any ideas how to get a formula to make a cell think it is blank?

Thank you,


I have a setup that (simplified slightly) looks like:

A1: 20
B1: =if(A1<50,"",A1)
C1: =isblank(B1)

C1: produces "false". Why? Isn't "" supposed to put "nothing" into a cell?

The point of all this: I want the cell to be truly blank, so that it doesn't
graph as a "zero" in a chart. (Yes, I have the option under Tools>Options
checked, so that blank values are not plotted as zeros.) However, it is
graphing my "not quite blank" cells, generated by the above formula, as
zeros. It does skip truly blank values (if I delete the formula altogether),
but doesn't when I try to force blank values using a formula like this.

Any ideas how to get a formula to make a cell think it is blank?

Thank you,


What I want to do is be able to make a formula that references a blank cell, that returns a blank cell.

So I have =A1. If A1 is blank that returns a 0. I want it to return an blank. Is there a way to do that?

I want to make a graph where I have numbers and cells that are blank. Right now I have numbers and zeros so when my data stops the graphs jumps down to zero and stay there.

Hope that's clear, thanks!

Firstly thank you for the help I have received in the past, much appreciated. My issue concerns a "league table" for my companies departmental shifts, I have already been helped in returning zero's in the top two tables but as you see in the last table, where it is using an offset formula to place the departments in "order" it is placing the top department in all the positions and not a blank as I would like. I am still very much a beginner and I have tried all sorts of IF statements but I am just so wide of the mark.
This is what the table looks like showing weeks 1 to 4 set at zero's. Normally once the year has gone under way it will be populated with positive values.

NPE	0	0	0	0
NPM	0	0	0	0
NPN	0	0	0	0
PE	0	0	0	0
PM	0	0	0	0
PN	0	0	0	0
PRODM	0	0	0	0
PRODN	0	0	0	0
Immediatelly below it is a table that will show the departments in their respective positions for each week of the period.
This is the code that lies in each cell within the second column and reads from the table above;

But as you see it places the top department in all of the cells but ideally I would like it blank. I do have an annual table
at the end of the book which reads from 13 periodical sheets but as you can imagine it is full of NPE! Is there a way out of
my dilemma?

I have attached an example sheet.

Many thanks.


I've made a ranking formula that actually works, but I need to have the formula to make blank values to 0 (zero). I have the totals on J5:J33 and the ranking value on K5:K33. Any assistance will be appreciated. thanks. Here is my formula:

=IF((SUM(IF( $J$5:$J$33=J5,1))) =1,(SUM(IF( $J$5:$J$33>=J5,1,0))),( SUM(IF($J$5:$J$33 >=J5,1)))-(( SUM(IF( $J$5:$J$33=J5,1))) -1)*0.5)

I have an accounting worksheet set up with eight columns. I need a way to program simple code that makes the cellpointer move to the first blank row of the table so I can start entering data.

For this macro to work I'm using relative references and before I activate the macro I place my cell in the first row of the first column. This way when I activate the macro the code reads that the row is filled and simply moves down to the first blank row.

Thanks in advance.

I've googled this and i'm stumped. In short, I have a worksheet to display information from another worksheet using a drop menu. The following function is returning a 0 (or blank after tools->view zero cell blank checked), instead of the TRUE argument.


The cell in question is blank, nothing in there. However, if I type something then that will appear.

Why can't I get it to say "N/A" instead of returning a blank, but it WILL show if something is there?

Thanks for any and all help! Let me know if clarification is needed.

Score card managers working rev2.xlsI am currently trying to solve the issue in column G of Manager Tab. Formula currently gives store number is criteria of C or V is met and Employee i.d. matches in shrink tab. If employee i.d. does not appear at all in the Shrink tab the formula returns ID Not in list. If the employee i.d. does appear in the Shrink tab but the audit "type" criteria of C or V is not met it is returning a 0 (zero). I need to change the formula to return the cell empty instead of zero. Any ideas?

=IF(ISNA(VLOOKUP(E2,Shrink!$A:$K,11,0)),"ID Not in list",LOOKUP(E2,CHOOSE({1,2},0,LOOKUP(2,1/((Shrink!$A$2:$A$1000=MANAGERS!E2)*(ISNUMBER(MATCH(Shrink!$K$2:$K$1000,{"C","V"},0)))),Shrink!$G$2:$G$1000))))

I have a spreadsheet I am using for financial statements. In one area, I have
to create sub-totals based on the rows above.

The problem is that some of the cells are set up as named ranges for use
elsewhere, and some are not. As a result, the formulas for my sub-totals
contain both names and cell references. When I try to copy these formulas to
adjacent cells for other months, some formula components change (eg. C35
becomes D35) while others don't (eg. Jan_Sales stays as Jan_Sales).

This makes a complicated edit, overly prone to errors. It is also not
something I am comfortable turning over to someone else to use on an ongoing

I could create names for the un-named cells, but this would result in a HUGE
number of names to set up. Is there any way to use the cell reference for the
named cells in the formulas I am creating?

i need a formula to count cells of collum D, that aren't blank
sorry i'm a beginner


pmarques's Profile:
View this thread:

In T2 I have this formula:

=IF((N20,VLOOKUP(A2'SHEET1'!$A$2:$C$288,3,FALSE)*1 0,"")

Once I enter a number in A2, all formulas work fine and I get the desired
result. However, when A2 is blank, #VALUE is displayed in T2 because of
formula in U2. I need T2 to be blank until a number is entered in A2. I'm
sure it is a fairly easy fix but I can't work it out. Can someone help with
this? Thanks!

Hello Biff from Steved

Thankyou and yes I've taken on board your comment about large Worksheet
but in this case it is a small one


"Biff" wrote:

> Hi!
> Sometimes it's more efficient to use conditional formatting to "hide"
> unwanted returns!
> Instead of using an array SUM(IF, use this:
> =IF(SUMPRODUCT(--('From Charters'!$A$1:$A$900="Roskill"),--('From
> Charters'!$B$1:$B$900="Period.1"),'From
> Charters'!$D$1:$D$900)=0,"",SUMPRODUCT(--('From
> Charters'!$A$1:$A$900="Roskill"),--('From
> Charters'!$B$1:$B$900="Period.1"),'From Charters'!$D$1:$D$900))
> If your wb is large with lots of these types of formulas consider this:
> This formula takes twice as long to calculate and uses relatively large
> arrays.
> Biff
> "Steved" <> wrote in message
> > Hello from Steved
> >
> > If no Data the cell is returning a 0 value I need it in this case to be
> > blank please.
> >
> > SUM(IF('From Charters'!$A$1:$A$900="Roskill",IF('From
> > Charters'!$B$1:$B$900="Period.1",'From Charters'!$D$1:$D$900,0),0))
> >
> > Thankyou.

i need a formula to count cells of collum D, that aren't blank
sorry i'm a beginner


I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted.

I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank.

What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added.

Hopefully that is not too confusing.

In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].

I am new to Excel VBA (Usually Access) so any help would be appreciated.

Thanks. Jonathan

Shortened for example;
I have created a drop down list with gas, groceries and travel as the categories.
column a is labeled expenses and formatted as currency.
column b is labeled category with the drop down list in each cell.

column d contains each item, gas, groceries and travel on separate rows.
column e will contain the total for each item in column d using "sumifs formula".

If there are no travel expenses, instead of returning $0.00 I want the cell to be blank.
I know how to do this in a simple sum function but sumifs are sumthin else.
I have tried using <> for not equal to and everything else I can think of and I usually get a VALUE error.
how do I get it to be blank instead of $0.00???

Windows XP and Excel 2007

Dear Excel experts

12345 -> 0000012345
12 -> 0000000012
432567 -> 0000432567
9 -> 0000000009
4444444444 > 4444444444

The 1st column shows the original figures. I wish to add "0" to the beginning of the cell to make the total number of digit becomes 10, what would be the formula? Please advise.

Thank you in advance.


No luck finding an answer? You could always try Google.