Tweet

# Excel For Noobs Tutorial: Cell Reference for Formulas and Functions

## Cell Reference for Excel Formulas and Functions

Most of your formulas will refer to cells or cell ranges for their function arguments. By referring to cell you make your worksheet dynamic, efficient and less error prone. So what exactly is cell reference? When you refer to a cell for a function argument or a component of a formula the result of the formula or function will be dependent on the value entered into the cells that the formula or function refers to. If we were to enter =A1+B1 into a cell then the result would be the value inside Cell A1 plus the value entered into cell B1. If we changed the values entered into either cell the result of the formulas would also change since it is dependent on the values in the referred cells.

There are three different types of cell reference:

• Relative cell reference: If you copy and paste a formula that uses a relative cell reference the cells that the formulas refers to will change relative to the cell it was pasted into. For example if we enter =SQRT(A1) into cell A2 and then copy and paste that formula into cell B2 it will then refer to cell B1 for its argument. Since we moved the formula one column to the right the cell that it referred to for its argument also moved one column to the right.

• Locked cell reference: When we lock a cell reference this means that no matter where we copy and paste the formula or function the cell reference will not change. The way that we lock cell references is by inserting the \$ symbol before the column letter and the row number. If we entered =SQRT(\$A\$1) into a cell and then copy and pasted that function into another cell, it would still refer to cell A1 since the cell reference is lock by the \$ symbols.

• Mixed of locked and relative cell reference: We can also mix locked and relative. For example if we entered =SQRT(\$A1) into a cell and the copy and pasted the function into a different cell the column would always be locked in column A however the row would change relative to the row that the function is pasted. For example we could paste the function several columns to the right but remain in the same row it was initially inserted and it would still refer to cell A1. This is because we remained in the same row and only change columns. Since the column was locked the cell reference didn’t change even though we pasted it several column to the right. However, if we were to move down one row then the function would refer to cell A2 since the row number of the cell reference is relative.
• In the screenshot below we are referring to the previous months balance in cell B19 and the adding the current months Income in cell C17 and subtracting the current month’s expenses in cell C18. If we use to autofill feature to complete this budget the cells that the formula refers to will be the same relative to the location of the cell they are pasted into.

Now look at the picture below where I used the Show Formulas feature. You can see that a formula is entered into all columns of the Balance row. The formulas are the same except that the cells the formulas refer to are relative to their location. The formula is could be stated as “one column to the left + two rows above – one row above”

If you take a look at the screen shot below you can see that we added another aspect to this budget and the formula. We add the factor that the account the balance is in is earning a small interest rate. We added the interest to the formula for the balance by multiplying the previous months balance by the monthly interest rate in cell A23. You can see that we lock the cell reference to cell A23 for the interest rate by entering \$A\$23. Now no matter what cell we insert this formula, it will always refer to cell A23 for the interest rate.

## How to Change the Type of Cell Reference

You can change your type of cell reference manually by inserting or removing the \$ symbols or you can use the cell reference keyboard shortcut by simply pressing the F4 key on your keyboard. First you point your mouse at the cell reference in your formula or function and then simply press F4. If you continuously press F4 you will cycle through all the different types of cell reference.

## How to Refer to cell in a Different Worksheet

You are not limited to referring to only cells within a single worksheet, you can also refers to cells in different worksheets and even entirely different workbooks. The notation used to refer to cells in a different worksheet is SheetName!CellAddress. If you were working in a workbook and wanted to refer to cell B2 in a sheet named Sheet2 then you could simply select cell B2 in Sheet2 or you could manually enter your cell reference by typing Sheet2!B2. Your sheet name will always be separated by an exclamation point (!).

If your sheet name has a space in the name for example Sheet 2 then you must enclose the sheet name in single quotation marks. For example, ’Sheet 2’!B2.

## How to Refer to Cells in a Different Excel Workbook

If you want to refer to a cell within a different workbook you would refer to the cell using the following format:

So if we wanted to refer to cell B2 in Sheet2 in a workbook named Data.xlsx then we would refer to that cell by entering [Data.xlsx]Sheet2!B2.

If there are spaces in the name of the other workbook or the worksheet in the other workbook then you will enter the workbook name and worksheet name into single quotations as follows:

So if you were referring to cell B2 in a sheet names Sheet2 in a workbook named Original Data then your would refer to that cell by entering the following:

=’[Original Data]Sheet2’!B2

It is easiest to refer to cells by select the cell reference with your mouse than it is to manually type the cell reference, especially when referring to a closed workbook that is save on your computer. This is because when the workbook is closed you must specify the path and folders that your workbook is saved in. For example, if we referred to the previous workbook except we didn’t have it open we would have to enter the following cell reference:

=’C:\My Documents\[Original Data]Sheet2’!B2

You can also refer to a workbook that is located on another computer if the computers are linked in a network of computers. For example if you were going to refer to cell B2 in a workbook names Original Data that was located in Documents and the computer was named Computer1 then you would refer to that cell by inserting the following:

=’\\Computer1\C:\Documents\[Original Data]Sheet2’!B2

Just remember that the path needs to be accurate or your cell reference won’t work.

As you can see cell reference can actually get pretty complicated, it is best to always enter your cell reference select the cell you are referring to with your mouse. Excel will automatically make the correct changes to the cell reference when you do things link change the name of a worksheet or workbook and when you open and close workbooks or change the location of the files on your computer.

When referring to a cell in a different workbook or a different worksheet Excel always uses the locked cell reference therefore if you copy and paste a formula that refers to another worksheet or workbook be sure to change the cell reference to relative.

Find the next tutorial in the links below.