Free Microsoft Excel 2013 Quick Reference

Show blank instead of zero's in formula?

Totally New to excel i need help please.
with this i have attached a screen shot.

how do i make the Zeros not to show when there are nothing in the other column.

Thank you

Post your answer or comment

comments powered by Disqus
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.

I have a formula that is copied over multiple rows. Some of these rows don't have numbers so the formula shows #NUM. Instead of deleting the formula in those rows, is it possible to have the formula show nothing instead of #NUM? Thanks!


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:
> >
> >

How would I ammend the statement below so that if there was no infomation to
return the resulting text would show blank instead of #N/A?


How do I show blanks instead of #N/A when I do a vlookup.

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

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 folks,

my spreadsheet is sowing numbers instead of letters in the column headers. anyone know how to change this back?

also need a formula for the following:

I have a figure in C5, and need D5 to show (C5 minus 2.5 percent)

cheers people

Excel 2003

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

Sometimes, a cell is blank whose contents are being linked to in
another sheet in a workbook. When that is the case, instead of 0,
what can we put in the code to just show a blank as well in the second
sheet? Right now, if the cell is blank, the link cell shows a "0".
Thank you.

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.

Hi Forum,

I have an array formula that takes a value in cell C2, finds any matching values in the range $A$2:$A$30000 and then returns the latest date associated with these values (the dates are in the range $B$2:$B$30000). This works well.

The problem I have is that when a date is not found in the range $B$2:$B$30000 it returns 00-Jan-00. I think this is because of the zero at the end of my formula which returns zero and then my date formatting shows that as 00-Jan-00. I need it to just show an empty cell ("") instead of 00-Jan-00.

I have tried using "" instead of zero (see formula below) but it returns #Value instead of an empty cell.

I also want it to return an empty cell ("") if the value in C2 is not found in the range $A$2:$A$30000. Again, it
is presently returning 00-Jan-00 in these cases.

Please see attached example spreadsheet.

I have spent a lot of time experimenting with my formula but have not been able to get the result I need.

Any assistance would be greatly appreciated.


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


I have a template with data only in column B, with fields going down the rows. I'd like to copy B2, B4, B6...B24 to the next blank row (so the values need to be transposed to a row vs. column) in a Consolidated Workbook.

Here's what I have so far:

NextRow = Worksheets("Sheet2").Range("A1000").End(xlUp).Row + 1 
For a = 2 To 24 Step 2 
    Worksheets("sheet2").Cells(NextRow, a / 2) = Cells(a, 2) 
Next a 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Except that the formula above pastes the first value in column B of sheet2, any ideas on how to get it into column A? Worksheets("sheet2").Cells(NextRow, (a / 2)-1) = Cells(a, 2) gives an error.

Also, instead of sheet2 in the same workbook, I need to paste the data into sheet2 of a different workboook (i.e. Main, located in My Documents.)

Thank you.

Hello all,

when ltoj is less than zero the following code enters a period in the cell instead of zero.

 ltoj = 0 
Cells(r, 10) = Format(ltoj * 24, "##.##") 'writes data in correct format

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
anyone have any ideas?


I'm trying to create a summary worksheet to show an overview of our
departments annual leave. Each member of the dept has a separate sheet to
record their leave on and we need another one to summarise.
However, when I try and create links from the individual (source) sheets to
the summary (destination) sheet, I get "0" displayed in the cell rather than
what's in the cell (should be displaying a date)
All cells in all sheets are formatted as general.

My boss has an excel spreadsheet that she had been working on.

The in the coordinates row for the columns, it is showing numbers
instead of letters.

How can we get it back to letters?


I'm looking up values in a column using the VLOOKUP with the FALSE included
so that if the lookup item isn't present, I purposely get a "#N/A". Can I
nest an "IF" statement so that if the vlookup returns a "#N/A", I want it to
appear blank instead of getting the "#N/A"? Thanks.

I use the following code to get pivot table data source throught ODBC connect to CSV. However, when I chose hide item in "Page" fields, it showed "all" instead of "multiple items". Any expert can help?

Thanks you!

Option Explicit

'Set Reference to Microsoft ActiveX Data Objects 2.7 Library

Const sConnStrP1 = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
Const sConnStrP2 = ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Const sFilter = "CSV File, *.csv"

Sub CreatePivotTableFromCSV()

Dim sFileName As String
Dim sFilePath As String

sFileName = Application.GetOpenFilename(sFilter, 1, "Select File", , False)

sFilePath = Left(sFileName, InStrRev(sFileName, ""))
sFileName = Replace(sFileName, sFilePath, "")

TestCSV sFilePath, sFileName

End Sub

Sub TestCSV(ByVal sFilePath As String, ByVal sFileName As String)

Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset

Dim pcPivotCache As PivotCache
Dim ptPivotTable As PivotTable

Dim SQL As String

Set cConnection = New ADODB.Connection
cConnection.Open sConnStrP1 & sFilePath & sConnStrP2

SQL = "SELECT * FROM " & sFileName

Set rsRecordset = New ADODB.Recordset
Set rsRecordset = cConnection.Execute(SQL)
'For Excel 2003 Use
Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
'For Excel 2007 Use
'Set pcPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
Set pcPivotCache.Recordset = rsRecordset

Set ptPivotTable = pcPivotCache.CreatePivotTable(TableDestination:=Range("B5"))


Set rsRecordset = Nothing
Set cConnection = Nothing

End Sub

Well so much for being done, if any one can help with the code below, would appreciate.
I am getting the #div/0 error again but elseware on the below code.

=IF(H9="", "", IF(H9="0", "0%", H18/H9))

Is there somthing I can add in here so when h18/h9 = 0 to show 0% instead of #div/0?

When to use SUM(IF()) instead of COUNTBLANK() in Excel

1. Start Microsoft Excel and open a new worksheet. ... Unlike other functions such as SUM, COUNTBLANK will accept ... Microsoft Office Excel 2007 • Microsoft Office Excel 2003

When to use SUM(IF()) instead of COUNTBLANK() in Excel

Unlike other functions such as SUM, COUNTBLANK will accept only one range argument, so ... plus (+) sign indicates that the two IF expressions are to be combined in a logical ...

When to use SUM(IF()) instead of COUNTBLANK() in Excel

Unlike other functions such as SUM, COUNTBLANK will accept only one range argument, so ... 1. Open TestBook1 and in cell A6 enter SumIf . 2. In cell B6, enter =SUM(IF ...

is there a way i can have vlookup return nothing or blank if the vlookup reference is found, but is blank for the 2nd part and have vlookup return a blank instead of a 0?

A1: blah
B1: [blank]


A5 should be blank and not 0

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