Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

How To Make Cell Blank If #n/a

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi All,

I have a cells with a formula that references a table, =HLOOKUP($D$6,Benefits,2,FALSE)

Not all cells are used all the time and appear with #N/A. I want to maintain the formula, but if it returns #N/A I want cell to appear blank. If I can do this with conditional formatting that would be great. Then I won't have to change each cell's formula.

Thanks so much for your help.

View Answers     

Similar Excel Tutorials

Find the Next Blank Row with VBA Macros in Excel
Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This method will skip any blanks ...
Delete All Empty Rows or Blank Cells from a Range in Excel
How to quickly delete all empty cells or rows from a range in Excel.  This allows you to quickly clean your data to ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
Install a Macro into an Excel Spreadsheet
This tip will show you how to copy an Excel Macro into your workbook or spreadsheet. You will learn the different l ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics

Hi guys,

I have a workbook (A) with cells calculated with this formula: =IFERROR(C13/D13-1;""). As you can see, if this calculation returns an error, then it displays a blank value.

Then, on another workbook (B), I simply "pick-up" these results to summarize them with this formula: ='[A.xlsx]Sheet1'!$E$15 and this cell is formatted with percentage style.

After that I created a conditional formatting rule that will fill this cell red, if the cell value is less than 0, green if it's greater than 0. I also created this rule to apply no filling colour in case the cell is blank: =""

All works fine as long as there are values. The problem is that when the calculation formula returns an error (blank on workbook A), the conditional formatting returns green even without any number being returned.

I browsed through the forum but everyone recommends using a conditional formatting based on a formula like = "" but that's what I'm doing and it just won't work.

Appreciate your help.

-I have two columns of numbers.
-There are blank cells in each column that appear sporadically.
-In each row of numbers, I want to create a conditional format that highlights a cell if the number in the cell is greater than a designated value.
-Currently I am using: Conditional formatting 'formula' =ABS(C4-C3)>.1 OR =ABS(D4-D3)<.1 for the respective column of numbers.
-This formula does not work now that I have empty cells between the values. All the blank cells are highlighted and the subtraction can not take place if the cell is blank.
-I want the formula to not see the blank cells and make the comparison between the first vale and the next vale in the series.
-I do not want to sort the blank rows out and then replace.
Thank you,

Please see attachment: TESTAE042701.csv

Hi everyone -

My project today has been figuring this formula out and... it's not going well :P

What I need is to use conditional formatting in cell A2 to accomplish the following:

"If for the range C2:H2 any cells are NOT blank, check cells C$1:H$1. If ANY of those corresponding cells in row 1 are blank, highlight cell A2 red."

I'm going to apply this formula in Column A for any customers added to the list (hence the $ in the C$1:H$1 reference). So cell A2 would NOT be highlighted because all quantities in cells C2:H2 have a corresponding lot number above. Cell A3 WOULD be highlighted because cell D3 does not have a corresponding number in cell D1. Does that make sense?

Also posted on

I seem to have a problem with a formula not recognising a blank cell.

In cell X4, I have the following formula:

=IF(L4="",0,IF(I4="S",0,'Employee Master'!AB5*M4*L4))*IF(N4=9999,1,N4)

In L4, the formula is:
=IF(A4="","",IF(F4="OVERTIME",1,IF($J4="C",VLOOKUP($K4,Casual_Days_Lookup,HLOOKUP($F$1,Casual_Days_Lookup,2,0),0),VLOOKUP($K4,Permanent_Days_Lookup,HLOOKUP($F$1,P ermanent_Days_Lookup,2,0),0))))

Nasty, I know....

These formulae work great as long as the result of the L4 cell is not equal to "". As soon as that happens, X4 returns the #VALUE error.

To test if there was a problem, I put the following formula in cell X5:
which returns "EMPTY" if the result of L4 is "".

Is someone able to explain to me why I'm getting a #VALUE error in cell X4 instead of "0" when L4 returns "", but an "EMPTY" value when testing for the ""???

I keep looking at it and looking at it....I'm sure it's something simple, but I just can't see it!!!

I'm using Excel 2003.


I am trying to make a conditional formatting formula that applies formatting based on whether the column A cell has a value.
Basically, if the conditional formatting is applied to cells G3:L3 and A3 is blank (or value is blank/0) then apply formatting.

