Spreadsheets Exercise 5

Spreadsheets – Exercise 5

  1. Create the following table in a new spreadsheet.

Computers R Us
ITEM / PRICE
Printer / 125.00
Scanner / 175.00
Mouse / 12.00
System Unit / 250.00
Keyboard / 35.00
Monitor / 150

2.Change the font of the heading “Computers R Us” to Arial size 16.

  1. Embolden the heading.
  1. Insert a new column between ITEM and PRICE called AMOUNT SOLD.
  1. Enter the following information into the new column:

ITEM / AMOUNT SOLD
Printer / 5
Scanner / 3
Mouse / 27
System Unit / 9
Keyboard / 5
Monitor / 8
  1. Add a new column to the right of PRICE called TOTAL.
  1. Embolden all column titles (e.g. ITEM, AMOUNT SOLD, etc).
  1. Embolden all ITEMS (e.g. Printer, Scanner, etc)
  1. Enter a formula to calculate the TOTAL for each item by using the correct formula (e.g. AMOUNT SOLD multiplied by PRICE)
  1. Format TOTAL cells to Currency format with the pound sterling sign.
  1. Format PRICE cells to Currency format with the pound sterling sign.
  1. Use the spell check facility and amend if appropriate.
  1. Insert a new sheet before the sheet you are currently working on and call it page 1.
  1. Copy all text and figures from the old sheet to the new sheet, called page 1.
  1. Return to the old sheet and create a Pie Chart, on that sheet, based on ITEM and AMOUNT SOLD columns only
  1. Use “Number of Items Sold” as the title for the chart.
  1. Use data labels to show value and include a legend.
  1. Modify the segment colours in the pie chart to yellow, blue, red, green, grey and white.
  1. Insert your name in the Header.
  1. Save the spreadsheet as EX5COMPLETE in your working area within a Spreadsheet folder.

Spreadsheets

Ex 5.

1. Type out all of the text leaving spaces as shown on exercise 5.

Don’t type in formula, leave these cells blank for now.

2. to 3. You should be able to complete these questions using the answers from the previous exercises.

4. Highlight Column B (as shown below)

Select Insert>Column

5. Type in the relevant information

6. to 12. You should be able to complete these questions using the answers from the previous exercises.

13. Select Insert>Worksheet, double click on the new worksheet


Type in ‘page 1’ in the blank space that appears.

14. Click on Sheet 1 to select your initial worksheet

Highlight all of your text and figures and then copy them onto the other worksheet (page 1)

15. Click on Sheet 1 to select your initial worksheet again

Select Insert>Chart

Select Pie Chart

Then click Next >

Click and drag to highlight the range A3 to B10

Excel previews your chart – click next

16. You will have a screen similar to the one below


In the Chart Title Box – type in ‘Number of Items Sold’

17. Click on the Legend Tab


Ensure there is a tick in the show legend box.
Then click on the Data Label tab

Ensure that you have clicked in the Show Value ‘radio’ button
Click on next, and then click on finish.
18. Double click to select a segment of your Pie Chart
The following window will appear


Click on the relevant colour and click OK. Repeat this for each segment.
19. View>Header and Footer – type in your name
20. Save as instructed

W:\I.T\_KS3 MATERIALS\Framework\Samples\Spreadsheets\Spreadsheets Exercise 5r.doc