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. Referencea. 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.
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.
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.
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.
Formula references in Excel – 10%
Formula references in Excel – 5%
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.
How to insert a function in Excel
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.
