Excel is very powerful and can easily help you in your daily task. You just need to understand how Excel thinks so that you can continue to be in charge of it and eventually tell excel what to do. While using Excel as a storage space for data is great, you can really unlock Excel when you start to use the formulas and functions. Once you master these, you can use Excel to calculate depreciation tables, calculating employee bonuses and commissions and even loan repayments.
The best part is that it’s not just for your own workplace, once you see how formulas and functions work you will even want to start using Excel for your own daily life from preparing a budget, your expenses for your weekend getaway or even your expenses for that round-the-world holiday.
Below are few of the basic terms that can help you out doing this.
Formula
In excel, a Formula is an expression that operates on values in a range of cells or a cell. Same with any other calculations a formula always starts with an equal sign (=) and once committed (press Enter), the result is displayed in that cell. An example of a formula would be: =A1+B1 which would take whatever value was entered into cell A1 and add it to the value that was typed into B1. After typing the formula and pressing the Enter key, the resulting value will be displayed in the cell in which you entered the formula.
Function
A function is a ‘mini-program’ that you can use to make more complex calculations. Functions are used inside formulas and therefore, you need to start with an equal sign (=). Formulas operate with cell references and are very powerful. One commonly used function is SUM, which will add up the values in a defined range. The function: =SUM(A1:A3) will sum up all values contained in cells A1 through to A13 and return the result once you commit the function by pressing the ENTER key.
Formula Bar
Formula bar is a special toolbar at the top of the Excel worksheet window, labeled with function symbol (fx). The first edit line shows cell reference of the currently active cell – this is called the Name Box.
The second edit line provides space to enter cell content and a helper tool to enter formulas:
Once you enter an equal sign into the active cell, frequently used functions appear in the Name Box on the left – a drop-down menu offers more options.
Value
Values are numeric data that is entered into a cell. When text is entered into a cell without being assigned a number format, we refer to them as labels. When data is formatted as a value type, it can be referred to in formulas and functions and used in calculations.
Range
A range refers to two or more cells. When these cells are together, we call this an adjacent range. Consider this example:
This adjacent range covers all the cells from A1 through to C3 – or in Excel syntax this is written as A1:C23. The colon (:) basically stands for ‘through to’. Whenever we want to define a range of cells that are not all in one place, we talk about non-adjacent ranges:
This range includes cells A1:A3 and C1:C3. In Excel syntax this is written as A1:A3,C1:C3.
Relative Reference
A relative cell reference is one that changes relative to the direction in which it is copied. Consider the below example:
A2 and B2 are relative cell references. When we copy the formula in C2 downwards into C3 and C4 with the fill handle, then Excel will assume that you want to conduct the same calculation in rows 3 and 4 as you did in row 2. In other words, Excel will perform the calculation A3*B3 in C3 and A4*B4 in C4. Excel effectively updates the row number in each of the cell references for every row that you copy your formula downwards.
Absolute Reference
Absolute reference or as we like to fondly call it, the DOLLAR thingy. A cell reference is absolute when it does not change whenever it is copied. To make a cell reference absolute, you must include a $ before each element of the cell reference: $B$1. This can be a bit cumbersome. The keyboard shortcut to turn a cell reference into an absolute cell reference is to press F4.
With these basic terms you can now start your very own excel template, whether it be for your workplace or for your own use. Just remember the difference between relative & absolute references, provide the values for the formula and get to know the different functions of Excel.
💡 Worth to know!
Order of basic mathematical operations
Calculations in Excel follow the general mathematical rules for calculations, in other words, MDAS which means Multiplication (*) and Division (/) come before Addition (+) and Subtraction (-). So, when you are using these arithmetic operators in your calculations, you need to keep these general rules in mind.
Example: =2+9*5
Excel reads the formula from left to right, so one might assume that it adds 2 and 9 together before it multiplies the result by 5. However, as multiplication takes precedence over addition, Excel will calculate 2 plus the result of 9 multiplied by 5. If you wanted Excel to choose the first path, you need to ‘tell’ this to Excel with the help of parentheses. The formula should look like this: =(2+9)*5.
