Troubleshooting Excel Formulas/Functions

(1)

Troubleshooting – Excel 2016

Troubleshooting – Excel Formulas/Functions

This guide will cover the following:

1. Avoid using complex formulas/functions 2. Resolve common errors

3. Let Excel assist you

4. Resolving errors using the Trace Error button 5. Error handling functions

6. Error checking functions

7. Checking & Fixing the errors – The Formula Auditing commands 8. Changing how formulas are calculated and checked in Excel 9. Updating Links in Formulas/Functions

10. Resolving Broken Links

In this document, we will learn how to troubleshoot our formulas/functions in Excel.

Avoid using complex formulas/functions

We should always try to simplify our formulas, for two reasons:

1. Complex cell formulas are error prone, for example when we fail to close our nested functions with the appropriate brackets/parentheses.

2. Complex cell formulas are tedious to edit, in the event we need to make changes to the cell references in a formula.

But how can we simplify our cell formulas, then? One good practice is to 'break down' our cell formulas into multiple cells by making use of cell references.

For instance, say we would like to sum the products of multiple differences. To do these in a single cell would increase our chances of error, especially when we need to pay closer scrutiny to the order of operation (BEDMAS) rules for mathematical operators. Instead, we can do this in three series of cells.

• The first series of cells would calculate the differences between our raw data.

• The second series of cells would refer to the first and calculate the product of these differences.

• Lastly, the third series of cells would refer to the result of the second and sum them up together.

By 'delegating' our complex operations to multiple cells, we can make our formulas leaner and simpler both for ourselves and for anyone encountering our formulas for the first time, such that they're less error prone and easier to edit should there be any change.

Alternatively, there are a few ways we recommend when to avoid using complex formulas:

Problem Using Complex Formulas Using an Easier Solution Cell referencing across

multiple sheets for a cell range for analysis

Using a complex cell reference involving the path name of the workbook, its worksheet name and the cell range.

Use Pivot Tables or conditional IF functions.

Cell referencing a

category of cells Using the nested IF function. Use Pivot Tables or Advanced Filter command.

VLOOKUP across

multiple sheets Using the nested VLOOKUP function. Use Pivot Table or other lookup functions.

You would have noticed that Pivot Tables are a great way to avoid creating complex formulas!

(2)

Page 2 of 13

Double-check your formula/functions

• Some good practices are shown in the table found in the Appendix - Good Practices to avoid formula/function errors.

• You may wish to check all your formulas in your worksheet. On the Formulas tab under Formula Auditing group, click on Show Formulas command.

• Correct these errors using the table found in the Appendix- Good Practices to avoid formula/function errors.

If you still have trouble resolving the error(s), read the next few sections to find an appropriate solution.

Let Excel assist you

• When you type in a formula that does not follow its syntax rule, Excel identifies it and

recommends an immediate solution on your behalf. You can choose either to accept or reject this correction.

Example

If you typed in =2+*3 , it is not a valid formula. Hence, Excel recommends to correct your formula to

=2+3 .

You may click Yes or No to accept or reject this correction.

If you reject the solution, another dialog box will open:

• Unfortunately, Excel still does not recognize what you are trying to do in that cell. You may click Help to get help from Excel.

• To acknowledge and resolve the error on your own, click OK. You will return to the cell with the broken formula in edit mode, and Excel highlights the part where an error has occurred.

Excel can only check for obvious errors such as syntax errors. Beyond that, we will need to double-check out formulas whenever it is necessary to do so.

(3)

Resolving errors using the Trace Error button

When there is an error in the formula/function, the cell will show (i) A small green triangle in the top left corner of the cell, and (ii) return an error value.

(i) The small green triangle in the cell will look like this:

This indicates that the cell’s content has broken one of the error checking rules in Excel.

By default, the default error checking rules are ticked below:

Example

Select the cell containing the Error Value. In our example, it is the cell C14.

This will reveal the Trace Error button: . If you place your cursor over it, an explanation of the cell’s Error Value, and a downward-facing arrow for the Error Menu will show up:

(4)

Page 4 of 13

You may click on the downward-facing arrow to reveal the Error Menu:

The first option shows the type of error in the cell. In our example, the cell D14 has an Error Value

#DIV/0 , which is a “Divide by Zero Error”.

Below is a table of the options found in the Error Menu and its description:

Option Description

Help on this Error Opens the Help window with the Error Type of the active cell, and provide solutions to resolve the error.

Show Calculation Steps Opens the Evaluate Formula dialog box, showing each step of your calculation and its result for the active cell.

Ignore Error

Excel error checking rules will be ignored for the selected cell.

