Broughton Hall High SchoolSpreadsheets Exercise 3

Spreadsheets – Exercise 3

  1. Create the following table in a new spreadsheet.

HALF YEAR BUDGET
JAN / FEB / MAR / APR / MAY / JUN
GAS / 356.80 / 410.50 / 390.75 / 345.24 / 315.10 / 305.80
STATIONERY / 56.60 / 76.90 / 69.35 / 54.35 / 77.40 / 51.70
PETTY CASH / 56.00 / 24.00 / 24.00 / 24.00 / 30.00 / 30.00
ELECTRICITY / 625.00 / 450.00 / 400.00 / 336.00 / 320.00 / 310.00
GAS / 120.00 / 90.00 / 46.00 / 38.00 / 32.00 / 30.00
TOTAL / formula / formula / formula / formula / formula / formula

2.Change the font of the heading “Half Year Budget” to Times New Roman size 16.

  1. Embolden the heading.
  1. Embolden all column titles (e.g. JAN, FEB, etc).
  1. Embolden all Row titles (e.g. GAS, etc, including TOTAL)

6.Using the AutoSum feature create totals for each of the months.

  1. Add a new column title to the right of JUN called AVERAGE (format the column title in line with the other column titles).
  1. Create a formula (Insert a function) to calculate the Average for each of the items (e.g. the average for GAS, etc)
  1. Format all numerical cells to Currency.
  1. After ELECTRICITY insert a new row and enter the following information:

TELEPHONE / 250.60 / 280.50 / 190.75 / 315.40 / 215.15 / 278.80
  1. Ensure the new row has the same formatting formulas as the rest of the table.
  1. Centre all the column titles from “JAN to JUN”
  1. Enter your name in a Footer.

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

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

Spreadsheets

Ex 3.

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

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

2. to 7. You should be able to complete these questions using the answers from the last two exercises.

8. Click in cell H4 and then click on the FUNCTION icon

(Shown below)

The following window will appear

Ensure Average is selected in the function name and click OK

The ‘Average’ box should appear – see the next page.


The cell range should be B4 to G4, if it is, Click OK.

IF NOT click on the red arrow (shown below)

Click and drag to select the cells from B4 to G4, press Enter and then click OK

Copy the formula into the cells for the rest of the items including the total (you can merely click and drag the black cross from the bottom left hand corner of this cell – see previous ex’s if you are unsure)

9. Highlight all the cells containing figures

Select Format>Cells>Number>Currency

10. Highlight Row 8 and select Insert >Row

Insert the relevant text in this row

11. Copy the formatting/formulas into this cell (you can merely highlight H7 then click and drag the black cross from the bottom left hand corner of this cell – see previous ex’s if you are unsure)

12. Highlight cells B3 to G3 and select centre.

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

Type your name in the section of your choice.

14. Highlight table – select relevant border option (see previous ex’s if unsure)

15. Save as instructed.