An Introduction to Excel Formulas (Part 2)

Welcome to part two of three in this series of blog posts introducing Excel formulas. During this blog post, I will introduce Reference Operators, Operator Precedence, and Error messages.

First, let’s have a quick recap of what was introduced in part one of this series. Excel formulas are one of Excel’s most powerful and useful features. During part one, I discussed what  formulas are, why they are useful, and how they can be used. Part one also introduced one method of entering a formula into an Excel spreadsheet and the component parts that make up a formula (Operands, Operators, and Functions).

If any of the terms above seem unfamiliar or a little confusing, then I suggest you review part one of this series by clicking this link.

You can download the example spreadsheets used in this blog post by clicking the button below or by clicking the link here.

Reference Operators

Reference Operators are essential to Excel formulas because they allow you to reference a range of cells (often more than one cell) or a combination of ranges. There are three types of Reference Operators described in the table below.

Reference OperatorDescription
:Range Operator
,Union Operator
(space)Intersection Operator

Range Operator

The Range Operator is represented by the colon symbol ‘:’ and is used to identify a collection of cells that are between and include two cells. Okay, but what does this really mean? A range could look like (B1:B10), which refers to all cells in column B, between and including rows 1 and 10. This example is highlighted in the image below.

Range Operator Example 1

Equally, a range could be (B1:G1), which refers to all cells in row 1, between and including columns B and G. As before, this example is highlighted in the image below.

Range Operator Example 2

Finally, a range can refer to groups of cells across both columns and rows. For example, (B1:G10) refers to all cells between column B row 1 and column G row 10. Again, this is highlighted in the image below.

Range Operator Example 3

Union Operator

The union operator is used to combine two or more different operators; for example, a cell range, a cell value, or the result of a function.

An example of how this might look is (B1:B10, G10, 33). In this example, we create a union of the values in the cell range B1:B10, G10 and the numeric value 33. This example is highlighted in the image below.

Range Operator Example 4

The Intersect Operator

The intersect operator is used to identify the cells that are common between two different ranges (where they intersect). An example of how an intersect operator might look is (B1:B10 B5:G5).

In this example, we have two ranges, B1:B10 and B5:G5, which are highlighted in yellow in the image below. The intersect of these two ranges is B5, highlighted in orange in the image below.

Intersect Operator Example

Operator Precedence

Operator Precedence refers to the order in which the operations will be carried out in the formula. For example, multiplication and division calculations are carried out before addition and subtraction.

The principle of Operator Precedence is important when one has a formula with two or more operators. For example, a formula that looks like =4+2/2+6 as three operators, addition(+), division(/) and addition(+) again

Operator Precedence Example

The order in which the operations are carried out, is as follows;

  1. division first, so 2/2 which equals 1,
  2. then addition, the first addition Operator is 4+1 (the result of the first operation), which equals 5,
  3. then the second addition Operator, which is 5 ( the result of the second operation), +6 which equals 11.

Within Excel, there is a default order of Operator Precedence. This default order of Operator Precedence is represented in the table below.

PrecedenceOperatorDescription
1-Signifies a Negative Number
2%Percent
3_Exponentiation (sometimes refered to as a carrot)
4* and /Multiplication and Division
5+ and -Addition and Subtraction
6&Concatenation
7"=, <, >, <=, >=, <>"Comparison Operators

However, the default order of Operator Precedence can be overruled, to some degree, by using parentheses (brackets). Using the early example, we could rewrite it something like =(4+2)/(2+6), now the operations enclosed in the parentheses will be carried out first.

Operator Precedence Example with Parentheses

The result of using parentheses changes the order of Operator Precedence as follows;

  1. all Operators in parentheses are performed in isolation first. So, the addition operation, 4+2, which equals 6, in the first set of parentheses, is carried out first,
  2. then 2+6,which equals 8, and is enclosed in the second set of parentheses is carried out next,
  3. then, after the operations in the parentheses are completed, the operation 6/8, which equals 0.75, is carried out.

Error Messages

Error messages are something you will experience quite a lot when first starting to use Excel formulas. Each of the following Error messages deserves a post in their own right. However, for now, to get you started on the right path, I’ll give a quick introduction to the ones you are likely to encounter.

Error ######

Excel error ###### is displayed when a column isn’t wide enough to display all the characters that are the result of and Excel formula. This error can easily be resolved by setting the column width to an appropriate size. It’s worth thinking about the type of data and the typical length of the formula result that will appear in a cell beforehand.

Error #NAME?

Excel error #NAME? is displayed when the text in a formula isn’t recognized as a function’s name. In Part One of this series, you were introduced to Functions. More often that not, this error will occur if you have typed a Function name incorrectly.

Error #DIV/0!

Excel error #DIV/0! is displayed when a number is divided by zero. An empty cell can also be zero in this instance. Excel gets just as confused as you would, if you tried to divide a number by zero. Be careful that a cell that is being referenced in a formula is not empty, or that it contains a zero.

Error #VALUE!

Excel error #Value! is displayed when a formula includes different data types that are not compatible, such as numeric and text. For example, you cannot really multiply 7 by the letter T.

Error #REF!

Excel error #REF! is usually displayed when a Cell Reference in a formula is not valid. This error can occur when you are deleting rows and columns that have been referenced by a formula, so be certain to check before deleting.

Error #NUM

Excel error #NUM is displayed when a formula contains an invalid numeric value, such as a symbol.

I hope you found this blog post useful. In part three, the final blog post in this series, I will introduce you to Text Operators and some Functions that will be helpful from day one. As always, if you have any comments, questions or feedback, please leave them in the comments box below.

If you liked this post, please share it with your friends on Facebook.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit