In Power Point presentations where there is a need to hide #N/A results for aesthetics purposes in Excel tables that were inserted in the presentation. There are two options to hide #N/A results. (A) By using IFERROR function, (B) By using the enhanced Conditional
Formatting of Excel 2010.
Read section (C) below on how to hide #N/A result in Excel's print out by using Page Setup.
A) Using IFERROR function
Let’s say that sometimes one of the cells in column E can display #N/A.
B) Using the Conditional Formatting
Read section (C) below on how to hide #N/A result in Excel's print out by using Page Setup.
A) Using IFERROR function
In the above example cell E6 contains the
function VLOOKUP(F6,$G$6:$H$10,2,FALSE) and displays the #N/A error.
The following IFERROR function, will replace #N/A with blanks:
=IFERROR(VLOOKUP(F6,$G$6:$H$10,2,FALSE),"")
B) Using the Conditional Formatting
We will format all cells in column C in such a way that each
time when the cell value is #N/A, Excel will hide the text.
- Click the Conditional Formatting in the ribbon of the Home TAB.
Pull down menu will open.
- Click Manage Rules...
This window will open
- In the above window, click the New Rule… button
This window will open
- Click the second line
„Format only cells that contain
This window will open
- Click the down arrow at 'Cell Value' and select the line 'Errors'
The window will look like this:
- Click the Format… button
- Click the down arrow of the 'Automatic' field
The next window will open
Colors pallet will open.
Select No-Color (or white color) for text and click the OK button.
Select No-Color (or white color) for text and click the OK button.
The next window will show up.
- Click the cursor in the beginning of the field Applies to
Press the keyboard Del key
to delete all the text in the Applies to field.
With the mouse, mark the area in which the #N/A error will be hidden, let's say cells C3
through C10 in the above example and press the keyboard's Enter key.
If this step was done correctly the
text in the Applies to field will show
=$C$3:$C$10
Click the OK button to
complete the process.
Now cells with #N/A, like C4 and C8, will look
empty.
C) Hide #N/A result in Excel's print out
1. Open the Page Setup, in the Page Layout TAB
Even better. Use IFERROR function, as this can replace the #N/A with actual text such as 'Can't divide by zero - correct quantity field' or something.
ReplyDeleteThanks for the clear instructions - have been looking for a simple solution like this and haven't been able to find one this straightforward
ReplyDeleteIFERROR allows the sheet to compute, so much much better - Thanks!
ReplyDeleteA much easier way to hide these errors is to go to Page Setup. On the Sheet tab, choose "blank" next to "cell errors as".
ReplyDeleteThis option only work for printing purposes, it doesn’t work when the Excel sheet is inserted in a Power Point presentation.
DeleteHi, I found this solution on excelvlookuphelp when I had the same problem
ReplyDeleteTutorial: Don’t show #N/A if the lookup value isn’t found
The Problem
You might be expecting that not all of your search values are going to return something from the search table. Instead of the formula returning #N/A you’d like the result to look different when your vlookup value isn’t found (either blank or an indicator to show that the value hasn’t been found or a zero if you’re wanting to do maths with the results).
The Solution
You can use the iferror function.
It works like this
= iferror (YourVlookupFormula, WhatToSayInsteadOf#N/A)
Here’s an example
=iferror(vlookup(D3,A:C,3,false), “No Value Found”)
Or if you would rather it was just blank then instead of having No Value Found, just have the two sets of inverted commas, like this
=iferror(vlookup(D3,A:C,3,false), “”)
Source: www.excelvlookuphelp.com
Your suggestion seems to be the same solution as written here at the top of this page. See last line in section A.
DeleteThanks! Very helpful page which helped me get out of some VLOOKUP errors with blank spaces.
ReplyDelete