Hence, the error alert and Error Menu will be removed from this cell.

Edit in Formula Bar Allows you to edit your formula/function in the Formula Bar.

Error Checking Options Opens the Formulas tab of the Excel Options dialog box, where you may change the options accordingly.

For additional assistance in resolving the error, click Help on this Error in the Error Menu, or press F1 on your keyboard.

(5)

(ii) Error values in Excel tell us why formulas or functions do not work. These error values help us to trace back where we made our calculation error and we should not ignore them.

Here is a table of the error values and what their description.

Error Value Error Type Description

#DIV/0! Division by zero

Look at the sign after #DIV. It’s /0 .

Argument(s) in your formula cannot be divided by 0. The value 0 may come from a constant 0, or a cell reference that evaluates to 0 or is blank.

#N/A Value not available

Usually, there does not exist an error in the formula itself.

There is no value available to return in a cell.

This error occurs frequently with VLOOKUP and HLOOKUP functions.

If you do not want to see this error value, you may replace it with a text string instead. This will make it easier to

understand why a cell returns #N/A

(Tip: Use the nested IF function or IFNA function)

#NAME? Invalid Name

- You have referenced a wrong cell or cell range

(Eg. You have spelt argument(s) incorrectly in your formula/function.)

- You did not enclose text string(s) with quotation marks (“ “).

#NULL! 2 cell ranges do not intersect

A space between 2 cell ranges is read as an intersection.

Replace the space with the following:

- comma (union operator): specifying 2 cell references - colon: between the starting cell and the ending cell of 1 cell reference

#NUM! Invalid number A number in the formula/function is invalid, as it is too small or large for Excel to calculate.

#REF! Invalid cell reference

This commonly occurs when you change the cell reference in a formula/function, or change the value of the referenced cell itself.

#VALUE!

Invalid argument or

operator

This commonly occurs when we try to perform mathematical operations involving text arguments.

All cells containing error values will populate a small green triangle in the top-left corner of the cell, except for the error value #VALUE!.

Tip!: If the data in your worksheet contains many error values, go to the Formulas tab under the Formula Auditing group, and select the Error Checking command.

(6)

Page 6 of 13

Error Checking Functions

An error checking function is the function ISERROR, which will return either TRUE or FALSE.

Error Function Syntax Description

ISERROR =ISERROR(value)

Checks whether a value is an error, except for

#N/A, and returns either TRUE or FALSE.

Alternatively, you may use Show Calculation Steps in the Error Menu.

Error Handling Functions

You may not want to see the error value when there is an error in your formula/function. In this case, you can use error handling functions, which will allow you to return a specified value instead (Recall the function IF).

IFERROR =IFERROR(value, value_if_error) Returns value_if_error if argument is an error, otherwise it returns the argument itself.

The IFERROR function is similar to IF(ISERROR()). Try using them in the Excel practice file provided!

Both functions should return the same result.

Tip!:You can use Conditional Formatting (covered in Module 5) to highlight a cell containing an error.

1. Select a cell range you want to check for errors.

2. On the Home tab under the Styles group, select the Conditional Formatting command and click Manage Rules. A Conditional Formatting Rules Manager dialog box will appear.

3. Select New Rule. This will open a New Formatting Rule dialog box.

4. In Select a Rule Type, click Format only cells that contain.

5. In Edit the Rule Description under the Format only cells with, click on Errors.

6. Click on Format… to choose a format for the cells containing Errors. When you are done, click OK.

7. Click OK again.

You should see the cells containing an Error Value highlighted in your worksheet.

Functions to check a cell's content

There are also several functions useful to check the content of a cell, such as its number format or if it is a blank cell:

ISNUMBER =ISNUMBER(value)

Checks whether a value is a number, and returns either TRUE or FALSE.

ISTEXT =ISTEXT(value)

Checks whether a value is a text, and returns either TRUE or FALSE.

ISBLANK =ISBLANK(value)

Checks if a cell is empty, and returns either TRUE or FALSE.

(7)

Checking & Fixing the errors – The Formula Auditing commands

• You can use the commands in the Formulas tab under the Formula Auditing group to assist you in checking and fixing the errors in your formula/function.

You may use the commands according to your needs.

Command Description Keyboard

Shortcut Trace Precedents Makes active the cells that are involved in the calculation of the

formula/function of the selected cell. Ctrl+[

Trace Dependents Makes active the cells that have referenced the selected cell, and

their calculations are affected by the selected cell. Ctrl+]

Remove Arrows Remove all arrows after using the Trace Precedents or Trace

Dependents command.

Show Formula Displays the formula instead of the result in the cells itself, for

the entire worksheet. Ctrl+~

