Cell References in Excel – Shiksha Online

References in Excel are a fundamental part of the spreadsheet. They refer to each cell of a workbook and thus access the value contained in any of them. Cell references in Excel are a widely used resource when performing mathematical operations. Different values ​​are used to obtain a result in any mathematical operation, and these values ​​can be called references in Excel. Read the article to learn more about cell references in Excel.

Cell referencing is a call the program makes to the content of a specific cell or range of cells. It is mainly used to perform mathematical operations with the values ​​of said cells. In this way, it is not necessary to enter numerical values; the Excel reference is sufficient.

There are different types depending on how we call the content; they include relative references, absolute references, or mixed references. Cell References allow us to enter data in Excel quickly and without the possibility of errors. 

Content

• Using Cell References In An Excel Formula
• Types of References in Excel
  • Relative Reference
  • Absolute Reference
  • Mixed Reference
• How To Switch Between Reference Types

Features of cell references –

• Contains the data for a single cell in the Excel sheet
• Created from a range of cells, a specific area of ​​the Excel sheet is made up of different adjacent cells.
• Formed from cells that are separated from each other. They can be cells from the same Excel sheet or different sheets in the same workbook.

Using Cell References In An Excel Formula

Click the cell where you will enter the formula.

Type = (equal sign).

Select the cell for the value you want, or you can also type its cell reference. This could be a single cell, a range of cells, or a cell in some other worksheet or workbook.

When you select the cell range, drag the cell selection to move or expand the selection.

Range Finder color-codes precedent cells

1. The first cell reference is B2, the colour is blue, and the cell range has a blue border with square corners.

2. The second cell reference is B3, with pink colour coding and a red border with square corners.

Note: If you do not see any square corner on a colour-coded border, Excel's reference is to a named range.

Press Enter.

Types of References in Excel

Excel references are mainly used in formulas in Excel. The contents of the cells are the values of the mathematical operation. However, what happens when we copy the Excel formulas to other cells? Depending on how the reference behaves, we can classify them:

Relative Reference

Relative references denote the location of the cell. This refers to the row and column where the cell with a value or a formula is located. Using that cell to move it to another cell will reference new cells based on their location.

The benefit of relative references in Excel is that when copying them to another cell, Excel will automatically modify them.

Example Of Relative Reference

Consider the following example. We have a formula that adds the total cost or value of the products in month 1, located in cell B7, as illustrated in the following Excel table.

Cell B7 is a relative reference that contains a formula that sums the values ​​of the range B2:B6. We will drag the formula in the other total fields for each month. When dragging this value, we can notice that the column numbers are adding up, so the references of these cells will change according to their corresponding ranges.

When copying the formulas to other cells, Excel will automatically adjust the references; that is, it will modify the columns and rows corresponding to each formula range. In addition, this will be relative to the number of columns that we have moved.

Absolute Reference

In absolute reference, the reference content is fixed. Absolute cell references have a dollar sign attached to each letter or number in a reference, e.g., $B$2. Even if the formula is moved to another location, it will maintain the result of the original cell. It is not modified when the location is changed. 

Must Explore – MS Excel Courses

Mixed Reference

In the absolute reference, we used the “$” symbol to fix both the column and the row. However, it is possible to pin only the column or pin only the row, and these variations are known as mixed references.

If we want to fix only the column, we put the “$” symbol before the column letter and leave the row without said symbol. On the other hand, to fix only the row, we can put the “$” symbol before the row number. 

Here is the summary of the types of cell references –

How To Switch Between Reference Types

A handy tip to enter any reference in a formula is to use the F4 key. When the editing cursor is over a reference, and we press the F4 key, Excel will change the reference type without the need for us to enter the “$” symbol manually. Let’s do the following example to know how this keyboard shortcut works:

• Select cell B1 and enter the formula =A1, but don’t press the Enter key.

• The editing cursor will be blinking at the end of the reference, and if you press the F4 key once, the reference will automatically become absolute =$A$1.

• If you press the F4 key again, you will have a mixed reference like =A$1.

• And if you press the F4 key a fourth time, the reference will be changed to the other mixed reference option =$A1.

• Pressing the F4 key a fifth time will return you to the original relative reference.

The F4 key will only change the reference over the editing cursor. So, if you want to apply this change to multiple references in the same formula, you must hover over each reference to be changed and press F4 each time.

Conclusion

You must know all the aspects of the references in Excel since they play a fundamental role in using the spreadsheet and the formulas. If you correctly understand how it works, you will be able to avoid many errors and incorrect results generated in Excel due to the incorrect use of references.