I have attached an example (that doesn't work this way)

How can I change the formula to do this?


Coud anyone help how I can get around using =ISBLANK to return true blank cell result when a cell contains a formula?

For example I would like to use conditional formatting to colour cell RED if B1 does not match A1. If I use ISBLANK this still returns the same false result (ie colours the cell RED) on cells which contain no match but contain a formula what has resulting in a blank expresson (ie "") which returns a blank cell.


I have written a vlookup formula in which it is looking up a specified cell value against a table on another worksheet. I want the formula to return a "-" if there is an error, or an empty cell with no information on worksheet with the defined range.

For example, on one sheet I have a table


state MVR Fee No Hit Credit

I want the OH cell to reference a range of cells on another worksheet that contains a full listing of active states with MVR Fee and No Hit Credits

Alabama blank blank
Arkansas $2.00 $1.00
Arizona $5.00 $1.00
Ohio blank blank

The formula I have written is as follows:

=IF(ISERROR(VLOOKUP(AL!$C$82,'Forms & Filings'!$A$5:$E$48,2,FALSE)),"-")

I was expecting this formula to return the exact value from the table, and if there was nothing (blank) to then return a -

Instead, when I run the formula, it returns FALSE in the cell...I'm not quite sure why.

Does this have something to do with the formatting of the cells? Both worksheets (in the same workbook) are formatted as currency.

Any help would be greatly appreciated! Thanks in advance.

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!

Hi Excel Gurus,

I'm looking for a formula that can do the following:

look @ a range of dates
see if the number below any of the ranges cells equals 2
if one does then return 'yes'
if any of the cells in the range are blank and one equals 2, then it returns 'yes'
if none of the cells equals '2' and at least one is blank, then (nothing)
if all of the cells have a value, but none equal 2, then 'no'

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1 1 1

cell output = "no"

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1 2

cell output = "yes"

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1

cell output = (blank)

Thanks in advance for your insights and I look forward to seeing what you can come up with.

=IF(OR(HLOOKUP(C3,G2:J4,2,FALSE)=2,(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+1,DAY(C3))),G2:J4,2,FALSE)=2),(HLOOKUP((DATE(YEAR(C 3),MONTH(C3)+2,DAY(C3))),G2:J4,2,FALSE)=2),(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+3,DAY(C3))),G2:J4,2,FALSE)=2)),"On Time","")

I've been trying to figure this one out for hours. Only arrived at the following, but couldn't result in blank and the condition of the range which allows for a response only once either a 2 exists or a value is included in the entire range.

The Novice


Works perfect except on a blank row where it naturally returns #VALUE!. I would normally do a simple [=IF(A735,] at the beginning of the formula and [,"")] at the end but I can't make it work here.

Can someone help me clean this up? Thanks

Hi everyone - I've seen a few posts on various forums with questions that initially seem similar to mine, but the given answer doesn't help me (e.g., here). I'm using XL2007 with Windows XP. Here's my issue:

