(c) Corb

ett 2

01

1

THE POCKET MONEY CALCULATORYear 7 – Excel Tutorial

(c) Corb

ett 2

01

1THE CHALLENGE!

Imagine that you are a young budding business entrepreneur (i.e. the boss of a business).

Your aim is to make as much money as possible in only one week.

The customers are your friends and family. Earn money by completing jobs (e.g. washing

the car, cleaning your room or taking the dog for a walk etc...)

Use a spreadsheet to keep a record of your pocket money for each day.

Add up your pocket money for each day and the whole week.

(c) Corb

ett 2

01

1LOAD MICROSOFT EXCEL1. Click on Start.2. Select Programs.3. Select Microsoft Office.4. Click Microsoft Excel.

(c) Corb

ett 2

01

1

TASK ONE - OUTLINE

Create the Pocket Money Calculator table from scratch.

Use the Fill Handle to enter days of the week.

Save your work.

(c) Corb

ett 2

01

1TASK ONE – CREATE A TABLE1. Give your table a title in cell A1.2. Label your columns in row 2 with headings. 3. Enter the label Day in cell A2 and Total in cell F2.4. Label cell B2, C2, D2, E2 with the job (i.e. Car Wash).

Column A

Row 2

Title

Label

5. Double-click between each column to automatically re-size it so that the words fit in each cell.

(c) Corb

ett 2

01

1TASK ONE – FILL HANDLENow that you have given your table a title and labelled

the columns it is time to label the rows.

1. In column A under the label Day you should enter the days of the week (i.e. Monday, Tuesday etc). BUT WAIT!

2. There is a quicker way! Use the fill handle to quickly update the table with each day.

3. Simply enter Monday into cell A3.4. Now position the mouse cursor over

the bottom right corner of cell A3 to the fill handle.

5. Hold down the left mouse button and drag it down the column until you see Sunday displayed. Let go of the mouse button.

6. You will see the cells automatically update with the days of the week.

(c) Corb

ett 2

01

1TASK ONE – SAVE YOUR WORKDon’t let all your hard work go to waste. It’s now time to

save your work.

1. Click on the Office Button.

2. Select Save As and click on Excel Workbook.

3. Locate your user area and save the workbook with a meaningful name.

Locate userarea

Meaningful file name

Click Save

(c) Corb

ett 2

01

1

TASK TWO - OUTLINE

Update the Pocket Money Calculator table with the amount of money earned for each job.

Save your work.

(c) Corb

ett 2

01

1TASK TWO – UPDATE YOUR TABLENow that you have a table to work with you will need to

record how much pocket money you earned for each job.

1. Click on cell B3 and enter 0.5. This represents 50p and will be shown as £0.50 when you format the cells later on.

2. Continue to update the other job columns for each day of the week. You may leave some of the cells blank.

3. Please leave the total column blank – do not try to add up the totals by yourself. You will use a clever trick later on.

4. Now save your work.

Number Guide

0.25 = 25p0.5 = 50p1 = £11.25 = £1.251.5 = £1.50

(c) Corb

ett 2

01

1

TASK THREE – OUTLINE

Add formatting to your table:1. Create borders.2. Format text by making column A and row 2

bold.3. Centre align the column label headings.

Format the data (numbers) in your table to currency.

Save your work.

(c) Corb

ett 2

01

1TASK THREE – BORDERSNow that you have the basic information in your table

lets smarten it up so that people can easily read and understand it.

1. Make the table look like a table by giving it borders.

2. Click on cell A1 and hold down the left mouse button.

3. Drag over the columns and rows to cell F9 (or to the bottom right corner of your table if you’ve added extra columns).

4. Click on the borders button located on the toolbar and click All Borders.

(c) Corb

ett 2

01

1TASK THREE – TEXT FORMATTINGMake your labels stand out by formatting them.

1. Click on row number 2 so that it is highlighted.

2. Click the bold button located on the toolbar.

3. Click on column A so that it is highlighted.

4. Click on the bold button located on the toolbar.

Click here to select row 2.

Click here to select column A.

Choose the B button to make the text bold.

(c) Corb

ett 2

01

1

TASK THREE – CENTRE ALIGN HEADINGS Make the column labels centre aligned. Do not centre

align column A. To do this follow these instructions:

1. Click on cell B2 and hold down the left mouse button.

2. Drag across the row to the label Total.

Centre align

Click cell B1

and drag across

3. Choose the centre align button from the toolbar.

4. Re-size your columns by double clicking between each one.

5. Save your work.

(c) Corb

ett 2

01

1TASK THREE – FORMAT NUMBERSDuring task two you were asked to update your tables

with the amount that you had been paid for completing each job. The numbers did not correctly represent money. You are now going to format the numbers to show the value of currency (i.e. money).

1. Click and drag from cell B3 to cell F9 (or bottom corner of your table).

Continues on next slide...

(c) Corb

ett 2

01

1TASK THREE – FORMAT NUMBERS

2. On the Home toolbar (or ribbon) click the expansion button in the number menu.

Click here

3. The Format Cells form will appear.

4. Beneath category choose currency.

5. Make sure that there are two decimal places selected and the symbol is £.

6. Click OK.

(c) Corb

ett 2

01

1

TASK FOUR – OUTLINE

Add simple formulae to the table to add up the total amount for one day.

Use the fill handle to copy the same formulae to other cells in the Total column.

(c) Corb

ett 2

01

1TASK FOUR – SIMPLE FORMULAE

Your spreadsheet table is nearly complete. Imagine that the Chief Executive of a local cinema called Premier Blockbuster Movies heard about your business venture and is considering giving you a job.

The Chief Executive spoke with you on the telephone and said:

“You have shown great initiative and I want you to come and work for my cinema. Tell me how much money you have earned and the job is yours”.

Your challenge is to add up the total amount of money earned for each day using a formulae.

Continues on next slide...

(c) Corb

ett 2

01

1

TASK FOUR – CREATE SIMPLE FORMULAE1. On your table find the cell reference for the column

table Total and the row label Monday (cell F3).

2. Click on the Formula Bar and input =SUM(B3:E3)

Formula bar

Cell reference B3 should be the first column after the Day column and cell reference E3 is the last cell in the same row before the Total column. If E3 is not your last column please replace it with your own (i.e. F3 or G3).

Cell F3

The blue box identifies the cells in the formula.

(c) Corb

ett 2

01

1

TASK FOUR – FORMULAE & FILL HANDLENow that you have entered the formulae into cell F3 you will

see that it automatically adds up the row in the total column. If you want to repeat a similar formulae you can use the fill handle to save time.

1. Click on cell F3.

2. Locate the fill handle and drag down the column to cell F9.

Fill handle

3. You will see that all of the totals have updated with the correct sum.