8 Common Errors in Excel & how to fix it 2023

8 Common Errors in Excel and how to fix it 2023


Struggling with Excel errors? Uncover the 8 most common Excel mistakes in 2023 and learn step-by-step how to fix them. From #DIV/0! to #SPILL!, our comprehensive guide makes Excel error troubleshooting a breeze. Click to become an Excel wizard today!


1. #DIV/0! 

Excel occurs when you attempt to divide a number by zero. Imagine trying to divide a pie into zero pieces. Impossible, right? This error appears when you are dividing something by zero.

  • Example: here A1 is 10, and B1 is 0.
  • If you enter the formula =A1/B1 in another cell, you’ll get the #DIV/0! error, because dividing 100 by 0 is mathematically undefined.

DIV

  • Resolution: To fix this error, you should ensure that the divisor (the value you are dividing by) is not zero. You could add a conditional statement to prevent division by zero. For example, you can use the following formula: =IF(B1=0, “Cannot divide by zero”,A1/B1)
  • This formula checks if B1 is zero; if it is, it returns the text “Cannot divide by zero”; if not, it performs the division.

if zero

2. #VALUE!

-Picture a lock without a key. This error shows up when Excel expects a specific type of data but finds something else. Perhaps a calculation is trying to multiply text with a number, creating a conundrum in the process.

  • Example: =A1*B1 where A1 is “text,” and B1 is 5.

value 1 value2

Now, if you attempt to multiply these two cells together using the formula =A1*B1 you’ll get the #VALUE! error. This happens because Excel expects numerical values for multiplication, but in this case, one of the values is text.

  • Resolution: To resolve this issue, you need to ensure that the cells being used in mathematical operations contain numerical values. In this specific case, you’d need to identify why text is present in cell A1 and either correct it to a numerical value or modify the formula to handle text appropriately.

3. #REF! 

What if you were following a map, and suddenly the road vanished? This is what happens when Excel finds a reference that doesn’t lead anywhere. It typically occurs when a cell reference is deleted or pasted over.

  • Example:= SUM(A1,B1,C1) where B1 was deleted.

delete

column deleted

  • Resolution: Replace or correct the deleted or incorrect reference. You would need to adjust the formula to reference valid cells or ranges. Depending on what caused the error, this might mean undoing the deletion, replacing the deleted reference with a valid one, or altering the formula to work with the remaining data.

4. #NAME? 

Imagine calling someone by a name they don’t recognize. Confusing, right? This error is seen when Excel doesn’t recognize text within a formula. Maybe a function name is misspelled or missing altogether.

  • Example: =SUMM(A1:A5) instead of =SUM(A1:A5)

SUMM

  • Resolution: Check for typos in function names or missing quotation marks around text. You can correct the error by fixing the typo in the formula. In this case, change SUMM to SUM(A1:B1)

SUM 2

5. #NUM!

It occurs when a numerical value in a formula or function is invalid. It can arise from a variety of situations, such as calculations that result in a number too large or small to be represented, or when using a function that expects specific constraints on its input values.

  • Example: A1: -4, B1 ‘=SQRT(A1)’
  • In this example, the formula in cell B1 is attempting to find the square root of -4. Since the square root of a negative number is not a real number, Excel returns the #NUM! error.

num

  • Resolution: You would need to correct the input to the square root function. If negative numbers might be present, you could use a formula like this to prevent the error:
  • =IF(A1>=0, SQRT(A1), “Input must be non-negative”) This formula checks if A1 is non-negative and only takes the square root if it is; otherwise, it returns a message explaining the issue.

if negative

6. #N/A

The #N/A error in Excel represents “Not Available” and usually occurs when a function like VLOOKUP, HLOOKUP, MATCH or XLOOKUP can’t find the value you’re searching for.

  • Example: =VLOOKUP(C1, A1:B3, 2, FALSE)
  • If cell C1 contains a number that’s not in the first column of the lookup range (A1:A3), like 4, the VLOOKUP function will return a #N/A error because it cannot find the number 4 in the lookup range.

NA RANGE 1

  • Resolution: Ensure the lookup value exists in the lookup range. Or  you can use the IFNA or IFERROR function to provide an alternative result when the lookup value is not found: =IFNA(VLOOKUP(C1, A1:B3, 2, FALSE), “Not found”)

IFNA

7. #NULL!

it occurs when you’re attempting to reference an intersection of two ranges that don’t actually intersect. This error is less common than some of the others, but it can still occur.

  • Example: Suppose you have the following data in cells: A1:A2 B1:B2
  • Here, Excel is trying to find the intersection between the range A1:A2 and the range B1:B2, but there is no intersection between these two ranges, so the formula returns the #NULL! error.

NULLNULL RANGE

  • Resolution:You should review the ranges you’re referencing to understand why you’re trying to find their intersection. If you meant to perform a different operation, you might need to replace the space with the appropriate operator, such as a comma to combine the ranges, a colon to reference a continuous range, or a mathematical operator like + or * to perform a calculation.

    For example, if you wanted to add the values in the corresponding cells of the two ranges, =A1:A2 + B1:B2

add

The #NULL! error in Excel is like trying to find the meeting point between two roads that run parallel and never intersect. By understanding the cause of the error and carefully selecting the correct operators for your intended calculation, you can steer clear of the #NULL! error and successfully analyze your data.

8. #SPILL! 

This error is associated with dynamic arrays, a feature introduced in Excel 365 and Excel 2019. This error occurs when a formula that returns an array result is blocked by something in the cells where it would normally spill over.

  • Example: =SEQUENCE(5,5) in a 5×3 range.
  • Resolution: Make sure there’s enough space for the resulting array.

Leave a Comment