• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

How to resolve error functions in Excel

0 Comments

How to resolve error functions in Excel. Microsoft Excel is popular due to its useful automated calculation features that users can operate with various functions and formulas. However, while using formulas in Excel, encountering errors is unavoidable. In this article, UniTrain will introduce you to some common errors in Excel:

– #DIV/0! Error

– #N/A Error

– #NAME? Error

– #NULL! Error

– #REF Error

1. #DIV/0 Error in Excel

#DIV/0! occurs when working with a formula that divides two values and the divisor (the number being divided by) is zero. It is an abbreviation for dividing by zero error. For example, 90 divided by 0 as shown in the figure below:

How to resolve error functions in Excel

How to resolve error functions in Excel

To resolve this issue, you need to divide by cells with values other than zero. However, when there are empty cells in the worksheet, users can use the IF function as shown below. Use the IF function to avoid #DIV/0 Error by:

Step 1: Assume there is an error as shown in the figure below.

How to resolve error functions in Excel

How to resolve error functions in Excel

Step 2: Use the IF function.

How to resolve error functions in Excel

How to resolve error functions in Excel

2. #N/A Error

This error means “no value” or “not available”. It indicates that the formula cannot find the value that the user wants it to return. For example, using Excel’s VLOOKUP, HLOOKUP, MATCH functions, and LOOKUP function, users may encounter this error if the referenced value is not found in the data source as an argument. Let’s take a look at the example below.
The user has entered “Roll No. of Students” as a number, but the student’s roll number is saved as text in the data source. Therefore, the #N/A error appears.

To fix this error, we can either enter the number as text or use the TEXT formula in the VLOOKUP function.

How to resolve error functions in Excel

How to resolve error functions in Excel

Users can use the TEXT function in the VLOOKUP function for the lookup_value argument to convert the entered numbers into text.

How to resolve error functions in Excel

How to resolve error functions in Excel

How to resolve error functions in Excel

How to resolve error functions in Excel

Users can also use the IFERROR function in Excel to display a message if VLOOKUP cannot find the referenced value in the data source.

3. #NAME? Error

This error is displayed when the user misspells the function name.

How to resolve error functions in Excel

How to resolve error functions in Excel

How to resolve error functions in Excel

How to resolve error functions in Excel

For example, misspelling the VLOOKUP function leads to a #NAME? error in Excel. The only way to fix this error is to enter and check the correct function name.

4. #NULL! Error

This error often appears when a cell reference is not specified correctly. Users encounter this error when they do not use white space characters properly. The white space character is called the “intersect operator,” which specifies the intersection range at any cell. In the image below, the user used a white space character, but the ranges A2:A12 and B2:B12 do not intersect, which is why this error is displayed.

error functions in Excel 9In the image below, the user can see the sum of the range B2:B12 displayed in cell D2 because, while specifying a range for the SUM function, the user selected two references (with white space), overlapping each other for the range B2:B12. That is why the sum of the range B2:B12 is displayed.

error functions in Excel 10The #NULL! error can also be displayed when using the intersect operator (white space) instead of:
Mathematical Operator (Plus Sign) to calculate the sum.
Range Operator (Colon Sign) to specify the starting and ending cell for a range.
Union Operator (Comma Sign) to separate individual cell references.

5. #REF! Error

This error is an abbreviation for the reference error. This error often occurs when:
– The user accidentally deletes a cell that was referenced in a formula.

– The user cuts and pastes a cell that was referenced to different locations.

error functions in Excel 11When the user deletes cell B7, cell C7 will shift to the left to replace B7, and the user encounters a reference error in the formula when deleting one of the referenced cells.

Rate this post
29
254 Views
How to create a table of contents for sheets in ExcelPrevHow to create a table of contents for sheets in ExcelMarch 27, 2023
Quick fix for Excel file not saving errorMarch 27, 2023Quick fix for Excel file not saving errorNext

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Windows 11 Home Key Global 2021 Windows 11 Home Key Global 2021
    Rated 5.00 out of 5
    $6.00
  • Windows Server 2022 Remote Desktop Services User Connections (50) Cal Key Global Windows Server 2022 Remote Desktop Services User Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $22.00
  • AVG Ultimate 2021 with Antivirus + Cleaner, Secure VPN 10 Devices 2 Years AVG Ultimate 2021 with Antivirus + Cleaner, Secure VPN 10 Devices 2 Years
    Rated 5.00 out of 5
    $47.00
  • Avast Premium Security 2021 10 Devices 1 Year Global Avast Premium Security 2021 10 Devices 1 Year Global
    Rated 5.00 out of 5
    $28.00
  • Avast Ultimate Suite 2021 3 Years 10 Devices Global Avast Ultimate Suite 2021 3 Years 10 Devices Global
    Rated 5.00 out of 5
    $90.00
Products
  • Microsoft Project 2016 professional CD Key Global Microsoft Project 2016 professional CD Key Global
    Rated 4.97 out of 5
    $9.00
  • Windows Server 2022 Remote Desktop Services User Connections (50) Cal Key Global Windows Server 2022 Remote Desktop Services User Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $22.00
  • Microsoft Office Professional Plus 2010 retail Microsoft Office Professional Plus 2010 retail CD Key Global
    Rated 4.90 out of 5
    $11.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.71 out of 5
    $99.00
  • Windows Server 2022 Datacenter Key Global Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $7.00
  • Windows Server 2019 Datacenter Key Global Windows Server 2019 Datacenter Key Global
    Rated 4.75 out of 5
    $7.00
  • Buy Office 2021 Professional Plus Key Global For 5 PC Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $68.00
  • Microsoft Visual Studio 2019 Professional Key Microsoft Visual Studio 2019 Professional Key
    Rated 4.84 out of 5
    $15.00
  • Windows Server 2016 Datacenter Key Global Windows Server 2016 Datacenter Key Global
    Rated 4.74 out of 5
    $10.00
  • Microsoft Visio Standard 2019 Key 1PC Microsoft Visio Standard 2019 Key 1PC $12.00
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.