Error Checking

Check for common errors when creating a formula/function.

Click on this command to reveal an Error Checking dialog box.

Click on the relevant options to resolve your error. Show Calculation Steps... option will open the Evaluate Formula dialog box.

In the drop-down menu of the Error Checking command, clicking on Trace Error option is similar to Trace Precedents command.

Evaluate Formula

Select this command and an Evaluate Formula dialog box will appear.

Use this command to resolve a complex formula/function, or nested formulas/functions

Evaluating the formula step-by-step will make it easier to trace the source of the error.

Watch Window

Add cells to the Watch Window list to keep track of the values in these cells, whilst you work on other parts of your worksheet.

The Watch Window is similar to a pinned email or note, which allows you to place special attention to it over the others.

(8)

Page 8 of 13

Watch Window command - Checking your formulas/functions

If you select the Watch Window command in the Formulas tab, a Watch Window dialog box will appear.

The Watch Window allows you to check your formula calculations in cells. This is especially useful when you are handling a large data set in a worksheet, such that you do not need to spent time manually check your cells individually.

The Watch Window allows you to track the contents of a cell’s:

• Book: Its workbook name.

• Sheet: Its worksheet name.

• Name (eg. Cell D3)

• Value : The value it returns.

• Formula: Its formula in the cell (eg. =A3/B3)

Tip!: You may shift the Watch Window dialog box anywhere on your screen, or resize it according to your preference.

You can add cells as entries to the Watch Window:

1. Select the cells you want to watch.

(You may wish to specifically select all cells with formulas/functions in them. To do this, go to Home > Find & Replace. Select Go To Special, and click on Formulas.)

2. On the Formulas tab under the Formula Auditing group, click on Watch Window.

3. The Watch Window dialog box will open. Click on Add Watch and click Add. Note that one cell is limited to one watch.

4. To show an entry in the Watch Window dialog box, double-click on the entry.

You can also delete a cell’s entry from the Watch Window dialog box, select the entry and click Delete Watch.

If your cells have external references in other workbooks, you need to open these workbooks for you to add these cells to the Watch Window.

(9)

Changing how formulas/functions are calculated and checked in Excel

You can change the way Excel checks its formulas/functions in Excel. This is useful when you wish to perform an analysis that violates Excel calculation and checking rules.

1. Click on File > Options. An Excel Options dialog box will appear.

2. Click on the Formulas tab.

3. You will see the options for Calculation options, Error Checking and Error checking rules.

• Calculation options

In Calculation options under Workbook Calculation, by default it should be Automatic.

You may choose to change it to the other options as required.

• Error Checking – Turning it on or off

Excel checks for errors in your worksheet in the background. If there are errors, a small green triangle will appear in the top-left corner of the cell. Remember, error values are good indicators to help you resolve your error(s)! Hence, it is not advisable to turn this feature off.

But, if you do not want Excel to check for errors, you can turn this feature off. Simply untick Enable background error checking and click OK.

Excel should not check for errors in your workbook anymore, and the small green triangle in your error cells will disappear.

• Error Checking Rules

To change the rules, tick or untick the options under Error checking rules, and click OK.

(10)

Page 10 of 13

Updating Links in Formulas/Functions

• If your worksheet contains formulas having external references to other workbooks, Excel alerts you to update the reference. You can choose to Update or Don’t Update the links now.

In fact, when you open the Excel practice file for this topic, you will see this dialog box (As we referenced Sheet20 in one of our cells. Find it!).

• To disable this dialog box from appearing each time you open the workbook, 1. Go to File > Options > Advanced > General

2. Untick the option Ask to update automatic links

The next time you open your workbook, the dialog box should not appear on your screen.

Resolving Broken Links

• When we make cell references to external workbooks, this also creates room for another source of error. These errors occur when a workbook referenced to has changed in location (or path in technical terms) or no longer exists. Here's how to resolve them:

To fix an external reference,

1. Select on Edit Links under the Connections group on the Data tab.

2. Select the link you want.

3. Click on Change Source on the right-hand side of the window. Select the new workbook location and then click Open.

4. Close the Edit Links dialog.

• To remove an external reference, we access the Edit Links dialog as per above, but instead of selecting Change Source, we select Break Link. Immediately, we are prompted that formulas and external references will be converted to their existing values. Click Break Links once we are certain.

• Either way, whether it is in fixing or removing a link, our error should be resolved in both situations.

(11)

Appendix

Good Practices to avoid formula/function errors

Category Good Practices More information Recall the topic on…

Cell

References Checking cell references

Double-click on a formula/function.

Excel will highlight the cell references each in a different colour.

Cell References

