Spreadsheets Exercise 1
In this exercise you will: Create a new spreadsheet workbook/file Insert and manipulate text and numbers Use the AutoFill function Save and name a worksheet Format data to currency format Perform basic calculations using the AutoSum function Replicate formulas Insert rows and amend data in a spreadsheet Apply shading to columns Add a header and footer Change the page orientation Print preview the spreadsheet Create a chart Format a chart
University of Ulster 2002/2003
1
1. 2.
Open the MS Excel 2000 application. Type the following title in cell A1: Warm Wear Half Year Returns
3. 4.
Make the title size 12 font and a dark blue colour. Starting in cell A3, type the following column headings: Item Jan Feb Mar Apr
Note: The first column heading ‘Item’ should be left-justified and bold. The other column headings should be right-justified and bold. 5. Under the ‘Item’ column heading enter the following row labels in bold. Gloves Hats Balaclavas Sweatshirts Woollen Socks Scarves Average Sales Note: You may need to increase the width of the ‘Item’ column to display the row labels in full. 6. Use the AutoFill feature to add the following column headings: May Jun
Hint: Place your mouse at the bottom right-hand side of the last cell, the pointer should change to a ‘+’, click and drag horizontally using your mouse. 7. 8. Save your spreadsheet and give it an appropriate name (e.g. clothing.xls). Add two other column headings, making sure they are right-justified and bold: Unit Price (£) Half Year Totals (£)
Note: If necessary, resize the column widths.
University of Ulster 2002/2003
2
9. Item
Enter the following data: Jan 42 32 19 45 41 Feb 29 13 3 51 21 Mar 23 9 6 36 47 Apr May 14 9 5 24 31 17 3 0 28 22 Jun Unit Price Half Year Totals (£) (£) 7 6.65 0 2 17 15 7.75 12.65 16.95 4.97
Gloves Hats Balaclavas Sweatshirts Woollen Socks Scarves
11
4
5
18
9
4
6.97
10. 11.
Save the spreadsheet. Rename the sheet Half Year Sales.
Hint: Choose the Format/Sheet/Rename menu options. 12. Insert a new column between the column headings Jun and Unit Price (£). Give it the heading Total Number Sold and ensure that it is right-justified and bold. Resize the column width if necessary. Change the figures given under the heading Unit Price (£) to a currency format.
13.
Hint: Highlight the relevant cells and from the Format menu choose Cells/Number/Currency. Make sure that the £ (English United Kingdom) and two decimal places options are highlighted. 14. Use the AutoSum feature gloves sold between Jan to calculate the total number of and Jun.
15.
Replicate the formula for the other items of clothing.
Hint: Place your mouse at the bottom right-hand side of the last cell, the pointer should change to a ‘+’, click and drag vertically using your mouse. 16. Use a formula to calculate the Half Year Total (£) of gloves purchased between Jan and Jun.
Hint: Multiply the Total Number of Items Sold by their Unit Price. 17. Replicate the formula for the other items of clothing.
University of Ulster 2002/2003
3
18.
Calculate the average sales per month.
Hint: Use the Paste function 19. Display the result to two decimal places using the Decrease Decimal function.
20. 21. 22.
Replicate the formula across the remaining months. Save your spreadsheet. There are a few mistakes in the spreadsheet. Make the following changes: The number of hats sold in March was 18 The number of gloves sold in June was 5 The number of balaclavas sold in February was 10
23.
Add a new row to show the sales of sunglasses. Insert the row after ‘Hats’ and before ‘Balaclavas’ and add the following data: Jan Feb 5 2 Mar Apr May Jun Total Number Unit Price Half Year Sold (£) Totals (£) 3 8 10 25 3.50
Item Sunglasses
24. 25. 26.
Ensure the Unit Price for the Sunglasses is displayed in a currency format. Calculate the Total Number Sold and Half Year Total of sunglasses. Apply a yellow colour to the Total Number Sold column and a green colour to row 3. Create a border around each cell in the spreadsheet.
27.
Hint: Highlight the relevant cells and choose the Format/Cells/Border/Outline & Inside option from the menu. 28. 29. 30. Save your spreadsheet. Change your Page Orientation to Landscape. Add the following Header and Footer to your spreadsheet, use Arial, bold and a size 8 font: Header: Footer: Spreadsheet Exercise Date Your Name Page Number
Note: The header and footer will only be applied to this sheet.
University of Ulster 2002/2003
4
31.
Use the Print Preview function to check your Header and Footer and also to ensure that your spreadsheet fits on to one page. Create a Clustered Column chart to display the number of each item sold between Jan and Jun.
32.
Hint: Highlight cells A3 to G10 and click on the Chart Wizard icon. 34. Ensure the chart includes the following: 34. 35. 36. 37. 38. A Title: X-Axis Title: Y-Axis Title: Legend Total Number of Items Sold between Jan - Jun Items Number Sold
Insert the chart on a new sheet labeled Half Year Totals. Change the size of the chart title to size 16. Format the text alignment of the X-Axis to 45 Change the scale of the Y-Axis from 10 to 5. Save your spreadsheet and exit from the spreadsheet package.
University of Ulster 2002/2003
5