Excel formula basics every Small to Medium Business Owner should know 4


Excel formula basics every small to medium business owner should know.

 

One of the most powerful features of Excel is its ability to allow you, the user, to specify relationships between the information you have stored in your worksheets. Having the ability to be able to specify these relationships i.e. using formulas between your information, can significantly enhance and improve the usefulness of your spreadsheets.

 

Formulas allow you to turn your spreadsheets from static displays of data to useful tools being able to undertake calculations and show the results of analysis. Turning jumbled data into meaningful information.

 

Excel Formulas


To begin, let us break down the structure of a formula in Excel so we can see how we can start to use them.

 

Thankfully all formulas in Excel are generally constructed the same way. They all start with an equals sign (=) followed by one or more elements. The equals sign simply tells Excel that we are storing a formula in this particular cell.

 

Each of the elements after the equals sign can be any or all of the following:

1. Constants
2. Operators
3. References
4. Functions

 

1. Constant

A constant is a value that is static and not the result of a calculation. For example, the numbers 1, 2, 3, 4 and 5 or even text like “ExcelSuperSite” are all examples of constants i.e. they do not change by themselves nor are they a result of a calculation.

A small but helpful tip – I usually stay away from using constants in formulas wherever possible as the result only changes after you modify the formula yourself. It is far better, from a spreadsheet design point of view, to use cell references in place of constants. This gets further discussed a little later.

 

2. Operators

Operators specify what type of operation (calculation) you want to perform on the elements in your formulas.There are four different types of operators that you can use:

a. Arithmetic
b. Comparison
c. Text
d. Reference
a. Arithmetic operators are used to perform basic mathematical operations such as addition, subtraction, or multiplication etc
Arithmetic Operator Description Example Formula Formula Result
+ Addition (plus) = 3 + 2 5
Subtraction (minus) = 5 – 2 3
* Multiplication (times) = 3 * 2 6
/ Division = 10 / 2 5
% percent = 20% 0.2
^ exponential = 3 ^ 2 9
b. Comparison operators are used to compare two elements. The result of a comparison is a logical value either TRUE or FALSE.
Comparison Operator Description Example Formula Formula Result
= Equals = 3 = 2 False
> Greater than = 5 > 2 True
< Less than = 3 < 2 False
>= Greater than or equal to = 10 >= 2 True
<= Less than or equal to = 10 <= 2 False
<> Not equal to = 3 <> 2 True
c. Text operators are used to join (concatenate) one or more pieces of text together.
Text Operator Description Example Formula Formula Result
& Concatenate (join) = “Excel” & “Super” & “Site” ExcelSuperSite
d. Reference operators are used to combine ranges of cells for calculations.
Reference Operator Description Example Formula
: Range – references all cells between two references =SUM(A1:A10)
, Union – combines multiple range references =SUM(A1:A10,C1:C10)
(space) Intersection – references all cells common to the two references =SUM(A10:D10 B5:B15)

 

Just like when you calculate a normal maths problem, Excel follows basic maths principles when it undertakes calculations. Excel starts from the left and works to the right obeying maths precedence rules – B O M D A S (Brackets – Order – Multiplication – Division – Addition – Subtraction).

If you combine more than one operator into a single formula, you can change the order of evaluation by using parentheses to enclose the part of the formula to be calculated first.

As an example, the following formula results in 7 because Excel calculates multiplication before addition (obeying standard math precedence rules). The formula multiplies 2 by 3 and then adds 1 to the result.

=1+2*3 = 7

However if we add parentheses to change the calculation order we can end up with a result of 9 instead. Excel adds 1 to 2 and then multiplies this result by 3 to give a total of 9.

=(1+2)*3 = 9

 

3. References

References in formulas tell Excel where to look for data or information to use in your formula.

Excel makes reference to cells in a worksheet by using the column letter and row number of the particular cell. As an example, the cell reference C5 refers to the cell at the intersection of column C and row 5.

image of Cell Reference in Excel

Cell Reference in Excel

References allow you to use the value from one cell in multiple formulas throughout your worksheets. As discussed earlier, I highly recommend that you use method to utilise constants in your formulas i.e. use a cell reference to the constant rather than the actual constant itself.

To try and explain this a little further, consider the following example:

Let’s say we want to calculate 10% of the following list of numbers. Easy enough done. Simply enter a formula in each of the cells in column C as shown below.
image of Simple formula in Excel using Constants

Simple formula in Excel using Constants

Now that was simple, so why would you want to use references instead of constants in your formulas? Consider the following extension to the above. Instead of a list of 5 numbers to calculate 10% of, you now have a list of say 50 numbers and to make things a little more interesting, you want to calculate a range of percentages, say 5%, 10% and 15%.

This COULD be done exactly the same way as the above example but it will require a lot of manual editing of all your formulas to get it done. There is nothing wrong with working hard, but I also prefer to work smart as well. So let’s start to get Excel to work for us rather than the other way round and us doing all the work.

Before we modify the formulas in column C, let’s first enter the percentage constant into cell C1 [click cell C1 and then type “10%” {without quotes} then press enter]

In cell C4 type:

= B4 * C1 then press enter

What this formula is saying is take the value of whatever is in cell B$ and then multiply that by the value of whatever is in cell C1.

Now copy this formula down to all the cells in column C.
Image of Formula using references in Excel

Formula using references in Excel

 

By entering the formula in this way, we can easily update the percentage constant in cell C1 to either 5% or 15%, as per our example, and we do not have to manually edit any of the formulas to get the results we are after. Similarly, all the values in column B could also be updated and we save ourselves a lot of work by having Excel do it all for us.

Image of Formula references in Excel - 10%

Formula references in Excel – 10%

image of Formula references in Excel - 5%

Formula references in Excel – 5%

Image of Formula references in Excel - 15%

Formula references in Excel – 15%

References Type
Reference to the cell in column C and row 1 C1
Reference to the range of cells in column C and rows 1 through 10 C1:C10
Reference to the range of cells in columns C to E and rows 1 through 10 C1:E10

 

4. Functions

Functions are predefined formulas in Excel that are available for you to use. A full listing of functions available for you to use can be found by clicking any cell in Excel and then pressing SHIFT & F3 (together) or by clicking the Insert Function button beside the Formula Bar.

Some of the more common functions include SUM, COUNT, AVERAGE, MIN, MAX etc. I am not going to go into these functions in this post but will follow up with posts about each of them, so we can look at each in a lot more detail.

Image of How to insert a function in Excel

How to insert a function in Excel

Image of Insert Function dialog box

Insert Function dialog box

 

Continue the Discussion

So that sums (sorry for the pun) things up for the basics for starting to use formulas in Excel. As always should something not make sense to you and if you would further like something explained, please leave a comment below and we’ll do what we can to clarify things?

 

Please Share

If you liked this article or know someone who could benefit from this information, please feel free to share it with your friends and colleagues and spread the word on Facebook, Twitter and/or Linkedin.

 

  • Great to see the construct of formulas explained. I use them all the time but don’t really think about how they are put together. Foolish me.

    • Thanks for your comments Ursula. Knowing the basics of how formulas etc are constructed then allows you to “push and extend” Excel to accomplish some very complex analysis… Start with the basics and move forward from there.

  • This was super helpful! I’ve been using Excel for years and love how it can do the calculations for me, but I was not aware that it could do exponents! I knew that Excel is far more powerful than what I’ve been using it for so I’m excited to keep learning more!

    • Hi Monica, We come across this ALL the time. Excel is a very powerful tool, but unfortunately most people have no idea of what is is capable of… We are aiming to change all that !! 🙂