How to Fix Common Formula Errors in Microsoft Excel

Formulas can be extremely useful tools for managing, evaluating, searching for, and analysing data. However, complications may arise when formulas are used. The following is a list of some of the more typical formula errors that can occur in Microsoft Excel, as well as resources that can provide more assistance.

Read Also: How to Make a Bar Graph in Microsoft Excel

How to Fix Common Formula Errors in Microsoft Excel

If you type a formula into a cell and then hit the Enter or Return key, you might receive one of the following errors instead of the result you were expecting to see in the cell. There are other kinds of errors besides those listed here, but these are some of the most typical mistakes that can occur using formulas in Excel.

Error: #####

This is arguably one of the Excel problems that can be fixed with the least amount of effort. You will see ##### in a cell if there is data in that cell but the column is not large enough to display that data properly.

To correct this, drag the letter heading to the right, which will widen the column. Alternately, you may accomplish the same thing by simply double-clicking the arrow with two heads that is located on the right side of the column. This modifies the width such that it is large enough to fit the longest string of data that is contained in that column.

Error: #DIV/0!

You are going to see the #DIV/0! if you are dividing by zero or a blank cell, there will be an error. If you haven’t entered all of your data before creating the formula, then you run the risk of this happening easily.

In this example, we are going to divide cell B2 by cell C2, and cell C2 will remain blank.

Verify that the value in the cell you’re working with is not blank or zero by entering a value into it. You can also use the IFERROR function to ensure that, in the event that an error occurs, the programme will return a result of your choosing rather than the #DIV/0! error.

Error: #N/A

When you try to use lookup functions such as XLOOKUP, HLOOKUP, VLOOKUP, or MATCH, you will receive this error if the formula is unable to locate a suitable match.

In the calculation that follows, we can see that the XLOOKUP function is being used, however the lookup value (20735) does not appear in our data set.

Check your data to see if the value you’re looking up is there, or double check that you entered the right number into the calculation. The problem can be fixed by doing one of these things.

FAQs

How do I get rid of red marks in Excel?

Choose the cells that have a fill colour or fill pattern already applied to them. Check out the article titled “Select cells, ranges, rows, or columns on a worksheet” for more information on how to select cells in a worksheet.
Click the arrow next to the Fill Color drop-down menu on the Home tab, then click No Fill to remove the fill from the text box.

What does a red box in Excel mean?

If there is a red box around an element of the document, it indicates that the element is an image.

How do you clear formatting in Excel?

Organizational Clarity

Mark in bold the area of the spreadsheet whose format you want to remove and then click the Remove Format button. Choose the Home tab from the menu. Choose Clear from the drop-down menu located in the Editing section of the Home tab. Choose Clear Formats from the submenu that appears when you click the Clear button.

What does a red flag in Excel mean?

When there are user comments in a particular worksheet cell, they will appear as red triangles.