Following certain guidelines when working in Excel can save you the headache of dealing with errors. And if something does go wrong with a formula, there are things you can still do. Let’s take a look at some recommended methods for creating formulas, and how to handle errors, should you encounter them.
Common Practices
Begin every formula with an equal sign (=)
Formulas typically start with the equal sign (=). Alternatively, you can begin with the plus sign (+), which will be automatically converted into an equal and plus (=+). However, this usage is only intended for backwards compatibility for Lotus 1-2-3 users. Equal and plus (=+) usage is often considered to make a formula confusing and dirty.
Match all opening and closing parentheses so they are in pairs
Usually Excel won’t allow you to enter a formula without the closing brackets, and will offer you a corrected version of the formula. Auto-correct works very well with simple formulas.
However, Excel may not be able help you with all use cases, especially with complex and long formulas. Luckily, Excel color codes all formula elements and you should pay close attention to that. Each time you open and close parentheses, the duo will have its own color. If you move your cursor onto one of those, both parentheses will be highlighted in bold.
Use quotation marks around text in formulas
A typical error when creating formulas is to type in strings without quotation marks. Without quotation marks, Excel will try to process the entered text as a formula, reference, or named range. This typically gives the #NAME! error as the software can’t recognize the text.
Advanced Features
Use Named Ranges and Tables
Instead of pointing direct references, it is often a good idea to use Named Ranges and Tables. This approach makes dealing with large data models much easier. Using productlist or products[name] is a lot more meaningful than a reference Sheet1!$B$2:$B$3584, especially when working across several tabs or workbooks.
Named Ranges can hold references and formulas. They will significantly improve the readability of your data models. You can assign named ranges by selecting the reference(s), typing in a name in the formula dialog on the left, and pressing the Enter key.
Tables are even better than named ranges when working with tabular data, mostly because they have a dynamic structure with added tools to navigate and filter individual columns. Tables also expand and shrink automatically when you add or remove data.
When using a Table, you can call individual columns, titles, or the entire table by typing in the corresponding names. For instance, entering Full Name in a formula will reference the first column below.
For more information about Named Ranges and Tables, please visit our Writing Efficient Formulas article.
Common Errors and a Workaround
Green Arrows and Formula Auditing
Excel will show a tiny green arrow at top-left corner of a cell if there is an issue with it.
Selecting the cell will display the Trace Error button (the button with exclamation mark) which will give an error description when you hover your mouse over.
Click the Trace Error button to see more information about the error and options to solve it. Clicking Help on this error will show you general guidelines for that particular error, whereas Show Calculation Steps will help troubleshoot the issue with Excel’s built-in help engine.
For more information about troubleshooting issues with Named Ranges and Tables, please see our Identifying and Analyzing Spreadsheets: Formula Auditing article.
Bypassing Errors - IFERROR Formula
IFERROR is a great way to handle errors. This formula tells Excel to do something else if there’s an error. For example, typos can cause mismatches in VLOOKUP searches and the IFERROR function will help handle those errors. IFERROR function will return a specified value when a formula returns an error.
IFERROR(value, value_if_error)
IFERROR has 2 parameters,
- value: The formula or cell reference containing the formula to be checked for an error.
- value_if_error: The value you want to return, when formula returns an error (i.e. #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
