Subscribe!

The #VALUE error is an error that is seen almost as often as #N/A. In short, the VALUE error occurs when a formula refers to a cell that contains a different than expected format. In this article, I will discuss the main causes of #VALUE! and some possible solutions.

The top 3 causes of #VALUE! error are:

1. Creating a formula that refers to a cell that contains a data type that cannot be handled.
2. Pressing enter after entering an array formula.
3. Entering a range when a single cell reference is required.

To correct the #VALUE! error, the first step should be to trace the error. You can do this by selecting the cell and then choosing “trace error” on the formula auditing toolbar (view=>toolbars=>formula auditing, pre-Excel 2007). This will show you what cells are originating the error. After you have identified the cells causing the error, look at the formula in your error cell. is it trying to perform data manipulation on the cells shown in the trace error? If so, make sure all of the cells are of the same data type.  The most common cause of the VALUE error is performing a mathematical task on cells that contain text.

To address the second most common cause (pressing enter after an  array formula is entered) simply press ctrl+shift+enter after the formula is complete rather than enter. This is how all array formulas must be entered.

The third common cause of the #VALUE! error is a simple fix as well.  Rather than entering C2:C20 in your formula, perhaps your formula only supports a single cell reference such as C2.

If you decide your error values need to be left as is but you do not want them to appear when you print, then you can remove them from the print file by doing this:

1. Select the worksheet with the error you don’t want to print.
2. On the File menu, click Page Setup, and then click the Sheet tab.
3. Under Print in the Cell errors as box, click <blank>, — , or #N/A.

Joseph Harris
Excel Help

If you found this article helpful, buy me a cup of coffee!

Related posts:

  1. #DIV/0! Division by Zero Error in Excel The #DIV/0! error happens when the denominator (the number on...
  2. How to Use ISERROR Function in Excel The ISERROR function is a versatile one that is very...
  3. Removing #N/A error in Excel using ISNA() The #N/A error happens most often when a lookup function...
  4. Paste Special – Transpose Excel Transpose...
  5. Excel Data Validation Data validation gives the Excel developer a way of dictating...

This entry was posted on Tuesday, March 3rd, 2009 at 2:43 pm and is filed under Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.