Before you start writing complicated formulas in Excel it is
imperative that you understand how cell referencing works.

To make this example easier to understand you can download
the attached file and follow the examples in this post. Click Here for the file.

First let’s understand the setup of an Excel spreadsheet.
Let’s think of it as a grid with the X axis being columns and the Y axis being
rows.

Columns are designated with letters, where rows are marked
by numbers. Refer to the graphic below.

To reference a cell we would
follow the XY framework with the column letter(s) first, and the row number
second. For instance A5 would reference the first column and the fifth row.

Excel formulas and Cell
Referencing

First of all, when typing a
formula in excel an = sign must precede any argument or logic, for instance if
we wanted to add cells B5 and B6 we’d type =B5+B6. It works the same for other
basic functions as well such as subtraction, multiplication, division, and so
on.

Relative and Absolute
Referencing

The way we referenced a cell
earlier, A5, is refered to as a relative reference. Meaning if you copy or drag
a formula containing those references they will move in relation to the cell
position. Let’s add a commission calculation to the sample file.

Select cell C2 and type
COMMISSION

In cell C3 type =.05*B3

Copy cell C3 (with the cell
selected right click and select Copy, or press Ctrl+C)

Left click on cell C3 and
move the cursor down to C12 this should highlight all the relevent cells.

Now paste the formula (press
Ctrl+V, or right click and select Paste)

Look at the formula in the
formula bar and you can see that the cell reference moves down with each row.

Now suppose this company
wants to assign a percentage of Sales Revenue to a cost such as Marketing, for
budgeting and planning purposes. Let’s use this as an example for absolute
referencing.

Add a row above row 1. (Left
click on the number 1, then right click and select insert) or (highlight any cell
in row 1 and press shift+spacebar, then press Ctrl and Shift and + at the same
time)

In cell A1 type MARKETING
BUDGET RATE

In cell C1 type .05 (Assuming
they’ll use 5% of their Sales Revenue to spend on Marketing) I used C1 because
we don’t have to adjust column widths this way.

In cell D3 type MARKETING
BUDGET

For this column we do want to
autofit our label. We can do this by double clicking the line between the
column D and E labels along the top, or by selecting the entire column
(Ctrl+Spacebar) and pressing Alt+H+O+I.

Now we’re ready for our
formula, select cell D4

Type =B4*$C$1 in cell D4. See
exlplanation of dollar signs below.

To apply this formula to the
entire data set we have to lock the cell reference (make it absolute) to lock
in a cell reference put a $ sign in front of the row or column you want to lock
in our example we want to lock both row and column, $C$1.

Copy the formula in D4 and
paste it to cells D4 through D13. You should notice that C1 is in the
calculation on each row.

Suppose we wanted to change
the amount for the marketing budget. Simply select cell C1 and type .035. You
should now see different numbers in column D than before.

This should demonstrate the
power of absolute referencing in spreadsheet automation. (you don’t have to
manually adjust formulas like you would for relative references) Of course
every situation is different and we have to use these techniques when each is
appropriate.

We should change the
commission to an absolute reference with an adjustable number at the top as
well. (Like the marketing budget) If everyone is being paid the same we will
want the ability to change the rate easily.

Select Row1 and insert a row.

Type COMMISSION in cell A1.

In cell C1 type .05

Go to cell C5 and type
=B5*$C$1

Copy C5 and paste in cells C5
through C14.

Totaling The Columns

Let’s cap off the example by
totaling each column on our spreadsheet. We’ll use a SUM function.

Select cell A16 and type
TOTAL.

In cell B16 type =SUM(B5:B14)

Copy cell B16

Paste in cells C16 and D16

You should now have a basic
grasp of excel cell referencing and basic formulas.

## No comments:

## Post a Comment