Excel Formulas Training

Microsoft Excel have a lot of very useful features, but what makes it such a versatile software programme, is the ability to write formulas to automate certain calculations and functions. Even the most detailed focused and mathematically gifted people can sometimes experience an oversight, and make a mistake which could be potentially disastrous, especially where money is at stake! One of the best ways to eliminate human error is to automate functions. To do this, a good working knowledge of Excel formulas is needed. So if you want to make your job easier and work more accurately, then Excel formulas training is required.

The formulas in Excel allow you to manipulate and sort data, calculate values in spreadsheets and generate reports. This function of Excel is not always understood very well, and in some cases formulas are applied incorrectly, which can also lead to mistakes. It is recommended that any person who works with Excel during the course of their duties, get trained on how to use the existing formulas in the programme, and also how to combine formulas for even better functionality.

A formula is basically a mathematical equation which allows the user to perform certain calculations or automate certain functions. In Excel, any formula you enter will always start with an equal sign, which allows the programme to classify any characters that follow the equal sign as part of a formula. Certain characters that follow the equal sign will then be interpreted as elements of a formula and enable relevant functions. The characters or elements in a formula will be separated by calculation operators. A formula may contain any or all of the following elements: reference, constant, function and operator. A formula which contains all of these will look something like this: =L5() / (G7-5).

Let’s do a little bit of Excel formulas training and take a look at what each of these mean:

Reference: The reference indicates to the system where the data is located in the spreadsheet. It usually identifies a range of cells and tells the programme what data to manipulate or calculate. You are not limited to the current worksheet, and can also refer to data in other worksheets or even other documents. This will enable one document to update itself the moment the other to which it is referenced, is changed or updated. For instance, the calculation +A1+A2 will add the values of the cells called A1 and A2 together. If the cell value is changed, the answer of the calculation will change accordingly.

Constant: This part of a formula does exactly what the name says: it remains constant throughout the calculation. One can sometimes use constants in the place of references, but keep in mind that if the cell contents are changed, the constant will not be changed. Often it is better to enter a value in a cell location and use the cell reference in the formula, especially where ranges of data is dealt with, as it has to be manually entered. An example of a constant is =A1+5. This means that the value in cell A1 will be added to 5. If the 5 is likely to change, then rather enter 5 into a cell and use the reference, and you will end up with something like this: =A1+B1.

Function: these are combinations of characters that perform certain functions. Certain values are entered in a certain sequence, which allows the character combination to be interpreted as a function. For instance, the equal sign indicates the start of a formula, and the asterisk * is interpreted as a multiplication sign. A typical function looks like this: =PI(), which indicates the value of PI.

Operator: This is a character or symbol which indicates the kind of function that is to be applied to the formula. For instance, * indicates multiplication, / is for division, and + will add values together. There are 4 kinds of operators, mathematical, comparison, logical and reference.

Excel will usually calculate a formula in a logical order from the left to right, so it is important to know in which order to enter the different segments of the formula. In order to learn more about this, we highly recommend that you attend one of our Excel formulas training courses. Our trainers are experienced users of advanced Excel and teach you all the formulas you need to learn. Contact us today to book your place!