How to Fix a Value in Excel with 6 Methods

"Every single cell says #value! but none had formulas. Since everyone at work uses the same spreadsheet, I'm not sure what went wrong because nobody has admitted to messing it up. Originally, there were just phrases or plain numbers in the cells. I tried it before, thus there is sadly no previous version to go back to. I am using Excel 2016."

Imagine you're working on a shared spreadsheet at work, and suddenly, every cell that used to have plain numbers or words now shows #VALUE! There's no old version to revert to, and no one admits they messed it up. If you're using Excel 2016 and facing this issue, don’t worry! We've got six simple and proven fixes to help you how to fix a value in Excel. Let's dive in!

Part 1. Understanding Excel #VALUE Error?

The #VALUE! error, a frequent occurrence in Excel, is a sign that something is amiss with your use of a function or formula. Instead of producing the expected output, Excel displays #VALUE! to indicate a problem that requires attention.

So, what are the usual suspects behind the #VALUE! error? Let's take a quick tour.

• Incorrect Data Types. If you use a formula that expects numbers but has text in one of the cells, Excel will return a #VALUE! Error. For example, if you try to add text and a number, you'll get this error.
• Spaces or Hidden Characters. Sometimes, cells might look empty but contain spaces or other hidden characters. These can cause Excel to display the #VALUE! Error.
• Incorrect Formula Arguments. If the arguments (inputs) you provide to a function are not what it expects, you might see a #VALUE! Error. For instance, entering text where a function expects a number will result in this error.
• Array Formulas Issues. When working with array formulas, if the array is not handled correctly or if the ranges are mismatched, you might encounter the #VALUE! Error.
• Text Formatted as Numbers. If numbers are stored as text, Excel might be unable to perform calculations with them, leading to a #VALUE! Error.

Understanding these causes can help you diagnose and fix the #VALUE! Error when it appears in your spreadsheet. In the next few parts, we'll look at how to fix a value in Excel and get your file back in working order.

Part 2. How to Fix a Value in Excel in 6 Methods?

Dealing with the #VALUE! Errors in Excel can be frustrating. In this part, we'll look at how to fix a value in Excel and get your file back in working order.

Fix #1. Fix Value in Excel Formula Using Repairit

Wondershare Repairit - File Repair

Try It Free Try It Free

1,010,994 people have downloaded it.

• Repair damaged Excel files with all levels of corruption, including blank files, files not opening, unrecognizable format, unreadable content, files layout changed, etc.

• Repairs all kinds of data stored in damaged Excel files such as tables, charts, formulas, comments, images, etc.

• Support all formats of Excel files: XLSX/XLSM/XLTX/XLTM/XLS

• Perfectly repair corrupted files with a very high success rate, without modifying the original file.

• No limit to the number and size of the repairable files.

• Support Windows 11/10/8/7/Vista, Windows Server 2003/2008/2012/2016/2019/2022, and macOS 10.10~macOS 13.

One effective way to fix the #VALUE! Error in Excel is to use Repairit Excel File Repair. This software repairs corrupted Excel files, ensuring that your formulas are restored correctly. Repairit is highly effective in handling various types of Excel file corruption, which often cause errors like #VALUE! It allows for multi-file repair and supports multiple Excel versions (including 2019, 2016, 2013, 2010, 2007, and Office 365). This tool also has received positive reviews from users who recovered their corrupted Excel files.

Using Repairit Excel File Repair is as follows:

Step 1. Download and open Repairit, and then go to the "File Repair." Hit "Add" to upload your #VALUE! In Excel files. Repairit allows you to upload several damaged #VALUE! Excel files at once.

Step 2. Click "Repair" to start the repair procedure. You may observe how Repairit recovers your #VALUE! Excel files.

Step 3. Click "Preview" next to the icon of any file to view the results. If you're happy with it, hit "Save" to save the recovered #VALUE! Excel file to a designated location on your Windows.

Fix #2. Use Excel Functions to Fix Value in Excel Formula

Use Excel's built-in functions instead of writing formulas with operators. Excel functions are typically designed to ignore non-numeric values.

Here's an example:

Step 1. Check if you use wrong functions. Like in cell B2, there's a dash (-), and it's not empty. If you write the formula: = A2 B2, you'll get a = A2 B2 error because Excel can't multiply 5 by a dash (-).

Step 2. If you use the PRODUCT function like this: = PRODUCT(A2, B2), The PRODUCT function will return 5 without giving a #VALUE! Error.

Fix #3. Fix Excel Value by Replacing the #VALUE Error

Sometimes, you anticipate a #VALUE! error occurring. The issue isn't that Excel returns an error but the #VALUE! error makes your worksheet look messy.

In such cases:

Step 1. You can easily replace it using the IFERROR function. For instance, if the following function returns a #VALUE! error, you can wrap it in the IFERROR function like this:

The IFERROR function will replace the #VALUE! error with a dash (-). You can specify any value instead of a dash, as desired.

Fix #4. Use “Evaluate the Formula” to Fix a Value in Excel

If you have tried all the manual fixes above and nothing works, let Excel evaluate the formula to identify the issue.

Step 1. Select the formula that returns the #VALUE! error, then go to the Formulas tab and choose Evaluate Formula. Excel will evaluate the formula step-by-step and highlight what's causing the error.

For example, Excel might show that 50 and 100 are added to make 150, but the next step highlights that the text is causing the error.

Step 2. Remove the identified cause of the error and rerun the formula to see if it works.

Fix #5. Check for Any Special Characters

Check your data for non-numeric values, such as text or special characters. Sometimes, these can be hidden, like space characters, which you might not easily differentiate from blank cells in Excel. To find them, utilize the ISTEXT function. For example:

Step 1. Cell A6 may appear empty but contains a space character. The ISTEXT function will return TRUE (indicating the cell contains text).

Step 2. Once you have identified the cells with space characters, clear them out. Then, try running your formulas again, and you'll likely avoid the #VALUE! error in your spreadsheet.

Fix #6. Check the Format of Your Data

For example, in the image below, we used the WEEKDAY function to find the day of the week for a given date. However, it returned a #VALUE! error because Cell A1 contained the date in a general format, which isn't recognized as a valid date for the function.

To fix this:

Step 1. Change the cell's format to Date.

Step 2. Now, try applying the WEEKDAY function again. Great! The #VALUE! error is gone.

Part 3. How to Avoid Excel #VALUE Error?

The #VALUE! error can be a hassle, but by taking a few simple steps, you can avoid it and keep your Excel files running smoothly. Here are five tips to help you prevent the #VALUE! error in Excel:

1. Validate Your Data Regularly: Ensure your data is accurate and consistent before applying formulas. Then, regularly check for and fix any inconsistencies in your data.
2. Handle Empty Cells Carefully: Be cautious when working with formulas that might include empty cells, as these can cause #VALUE! errors.
3. Use Text Functions for Non-Numeric Data: When working with text, use Excel’s text functions rather than formulas with operators. Text functions are designed to handle text data correctly.
4. Document Your Formulas: Keep notes or comments explaining what your formulas do. This can help you or anyone else understand and troubleshoot the formulas later.
5. Test Formulas with Sample Data: Before applying complex formulas to large datasets, test them on a small sample to identify potential issues.

Conclusion

Correcting the #VALUE! Making an error in Excel doesn't have to be difficult. With the right approaches and patience, you can resolve these errors and prevent them from happening. Remember to keep your data clean, validate your formulas, and document your work to simplify troubleshooting. Hope you had a great learning on how to fix a value in Excel!