GW_E3_F/502/0168_SS_BASICS_L3

• Computing ICT
• Further Education (Key Stage 5)

Lesson Outcomes

1. Create Simple Formula
2. Use cell References
3. Use Point and Select method to create a Formula
4. Edit and Modify Formulas

A Simple Formula

Select The Cell

Select the cell that will contain the formula. In our example, we'll select cell B3.

Type the equals sign (=)

Notice how it appears in both the cell and the formula bar.

Type the cell address

Type the cell address of the cell you want to reference first in the formula: cell B1 in our example. A blue border will appear around the referenced cell.

Type the mathematical operator

Type the mathematical operator you want to use. In our example, we'll type the addition sign (+).

Type the cell address

Type the cell address of the cell you want to reference second in the formula: cell B2 in our example. A red border will appear around the referenced cell.

Press Enter on your keyboard.

Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we've modified the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically recalculate and display the new value in cell B3.

Which formula would add the January and February profits?

1. B2+B3
2. B3+B4=
3. B2+B3=
4. =B2+B3

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we've modified the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically recalculate and display the new value in cell B3.

To create a formula using the point-and-click method:

Rather than typing cell addresses manually, you can point and click on the cells you want to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we'll create a formula to calculate the cost of ordering several boxes of plastic silverware.

Select the cell that will contain the formula

1. Select the cell that will contain the formula. In our example, we'll select cell D3.
2. Type the equals sign (=).

Select the cell you want to reference

1. Select the cell you want to reference first in the formula: cell B3 in our example. The cell address will appear in the formula, and a dashed blue line will appear around the referenced cell.
2. Type the mathematical operator you want to use. In our example, we'll type the multiplication sign (*).
3. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell.

Using The File Handle To Copy Formula

The File Hand

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. Review our lesson on Relative and Absolute Cell References to learn more.

To edit a formula:

Sometimes you may want to modify an existing formula. In the example below, we've entered an incorrect cell address in our formula, so we'll need to correct it.

Select the cell containing the formula

Select the cell containing the formula you want to edit. In our example, we'll select cell B3.

Click the formula bar to edit the formula.

Click the formula bar to edit the formula. You can also double-click the cell to view and edit the formula directly within the cell.

A border will appear around the cell.

1. Click the formula bar to edit the formula. You can also double-click the cell to view and edit the formula directly within the cell.
2. When you're finished, press Enter on your keyboard or click the checkmark in the formula bar.
3. The formula will be updated, and the new value will be displayed in the cell.

Challenge

1. Open an existing Excel workbook. If you want, you can use the practice workbook.
   
2. Create a simple addition formula using cell references. If you are using the example, create the formula in cell B4 to calculate the total budget.
   
3. Try modifying the value of a cell referenced in a formula. If you are using the example, change the value of cell B2 to $2,000. Notice how the formula in cell B4 recalculates the total.
   
4. Try using the point-and-click method to create a formula. If you are using the example, create a formula in cell G5 that multiplies the cost of napkins by the quantity needed to calculate the total cost.
   
5. Edit a formula using the formula bar. If you are using the example, edit the formula in cell B9 to change the division sign (/) to a minus sign (-).
6. Use The snipping tool to create an image of the completed Worksheet

Upload Completed Screen Shot of Completed Excel Sheet