MICROSOFT EXCELTopic 5.1

INTRODUCTIONWHAT DOES A SPREADSHEET LOOK

LIKE?These are know as ‘Columns’. A,B, C ect…These are know as ‘Rows’. 1,2,3 ect…

A box in a spreadsheet is called a ‘cell’. A selected

cell is known as the ‘Active cell’

Information in the spreadsheet is know as the ‘Data’. Information we use in formulas is called a ‘Value’.

Each cell is identified by a unique ‘cell reference’. The first part consists of the column letter and the second part consists of the row number. Ie. Cell Reference: F9, is the selected cell as it belongs to column F and row 9.

A ‘Label’ describes what is happening in a cell.

WHAT ARE SPREADSHEETS USED FOR?

To create long lists of data. To create charts and graphs from

the data you input. To monitor aspects of businesses

such as sales, costs, wages and expenses. Some people monitor their wages, weekly house costs and manage their finances and budgets using spreadsheets.

To display both numeric and text information.

WHY SHOULD I USE A SPREADSHEET RATHER THAN A PEN AND PAPER? More effective data handling as formulae can

be used to solve calculations instantly. You can carry out’ what if scenarios’ such as ‘how

much money would I have left if I brought a new car?’.

The information can be presented in a variety of ways as it can be formatted and also generated into tables, graphs and charts. Presentation is more flexible.

Can be used as a model for real life scenarios. Much quicker than using a pen and paper. Can make alterations quickly to data. More reliable than pen and paper.

ADDITION FORMULA When we produce formulas they always begin with

an ‘=‘. Our cell references always belong inside brackets in

our addition calculations, don’t forget to close them! Next we enter our cells in the calculation using their

cell references, we wish to add these items so we include the ‘+’ in-between the cells. I.e.. =(A2+A5) and press enter.

We can create alternative formulas using /,*,-, these do not include brackets i.e. =A2*C3

AUTO-SUM FORMULA…. Auto-sum is a quicker way of completing an

addition calculation for an infinite amount of figures. First we

highlight the cell values we wish to add together.

Next we select the ‘Auto-sum’ button on the right hand side of the main menu.

On pressing, we will see the total of our values appears under the selected cells.

** We can also experiment with the average function part of the Auto-sum button.

TASK 1 Find the ‘Pet Food’ spreadsheet on the ‘Lesson

1’ page of the Excel website and ‘Save As’ to your user area.

You are to fill out the ‘Pet column’ with animals of your choice, the ‘Food Price’ with how much you think that particular pet’s food costs. Then you decide how many bags of food that animal eats per year and fill that column in also.

Your task is to use the shown Auto-sum formula to calculate totals for the cost for one bag of each animals food and how many bags per year all the animals eat.

Place these in a totals column.

EXTENSION… Use a multiplication formula to calculate the

cost of buying the bags of food for a particular animal all year.

MEMORY TEST… In pairs, list as many advantages of using

spreadsheets as you can think of in 1 minute. When you’ve written them down stick your

post-it note around the whiteboard.

ACTIVITY 2 For the next 15 minutes you are to explore the

formatting capabilities of Microsoft Excel using the tool bar available and the format cell options on right click.

Your challenge is to edit all of the cells on your spreadsheet which contain values or data.

This may be altering the shading, fill, text, font, highlighting, margins and lines.

It must look appropriate and be consistent throughout your spreadsheet – no rainbow effects please!

WHEN FINISHED…. When you have completed your spreadsheet

please send an email to your teacher with your work attached before you leave.

Feedback will be given back to you after your work has been marked.

LAST 5 MINUTES….

Well done for working as instructed, we will now finish the lesson with a game to test your memories!

‘Dunk The Teacher’ – Good Luck!