Broughton Hall High Schoolspreadsheets Exercise 2

Broughton Hall High SchoolSpreadsheets Exercise 2

Spreadsheets – Exercise 2

Create the following table in a new spreadsheet.

House of Wearing

Summary of Departmental Income

Department

Budget

Income

Difference

Bedding

1026

1137.64

formula

Kitchen

3834

4025.38

formula

Materials

766

1055.66

formula

Gents Clothing

2069

2256.25

formula

Ladies Clothing

3925

4038.50

formula

Children’s Wear

3070

4068.05

formula

Stationary

542

1060.34

formula

Music Store

1685

1976.45

formula

Hi-fi and TV

1067

5271.68

formula

TOTAL

formula

2.Put borders around the whole of the table and between each cell.

3.Change the font of the heading “House of Wearing” to Arial size 18.

4.Change the font of the sub-heading “Summary of Departmental Income” to Arial size 16.

Embolden the heading and sub-heading.

Embolden all column titles and “TOTAL”

7.Using the AutoSum feature create totals for the Budget and Income.

8.Create a formula to calculate the difference between Budget and Income for each department (i.e. Income - Budget)

Format all numerical cells to two decimal places and with a thousand seperator.

Change the width of the Department column to 18.

Centre all the column titles from “Department”…to “Difference”

Insert a new row after the Bedding Department, and enter the following:

Children’s Footwear

2300

2763

formula

Create a formula for the difference for the new department and ensure that this new record is updated in your total formulas.

Enter your name in a Header.

Use the spell check feature and amend if necessary.

Save the spreadsheet as EX2COMPLETE in your working area within a Spreadsheet folder.

Spreadsheets

Ex 2.

1. Type out all of the text leaving spaces as shown on question 2.

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

2. Highlight all of the cells – this should be A16 to D16, and then select your Border

Option as shown below.

Choosing this option will place the appropriate border around the whole table and each cell.

3. Highlight the heading and then make the relevant changes

4. Highlight the sub heading and then make the relevant changes

5. Highlight A1 and select Bold, then A3 and select Bold

6. Highlight A5 to D5 and select Bold, then A16 and select Bold

7. Click in cell B16 and then click on the AutoSum icon

(Shown below)

Excel automatically selects the most likely formula for this cell,

It should be =SUM(B7:B15)

If you don’t have this cell range, click and drag from cell B7 to B15.

Press Enter.

Now, copy this formula into cell C16 by dragging the black cross over from the B16.

(See previous exercise answers if you are unsure)

8. Position the cursor in cell D7, then click on the = sign

Highlight Cell C7, Press – (the minus sign on the keyboard)

Highlight B7 and then press Enter.

Highlight D7 and then drag the black cross in the bottom left hand corner of the cell to D16.

9. Highlight all the cells containing figures

Select Format>Cells>Number>Currency

Ensure that 2 decimal places and thousand separators are selected

10. Highlight Column A, Select Format>Column>Width

Type in 18 and then click OK

11. Highlight cells A5 to D5 and select centre.

12. Highlight Row 8 (as shown below)

Select Insert>Row from the main menu. Type in the specified text

13. Copy the formula in D7 to D8. Your total formula should update accordingly

14. View>Header and Footer, then Click on Custom Header

Type your name in the section of your choice.

15. Use the spell check feature; click yes if the ‘Do you want to continue checking at the beginning of the sheet’ appears

16. Save as instructed.

W:\I.T\_KS3 MATERIALS\Year 9 04\Spreadsheets\Spreadsheets Exercise 2.doc