This tutorial will demonstrate how to use relative references in Microsoft Excel Formulas. Microsoft Excel allows users to create formulas for three different kinds of cell references: absolute, relative, and mixed.
All cell reference formulas appear with the basic structure =A4, where A is the column and 4 is the row. By placing a dollar sign before A or 4 that column or row becomes fixed.
Relative Formulas
When a formula appears =A4 the column and the row in the formula is relative. Adjusting the contents of the column or of the row can change the output of the formula.
Mixed Formulas
When a formula appears =$A4 the column is fixed and the row is relative.
When a formula appears =A$4 the column is relative and the row is fixed.
Absolute Formulas
When a formula appears =$A$4 the column is fixed and the row is fixed.
Absolute cell references do not change when a formula is copied from one cell to another. Placing a dollar sign before either the row or column fixes the row or column so it will not be changed in the formula.
This tutorial will focus on relative references, used to create relative formulas in Excel.
Let’s say we have a list of five numbers, occupying cells A1, A2, A3, A4, and A5. We want to transfer the contents of cell A1 over to cell B1 using a relative reference.
In order to do this we click on cell B1 and type in either the cell itself or in the formula bar: =A1.
It appears like this:
You’ll notice that when =A1 is typed into the cell or formula box that a blue box surrounds that cell. There is no significance to the cell’s proximity. We could just as easily type =A1 in cell B4. For example:
When we hit the Enter button the result of the formula appears in the cell:
The contents of cell A1 are now in cell B4 by means of using a relative reference formula.
Should we change cell A1 and make it 9387, the contents of cell B4 will also appear 9387 automatically.
If we want to do something slightly more complex than referencing one cell, we can try multiplying two cells in a formula and then altering one of the cells.
Let’s type into cell B5 the simple multiplication formula =A1*A2. The formula appears as follows:
If we add 2 to cell A1 so that it is 9389, the result of our relative reference formula in B5 changes automatically. For example:
Using relative references in Excel can be extremely beneficial because they reduce the amount of labor and adjustments the user has to make in order to get the results needed.
As formulas become increasingly complex in your Excel spreadsheet you will find all the more use for effectively employing relative reference formulas, as well as absolute and mixed cell reference formulas.
