Errors in “Formulas”

When we have typed a “Formula” in which there is an (potential) error, “Excel” will point this out by placing an error icon next to the cell. When we move the mouse over this icon, “Excel” will inform us what is wrong with the “Formula”. In this case, there is a value in the “Formula”, which is an incorrect data type because I tried to multiply text data.

When we click on this icon, we see a drop-down menu with different options depending on the error we made.

 

The options which are always mentioned are:

 

-“Help on this error”
-“Ignore Error”

-“Show calculation Steps”
-“Edit in Formula bar”
-“Error checking options”

When you click on the last, “Error Checking Options”, the “Options for Excel” dialog box will open with the “Formulas” tab selected.

Here, various rules for checking the error are shown. You are also able to make corrections, if you wish.

When we click “Ignore Error”, the error icon will disappear.

By clicking on the “Edit in formula bar” we can adjust the “Formula”.

 

Clicking on the “Error Checking Options ” in the “Formula Auditing ” tab in the “Ribbon” and selecting “Trace Error”, shows the cells used in the “Formula” by blue arrows:

To hide these arrows, you improve the “Formula” or click the “Remove Arrows” button

 

Changing “Formulas”

We may change the “Formula” in our cell at any time.

Just double-click the cell.

This opens the “Formula” in the cell, and shows us the functions plus the cell or “Range” of cells that had been used.

 

Select the portion of the “Formula” that you want to change, and type or click a cell to modify.

 

For example you can change the“Range” in a “Formula”, by clicking and dragging the corners and/or edges of the blue box around the “Range”.

 

 

 

“Trace Precedents”

We can trace each cell that is used in a “Formula” by selecting the source or target cell(s). When we do this, “Excel” will display a blue line, either from the target cell to the source cells, or from the source cells to the target cell, depending on the button you clicked in the toolbar. It is useful to know which cell gets its information from the “Formula” , especially when you work with big size files.

To know the source cells, first choose the “Formula”, and click the “Trace Precedents” button

In some cases, the source cells are also the “Formula” cells, by repeatedly clicking on the “Trace Precedents” button, we can see the actual source of cells:

If we also linked data in another “Worksheet” or another “Workbook”, select the linked cell, and click the “Trace Precedents” button.
A black dotted line with a “Worksheet” icon is seen. When we click the arrow, the dialog ‘Go’ appears in which we find a list of all linked cells.

Select one and click OK. The linked cell, or in this case the “Range” in the other sheet is immediately selected.

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats