Section 1

Scenario: You have just started work at a dog kennel that uses Excel to keep a record of all the dogs that come to stay, the staff and the expenses. As you learn more about the business you find that the workbook has not been set up quite the way you want it. You have been asked to make improvements and update some records.

  1. Open the spreadsheet named Kennels.xlsx –save this workbook as Kennels-yourname in the area of your choice.
  2. Use the correct tool to automatically resize the width of column C so that the address can be displayed in full.
  3. Change the width of column A to 12
    Change the width of column B to 10
  4. Select cells A3 to D3 and change the formatting of the text to:
    Font style:ArialFont colour: Blue
    Font size: 11 ptEmphasis: Bold
  5. Select cells A3 to D3 and format the cells with a light green fill.
  6. A new employee called Sammy Lennon has just started working at the kennels and you need to add his details to Sheet1. Create a new row just below Ben Harris and enter the following details in the newly created row:
    Name: Sammy Lennon
    Address: 3 The Elms, Boxton
    Starting date: 1 August 2007
  7. The worksheet should also have a column for the employees’ phone numbers. You will need to create a new column after the Address column and type the heading Phone. Resize the width of the new column to 15.
  8. Jenny Ryder has decided to do some travelling and has left the company. We don’t need her details any more so delete the row containing her details from the worksheet. Ensure you don’t leave a blank row.
  1. The workbook would be easier to use if the worksheets had more relevant names.
    Rename the worksheets as follows:
    Sheet1 = Staff
    Sheet2 = Dogs
    Sheet3 = Expenses
  2. Go to the Dogs worksheet. The border is not required. Select the cells that contain a border and remove the border.
  3. In your day-to-day work you’ll mostly use the Dogs worksheet so it would be better if it was the first sheet in the workbook. Move the sheet called Dogs so that it is the first worksheet in the workbook.
  4. You’ll need another worksheet to keep details of the customers. Create a new worksheet, name it Customers and place it at the end of the workbook.
  5. The worksheet called Dogs holds the details of the pets’ owners, but they should really be on the Customers sheet.
    In the Dogs worksheet, movethe details of the customers in cells F3:H9and paste into cell A1 in the new worksheet called Customers. Resize the columns as appropriate so all data is visible.
  6. Return to the Dogs worksheet. It needs some tidying up and formatting.
    Columns F, G and H are now not required; delete these columns.
    Resize column C so that all data is visible.
    Resize column D so that it is not so wide.
  7. Go to cell A5 and enter 125 – use the fill handle to replicate the data (up to cell A9) so that the next figure will be 126 and so forth.

Hint: you must select two cells so that the number sequence is recognised

  1. Format the data in cells H4 to H9 as: currency, 2 decimal places and the £ sign.
  2. Format the date in cells F4 to F9 so that it is the same date format as column G.
  3. Go to the Staff worksheet. Place the cursor in cell A3 and use the Format Painter to copy the formatting of this cell to cells A1 to C1 in the Customers worksheet.
  4. Return to the Staff worksheet. Merge and Centre the cells A1 to E1. Bolden this text and increase the font size to 12pt.
  5. One of the headings on the Staff worksheet does not represent good practice.
    Which column is it and what would have been a better option for this data?
  6. The Expenses worksheet is no longer required. Delete this worksheet.
  7. Save the spreadsheet using the same filename. Close the workbook.

Section 2

The owner of a small cafe uses this worksheet to record what food is sold each day. The information is hard to read – so you will have to apply some formatting changes to the text to make it easier to read.

  1. Open the spreadsheet named Café fare.xls
    Save this file as an Excel 2010 document with the filename Cafe-yourname
  2. The text in the workbook is small and difficult to read. Change the size of the text in the entire worksheet to 10pt.
  3. The headings in row 1 should stand out more clearly. Change the size of the text in row 1 to 12pt, bold, and make the colour of the text darkblue.
  4. Fill the cells in the range A1toG1 with a light turquoise (aqua) background fill colour.
  5. Change the height of row 1 to 22.
  6. Column A is not wide enough to display all the text – widen this column so that all data is visible.
  7. Cell A2 contains a heading so it should stand out clearly. Change the font type in cell A2 to Tahoma, make it bold and give the cell a light green fill.
  8. There are other headings in column A and they should have the same formatting as Cell A2. Copy the formatting from cell A2 to cells A5, A9, A16, A21 and A25
    Hint: remember to use the Format Painter tool
  9. Centre align all numerical values in the cell range C3 to G30.
  10. Format the cells B3 to B30 as currency, with 2 decimal places and the £ sign.
  11. To make the worksheet look more like a table when printed, add borders around all the cells in the range A2 to G30
  12. Add a thick box border around the cells A1 to G1.
  13. Change the cell alignments as follows:

Cell A1 / horizontal alignment:left / vertical alignment: centre
Cell B1 / horizontal alignment: right / vertical alignment: centre
Cells C1 to G1 / horizontal alignment: centre / vertical alignment: centre
  1. Shepherd’s pie is no longer on the menu. Delete this record and ensure you do not leave a blank row.
  2. RenameSheet 1 as Menu.
  3. Freeze the top row of the Menusheet.
  4. Save the spreadsheet leaving the workbook open.
  5. Open the document named Staff records and go to Sheet 2. Rename this worksheet as Staff.
  6. Select cells A1 to F1 and format the cells to wrap text.
  7. Move the Staff worksheet from Staff records to the workbook named Cafe-yourname and place this sheet at the end of the workbook.
  8. Change the magnification of the Staff worksheet to 75% by using the Zoom tool.
  9. Save and close the Staff records.
    Save and close Cafe-yourname.
  10. True or False?
    The horizontal alignment option allows you to position data relative to the left and right margins of a cell.
    Changing the magnification of a worksheet to 50% will decrease the size of data on the screen but this change in magnification will not affect the printed worksheet.
    When a new row is inserted, the new row appears below the selected row.
    When a new column is inserted, the new column appears to the left of the selected column.
  11. You have now completed this task. Please ensure you have the following files ready to email to the team for checking
    SS Task 2-yourname (this document completed)
    Kennels-yourname
    Staff records
    Cafe-yourname

L:\MOLU\MOLU common folder\ECDL taught sessions\Spreadsheets\ECDL SS task 2.docx1