Have you ever had difficulty using formulas and functions in Microsoft Excel? I know I have. Thomas Bradshaw from WestCal Publishing has to start using Excel for reporting, invoicing, ordering, and timesheet keeping as he trains to become an account manager. In order to do this, Thomas needs to learn how to use formulas and functions. One of the most powerful features in Excel is the ability to perform real-time calculations on data within workbooks. Using these powerful features will enable Thomas to save time and reduce errors when trying to calculate complex or simple equations. Formulas are mathematical expressions that calculate the value of cell contents. When cells contain numerical data, you can perform multiple mathematical operations on the cell content. The results of these operations will be shown in the cell that contains the formula. Formulas can be simple such as adding two cell values, or quite complex, involving multiple mathematical operations. Let's take a look at the formula bar. You can start by opening the WestCal Publishing sales report document. Firstly, a function is a pre-defined formula and a quick way to enter certain calculations. For example, the SUM function creates a formula that adds blocks of numbers. There are many functions built into Excel, and you'll explore more of them in another lesson. The fx button on the left side of the formula bar opens the insert function dialog box. However, typing a formula into a cell or into the formula bar just below the ribbon is the most straightforward method of inserting basic Excel formulas. The process always starts by typing an equal sign followed by the name of an Excel function, a calculation, a value, or a cell reference. You can tell if a cell contains a formula by clicking it to make it the active cell. If there was a formula in the active cell, it will be shown in the formula bar. Here the SUM function is being used to add data from Cell C2 through to F2. Now let's take a look at some of the important elements that make up an Excel formula. You may have noticed the formulas always begin with an equal sign. You could read it as the number in the cell as a result of the equation. Formulas can contain cell references such as A1, numbers such as 23, or functions such as SUM(B2:B9). What that says to Excel is get the sum of everything from B2-B9. Cell references are not case sensitive, these are all examples of valid formulas. You can enter a formula by typing directly into a cell or into the formula bar. If you include a cell reference in a formula such as B3 multiplied by 6, and that cell reference itself contains a second formula such as B1 plus B2, stored in B3, that second formula B1 plus B2 will be evaluated first, and the result will be used in B3 multiplied by 6. Let's look at some examples of the symbols that are used in formulas to tell Excel what mathematical action to perform. Here you have a list of eight operators listed from top to bottom in order of precedence. Operators are symbols that represent specific mathematical operations including the plus sign, minus sign, division sign, and multiplication sign. Operators specify the type of calculation that you want to perform on parts of a formula. When you create a formula with several operators, Excel evaluates and performs the calculations in a specific order. For instance, Excel always performs multiplication before addition. This order is called the order of operator precedence. This means that in Excel, some mathematical symbols are considered to have a higher priority than others, and this influences how Excel processes a calculation. The table onscreen shows the operators listed from top to bottom in order of precedence. Excel does not simply calculate expressions from left to right, certain operations are performed before others. If a formula read B1 plus C5 minus D10, that would be processed left to right. But if a formula read B1 plus C5 multiplied by D10, the multiplication will be done first and then the addition. You can see how that might produce a result that you didn't intend. You now know that Excel follows certain rules of precedence. Multiplication and division have higher priority than any other mathematical operator, but it's important to understand that they have equal priority to each other. For example, in the formula B1 multiplied by C5 divided by D10, Excel simply works from left to right. Addition and subtraction are next in overall priority, but again have equal precedence calculated from left to right. Greater than, less than, have equal precedence calculated from left to right but lowest priority overall. How can you be confident that Excel is processing your formula in the right order? You can impose your own order of operations by enclosing expressions in parenthesis. The operations inside the parenthesis will be evaluated before the operations outside. Putting part of the formula in parenthesis indicates to Excel to do this part of the calculation first. If you have parenthesis within parenthesis, the expression in the inner parenthesis will be evaluated first and the results will be used to evaluate the expression in the outer parenthesis. One easy way to remember precedence order is to remember the word BEDMAS, which stands for brackets or parenthesis, exponents, division, multiplication, addition, and subtraction. As you've seen, cell references are included in formulas using the column first and row number format. If the same formula needs to be included on every row of a block of data, Excel speeds up the process for you by allowing you to create the formula in one cell, and copy it into other cells underneath. This is a huge time saver. But what happens to the cell references when Excel copies the formula? Cell references are naturally relative references which simply means that if a formula is moved or copied to another location, Excel will change the references relative to its new location. For example, the formula C1 plus D5 copied down a row, would become C2 plus D6. In a situation where you need the same formula to be repeated in every row of a block of data, and still work correctly in each row, this is incredibly useful as it means that you can copy the formula to save time. But what about a situation where you don't want one of the references to be adjusted when you move or copy the formula. You can prevent the adjustment by changing the reference from relative to absolute. Absolute references use dollar signs before the cell or row to make sure a formula always references the same location, no matter where the formula is copied or moved to. You can assign absolute cell references in three ways, dollar column, dollar row. Both the row and column reference will not change. Dollar column row, the column reference will not change but the row can. Column dollar row, the row reference will not change but the column can. As you can see, a dollar sign simply fixes part of the cell reference.
