An Introduction to Excel Formulas (Part 1)

In this series of blog posts, I’ll introduce you to one of Excel’s most powerful and useful features,  Excel formulas.

Formulas can be used for a multitude of things, such as calculating tax on an invoice or the volume of water an object can hold, removing or replacing specific characters in a string of text, identifying how often a certain value appears in a range of cells, and so on. The possibilities are almost endless.

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

 

What is a Formula?

Formulas are used to perform calculations and operations on data in a cell or a range of cells. These calculations can be very simple arithmetic calculations such as addition, subtraction, multiplication, and division, or complex mathematical, financial, and engineering calculations.

The real power of spreadsheets over other applications is their ability to build and use formulas. Formulas are built using various component parts that include functions, operators, and operands. Don’t worry if you don’t know what these terms mean; I’m going to explain them in the next few sections.

What are Operands?

Okay, let’s start by explaining what an operand is. An operand is a value (numeric, alpha, symbol, or some combination of the aforementioned items) that is used in a formula. The Operand can be entered directly into a Formula as text, can be accessed via a reference such as a Cell Reference, or be the output of a Function. For an explanation of what a cell reference is, please see this blog post.

Again don’t worry too much if all this doesn’t make sense straight away, all you really need to remember at the moment is that an Operand is a Value that will be used in a formula.

There can be zero or more Operands in a Formula.

What is an Operator?

Operators describe the relationship and the action to be carried out between two parts of a Formula, usually the relationship between Operands.

There are four types of Operators as follows:

  • Arithmetic.
  • Comparison.
  • Text Concatenation.
  • Reference.

For this blog post, we will focus on one type of operator, Arithmetic

Arithmetic Operators

Most people will be familiar with the Arithmetic operators even if they don’t recognize the term. Arithmetic operators relate to normal mathematical operations such as addition, subtraction, multiplication, and division. The main difference when using them in Excel is that some of the symbols are used to represent the operators look a little different.

There are a couple of extra operators that you may or may not be familiar with. Do not worry about those for now; we will not be discussing them in this blog post. They have just been included for completeness.

Arithmetric Operators

Arithmetic OperatorDescription
+Plus Sign
-Minus Sign
*Multiplication
/Division
%Percent
^Exponentiation

A formula can have zero or more Operators, depending on the Formula.

What is a Function?

Functions pretty much do as the name describes, they perform a Function. But what does this mean? Well, to understand this better, we need to take a closer look.

To enter a Function you must first type an ‘=’ equal sign, then enter the Function name, and after the function name, you type parentheses ‘()’. Most of the time, between the parentheses you enter Arguments. Arguments are a method of inputting Operands into the function. Remember that an Operand is a value and can be  a numeric, alpha, symbol, or some combination of the aforementioned.

Therefore, using the SUM() function, a formula could look like the following ‘=SUM(10+10)’. In this example, SUM() is the Function, both number ’10’s are Operands and the ‘+’ plus sign is the operator. In Excel, this formula would look something like the image below.

SUM() Function Example

SUM() Function Example

Excel has several built-in Functions that are grouped together. These Functions can be found in the Formula Ribbon Tab and will look something like the image below. Depending on the version of Excel you’re using, it might look slightly different.

Excel Function Tab

Excel Function Tab

Putting it All Together with Some Examples

For now, we will only deal with one of the functions from the Math and Trig grouping, the SUM() function.

We already saw the SUM() function earlier in this blog post. However, in this earlier example, we entered the formula “=SUM(10+10).” In truth, we don’t actually need to use the SUM() function for this formula to work. We could have just as easily entered “=10+10,” and it would have still worked.

Where the SUM() function really comes into its own is when you start to add up ranges of cells. In the example below, cells B1, B2, B3, and B4 are added together. These cells are identified by the cell range B1:B4. The placement of a colon (“:”) between B1 and B4 indicates a range of cells.

Excel SUM() Range Example

Excel SUM() Range Example

If we had entered “=SUM(B1, B2, B3, B4),” the result would have been the same.

Taking the above example a little bit further, we can come up with a formula to calculate the VAT on the sale of items. The VAT is a type of sales tax that is added to the sale of certain products and groceries.

In the example below, we see the formula ‘=SUM(C2:C5)*F2’ in cell C8. This formula works out the VAT on the prices of items in cells C2, C3, C4, & C5. Firstly the formula adds up the values of C2, C3, C4, & C5, using the SUM() Function. It then multiplies the answer by the percentage in Cell F2, in this case, 17.5%.

Excel SUM() Range Example 2

Excel SUM() Range Example 2

In part two of this Introduction to Excel Formulas series of blog posts, I will discuss some additional Operator types, Operator Precedence and we will also take a look at some of the Error Messages you might see, what they mean, and some of the things you can do to prevent them.

Hopefully, you will have found this blog post useful. 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