Chapter 7 - Formula Referencing

Training Guide

Microsoft Excel 2010

– Level 1

7 –

Formula referencing

Microsoft Excel 2010 - Level 1

FORMULA REFERENCING

Formulas provide the key to working successfully with

Microsoft Excel. When you create a formula you write it

using cell references rather than actual hard-coded

values – this is how spreadsheeting works.

It is important to fully understand and comprehend this

concept to be able to exploit the full potential of Microsoft

Excel. There are indeed also some potential traps that you

can fall into with cell referencing if you don’t take enough

care.

INFOCUS

In this booklet you will:

9 gain an understanding of relative and absolute

cell addressing in formulas

9 learn how to confidently create relative formulas

in a worksheet

9 learn how to identify potential problems with

relative formulas

9 learn how to create absolute references in a

formula

9 learn how to create mixed references

Microsoft Excel 2010 - Level 1

ABSOLUTE VERSUS RELATIVE REFERENCING

Excel is a calculation tool and as such is

geared around the concept of formulas.

Formulas are entered into a worksheet

using cell references rather than actual

values. Each time a formula is entered

using cell referencing, a shape is created and

it is this shape which determines where

Excel goes to pick up values for use in the

calculation.

Understanding Formula Shapes

When you create a formula you don’t enter numbers into the formula – you enter cell

references. This creates a great time advantage when you need to duplicate that formula

in other cells. Formulas are merely shapes that reference cells in specific locations in the

worksheet or workbook. In the example below, say you have a formula in cell E5 that is

written as =D5*C5. To Excel this is interpreted as a shape that says “from my current

position (E5) go left one cell (D5), take the value there and multiply it by the value two

cells left (C5)”. This becomes the formula shape – left one multiply by left two.

When this formula, or shape, is then copied or filled to adjacent cells it is the shape that is

copied and the cell references within those copied formulas change relative to the shape

(which doesn’t change). For example, if you copy =D5*C5 from E5 to E6 the formula in E6

changes to =D6*C6 – but the shape is still – left one multiply by left two.

This is called relative referencing – because the cell address changes relative to the

current location.

Absolute Referencing

There will be times however when one or more cell references in a formula are always

required to be located in a specific cell, row, or column. In the example below, sales tax of

10% is located in cell B4 and must always be referenced from B4. The sales tax for the

dining table is calculated using the formula =B8*B4 while the sales tax for the sofa is

calculated using the formula =B9*B4. These are different shapes and if you were to copy

the formula from C8 (=B8*B4) to C9 it would copy the shape and the formula would be

=B9*B5 – which is wrong.

When dealing with cells in formulas that must be referenced from the same location, you

must fix these cell references by making them absolute. This is done by placing a $ sign in

front of either or both the row and column co-ordinate of the cell depending on which of

these has to remain fixed (e.g. $B$4, $B4, or B$4). In the example above, both row and

column co-ordinates need to be fixed and the formula in C8 should be written as

=B8*$B$4 before it can be copied to adjacent cells.

Microsoft Excel 2010 - Level 1

RELATIVE FORMULAS

Try This Yourself:

Open

File

Before starting this exercise

you MUST open the file

E811 Absolute_1.xlsx...

Click on B9 to make it the

active cell

Click on the Sum command

in the Editing group on

the Home tab of the

Ribbon to commence a

formula in the cell

Press to complete the

formula

Click on B9 again, and drag

the small square handle

(the fill handle) at the

bottom right corner of the

cell to E9

This will copy the formula

shape from B9 to E9…

Click in cell F6 to make it

the active cell

Click on the Sum command

in the Editing group to

commence a formula in this

cell

Press to complete the

formula

Click on F6 again, and drag

the fill handle to F9

This will copy the formula

shape from F6 to F9

For Your Reference…

To create a relative formula:

1. Click on the cell in which you want the

formula

2. Type or enter the formula and ensure

that the formula contains references to

other cells

Handy to Know…

• The formulas that we created above

copied successfully and correctly

because the same formula shape is

used in each of the cells in which the

formula was copied – this is the real

benefit of relative formulas.

Most of the formulas that you enter into a

worksheet, unless you are dealing with

quite complex mathematical modelling, will

be relative formulas where the cell

references will be able to change when the

formulas are copied to other locations. This

is particularly true of columnar worksheets

where there is a series of columns that all

have the same shape and structure.

Microsoft Excel 2010 - Level 1

PROBLEMS WITH RELATIVE FORMULAS

Try This Yourself:

Open

File

Before starting this

exercise you MUST open

the file E811

Absolute_2.xlsx...

Click on C11 where we

need to calculate the

product’s surcharge

Type =B11*B5

Notice the shape of the

formula, as indicated by

the highlighted cells…

Press to complete the

formula

Okay, now we can copy (or

perhaps fill, as the

destination cells are

adjacent) to the other

cells…

Click on C11 again

Move the mouse pointer to

the fill handle at the

lower right corner of the

cell and drag down to cell

C22

Yuk! The results are

meaningless!

Copying formulas can present problems

when the shape of the formula varies from

cell to cell. This can occur when one or

more values in a formula must be located

in a specific cell, row, or column – that is,

the formula must include an absolute cell

reference. In this exercise you will see some

strange values when you copy a formula to

other cells using the default copying method.

Handy to Know…

• So what has gone wrong with the formulas above? The whole problem hinges on the fact

that each of the formulas in column C uses a different shape from the other formulas. For

example, the first formula in C11 is =B11*B5. The shape of this formula is one left,

then six up and one left.

• The second formula in C12 has become =B12*B6 because it uses the same shape as

the originating formula in C11. In reality the formula should be =B12*B5. All of the

formulas above must reference the Head Office Surcharge of 12% which is in cell B5 –

that is, the cell reference must be absolute.

Microsoft Excel 2010 - Level 1

CREATING ABSOLUTE REFERENCES

Try This Yourself:

Same

File

Continue using the

previous file with this

exercise, or open the file

E811 Absolute_3.xlsx...

Click on cell C11 then click

on the B5 reference of the

formula in the Formula

bar at the top of the

screen

Press the Absolute key

several times and

notice how the reference

cycles through various $

sign configurations. Press

the Absolute key

until

B5 appears as $B$5

Press to complete the

editing

Click on C11, move the

mouse pointer to the fill

handle at the bottom right

of the cell, and drag down

to C22 to fill the formulas

down – successfully this

time

For Your Reference…

To make a cell reference absolute:

1. Press

to insert $ before the row

and column references, or type $

before each

To remove absolute references:

1. Press

until all $ are removed, or

delete the $ from the formula

Handy to Know…

• To make a row reference absolute,

press

twice to insert $ before the

row reference only. To make a column

reference absolute, press

three

times to insert $ before the column

reference only.

There will be times when you need to

create a formula which references cells

that must remain the same even when it is

copied around the worksheet. That is, you

will need to make the cell references

absolute. A cell reference in a formula is

made absolute by preceding its row

coordinate or its column coordinate or both

with a $ sign.

Microsoft Excel 2010 - Level 1

CREATING MIXED REFERENCES

Try This Yourself:

Open

File

Before starting this

exercise you MUST open

the file E811

Absolute_4.xlsx...

Click on E11 and type

=D11-(D11*E9) and

press

Use the mouse pointer to

fill down to E22 then

across to G22 – we’ve got

problems!

Click on E11 then click on

the first D11 reference in

the formula in the

Formula bar

Press three times until

the reference appears as

$D11

Click on the second D11

reference in the formula

bar and press

three

times until the reference

appears as $D11

Click on the E9 reference

in the formula bar, press

twice until the

reference appears as E$9

then press

Click on E11 then use the

mouse pointer to fill down

to E22 and across to G22

Sometimes problems with formulas are

more subtle. This often occurs when the

cell reference only needs part of its

coordinates locked in place. In this

exercise we will use a formula to create

three price lists. Because we will copy the

formula both across columns and down

rows, we will need to apply only partial

absolution to the cell references used in

the formulas.

Handy to Know…

• In this scenario we created a formula to calculate a discounted product price. As we copy

the formula to the right it picks up the wrong original product price. Then, as we copy

the formula down it may, depending upon which column it is in, pick up the correct

product price, but it then doesn't pick up the correct discount percentage. So, as we copy

the formula across we need to ensure that the column coordinate pointing to the original

product price is locked, but that the row is free to change as we copy down: =$D11-

($D11*E9). However, the discount percentage is always in row 9. Therefore the formula

needs to be further modified: =$D11-($D11*E$9).

Microsoft Excel 2010 - Level 1

CONCLUDING REMARKS

Congratulations!

You have now completed the Formula referencing booklet. This booklet was designed to

get you to the point where you can competently perform a variety of operations as listed in

the objectives on page 2.

We have tried to build up your skills and knowledge by having you work through specific

tasks. The step by step approach will serve as a reference for you when you need to repeat

a task.

Where To From Here…

The following is a little advice about what to do next:

• Spend some time playing with what you have learnt. You should reinforce the skills

that you have acquired and use some of the application's commands. This will test

just how much of the concepts and features have stuck! Don't try a big task just yet if

you can avoid it - small is a good way to start.

• Some aspects of the course may now be a little vague. Go over some of the points

that you may be unclear about. Use the examples and exercises in these notes and

have another go - these step-by-step notes were designed to help you in the

classroom and in the work place!

Here are a few techniques and strategies that we've found handy for learning more about

technology:

• visit CLD’s e-learning zone on the Intranet

• read computer magazines - there are often useful articles about specific techniques

• if you have the skills and facilities, browse the Internet, specifically the technical

pages of the application that you have just learnt

• take an interest in what your work colleagues have done and how they did it - we

don't suggest that you plagiarise but you can certainly learn from the techniques of

others

• if your software came with a manual (which is rare nowadays) spend a bit of time

each day reading a few pages. Then try the techniques out straight away - over a

period of time you'll learn a lot this way

• and of course, there are also more courses and booklets for you to work through

• finally, don’t forget to contact CLD’s IT Training Helpdesk on 01243-752100