Excel Baby Steps: Understanding Cell Referencing and Basic Formulas

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.