Excel Beginners

Intro To Formulas: Step 3 To Mastering Spreadsheets

Giving directions even a toddler could follow

The Four Components Of A Formula - References

Overview

A way to denote another cell whose information you would like to use as an input. There are two methods of notation which Excel accepts: R1C1 notation, and A1 notation. By default, Excel is configured to use A1 notation as it is the more direct and simpler of the two. This setting can be changed for R1C1 notation in the settings. What is consistent between both notations is the concept of fixed and variable references. These allow users to dictate how the references made in a formula are allowed to move/shift if the formula is copied into different cells. R1C1 notation will not be covered in this guide as it goes beyond the scope of what is needed to perform well within Excel.

Using References

References allow users to take values from different cells and use them in another location. The simplest references are those which point to another cell and display its value with no modifications. Post this, you can begin to experiment including them in formulas where you modify values through the use of operators.

In Cell A1 enter: =5. In Cell A2 enter: =A1.

Both cells should now display ‘5’.

What we have just done is used a variable cell reference in A1 notation to tell Excel to display the value from one cell in a different one.

Let’s change this from a variable reference to a fixed reference. In Excel we use the dollar symbol ($) to do this. By placing this symbol to the left of either the column letter or the row number in a reference, we are telling Excel that we do not want that portion of the reference to change.

In Cell A3 enter: =$A$1. 

Now select the cell A3 and paste it into cell A4.

We should see that now in both cell A3 and A4 we have the formula ‘=$A$1’. To see the impact of a fixed reference, let us copy the cell A2 which uses a variable reference and see what happens.

Select and copy the cell A2 and paste it into the cell A5.

By looking at the contents of cell A5 we should see that the formula is now ‘=A4’. This is due to the fact that we did not use any dollar signs to limit how Excel modifies the formula based on it’s relative position to the cell.

Experiment with this by performing a similar test with another set of cells.

Enter the value of your choosing into cell E1. Try to see which variable you will need to lock ($) so that the formula you will put in cells E2 and F1 can be copied into adjacent cells while still referencing the original cell E1. Use a limit of ONE dollar sign ($) for this!

There are two variations you can use to accomplish this:

 ‘=$E1’ and ‘=E$1’

Try them both and see how copying a formula to different cells impacts your references!