Sem título

Página 1

A spreadsheet application such

as Excel can store all its data in a single file.

This file is called a 'workbook'.

A workbook is made up of one or more 'worksheets'. A worksheet is a single tab in the spreadsheet workbook.

Cells

□

A cell reference always starts with a letter

and ends with a number e.g. A5 or H28

□

A range of cells is a set of cells in a row or

column

□

Data entered into cells can be different data

types e.g. text, number, currency, date, percentage etc.

□

Each cell can contain:

§ Labels – any text in a cell describing the data

in a row or column(titles, headings that identify data)

§ Data – any values (text or numbers) that are

entered for the use of calculation

§ Formulas

and functions – mathematical

calculations carried out automatically

Formulas

□

Must start with an =

□

Formulas perform calculations on cell

contents.

□

Formulas appear in the formula bar.

□

The result of a formula is automatically

recalculated every time the data on a spreadsheet changes.

§

=(B3*C6)+E12

§

Below are the 4 main mathematical operations:

Ø

+ Add

Ø

- Subtract

Ø

* Multiply

Ø

/

Divide

Functions

□

Must also start with an =

□

Functions are part of spreadsheet software.

They are specialised calculations built in to the spreadsheet software.

□

Function name describes what a function can do

SUM, COUNT, AVERAGE, MIN, MAX, IF

§ =SUM(D2:F7)

Formatting

□

The content of a cell can be formatted:

□

Font style, size, colour, border style, alignment,

cell colour.

□

Multiple cells can be merged in to one cell

□

Cells are often formatted to reflect the data

that is held in them. Data types include:

□

Currency, number, real, integer (whole

number), date, text

Replicating cells

□

When you position the mouse pointer in the

bottom right hand corner of the cell and drag down or across the spreadsheet

tries to respond intelligently. If there is a formula in the cell, the

spreadsheet tries to adjust it as it goes down the page to fit in with the

new position and the cells being referred to.

Graphs & Charts

□

Using charts/graphs allows us to notice

patterns & trends more easily

□

Specific data can be selected on a spreadsheet

and a chart/graph of this data can be created.

§ Bar charts – to compare values

§ Pie charts – to show how a total is

divided among a number of different categories.

§ Line graph – display trends

Cell Referencing

□

When a formula is copied by ‘filling down’ the

cell references in cells are adjusted as you drag the pointer down the page.

□

Relative

cell references:

□

If a formula is copied by filling down, the

cell references are adjusted as you drag the replication pointer down the

page. This is because it uses the relative

address of the cells based on their position in the worksheet.

□

The

cells D2, D3 and D4 now contain formulas that are relative to the rows that

they are in. The formulas are relative to their position in the spreadsheet.

Relative cell references is

when a cell is used in a formula and the formula is copied to a new cell

reference (address) , the cell references (cell address) changes to take

account of the formula’s new position.

□

Absolute

cell references:

□

Some we need to use part of a formula that

does not change. For example is VAT was recorded in B2 as 20% then the B2

part of the formula must not change because the rate will be 20% for

everything. All the other cell ranges will change. When the formula is copied

down, the relative address changes but the absolute doesn’t.

□

These use the $ character in front of the letter and number that are required

to stay the same. Think of it as the $ as glue. It glues the absolute cell

into every formula it is needed in.

Absolute cell referencing is

where a reference to a cell used in a formula where, when the formula is

copied to a new cell reference (cell address), the cell reference (cell

address) does not change. It is glued in place by the $ character.

=$B3 would ‘glue’ column B

=B$3 would ‘glue’ row 3

=$b$3 would ‘glue’ that

specific cell

‘What if?’ calculations

□

Changing the data in a spreadsheet model

allows you to make and test predictions.

You may explore what would happen to your profit if VAT changed to 30%. These

are known as what if calculations.

They allow you to use a spreadsheet to see what would happen if certain

figures changed in a spreadsheet.

Benefits of using a

spreadsheet

□

Automatic recalculation

§ Every time the content of a cell is

changed, calculations are performed and other cells are updated if necessary.

□

Accurate calculations

§ Providing the formulas have been set up

correctly, the formulas will always produce a correct calculation

□

Graphs/Charts

§ Can be drawn using data in selected cells.

□

Replication

§ Once the first formula is correctly

entered, it is possible to drag across to adjacent cells in a row or column

so the computer copies the pattern of the calculation.

□

What if calculations

□

Formulas can be reused and stored

□

Calculations involving

large numbers are possible