Syntax Parentheses in formula/function

Make sure that you place the parentheses () at the correct place.

When you are typing a

formula/function, the parentheses are coloured differently for you to

distinguish a pair of open & closing parentheses from another.

Advanced formulas:

- The order of operations (BEDMAS)

Syntax Use a colon to enter a cell range in a formula/function

A colon (:) is used to separate the first and last cell in a cell range, when we are typing a cell reference. (eg. A1:C1)

Cell references

Syntax

Use a comma to enter 2 non- adjacent cell references in a

formula/function

A comma (,) is a union separator, used to indicate that there are 2 different cell or cell ranges that are being referenced in the formula/function.

You may also use a comma to represent adjacent cell references, although having just one cell range is a more efficient method.

(eg. A1:C1 instead of A1, B1, C1 .)

Cell references

Syntax Enter all required arguments

Enter all the required arguments according to the formula/function syntax.

The required arguments for functions will vary. To find out the required arguments, type in a cell: equal (=) and the function name. Excel will show the syntax of this formula and its required arguments.

Optional arguments will be inducated by square brackets around them in the

Lookup functions:

- The optional argument:

[range_lookup]

Logical Functions:

- The function IF

Syntax Enclose text strings in double quotation marks

Without the double quotation marks, Excel recognizes the text string as a function instead, and may return the Error Value #NAME? .

Text Functions:

- The CONCATENATE function and & to

form a sentence

Syntax

Enclose

workbook/worksheet names in single quotation marks

when referencing it

The only time you can omit the use of single quotation marks is when your worksheet/workbook name has no space characters in it.

To reference an external workbook more accurately, open the external workbook and use the point-and-click method to select the cell range you want from that workbook.

Your formula/function will populate the file path, worksheet name and cell reference in the correct syntax style.

Cell references:

- Using cell references across multiple

worksheets

(12)

Page 12 of 13

Cell References

Include a valid name & path when referencing external

workbooks

External references must be valid for Excel to locate the source of the workbook using its name and its path.

Cell references:

- Using cell references across multiple

worksheets

Typing Check for multiple lines

Did you know you can create multiple lines within a cell? Simply press Alt+Enter on your keyboard.

Because of this, when you are editing your formula/function, it may not be shown fully in the cell or the formula bar. To resolve this, change the cell size, use the Wrap Text command, or press the down arrow on your keyboard to check if your cell has multiple lines.

Basic Cell Formatting in Module 2:

- Wrapping text - Adjusting cell

alignment

Typing Use a maximum of 32,767 characters in a cell

Excel permits a maximum of 32,767

characters in a cell. -

Typing

Nest a maximum of 64 functions in a formula/function

Excel permits a maximum of 64 levels of nested functions in a

formula/function.

Instead of nesting many functions within a single formula/function, try to split your nested functions into several cells such that it is easier to edit your formula/function and its arguments in the future.

Logical Functions:

- The function IF

Typing

Check if there is an apostrophe (') at the start of

a formula/function

The apostrophe (') will cause the formula/function to be read as a text instead.

-

Syntax

Check that your formula/function starts with

an equal sign (=)

If your formula/function does not start with an equal sign (=), Excel will not recognize it as a formula/fuction.

Formulas & Functions:

- The syntax of a formula/function

Cell

Format Check the format of your cell

Sometimes, an error does not exist in a cell, you simply need to change it to the correct format using the

commands found under the Number group on the Home tab.

Cell Number Formats in Module 2

Cell Format

Enter numbers without formatting it

Use the Number Formatting tools you have learnt earlier, and not

- a dollar sign ($), which is used to lock a column or a row reference - a comma (,) which is already used as a seperator in formulas/functions within a single argument.

Cell

Format Check the format of your cell

If the results of your formula/function looks different from your

expectations, click on the cell and check its formatting in the Number Format box on the Home tab.

(13)

Others Avoid pasting the wrong values

A common mistake people make is in copying and cutting values from external sources. By default, when we transfer cells from one workbook to another, the cell formula/function is also lifted, together with its relative cell references. However, what happens when the destination cell lacks the relevant relative cell references to perform the function properly, or it so happens that we merely want to copy the values over?

Blindly copying data over, in this case, would result in a #REF! error.

To mitigate this error, simply choose to paste values only - if you do not require the formula - or manually edit the formula references in the

destination cell again - if you need to reperform the function in its

destination.

Cut, Copy & Paste in Module 2

Syntax Error Check – Circular References

When a formula/function referenced the cell that it is located in , a circular reference occurs.

Recheck your formula/function if the cell references are typed correctly.

Normally, circular references are for iterative calculations.

-