99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
How To Create Formulas In Excel
Fri 21st May 2010
Formula lets you carry out calculations by using cell references in the formula. The cell containing the formula shows the result of the calculation. Change the numbers in the cell and the formula result also changes.
To start creating a formula, launch Excel and open a new worksheet. Then enter two numbers into different cells. For example we'll type 10 into cell C2 and 5 into cell D2. We'll put a formula in cell E2 which will add the two numbers.
To do this, click into cell E2 and type in =C2+D2. All formulas in Excel start with the equals sign, so when you type = Excel knows you are creating a formula and expects cell references to be entered. Then press the Enter to finish.
You'll see that cell E2 shows the answer, 15. To see the actual formula, select cell E2, and look above the spreadsheet to the formula bar. The bar has a small fx symbol to its left. In the formula bar you'll see the formula =C2+D2 and the letters always show in capitals. Note that when you enter a formula and press Enter, Excel adds the formula to the cell and then moves the cursor down one cell. So to have a look at a formula you've just entered, you need to move the cursor back up one cell.
If you then change, say, the number in cell C2 to 20 by overtyping the new value and pressing Enter. The formula result will change and cell E2 will show the new value of 25.
When you create a formula, you don't need to type in the cell references such as C2 and D2. Instead you can let the mouse do the work by left button clicking on the cell in question. As you click on a cell, you'll see that the cell ref is added to the formula as you progress. To show how to do this, click into a different empty cell, say F2, and type "=" and then left click on cell C2 (you'll see C2 appear in the formula), then type a minus sign - , and left click on cell D2. If you look in the formula bar you'll see your new formula =C2-D2. Then press Enter to finish. Cell F2 now shows the answer, 15.
So we've seen that the formula to add values in cells looks like =C2+D2 and the formula to subtract values looks like =C2-D2. If you want to multiply values in two cells you need to use the star symbol *, which is added using SHIFT+8, so the formula would look like =C2*D2. If you want to divide values in two cells you need to use the forward slash symbol / , so the formula would look like =C2/D2.
If you want to do a calculation using "To the power of", also called an exponential, you need to use the caret symbol ^, which is added by SHIFT+6. Suppose cell F4 contains 2 and cell G4 contains 3. If you enter this formula =F4^G4 into cell H4 and press Enter, you'll see the answer 8, which is 2^3 or 2*2*2.
Some formula can contain multiple cell references. So =(A1+B1+C1)*G1 would add all the numbers in cells A1, B1 and C1 together, and then multiply the result by the number in cell G1. Notice that if you put a calculation in brackets, Excel will do this first, and then carry out the multiplication. If a formula contains multiple parts Excel follows a rule called Order of Calculation to process each part in turn.
The Order of Calculation in Excel determines how the calculation is done. Brackets are done first and if there are multiple brackets inner are first. Next are Exponentials, then Divisions and Multiplications, then Additions and Subtractions. If there are two of the same, such as two sets of brackets alongside each other or two multiplications, Excel will do the leftmost first, then the other.
Memory joggers are often used to remember Order of Calculations. One example is BEDMAS which stands for Brackets, Exponential, Divide and Multiply, Add and Subtract. Also remember for the same items, Left to Right applies.
As an example try typing this into any empty cell =15+5/5 and press Enter. You should see the answer 16. Now in a different empty cell type in =(15+5)/5 and press Enter. This time you should see the answer 4. The difference is explained by the Order of Calculation.
Interested in learning more? Why not consider attending a training course to really boost your Excel skills.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Royal College of Physicians
Team Administrator - Specialty Recruitment Department
Richard was an excellent trainer. Good pace and teaching.
Charlton Park Academy
The facility at Limehouse was brilliant. Very easy to find and the work space was well set out. Meera was very helpful and patient with us all. The coursework was delivered at just the right pace and was interesting and relevant to us all
Actelion Pharmaceuticals UK Ltd
The trainer was very approachable and helpful.