Free Microsoft Excel 2013 Quick Reference

Conditional formatting when the cell is blank Results

A large proportion of questions in Excel newsgroups and Q&A boards regard #N/A (the Not Available error) the look up worksheet functions return.

The quotes that follow are from the Help file (of Excel 2000).

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Remarks

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

LOOKUP(lookup_value,lookup_vector,result_vector)

Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

LOOKUP(lookup_value,array)

If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Remarks

If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.

If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.

MATCH(lookup_value,lookup_array,match_type)

Remarks

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.

MATCH does not distinguish between uppercase and lowercase letters when matching text values.

If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters, asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Although kindred, I excluded INDEX and CHOOSE, because they don't return #N/A.

What follows is a quote from a reply of mine at microsoft.public.excel.worksheet.functions to a post entitled "Conditional formatting to hide #N/A results", along with a reply to the same post by Harlan Grove:

It involves a proposal to extend the syntax of the look up functions quoted above with an (extra) optional argument.

QUOTE

A richer syntax for lookup functions would allow us to escape testing what
these functions compute (return), so avoiding the "compute twice" trap we
often see.

My proposal is simple: Add an optional slot to the sysntax of these
functions.

For VLOOKUP this surgery would give:

=VLOOKUP(lookup-value,table-array,col-index-num,{range-lookup},{return-value-when-not-available})

{} means optional; the default value for the 5th argument should be #N/A (in
view of backward compatibilty).

Examples:

=VLOOKUP(x,Table,c,0) [ return #N/A by default upon failure ]

=VLOOKUP(x,Table,c,0,"") [ return blank upon failure ]

=VLOOKUP(x,Table,c,0,0) [ return 0 upon failure ]

=VLOOKUP(c,Table,c,1) [ return #N/A by default upon failure; although in
most [cases] avoidable by approriate structuring of Table ]

=VLOOKUP(c,Table,c,1,0) [ return 0 upon failure; although in most [cases]
avoidable by approriate structuring of Table ]

PS. This is a renewal of a thread I was involved with at
http://www.mrexcel.com/archive2/messages/13513.html

What do you think? Am I overlooking something that would stand such a change
in the way?

Aladin

"Harlan Grove" wrote in message
news:xOuG8.27486$D41.1032708@bgtnsc05-news.ops.worldnet.att.net...
> Peo Sjoblom wrote...
> >One possible way would be to use a white font and the formula
> >
> >=iserror(a1)
> >
> >assuming you want to hide it in A1
> >
> >You can of course trap the error using if and isna in the formula as
well..
> >
> >=if(isna(formula),"",formula)
>
> But it involves evaluating formula twice, which can seriously slow down
> Excel when formula is long and/or complicated. There are times when VBA
> user-defined functions can actually speed up Excel.
>
> 'trap errvals and return specified value or "" instead - return v if
> 'it's not a marched errval
> 'args: v is the *scalar* value to check
> ' e is an optional list of additional args used as follows
> ' - if 1st arg after v isn't an errval, use it as the return value
> if v is a
> ' matched errval; otherwise, use "" as the return value
> ' - all remaining args are treated as errvals to match v against,
so
> if
> ' no remaining args, match all errvals
> 'note: nonerrval args after 2nd arg effectively ignored
> '
> Function errortrap(v As Variant, ParamArray e() As Variant) As Variant
> Dim i As Long, m As Long, n As Long, t As Variant
>
> errortrap = v
>
> If Not IsError(v) Then Exit Function 'return quickly when not errval
>
> n = UBound(e)
>
> If Not IsError(e(0)) Then
> m = 1
> t = e(0)
> Else
> m = 0
> t = ""
> End If
>
> If n < m Then 'no more args, so matches all errvals
> errortrap = t
> Exit Function
> End If
>
> For i = m To n 'check specified errvals
> If v = e(i) Then
> errortrap = t
> Exit Function 'can return now
> End If
> Next i
> End Function
>
>
> In this case, use as =errortrap(formula,#N/A) or
> =errortrap(formula,"",#N/A) (to be explicit). More generally, to pass
> #VALUE!, #NUM! and #DIV/0! errors but replace #N/A, #NULL!, #REF! and
#NAME?
> errors with, say, -1, use =errortrap(formula,-1,#N/A,#NULL!,#REF!,#NAME?)
.
>
> If formula is simple, this will likely slow Excel down. However, if
formula
> involves 6 levels of nested fucntion calls, this will likely speed Excel
up.
>
> >another way assuming
> >
> >=if(countif(d2:d100,b1)=0,"",vlookup(b1,d2:e100,2,0))
>
> Better than evaluating the VLOOKUP twice.
>
> >or
> >
> >=if(countif(d2:d100,b1)=0,"",index(e2:e100,match(b1,d2:d100,0)))
>
> No benefit in this case to using INDEX(.,MATCH(.,.,0)) vs
VLOOKUP(.,.,.,0).
>

UNQUOTE

As long as Microsoft has not taken up the above proposal, we should at least live by a set of reasonable rules shown in the figure below (I posted this figure a while ago at this board, but I can't give here the hyperlink, simply because I couldn't find it via the board's search facility, hence this re-post.)

Microsoft Excel - VLOOKUP.xls___Running: xl2000 : OS = Windows (32-bit) NT 5.00(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elpF6F7F8F9F10F16F17=
ABCDEFGHIJKLM1IDLast NameSalary

2010008Smith46,223.00
EXACT MATCH (0 or FALSE, both means the same thing to Excel)

3010002Miller70,000.00
VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0)

4010007Young46,223.00

What is better?

5010005Thomas44,662.00
Look UpSalary?

6010001Smith70,000.00
01000270,000.00this

7

70,000.00this, if #N/A must be avoided, because efficient

8

70,000.00inefficient

9

70,000.00inefficient & bad practice

10Commission Table

70,000.00inefficient & bad practice

11Sales%

1200.00%

APPROXIMATE MATCH (1 or TRUE or nothing, all means the same thing to Excel)
1325000.20%

VLOOKUP(lookup-value,lookup-table,table-column-to-look-in)

1450000.40%

What is better?

1560000.60%

Look UpCommission

1670000.80%

7,400.880.80%this

1780001.00%

0.80%this, if #N/A must be avoided, because efficient; seldom needed
1890001.20%

As above, no ISNA, ISERR, or ISERROR.

19

20SPECIAL NOTE: INDEX/MATCH is faster than VLOOKUP, in particular when the formula must be copied to a huge number of cells.Sheet1
To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.0] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-29 16:12 ]

I'm using Excel 2008 and would like to have a series of cells that may have a value by a calculation, or they may be empty. In the case where they are empty, I would like to be able to use the "Center Across Selection" feature. Normally for blank cells, I just use two sets of quote symbols so nothing is displayed, but in this case, I would like to center over that selection. Is there any way to do this?

If the context is useful, I am trying to highlight when periods occur in a project plan. For some efforts, there may be 12 month periods, 6 month periods, etc. In the below picture, I have conditional formatting working above the text to show the bounds of each 5 month period, and I have the number of the period in the first month of that period. I would like to change the text to "Period "&#. If I do this, a good portion of the text will be truncated, but if I could center across selection, the formatting would be improved.

Any help is much appreciated!

I have some workbooks that have been created by persons unknown and the cells change colour when data is entered. (Blank cells stay white)
It dosent appear that any conditional formating is set and I would like to be able to replicate this type of formatting. (I would also like to edit which colour is used)
I cant see that anything is done in VBA either but I not a whiz with this.
The colour change dosent seem to work on the whole worksheet but I cant see any ranges that have been created that it may apply to.

Is this something in the auto format or styles that I havent come across yet?

Once again I must humbly come to the great minds for assistance.

I have a spreadsheet that I am setting up to track training dates. Using Vlookup, it will return the date of training in each of many categories for each employee. Some employees will not require some of the training, so the date cell will be blank. But on my tracking sheet, if the Vlookup finds no date it fills the cell with 1/0/00, and I need to be rid of that.

I considered using conditional formatting, but I am already using that to identify training that is about to expire. If the date returned is within a month of expiring (annual training) then it will change the font to purple, and if it is within two weeks of expiring then the font will be red. So, when I tried to make any 1/0/00 entry change to a white font to make it invisible, it just doesn't work. This is the Vlookup I am using:

=VLOOKUP(J4,'[Required training by position PII presentation.xls]Employee Dates'!$A$4:$AN$16,4,FALSE)

Any assistance with this would be greatly appreciated. Thank you!

Hey,here is my delema.

-I have a drop down box with 2 options... "Yes" and "NO" in cell A1
-When you select "NO" I want cell B2 to say "N/A" and the background turn grey
-When you select "YES" I want cell B2 to be blank and ready to type text

any ideas.. im guessing conditional formating on this one but I can't figure out the formula... Please Help

I am having some difficulty with conditonal formating on a form am I am trying to create in Excel 2003.

It's pretty hard to put into words as an example so I have uploaded my spreadhseet here: http://rapidshare.com/files/149674609/Book1.xls

Basically I have a couple of lookup functions that get data from the 'Data' sheet. The look up is searching based on the data in cell B4.

This then returns the results in Cells B13:B16. Cells D13:D16 I have some conditional formatting rules.

I want certain cells to appear with data and displayed with conditonal formatting based on the selection from a drop down list.

For example:

I have conditional formating on B13 so it appears blank until a selection is made in Cell B4. Then depending on what data is chosen in B4 different information will be displayed in cells B13:B16 and different formating in D13:D16.

Conditional Formating on B13 is:

Cell Value = 0 (Format - white text with no borders)
=ISERROR(B13) (Format - white text with no borders)

Conditional formating on D16 is:

=ISERROR(B13) (Format - white text with no borders)
=NOT(ISBLANK(B13)) (Format - black text with a black border)

The Scenario:

Cell B3 contains the Text "New_User" (selected from a drop down list)
Cell B3 contains the Text "Sales" (selected from a drop down list)
Cell B4 contains the Text "BS" (selected from a drop down list)

The lookup function in B13: =LOOKUP(B4,Data!A12:B15)

This would return "Sales Rep Postal Codes:" in Cell B13. B16 should then use conditional formating to place a black border around the cell with black text.

This all works fine. However, if the selection made from the drop down list in B4 is "T" then this returns a value of "0" in B13, as a result the conditional formating on B16 is black border and black text when i want it to be no border with white text.

If the selection in B4 is "BS" or "O" then I want cell B16 to have black border and black text. If no selection has been made in B4 or if "T" is selected then I want B16 to have no border with white text.

I hope somone understands what I'm saying and can help me!! I have tried everything I can think of to get it to display teh way I want.

I have uplaoded the spreadhseet on the link above because it will probably be easier for you to understand what I'm trying to do if you look at the spreadhseet, I tried to use the HTML maker but it doesn't display correctly.

Please help! Thanks in advance!

John

Hi everyone,

I know a big issue for people is that when you use an IF formula to
return a blank cell and graph it, it returns a zero value, for example
if(a1>0,a1,"") returns a blank cell that charts as a zero.

There is a solution to this problem that does not involve the more
common returning of an NA() value and conditionally formatting it out.

The solution is the use of dynamic ranges to set the chart values. It
is relatively easy and good practice as you can easily update and
reference data on other sheets. What you do is define a named range
for your data which dynamically extends with your information.

Step 1:
>From the top file menu's select Insert > Name > Define.

Step 2:
Give it a name that you will remember, that cannot start with a number.

Step 3:

Define your range using the following formula:

=$b$17:INDEX($b$17:$m$17,1,MAX(IF($b$17:$m$17"", COLUMN($b$17:$m$17)))-COLUMN($b$17)+1)

But replace $b$17 with the FIRST cell in the row, and replace $m$17
with the LAST cell in the row. (Make sure you use absolute references
($) otherwise it will not work!).

NB: If your data is in columns, not rows, you can reverse the last two
formulas in the INDEX formula and use the ROW function instead of
COLUMN. (ie, the INDEX formula uses row and then column -
INDEX(array,row_num,column_num)). Please post if you would like a ROW
formula posted.

Step 4:

In your chart, right click, select Source Data, select the series you
wish to set dynamically, and in the Values box enter ='[Sheet
name]'![Named range from Step 2].

Good luck!

Hi, I'm using the following formula. The problem is that sometimes
my value isn't found and I'm not getting a blank but rather #N/A.

=IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been
converted to PPO LOBs", "")

1. Is there a fix to my formula? or
2. In the cell that results #N/A - I could set a conditional format to make
a white background and white font. However, when I try that - it's not
working either.

Cell format = #N/A doesn't do what I want.

I have a column with the cells formatted to show dd/mm/yyyy hh:mm. There is
also a formula that adds a day if certain conditions are met. When I copy
this formula down so that every cell has it, the cells on rows that haven't
been used yet show as 01/01/1900 00:00. How do I show these as blank cells -
the sheet is quite confusing otherwise!

I'm creating a worksheet where I want to carry the formula down the sheet,
eventhough cells are blank. I keep getting error values. I understand why
the error values, but I do not want them to display.

My formula is: =(B6-B5)/ABS(B5)

If tired:

=IF(ISERROR(B7-B6)/ABS(B6),"",(B7-B6)/ABS(B6))

and

=IF((B8-B7)/ABS(B7)=0,"")

but #DIV/0! keeps displaying.

Is there an option or conditional format that will give me the option to not
display error values? I have to create worksheets for several other formulas
and I do not want the error values to display when cells are empty or when
dividing by zero.

Thanks,

Cyrus

I am creating worksheet for use where I want to carry on the forumla down the
worksheet, even though cells are empty.

My formula is =(B6-B5)/ABS(B5)

I get the error message "#DIV/0!"

I have tried the following and the cells still inputs error value. I want
the error value not to display. I want the cell to remain blank.

=IF(ISERROR(B7-B6)/ABS(B6),"",(B7-B6)/ABS(B6))
and
=IF((B8-B7)/ABS(B7)=0,"")

Please help. I'm looking for this function for several other worksheets as
well. Is there an option or conditional format I can use to eliminate the
error values from being displayed?

Thank you!

Cyrus

I have a column with conditional formatting applied. Say I'm formatting
cell E4.

The column shows the number within a processing cycle which need
warning traffic lights when certain days are reached so far I have set
up:

Condition 1 shows orange if the cell value is between 9 and 11.

Condition 2 shows red if the cell value is greater than or equal to
12.

My problem is that I need a 3rd condition to grey out cell E4 if a date
is entered into cell G4. I've tried all sorts but the formatting will
not work.

Firstly can someone suggest a formula for condition 3.

Secondly do I actually need formulas in conditions 1 and 2 to state
apply formating as per the criteria above , but only if G4 is blank?

any guidance would be greatly appreciated.

Thanks

--
HDV
------------------------------------------------------------------------
HDV's Profile: http://www.excelforum.com/member.php...o&userid=26299
View this thread: http://www.excelforum.com/showthread...hreadid=469842

I am trying to sort a list of users. The way I have it set up is in one
list people who have license agreements are highlighted green. That was
easy. I need to pull out ONLY the people who have been highlighted and
sort them in another column.

The way I do this is in another column like "M" and "N" I have the
conditional formatting equation which is

=IF(A3="","",ISNUMBER(MATCH(A3,B:B,0)))

that will show TRUE or False. In the next column over I have an
equation to pull the name from the original row I am sorting.

=IF(M3="","",IF(M3=TRUE,A3,""))

Then I tie it to a "Sorting" button to paste special, values only, into
Column C and automatically sort it.

Private Sub sortuser_Click()
Dim deleter As Integer

Range("N2:N30").Copy
Range("C2:C30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=False
Range("a1").Select

Range("C2:C30").Sort Key1:=Range("C2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

End Sub

but whenever it sorts there are blank spaces it copies and sorts them
to the top. but If i delete the contents, even though there isn't
anything in the cell, and sort it, it will sort will the first name in
Cell C2(where is should be) Even when I paste special with ignore
blanks it still does it, any Ideas?

--
Xiazer
------------------------------------------------------------------------
Xiazer's Profile: http://www.excelforum.com/member.php...o&userid=31581
View this thread: http://www.excelforum.com/showthread...hreadid=517234

Using MS Excel 2003:

As and example, I entered into Conditional Format for cell B1:
"Formula is", IF(A1="","Test",""). For true condition, the format is set to
fill Yellow and font color red.

I'm expecting cell B1 to display, "Test" in red text with yellow background
when A1 is blank, but it does not work. No errors are displayed. Any ideas?

when using a pivot - the word "BLANK" transfers in when the data sheet
has a blank area...

How can I get that to be just blank so that I can use that cell in a
conditional formatting scenario?

I want to say...
=IF(Q8="blank")

if Q8 is blank, then turn red
if Q8 has a value, then turn green

Angela

--
anjgoss
------------------------------------------------------------------------
anjgoss's Profile: http://www.excelforum.com/member.php...o&userid=26602
View this thread: http://www.excelforum.com/showthread...hreadid=398765

this is a question on fundamentals of excel

for e.g. cell A2 is blank. I confirm whether A2 is blank by going to
another cell and typing which gives . But when I go to
another cell and click edit-clear-all (to celar any formats etc) and type
it does NOT give a blank cell but gives (zero).

If I copy and paste a blank cell to another cell it gives blank.

the above descrepancy gives problem in creating formulas. for e.g.
A2 is 2, A3 is blank
If I type in some other cell the formula it gives 0 and
not Blank which A3 is .
is it because if the condition is true it is blank and even if it is false
it is blank.

this formula may be trivial but I have a formula which gives this type of
problem.

Elucidation please.

Hi,

Depending on the values in column U, I want column V to have a value.
I am using an IF formula for this.
There is one problem. Some cells in Column U (eg U26) are blank(have
no values). However if I user U26 ="" for testing the condition, this
is resulting in FALSE. If I use =ISTEXT(U26), this is resulting in
TRUE. Similarly, ISBLANK(U26) is returning FALSE. There is no formula
in the cell. The column (U) is formatted as General. However, when I
press "Delete" in U26, ISBLANK returns TRUE and ISTEXT returns
FALSE. What could be wrong and how can I handle this?
One solution is to "Delete" the contents of the Blank cells. This is a
bit cumbersome as such blank cells are scatterred across the column. I
am posting this as the knowing the cause of the problem and the
solution would help me on future occasions also.

Thanks in advance for all the help.

Regards,
Raj

I have applied several conditional formats and had some conflicting results.
At this point, I would like to delete all conditional formatting and start
over. When I go to edit > go to > conditional formatting to highlight all or
same formatting, then go to format > conditional formatting, it is blank. It
does show my previous conditional formatting settings if I only select one
cell of the sheet that I know that it was applied to, so it is there. It is a
huge sheet, so I would like a way to identify and remove it all at once.
It almost seems that the "go to" method of highlighting is picking
additional cells that were not formatted.....
thanks in advance for any help,
Robert

Hi
Experts

I have developed a macro which imports data from another excel file.
Ex: consider I have 2 excel files A & B.
I run macro in file A today, it copies whatever data from file B.
say 20 rows have been filled in file A(starting from A5 to A25). I do some
changes on the file A which has few conditional formatting and forlumas.
I re-run the macro after 8 days in file A to get the new updated data from
file B.

The problem I am facing is >>>
"If I re-run the macro, it deletes the data from file A. It pastes
everything starting from top left cell.
When I generated macro in file A, I have selected top left cell while
recording macro.

My requirement::::::
Now I want the macro to put the data from the cell A27 onwards and also
retain the changes to the data from Cell A5 to A25.
So everytime I run the macro it should add data to the excel file from the
available blank cell and not copy over the same data.

Thnanking in anticipation

Hi all,

-I have an IF Stmt in Cell G97 that reads: =IF(M96=0,"",G96+1)
-M96 also has an IF Stmt that reads: =IF(G96


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