Tips on Correcting the Most Common Excel VALUE Errors and How to Fix Them

If you've ever encountered #VALUE! errors while using Excel, you're not alone. In this article, FPT Shop will show you the most common #VALUE! errors in Excel and how to fix them.

By
Administrator
-
0
253

The VALUE error in Excel can occur due to various factors, resulting in different methods of fixing it. For a better understanding of this error, please refer to the article below.

What is the VALUE error in Excel?

The VALUE error in Excel occurs when the #VALUE error message is displayed by Excel, indicating an error with the formula input or the cells being referenced. The VALUE error is a broad term as it can have multiple causes. The article below will cover the most common VALUE errors.

Common #VALUE errors in Excel and how to fix them (Figure 1)

Common VALUE errors in Excel

1. VALUE error in Excel with blank cells

The #VALUE error with blank cells occurs when the formula includes cells with spaces or empty cells. Sometimes, spaces can make a cell appear empty even though they are not actually empty, and these are called hidden spaces.

Solution:

  1. Select the cells referenced by your formula. If these cells are arranged in a row or column, you can select the entire row or column to remove multiple spaces at once. In the example below, I will select the entire column E.

Common #VALUE errors in Excel and how to fix them (Figure 2)

  1. Select the Home tab and click on Find & Select > Select Replace. You can use the keyboard shortcut Ctrl + H to save time.

Common #VALUE errors in Excel and how to fix them (Figure 3)

  1. When the Find & Select dialog box appears, enter a single space in the Find what box and delete all characters in the Replace with box.

To remove all spaces in the selected cells, click Replace all.

Common #VALUE errors in Excel and how to fix them (Figure 4)

If Excel gives you the desired result after removing the spaces using this method, it means the VALUE error in Excel has been fixed.

If Excel still displays the #VALUE error, it means there are hidden characters other than spaces that make the cell appear empty. In this case, go back to the Home tab > Select Sort & Filter > Filter.

Common #VALUE errors in Excel and how to fix them (Figure 5)

  1. Click the filter arrow > Deselect Select All and then select the Blanks checkbox > OK.

Common #VALUE errors in Excel and how to fix them (Figure 6)

  1. After removing all spaces and empty cells, turn off the filter and check the formula again. If Excel gives you the desired result, it means the error has been fixed.

2. #VALUE error when the list contains text or special characters

This #VALUE error occurs when the referenced cells contain text or special characters.

The solution is to find and remove the special characters using the following instructions:

  1. Create an additional column TEST right next to the data column you want to calculate. Use the ISTEXT function to check.

Common #VALUE errors in Excel and how to fix them (Figure 7)

  1. Press the Enter key and then drag the mouse pointer to the remaining positions to display the test results.

Common #VALUE errors in Excel and how to fix them (Figure 8)

  1. Check and correct the cells with a TRUE result. If Excel gives you the desired result, it means the error has been fixed.

3. #VALUE error in Excel when using mathematical symbols

This #VALUE error often occurs with the plus sign (+) and the multiplication sign (*).

The solution is to use the SUM function for addition and the PRODUCT function for multiplication.

Follow the examples below:

  • To calculate the sum for the cells from F6 to F13, instead of entering the formula as =F6+F7+F8+F9+F10+F11+F12+F13, you should use the function =SUM(F6:F13).

Common #VALUE errors in Excel and how to fix them (Figure 9)

  • To calculate the product of the cells from A2 to C2, instead of entering the calculation as =A2*B2*C2, you should use the function =PRODUCT(A2,B2,C2).

Common #VALUE errors in Excel and how to fix them (Figure 10)

Learn more: How to use Excel: 5 basic functions for beginners

4. #VALUE date error (DAYS function)

The #VALUE date error occurs when the date data you enter into Excel does not match the default date format on your computer. For example, the format on your computer may be dd/mm/yyyy, but in Excel, it is dd-mmm-yy. This lack of consistency results in the #VALUE error.

The solution is as follows:

  1. Press the Windows key on the keyboard and search for and access the Control Panel dialog box.
  2. Open the Control Panel dialog box and select Clock & Region.

Common #VALUE errors in Excel and how to fix them (Figure 11)

  1. In the Region section, select Change date, time, or number formats to open the date format editing dialog box.

Common #VALUE<a href='https://meo.tips/featured/what-are-the-drawbacks-of-optimizing-miui-how-to-disable-miui-optimization-on-xiaomi-phones/' rel='dofollow' class='onpage-seo' > errors</a> in Excel and how to fix them (Figure 12)” style=”display: block; margin-left: auto; margin-right: auto;”></p> <ol start=

  • Select Format and in the Short date section, choose the format that matches the date data you want to enter. Click Apply and then click OK.

    • Common #VALUE errors in Excel and how to fix them (Figure 13)

    1. Go back to Excel, perform the date functions, and if you no longer see the #VALUE error, it means the error has been fixed.

    Above are just a few common VALUE errors in Excel, and there are many other errors caused by different reasons. I hope these tips and instructions will help you use Excel more effectively.

    RELATED ARTICLESMORE FROM AUTHOR