Different values are used to obtain a result in any mathematical operation, and these values can be called references in Excel. Read on the article to learn more about cell references in Excel
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.
It 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.
Must-Read – Introduction to MS Excel – A Beginner’s Guide
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.
- Using Cell References In An Excel Formula
- Types of References in Excel
- 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 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 color is blue, and the cell range has a blue border with square corners.
2. The second cell reference is B3, with pink color coding and a red border with square corners.
Note: If you do not see any square corner on a color-coded border, the reference Excel makes is to a named range.
Read MS Excel Tutorials
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 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, they will be automatically modified by Excel.
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 fields of the total 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.
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
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.
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.
How will you make a relative reference in Excel?
If you press the F4 key twice, you will notice that Excel only inserts the $ sign for the row, and if you press the F4 key three times, it will create a mixed reference with the column fixed. Pressing the F4 key a fourth time will remove all the $ signs, and we will have a relative reference again.
What is an absolute reference?
Absolute References in a formula always refer to the same cell or a range of cells in a formula. The absolute reference remains the same even if the formula is copied to another location.
What is absolute and relative reference in Excel?
There are two types of cell references, mainly - relative and absolute. The relative references change when they are copied from one cell to another. Absolute references remain constant no matter what cell they are copied to.
What is a range in Excel?
A range in Excel refers to the Excel section containing a series of data, delimited by rows and columns. A range is like the coordinates that mark data location on a sheet in Excel. For example, we can say that the range A1:C2 is made up of cells A1, B1, C1, A2, B2, and C2. When interpreting it, we can refer to the cells from A1 to C2.
Download this article as PDF to read offlineDownload as PDF