CAR IMPORTS SPREADSHEET
In this spreadsheet you will build a spreadsheet on car import prices. The skills you will practise are:
§ Entering text labels & values
§ Entering formula
§ Relative cell referencing
§ Absolute cell referencing
§ Formatting numbers as currency
§ Wrapping cell content
§ Formatting – adding borders
§ Find & replace
Relative cell referencing
§ This is the default setting in Microsoft Excel. In the screenshot Excel remembers the formula as = the cell 2 above & one to the left. Look at the screenshot on the left.
§ This means that when you copy the formula to a different cell, for example B3 to C3 the formula becomes =B1, the cell 2 above & one to the left of C3. Look at the screenshot on the right.
§ Open a new workbook, save it in your ICT folder as AbsCellRef, and practise the above example on sheet 2.
§ Enter some formulae and replicate them, and look in the formula bar to see how Excel updates the cell references.
Absolute cell referencing
§ This is used when you always want to refer to the same cell in a formula.
§ For example when you want to work out the price of a number of tickets sold, you create a formula in cell B4 that multiplies the value in A4 by the value in B1. Look in the screenshot below:
§ If you replicate the formula you have just entered to cell B5, by default Excel uses relative cell referencing so it multiplies the value in A5 by B2, 1 to the left & 3 up. Look at the screenshot below:
§ To make the formula work you have to make cell B1 an absolute reference by putting a dollar ($) symbol before the cell reference letter & the number. For example $B$1. Look in the formula bar in the screenshot below:
§ Once you have the absolute cell reference in the formula you can replicate the formula down.
§ On sheet 3 of the open workbook, practise the above technique.
Setting up the spreadsheet - Entering text labels & values
§ On sheet 1 of the open workbook, enter the text labels and values exactly as they appear in the screenshot below:
Entering formula – From relative to absolute cell referencing
§ The first formula you need to enter is in row D to work out the price of an imported vehicle in pounds. You will have to divide the price in euros of a car by the exchange rate of how many euros there are to the pound.
§ Click in cell D7, enter a formula that divides the value in C7 by B2. This will be =C7/B2
§ We need the same formula in all of row D, so copy the formula down to row 13.
§ You will find that by default Excel has used relative cell referencing so the formulae have not worked. Delete the formula in cells D7 to D13.
§ In cell D7 now write an absolute cell reference by putting the $ symbol before the column & row reference, so it always refers to the value in cell B2. The formula will be =C7/$B$2
§ Now you can copy this formula down to cell D13, and it will work out the correct amount each time.
Entering formula - £ saving
§ In cell E7 you need to enter a formula that subtracts the imported price in pounds in row D from the UK price in row B. The formula will be =B7-D7 (This formula uses relative cell referencing.)
§ Copy this formula down to cell E13.
Entering formula – Calculating a percentage
§ In row F to work out the percentage saving for each car, you need to divide the £ saving value by the UK price in pounds.
§ Copy this formula down.
§ Use the Increase or decrease decimal button on the toolbar to adjust the figures to display 2 decimal places.
§ The use the percent style button on the toolbar to display the figures as a percentage.
Formatting numbers as currency
§ Select cells B7 to B13, then hold down the Ctrl key while selecting cells D7 to E13.
§ Right click anywhere within the selected cells to bring up the shortcut menu, choose Format Cells. Make sure the Number tab is selected, then choose Currency, Excel should choose the £ symbol by default, select to show 0 decimal places. Click OK.
§ Repeat the above to format the cells in row C to display the euro symbol.
Wrapping cell content
§ Some of the car makes & models are quite long, it would look neater if the longer descriptions ran onto 2 lines.
§ Select cell A12, then right-click it. Select Format Cells from the shortcut menu that appears.
§ In the Format Cells dialogue box, select the Alignment tab.
§ In the Text control section, click the check box next to Wrap text. Click OK.
§ Now resize column A so it is now too narrow for all the text. Then widen row 12. The text in cell A12 will fill onto the second line.
§ Do the same for cell A7.
§ Now make the column headings in row 6 wrap over 2 lines.
Formatting – Adding borders
§ To make the spreadsheet easier to read, you are going to add some borders.
§ Highlight cells B5 & B6. Click the small down-arrow on the Borders icon on the Formatting toolbar, select Outside Borders.
§ Repeat this for the other column headers.
§ Then draw a border around the whole table, choosing the Thick Box Border.
Find & Replace
§ There aren’t many records in this spreadsheet, but in a large spreadsheet it is useful to be able to search.
§ To find a cell containing a particular word or value, select Edit → Find from the menu at the top of the screen.
§ Enter VW in the Find What box:
§ Click Replace and type in Volkswagen
§ Click the Replace All button. Click OK, then close the box.
Page 3 of 6