European Computer Driving Licence

1. European Computer Driving Licence Module 4 – Spreadsheets Chapter 4.8 – Cell Referencing

2. This is the default setting in Excel Excel actually remembers the formula as =the cell 3 above and one to the left When you replicate a formula Excel will automatically adjust the formula Relative cell referencing

3. Absolute cell referencing is used when you want to refer to the same cell The Car Imports project will take you through this step by step Absolute cell referencing

4. Click where you want the result of the calculation to appear (in this case cell D7) To convert from Euros to Pounds, you need to divide the Euro amount by the exchange rate The formula required is =C7/B2 Use the Fill handle to replicate the formula and see what happens! Entering a formula

5. If you click on each cell below D7, you will see the formula in the formula bar Notice how the second part of the formula points to one cell below the previous one? To correct this, click on cell D7 To stop the second cell reference changing, type a $ sign before the column letter and row number (your formula should now read =C7/$B$2) and press Enter Replicate this formula and you should now get the correct results Making a relative cell reference absolute

6. To calculate the £saving click in cell E7 Use the formula =UK Price in pounds – Imported Price in Pounds Copy the formula down using the Fill Handle If all has gone well, your formula should look like this Entering the other formulae

7. To calculate the % Saving, click in cell F7 and enter the calculation required:=£Saving / UK Price in Pounds Use the Increase Decimal and/or Decrease Decimal icon to display the cell to 2 decimal places Copy the formula down for all the cars Increase decimal Decrease decimal Calculating a percentage

8. Select the cells to be formatted (ie cells F7 to F13) On the Formatting toolbar, click on the % icon Formatting percentages

9. Select the cells you want to format (in this case B7 to B13, hold down the control key and select D7 to E13) Right-click anywhere within the selected cells and select Format Cells from the shortcut menu On the Number tab, select Currency and set the decimal places to 0 Click OK The currency format

10. Highlight the cells to be formatted (in this case cells C7 to C13) Right-click and select Format Cells Click in the symbol box and select the Euro symbol € Ensure decimal places are set to 2 Click OK Formatting to Euro currency

11. It is useful to merge and centre titles to improve the look Select the cells to be merged (in this case C5 and D5) On the formatting toolbar, click on the Merge and centre icon The text will be centred within the selected cells Merge and centre cell contents

12. The quickest way to do this is press Ctrl and ; Or type the date in using slashes or dashes (eg 01/06/04 or 01-06-04) To format the date, right-click the cell and select Format Cells from the shortcut menu Click on the Number tab Select the format you require then click OK You can widen the column to show the date in full Inserting a date field

13. Sometimes a row label can be too long – it looks better if it runs onto two lines rather than having a column that is too wide Select the cell (in this case A12) Right-click and select Format Cells Click the Alignment tab Under the Text Control section click the checkbox next to Wrap text Wrapping cell content

14. Resize column A so that it is just the right width for Currency exchange rate: You will find that not all the text displays Increase the row height by clicking and dragging between row headers 12 and 13 Wrap the text in cell A7 You can also do this with the column headings Price in Euros and Price in pounds Wrapping cell content

15. You can add different borders to improve the appearance of your worksheet Select cells A2 to F2 Click on the down arrow on the Border icon Select Outside Borders An outside border will appear around the selected cells Adding borders

16. If you wanted to delete a border, select the cells, click the down arrow on the Border icon and select No borders To draw a heavy border around a range of cells, select the cells then click on the down-arrow on the Border icon and select the Thick Box Border icon Other border options

17. To find a value, click Edit, Find from the menu Type the value you want to search for (eg VW) then click Find Next Excel makes the cell containing VW the active cell Click on Find Next to find the next cell containing VW Find and Replace

18. To replace VW with Volkswagen, click on Edit, Replace from the menu Click inside the Search box and type VW Click inside the Replace with box and type Volkswagen Click on Replace All A message appears telling you how many replacements have been made, click OK VW has now become Volkswagen – click on Close Replacing a word or value

19. Useful for automatically adding current date and page numbers on larger documents Select View, Header and Footer from the menu Click the Custom Header button You can type text into one of the text boxes or add a field Adding Headers and Footers

20. To format a header, click inside the text box and click the Format icon The Font dialogue box appears Select your options (eg size 12, bold) then click on OK Click OK to exit the Custom Header dialogue box Formatting a custom header

21. Click the Custom Footer button Click inside the left section – click the file name icon, type a comma and space then click the sheet name icon Click inside the centre section – click the Page Number icon Click inside the Right Section – click the Date button, type a comma and space then click the Time button Click OK Format No of Time File Insert Pages Name Picture Page Date File Sheet No Location Name Adding fields to a custom footer

22. You will not be able to see your headers and footers in Normal view Click on Print Preview to see the results Click on Print to print a copy Close and save your spreadsheet Previewing your headers and footers