Cell References in Excel – Shiksha Online

# Cell References in Excel – Shiksha Online

6 mins read1.7K Views Comment
clickHere
Rashmi Karan
Manager - Content
Updated on Mar 3, 2023 13:00 IST

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.

#### 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.
How to Merge Cells in Excel – Shiksha Online
Learn how to merge cells in Excel through real datasets.
How To Calculate Percentage In Excel
Percentages are a very powerful tool for data analysis. They can be used to make data more concise, easier to understand, and more comparable. In this tutorial, we will learn...read more
Weighted Average in Excel – Shiksha Online
This tutorial will teach you how to calculate a weighted average, also known as a weighted mean.

## 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.

Press Enter.

How To Enable Macros In Excel
A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse...read more
How to Import Text Files to Excel
The article covers two Excel options to import text files to excel to ensure better data management and analysis.
Freeze Panes in Excel – Shiksha Online
Discover the powerful feature of freeze panes in Excel that allows you to lock rows or columns, ensuring important information stays visible as you explore large datasets or create complex...read more

## 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, 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.

### 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.

## FAQs

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.