Microsoft Excel 2007 - Excel Formulas

What Are Formulas?

Formulas are an important feature of spreadsheets and Excel 2007 provides a rich environment in which to build them. We can assign to cells formulas that involve the contents of other cells. To indicate that what you are typing is a formula, you must start by typing a '='. Everything after the '=' is part of the formula. This makes sense as the contents of the cell equals the formula you are typing.

Example Formula To Create Total Price

Let's illustrate the use of formulas in Excel 2007 with an example. Suppose we have a spreadsheet that holds the product name and price (exclusive of VAT) values for a number of different laptops you are thinking of buying. It would help you make your buying decision if you could see the price inclusive of VAT, so what we are about to do is create 2 new columns that hold the VAT and the Price + VAT.

What Mathematical Functions Do We Need?

We need to break down what mathematical functions constitute this calculation:

VAT = Price x 17.5%

Total Price = Price + VAT

VAT first: in formula terms, cell C2 = cell B2 * 0.175. For the Total Price, D2 = B2 + C2. Now that we've done the groundwork, we can enter these forumulas into the cells themselves. Make cell C2 active by clicking in it and type =B2*0.175. Don't forget the '=' sign! At the point where you finish typing B2 a blue outline appears around the referenced cell as a visual aid.

On pressing enter the formula is calculated and the result is displayed. 59.5 is indeed 17.5% of 340. Making the result cell active again displays the formula in the formula bar. To change the formula press F2 - note that the referenced cells are outlined in the same colours as those used in the formula - or you can click in the formula bar and change it there.

That's the VAT calculated, on to the total price. Make cell D2 active and type =B2+C2. When you press enter the result is calculated.

Copying Formulas To Other Cells

But wait. What we've done is correct but we've only calculated the total price for the first product in our table. What about the other 4 products? We can copy the formula in cells C2 and D2 (we need both) to the other cells in the C and D columns by first of all selecting cells C2 and D2 (drag over them). Then hover over the bottom right corner of your selection until you see a '+' sign. When you see that drag handle, click and drag downwards until you have included the cells you want to apply the formula to. When you release the mouse, those newly selected cells contain their VAT and total price values.

If you inspect those cells, each will have similar formulas to the ones that we typed in - the only difference is the row number referenced.

Using The formula Bar

You can type a formula into the cell or directly into the formula bar by clicking in it first. If using the formula bar, you can use the left/right arrow keys to move the cursor along the formula, but if typing the formula into the cell itself, left and right moves the active cell. Using the formula bar is better for correcting mistakes, especially in long formulas as you can expand the formula bar by clicking on the down arrow to the right. This button toggles the expand and collapse of the formula bar.

Knowing how to create formulas leads us nicely onto learning how to merge columns in Excel.