In this lesson we discuss cell references, how to copy or move a formula, and format cells. To begin, let’s clarify what we mean by cell references, which underpin much of the power and versatility of formulas and functions. A concrete grasp on how cell references work will allow you to get the most out of your Excel spreadsheets!
Note: we’re just going to assume that you already know that a cell is one of the squares in the spreadsheet, arranged into columns and rows which are referenced by letters and numbers running horizontally and vertically.
What is a Cell Reference?
A “cell reference” means the cell to which another cell refers. For example, if in cell A1 you have =A2. Then A1 refers to A2.
Let’s review what we said in Lesson 2 about rows and columns so that we can explore cell references further.
Cells in the spreadsheet are referred to by rows and columns. Columns are vertical and labeled with letters. Rows are horizontal and labeled with numbers.
The first cell in the spreadsheet is A1, which means column A, row 1, B3 refers to the cell located on the second column, third row, and so on.
For learning purposes about cell references, we will at times write them as row, column, this is not valid notation in the spreadsheet and is simply meant to make things clearer.
Types of cell references
There are three types of cell references.
Absolute – This means the cell reference stays the same if you copy or move the cell to any other cell. This is done by anchoring the row and column, so it does not change when copied or moved.
Relative – Relative referencing means that the cell address changes as you copy or move it; i.e. the cell reference is relative to its location.
Mixed – This means you can choose to anchor either the row or the column when you copy or move the cell, so that one changes and the other does not. For example, you could anchor the row reference then move a cell down two rows and across four columns and the row reference stays the same. We will explain this further below.
Relative References
Let’s refer to that earlier example – suppose in cell A1 we have a formula that simply says =A2. That means Excel output in cell A1 whatever is inputted into cell A2. In cell A2 we have typed “A2” so Excel displays the value “A2” in cell A1.
Now, suppose we need to make room in our spreadsheet for more data. We need to add columns above and rows to the left, so we have to move the cell down and to the right to make room.
As you move the cell to the right, the column number increases. As you move it down, the row number increases. The cell that it points to, the cell reference, changes as well. This is illustrated below:
Continuing with our example, and looking at the graphic below, if you copy the contents of cell A1 two to the right and four down you have moved it to cell C5.
We copied the cell two columns to the right and four down. This means we have changed the cell it refers two across and four down. A1=A2 now is C5=C6. Instead of referring to A2, now cell C5 refers to cell C6.
The value shown is 0 because cell C6 is empty. In cell C6 we type “I am C6” and now C5 displays “I am C6.”
Example: Text Formula
Let’s try another example. Remember from Lesson 2 where we had to split a full name into first and last name? What happens when we copy this formula?
Write the formula =RIGHT(A3,LEN(A3) – FIND(“,”,A3) – 1) or copy the text to cell C3. Do not copy the actual cell, only the text, copy the text, otherwise it will update the reference.
You can edit the contents of a cell at the top of a spreadsheet in the box next to where is says “fx.” That box is longer than a cell is wide, so it is easier to edit.
Now we have:
Nothing complicated, we have just written a new formula into cell C3. Now copy C3 to cells C2 and C4. Observe the results below:
Now we have Alexander Hamilton and Thomas Jefferson’s first names.
Use the cursor to highlight cells C2, C3, and C4. Point the cursor to cell B2 and paste the contents. Look at what happened – we get an error: “#REF.” Why is this?
When we copied the cells from column C to column B it updated the reference one column to the left =RIGHT(A2,LEN(A2) – FIND(“,”,A2) – 1).
It changed every reference to A2 to the column to the left of A, but there is no column to the left of column A. So the computer does not know what you mean.
The new formula in B2 for example, is =RIGHT(#REF!,LEN(#REF!) – FIND(“,”,#REF!) – 1) and the result is #REF:
Copying a Formula to a Range of Cells
Copying cells is very handy because you can write one formula and copy it to a large area and the reference is updated. This avoids having to edit each cell to ensure it points to the correct place.
By “range” we mean more than one cell. For example, (C1:C10) means all the cells from cell C1 to cell C10. So it is a column of cells. Another example (A1:AZ1) is the top row from column A to column AZ.
If a range cross five columns and ten rows, then you indicate the range by writing the top-left cell and bottom right one, e.g., A1:E10. This is a square area that cross rows and columns and not just part of a column or part of a row.
Here is an example that illustrates how to copy one cell to multiple locations. Suppose we want to show our projected expenses for the month in a spreadsheet so we can make a budget. We make a spreadsheet like this:
Now copy the formula in cell C3 (=B3+C2) to the rest of the column to give a running balance for our budget. Excel updates the cell reference as you copy it. The result is shown below:
As you can see, each new cell updates relative to the new location, so cell C4 updates its formula to =B4 + C3:
Cell C5 updates to =B5 + C4, and so on:
Absolute References
An absolute reference does not change when you move or copy a cell. We use the $ sign to make an absolute reference – to remember that, think of a dollar sign as an anchor.
For example, enter the formula =$A$1 in any cell. The $ in front of the column A means do not change the column, the $ in front of the row 1 means do not change the column when you copy or move the cell to any other cell.
As you can see in the example below, in cell B1 we have a relative reference =A1.When we copy B1 to the four cells below it, the relative reference =A1 changes to the cell to the left, so B2 become A2, B3 become A3, etc. Those cells obviously have no value inputted, so the output is zero.
However, if we use =$A1$1, such as in C1 and we copy it to the four cells below it, the reference is absolute, thus it never changes and the output is always equal to the value in cell A1.
Suppose you are keeping track of your interest, such as in the example below. The formula in C4 = B4 * B1 is the “interest rate” * “balance” = “interest per year.”
Now, you have changed your budget and have saved an additional $2,000 to buy a mutual fund. Suppose it is a fixed rate fund and it pays the same interest rate. Enter the new account and balance into the spreadsheet and then copy the formula = B4 * B1 from cell C4 to cell C5.
The new budget looks like this:
The new mutual fund earns $0 in interest per year, which can’t be right since the interest rate is clearly 5 percent.
Excel highlights the cells to which a formula references. You can see above that the reference to the interest rate (B1) is moved to the empty cell B2. We should have made the reference to B1 absolute by writing $B$1 using the dollars sign to anchor the row and column reference.
Rewrite the first calculation in C4 to read =B4 * $B$1 as shown below:
Then copy that formula from C4 to C5. The spreadsheet now looks like this:
Since we copied the formula one cell down, i.e. increased the row by one, the new formula is =B5*$B$1. The mutual fund interest rate is calculated correctly now, because the interest rate is anchored to cell B1.
This is a good example of when you could use a “name” to refer to a cell. A name is an absolute reference. For example, to assign the name “interest rate” to cell B1, right-click the cell and then select “define name.”
Names can refer to one cell or a range, and you can use a name in a formula, for example =interest_rate * 8 is the same thing as writing =$B$1 * 8.
Mixed References
Mixed references are when either the row or column is anchored.
For example, suppose you are a farmer making a budget. You also own a feed store and sell seeds. You are going to plant corn, soybeans, and alfalfa. The spreadsheet below shows the cost per acre. The “cost per acre” = “price per pound” * “pounds of seeds per acre” – that’s what it will cost you to plant an acre.
Enter the cost per acre as =$B2 * C2 in cell D2. You are saying you want to anchor the price per pound column. Then copy that formula to the other rows in the same column:
Now you want to know the value of your inventory of seeds. You need the price per pound and the number of pounds in inventory to know the value of the inventory.
We add two columns: “pound of seed in inventory” and then “value of inventory.” Now, copy the cell D2 to F4 and note that the row reference in the first part of the original formula ($B2) is updated to row 4 but the column remains fixed because the $ anchors it to “B.”
This is a mixed reference because the column is absolute and the row is relative.
Circular References
A circular reference is when a formula refers to itself.
For example, you cannot write c3 = c3 + 1. This kind of calculation is called “iteration” meaning it repeats itself. Excel does not support iteration because it calculates everything only one time.
If you try do this by typing SUM(B1:B5) in cell B5:
A warning screen pops up:
Excel only tells you that you have a circular reference at the bottom of the screen so you might not notice it. If you do have a circular reference and close a spreadsheet and open it again, Excel will tell you in a pop-up window that you have a circular reference.
If you do have a circular reference, every time you open the spreadsheet, Excel will tell you with that pop-up window that you have a circular reference.
References to Other Worksheets
A “workbook” is a collection of “worksheets.” Simply put, this means you can have multiple spreadsheets (worksheets) in the same Excel file (workbook). As you can see in the example below, our example workbook has many worksheets (in red).
Worksheets by default are named Sheet1, Sheet2, and so forth. You create a new one by clicking the “+” at the bottom of the Excel screen.
You can change the worksheet name to something useful like “loan” or “budget” by right-clicking on the worksheet tab shown at the bottom of the Excel program screen, selecting rename, and typing in a new name.
Or you can simply double-click on the tab and rename it.
The syntax for a worksheet reference is =worksheet!cell. You can use this kind of reference when the same value is used in two worksheets, examples of that might be:
Today’s date Currency conversion rate from Dollars to Euros Anything that is relevant to all the worksheets in the workbook
Below is an example of worksheet “interest” making reference to worksheet “loan,” cell B1.
If we look at the “loan” worksheet, we can see the reference to the loan amount:
Coming up Next …
We hope you now have a firm grasp of cell references including relative, absolute, and mixed. There’s certainly a lot.
That’s it for today’s lesson, in Lesson 4, we will discuss some useful functions you may wish to know for daily Excel use.