I have a table of values with a header row (the header row is a series of time points - 0, 4.5, 9, 13.5, 18, etc.). Also, I have two cells that I am referencing for start and stop time points (so, let's say I want to use 4.5 and 13.5). Ideally, my formula would be able to use a time point for an HLOOKUP in the table and then drop down to the appropriate row to mark that cell as the part of the AVERAGE function range. My first attempt looks something like this:


Excel doesn't like the above formula and my guess is because the HLOOKUP gives me values not cell addresses. Is there a function I can insert to get the cell address from the HLOOKUP? I tried to incorporate the ADDRESS and MATCH functions unsuccessfully. But I'm not even sure if those are what I need.

In the end, I want to be able to change the time points in the referenced cells (for this example, B2 and B3) and have my table of calculated values adjust their averages based on the new window of time points given.

Define my average window:

My table of averages:

Thanks for the help! Please let me know if something isn't clear.

Hi all,

I'm using the following as conditional formatting but the green one isn't quite working as intended - it's formatting blank cells as green when I want them to stay blank

Goes Red: Formula is =AND((G4=TODAY()),(H4=""))

The reason for the H4="" is that the H4 column is completed date - I want these cells to change colour only if there is no completed date i.e. they're outstanding. Otherwise the cell should stay blank - in the case of there being nothing in G4 or H4 the cell stays blank.

The red formula seems to be working OK, blank cells not affected (when I remove the green condition the cell stays blank), but blank cells are going green when this condition is present.

Confused much? :D


I have a basic VLOOKUP formula. But, the table it is looking at will often have blank cells and non-blank cells. For the cells with dates in them, the formula works fine. For the cells without dates, it pulls back a zero, rather than the blank cell.

What can I add to this formula to allow the blank cells to be returned with a blank cells instead of zeros?

=VLOOKUP($B4,'Reference Tables'!$D$3:$E$200,2,FALSE)

I am using this formula;


In worksheet that contains my array, the cells in column 10 either have a time in them (in 24hr format), or are completely blank. The formula is working fine as long as there is a time in the cell column 10. If the cell in column 10 is completely blank, the forumla returns 0:00 which is midnight. That's not what I want. If the cell in column 10 is blank, I want my formula result to be blank. How can I accomplish that?

I am trying to have a conditional format where if there is a value in cell A7 then C7 will be become highlighted yellow. The problem is that we made a change and the A7 value is now determined by a formula, even if the formula returns a blank the old conditional format we used thinks the formula is a value. Does anyone have any idea how to adjust or change the formula we were using? The old formula we are using is:


Hi Group,

I have a range of cells (H10:H24) that contain a 'nested IF-OR formula'
that returns either blank or a value.

How do I set conditional formatting to have the cell not filled if
the formula returns blank but filled if it returns a value?

Thanks for any help

I've got this OR/AND conditional formatting formula in all cells across 4 columns:


It works great. When I apply my pivot filter in $B$8 and choose either "upstream" or "downstream" it correctly colors my pivot table cells 'Red' as specified by my conditional format criteria, if the cell value is also greater than > than the absolute reference cell threshold value in $C$6. Great, right?

But when I add a field in my pivot table where I need it to look at a different absolute reference cell value, which I need it to again color the cell G22 'Red', IF the cell value is less than < than the absolute reference cell threshold value in $D$6, suddenly the formula doesn't work properly. It shades some cells 'Red', that are lower in value than $D$6, and some that are higher. It also shades rows 'Red' outside the pivot table area...

Here's my formula that doesn't work (see below). I think it has something to do with the condition if the cell value is zero '0' or 'blank'. I think I need to make this formula more robust to accomodate those 2 conditions. But then again, why did the first formula condition work perfectly?


In a pivot table how does one set up conditional formatting with a formula
with references to calculated field values such that the conditional
formatting can be copied to other cells in the pivot table? I can set up the
conditional formatting for a single cell in the pivot table just fine. But
when I attempt to copy the formatting from one cell in the data area to
another cell, the formula in the conditional formatting is not using relative
references. I am running Office XP.

I have a vlookup formula which looks at a table and returns the value of a cell which cross-matches the value in the row and specific column (Not sure I am explaining this correctly). Let me see if I can be more clear. I have test questions which only apply for certain workout types. My formula looks at the workout type (column B) and matches the question ID (Row 4) to the table on sheet 2. Where these intersect, the cell is either blank or contains a "x". I would like to replace the formula with conditional formatting to gray the cell out if where the workout status and question ID intersects is blank.

I have attached a spreadsheet. The top table on Sheet1 contains the results of the vlookup formula. The bottom table on Sheet1 shows how the conditional formatting would look. I need to use conditional formatting because if the question applies to the workout status, the tester will need to input into the cell. If the workout status does not apply, they do not need to answer the question. I need this to be visible so the tester does not waste time answering questions which do not apply.

The table for the vlookup formula is on Sheet2 of the workbook

Please let me know if any additional clarification is needed. AND a big thanks of appreciation for the help/support.

I have a large spreadsheet which I require people to regularly complete. Due to the complexity of my business there are 180 columns, each of which has a variety of rules towards its completion.

I have used conditional formatting to check as many of the rules as possible (e.g. if somebody enters that delivery is required then the columns for delivery address cannot be blank).
Each column has the same conditional formatting but can have 1,2 or 3 rules. Min is always 1.
Most columns are fairly unique conditional rules.
There is a variable number of rows each time it is filled out (i.e. people add rows as required) but they copy and paste an existing row so conditional formatting is copied as well.
All conditional formatting rules use the "formula" setting and nearly all use many "AND", "OR" statements to make all the necessary checks.
If a cell is found to have failed the validation checks (e.g. is blank when should have an entry, has an entry when it should be blank) then it highlights the cell in red.

I now want to have a count of the number of "red" cells to show me quickly if it has been completed "correctly" or not.

I have written the vba code to check all the cells but cannot find out how to record if the conditional formatting is being used or not. "colorindex" returns the original colour of the cell not the conditional formatting.

Any help greatly appreciated.

I have the following formula in a column:

Basically, do an hlookup and if the value returns a blank, return NA(). Basically, I'm returning #N/A.
Why would I return an error for my column? Because the column is being used for a line chart and I don't want to chart zero values. By doing the NA(), it returns #N/A and thus no zero values get charted.

My problem is this, my column of values looks ugly w/ the #N/A. I'm trying to conditionally format so that if any cell in the column has #N/A, then the font will equal the pattern so essentially, the #N/A will be invisible. No condition in the conditional formatting is working either in "Cell Value Is" or "Formula Is". What does the condition have to be in order to catch the #N/A? I've tried "#N/A", "NA()", NA(), , 0, isblank(), iserror(), etc., etc.,...

Is there a better way to do all of this?


I have a small glitch with a conditional formatting formula that I was once told could be solved by rounding. I can not find the information I was given so I must ask again for this help.

I have a column of times similar to bellow:

I am using conditional formatting to blank out the start and end repeated times as follows.

Cell A2: Cell Value is equal to =A1 (then dark blank out)
Cell A3: Cell Value is equal to =A$1+A2-A3 (then dark blank out)
_______Cell Value is equal to =A2+A3-(A$14+TIME(0,0,1)) (then dark blank out)
Cell A4: Cell Value is equal to =A$1+A3-A4 (then dark blank out)
_______Cell Value is equal to =A3+A4-(A$14+TIME(0,0,1)) (then dark blank out)
...and so on.

When it works properly I would only see the times in cells A1, A3 to A12 and A14.
When the glitch occurs I would see the times in cells A1, A3 to A14.

Because of the 0:00:01 (needed so the formatting will DARK BLANK Cell A1 and A2) the dark blanking of A13 and A14 sometimes does not work depending on the times in the cells. Most of the time it works fine, but there seem to be rare situations that the formatting doesn't work.

As I said before there was someone on a board much like this one that told me to ad a rounding function to the formula, but as with many functions in Excel I'm not fluent enough with it to know how to apply it.

Please help

I am using a vlookup formula to reference a cell on another tab. I have tried 2 different formulas to do this.

The first one I used:
=IF(ISNA(VLOOKUP($A10,'ESC''s'!18:18,4,FALSE))," ",VLOOKUP($A10,'ESC''s'!18:18,4,FALSE))
I had issues when copying this formula over across the row into other columns to reference the other cells. I couldn't get the formula to automatically adapt the cell location without manually changing it it.

I then had someone explain that I could reference another cell location that would help it to adapt to my needs, which works, but still returns a 0 instead of a blank.

This is my new formula:

I need a blank cell or else my conditional formating on the spreadsheet fails. I know this has been discussed before, but nothing seems to work when I try it.

Good afternoon

I am trying to do up some formula for a cell (CELL A) which refers to a calculation of another two cells (Let's say CELLS B & C). A value in Cell A can only have a value of between 2 and 10 inclusive) (because Cells B and C can only be a value of 1 to 5).

If there is no value in Cells B & C, the result in Cell A will be zero or 1, hence invalid and I want Cell A to give just a blank cell which is working.

This part I have worked out but the next part is that, another cell (CELL D) is to use a number of nested IF formula to produce a result. For example, if Cell A comes up with a result of 9 or 10, then it displays the word "EXTREME", if it is 7 or 8, it displays "HIGH" etc etc all the way down to a value of 2 where it displays "LOW".

Again, a value of 0 or 1 is not valid so in CELL A, it is blank but I can't get Cell D to also be blank. It automatically puts up 'EXTREME"

I have probabaly totally confused and if you got this far in the thread then I am impressed! but if you can help that would be great.

I need some help, this is my first time on this forum.

I got as far as returning the value I wanted if there is data in my spreadsheet with my HLOOKUP formula, however I want to make it dynamic so that when I add a row of data I don't have to keep changing the formulas. Here is the formula I am using =IF(HLOOKUP($DB$6,$CM$1:$CZ$73,9,FALSE)>0,1,HLOOKUP($DB$6,$CM$1:$CZ$73,9,FALSE)).

Thanks if anyone can help with this one it would be great.