Table of Content
    MS Office File Repair

    How to Fix the #Value! Error in Excel?


    Table of Content

      Summary: #Value! is a common error that occurs when using formulas in Excel. It can be due to an issue with the cells you are referencing or use of formulas in the wrong type or format. This blog will discuss some cases when this error may occur and the solutions to fix the issue. You’ll also find about an Excel repair software that can help fix the error if it has occurred due to corruption in Excel file.

      You may experience the #Value! error in Excel when trying to enter invalid data type into the formulas. Sometimes, it appears when a value is not the expected type or when dates are given a text value. This Excel error may occur due to several reasons. However, the exact cause of this error is difficult to find. Below, we will be discussing some cases where you may get this error and the solutions to resolve the issues. 

      Case 1: Wrong Argument Data Type in Formulas

      Sometimes, Excel throws the “#Value!” error if it recognizes incompatible arguments in the formulas.

      For example: The Date function in the sheet expects only numerical values as arguments. In the below image you can see that when the formula’s string value is used in the month (January), it resulted in the #VALUE! error.

      Image of #Value! error in Date Function

      Solution

      To fix the issue,

      • Double-click the formula to verify the type of arguments.
      Image of Solution to fix #Value! error in Excel
      • Correct the argument in the cell (B2).
      Image of Correcting Argument In Cell to fix #Value! error in Excel

      The formula will work as expected.

      Case 2: Using the Basic Subtraction Formula

      Users often experience the #Value! error, when using the basic subtraction formula in Excel. 

      Image of #Value! error in Excel in Subtraction Formula

      Solution

      Check the formula and the type of values in the cell. If these are correct and the error persists, then follow these steps:

      Image of Correcting Basic Subtraction Formula to fix #Value! error in Excel
      • Go to the Start button on Windows, type Control Panel, and double-click on it.
      • Click Clock and Region > Region.
      Image of Clock And Region Window in Control Panel to #Value! error in Excel
      • On the Format tab, click Additional Settings.
      Image of Region Window For Additional Settings
      • In the Customized Format window, search for List Separator.
      Image of Customize Format Window
      • Check if the List Separator is set to minus (-). Change it to comma (,).
      Image of Apply List Seperator In Customize Format Window
      • Click OK.
      • Now, open the Excel file and again try to use the formula.

      Case 3: Wrong Text Value

      The #Value! error can also occur due to the formula’s wrong value.

      For example: If you are using the formula to add values in cells and Excel recognizes the unexpected text value, you may get a #Value error.

      Image of #Value! error in Excel because of Wrong Text Value

      Solution

      To fix the issue, you can correct the value or use the SUM function. It is recommended to use functions instead of operations to reduce the errors. In Excel, the formulas with math operators may not able to calculate the text in the cells. The SUM function automatically ignores the text value(er), calculates everything as numbers, and displays the result without the #Value! error.

      Image of Highlighting Arguments Of-Sumfunction to fix #Value! error in Excel

      Case 4: Blank Space in Cells

      You may get the #Value! error if your formula refers to other cells with space or hidden space. Sometimes, spaces that make a cell display blank but actually they are not blank.

      Image of #Value! error in Excel because of Blank Space

      Solution

      You can either delete the space or replace the blank space. Here’s how:

      1. Delete the Blank Space

      First, check if a cell is blank or not. To do this,

      • Select the cell that looks blank.
      • Press F2.
      Image of Blank cell Not Showing Space and hence the #Value! error in Excel

      The blank cell won’t show space.

      Then, press the Backspace key to delete the space. It will fix the error. 

      Image of space removed to fix the #Value! error in Excel

      2. Replace Blank Space

      You can also use the “Find and Select” option to replace the blank space in Excel. Here are the steps:

      • Open the Excel file that shows #Value! error.
      • On the Home tab, click Find & Select > Replace.
      Image of Find And Select Option
      • In the Find what field, type a single space and delete everything in the “Replace with” field.
      Image of Find And Replace Window
      • Click Replace All > OK.
      Image of Result After Replacement With Find-And Select Window

      Case 4: Problem with Network Connection

      Many users have reported experiencing errors when using Excel online due to problems with the network connection.

      Solution

      Check your Internet connection and see if it is working properly.  

      Case 5: Wrong Formula Format 

      If you enter the wrong formula with a missing parenthesis or comma, then Excel can throw the #Value! error. The error can also occur if the application finds a special character within a cell.

      Solution

      Correct the formula and use the ISTEXT function to find the cells with issues.  

      Case 6: Corruption in the Excel File

      If none of the above works, then it indicates the Excel file is corrupt. The formulas in the Excel file do not work due to corruption. 

      Solution

      You can use the Open and Repair utility in Excel if you are getting the error due to corruption in Excel file. In case the utility fails or the Excel file is severely corrupt, you can use a third-party Excel repair software, such as Stellar Repair for Excel. It is a powerful tool to repair corrupted or damaged Excel files and recover all its data, with 100% integrity. The tool supports Excel 2019, 2016, and older versions. 

      Closure

      There are several reasons that can trigger Excel to throw the #Value! error. It can occur if there is an incorrect argument data type in formulas or blank space, text, or special characters within a cell. This blog discussed the possible scenarios when this error occurs. You can apply the solutions mentioned above to fix the error. If the #Value! error occurs due to corruption in the Excel file, then you can use Stellar Repair for Excel. It is a reliable tool that helps in fixing corruption-related errors in Excel. 

      Was this article helpful?

      No NO

      About The Author

      Monika Dadool linkdin

      Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing. She is a tech enthusiast and expert who specializes in writing about SQL Server, MySQL Server, MariaDB Server, Microsoft Access, Active Directory, email recovery, Microsoft 365, pattern recognition, machine learning, data recovery, file repair, and operating systems like Linux, Windows, and Mac. She also writes about accounting software such as QuickBooks and Sage 50, as well as web-scripting languages like HTML, JavaScript, Python, PHP, Visual Basic, ASP.NET, and AJAX. Monika is passionate about researching and exploring new technologies, and she enjoys developing engaging technical blogs that help organizations and database administrators resolve various issues. When she's not creating content, you can find her on social media, watching web series, reading books, or exploring new food recipes.

      Leave a comment

      Your email address will not be published. Required fields are marked *

      Image Captcha
      Refresh Image Captcha

      Enter Captcha Here :

      Related Posts

      WHY STELLAR® IS GLOBAL LEADER

      Why Choose Stellar?

      • 0M+

        Customers

      • 0+

        Years of Excellence

      • 0+

        R&D Engineers

      • 0+

        Countries

      • 0+

        PARTNERS

      • 0+

        Awards Received