Using Excel for calculation activities - KS1/2



Using Excel for calculation activities - KS1/2

|Activity 1 - ‘Water Conservation’ – individual/paired activity to model water usage (links to KS2 geography). |

|[pic] |

|Open the file from Moodle. Change the ‘number of uses per day’ to reduce water consumption. The spreadsheet automatically calculates |

|daily and weekly totals, and the graph changes to reflect this. |

|How to create this activity |

|Entering your data and expanding column width |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

|Putting a border around cells |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

|Adding colour to cells |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

|Changing the font colour |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

|Adding formulas | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

|Copying your formula |

| |

|[pic] |

| |

| |

| |

| |

| |

| |

| |

| |

|Using Autosum to add a column of numbers | |

| | |

|[pic] | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

|Adding a graph of daily water usage |

| |

|[pic] |

|[pic] |

| |

| |

| |

|To format the numbers on the Y axis |

| |

| |

| |

| |

| |

| |

| |

| |

| |

|Activity 2 - ‘Fraction/Decimal converter’ – whole class/group activity led by teacher . Possibly oral/mental starter |

| |

|[pic] |

|How to create this activity |

| |

|[pic] |

|Put your formula in to calculate the decimal equivalent |

|[pic] |

| |

| |

| |

| |

| |

| |

| |

| |

|To control the number of decimal places |

| |

|Select the relevant cell |

| |

|[pic] |

| |

|[pic] |

| |

|To insert your ‘post-it note’ to cover the answer |

| |

|[pic] |

| |

|Activity 3 - ‘Magic Pyramid’ – whole class or group activity for problem solving in maths, led by teacher. Numbers are entered into |

|bottom three ‘bricks’ of pyramid in order to make a given total in the top brick (e.g. 100). |

| |

|[pic] |

| |

|How to create this activity | |

| | |

|Select/highlight your sheet and change the background colour. Make the font bigger (e.g.36) for this selected | |

|area too. | |

|[pic] | |

|Enter your formulas. Each brick should be the total of the number in the bricks below. You may need to make the| |

|cells wider | |

| | |

| | |

|[pic] |

| |

| |

|Activity 4 - ‘Fizz buzz’ - Spinning buttons to generate numbers. Teacher led activity to solve number problems. |

| |

|[pic] |

|How to create this activity |

| |

|Insert a number (any number) into a cell. Increase the font size to at least 72. Colour the background |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

| |

|[pic] |

|[pic] |

|Activity 5 – ‘Picture This’. Spreadsheet modelling to help make decisions in Design and Technology. Individual or paired activity. |

| |

|[pic] |

|How to create this activity |

| |

|Enter text into your cells. Add background colour. |

|Enter your formulas. Notice the use of brackets to find perimeter. This could also be written as =D11+D11+E11+E11 (i.e. perimeter is |

|total distance around the outside) |

|[pic] |

|[pic] |

|You may wish to protect your worksheet if you want children to be able to change only particular cells |

| |

|[pic] |

| |

| |

| |

|[pic] |

| |

| |

| |

| |

| |

| |

|Activity 6 – ‘Sorting data and conditional formatting’ |

| |

|Open the file ‘Sorting data and conditional formatting’ |

|[pic] |

| |

| |

|To sort the data alphabetically |

| |

|[pic] |

|[pic] |

| |

| |

|Conditional formatting –(e.g. to highlight those falling below particular scores) |

| |

|[pic] |

| |

| |

| |

|[pic] |

| |

-----------------------

Enter the row heading in the relevant cells.

Expand/reduce the cell widths as necessary: go between the cell heading AB and either drag the column or double click

Continue to enter the column headings and change the size of the cells to accommodate them.

Select/highlight all of the cells. In the Borders drop-down menu, select All Borders.

Select/highlight all of the cells. In the Fill Color drop-down menu, select your colour.

Select/highlight all of the cells. In the Font Color drop-down menu, select your colour.

Enter the rest of your data.

Calculations in Excel always begin with =

In this case it will be:

The cell with the amount of water per flush (C2) x the cell with the number of flushes(uses) per day (D2). We write this as:

=C2*D2 Then press enter

The weekly total will be: =E2*7 (i.e. the daily total x 7 days). Enter.

For the 4 operations use:

+ add; - subtract; * multiply; / divide

You can enter formulas all the way down your column. Alternatively, click on cell E2 (with the daily total of flushes). Hover the mouse point over the bottom right-hand corner of the cell, until it becomes a cross hair +. Drag down the column. The formula pattern should be repeated down the column.

Repeat the process for the Weekly total column.

To add a column of number you could use

= E2+E3+E4, etc. A quicker way is to use Autosum

Click in the cell where your total needs to be (e.g. E9). Go to the Formulas tab. Choose Autosum. The column of numbers to add should become surrounded by a dotted line. Press enter.

Repeat the process for the weekly total.

Select (highlight) the information in the Daily Activity column. Hold control down on your keyboard and select (highlight) the Daily Total column information.

Go to the Insert tab and choose your graph type – a ‘column’ (bar chart) in this case

Move the graph into position

Go to the Format tab.

Select Vertical (Value) Axis. (May currently say Chart area)

Remove ‘post-it’ to reveal the answer

Enter your text.

Select the relevant cells and make the font bigger

Change the colour of the background

Put a border at the bottom of cell C2 (under the numerator

If your answer is incorrect, make the cell wider

In the Home tab, select Format – Format Cells

In the Number tab, select Number.

Specify the number of Decimal places.

Select the ‘Insert’ tab.

In Shapes make your choice.

Click on your Excel sheet and drag the shape to the size you require

Put a border around your bricks. Colour the top 2 rows of bricks.

Use a different colour for the bottom row (where you will input numbers to make 100)

Children will give numbers to enter in the bottom row of bricks to make your given target number. Can they give alternative ways of making the target number?

Click on the File tab (or the Microsoft Office button in version2007) and then Options.

Choose ‘Customize Ribbon’

Tick the ’Developer’ tab. OK.

The Developer tab will now be shown in the ribbon. Click on the Developer tab.

Insert a spin button from the ‘Form Control’. Draw the spin button out.

Right mouse click on the spin button and format control

In Cell link, give the reference for your cell (in this case D7).

Notice you can also change the ‘increment’ (e.g. go up in 2s or 3s).

Click OK, click on any cell to deselect your spin button, and then test your spin button.

Cell D7

Length x width

Cost of wood x perimeter

Cost of plastic covering x area covered

Cost of wood + cost of plastic covering

Distance around the outside of the frame. Could also be written as =D11+D11+E11+E11

Go to the Insert tab to get your Word/Smart Art heading and to insert a picture

Click and drag to select the cells YOUDO NOT WISH TO LOCK. In this case D11 and E11 because children will input data here.

In format, choose Lock Cell (this has the effect of unlocking as all cells are locked by default once we protect the worksheet).

Go to Format and Protect Sheet

Select the column of data you wish to sort (e.g. Last name, alphabetically)

Go to the Data tab

Click on Sort

Expand the selection (because you want the rest of the data to be resorted along with the last name).

Sort

In the Home tab, select Conditional formatting – Highlight Cell Rules – Less than

Select the column Marks /10

Write the value 5 (i.e. you wish the marks below 5 to be formatted differently, so they stand out)

-----------------------

Page1

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download