In Excel, cell reference in Excel are used to identify the location of a cell or a range of cells in a worksheet.

In this article, you will learn how to use absolute, relative, and mixed cell references, hence learning the distinction among them.

Here, we have shown a dataset of** Product** and **Sales **where we have individually referred the cells **C4** to **C12 **referring the values of corresponding cells and summed them up.

Here, you will learn how to use a cell reference in functions like **SUM**, **OFFSET**, how to switch among the absolute, relative and mixed cell references, and will come to know what is the difference between **A1 **and **R1C1 **cell references.

⏷What is Cell Reference?

⏷How to Create or Change a Cell Reference

⏵A Simple Reference

⏵Reference a Cell Range

⏵Cell Reference in a Function

⏵Reference Text in Another Cell

⏵Use Cell Reference in a Formula Using Worksheet Name

⏵Cell Reference Using OFFSET Function

⏷Clicking Cells to Refer

⏷Types of Cell References

⏵Relative Cell Reference & Its Application

⏵Absolute Cell Reference & Its Application

⏵Mixed Cell Reference & Its Application

⏷Switch Among Relative, Absolute, and Mixed References

⏷Difference Between Absolute, and Relative Cell Reference

⏷Refer Entire Row or Column

⏷Refer Entire Column Except for First Few Rows

⏷Variable Row & Column Number As Cell Reference

⏷Cell Reference to Refer Another Worksheet or Workbook

⏷Difference Between A1 and R1C1 Reference Style

⏷Named Range to Refer Cells

⏷Circular Reference in Excel

⏷3D Reference in Excel

## What is Cell Reference?

A cell reference in Excel identifies the location of a specific cell, indicated by its column letter and row number. It is crucial in creating formulas, and there are three types: Absolute ($) references stay fixed when copied, Relative references adjust based on the new location, and Mixed references have a combination of absolute and relative components.

Cell references are necessary in Excel to create dynamic formulas that can be replicated and adapted, allowing for efficient data analysis and manipulation. They enable formulas to automatically adjust when copied to different cells, ensuring flexibility and accuracy in spreadsheet calculations.

## How to Create or Change A Cell Reference

Here, we have shown you how to create change and use the cell reference in different circumstances. We will learn about different types of references and their applications.

### – A Simple Reference

A simple reference in Excel involves pointing to the content of a single cell using its column letter and row number (e.g., **C6**).

In this section, we have used **Product** and corresponding** Sales **data. Here we will use individual product sales values from **C6** to **C14** and sum them up individually.

- Here we have used the formula below in the
**C16**cell:

`=C6+C7+C8+C9+C10+C11+C12+C13+C14`

### – Reference a Cell Range

Here we have **Product** and **Sales **for two different months, **January** and **February**. As the product name is same in the both months, we have just referred to the cell values of **January** in **February**.

- We have referenced a cell range
**B4**to**B16**in the**E6**To do so, we have used the data below.

- Here we have used the formula below in
**E6**cell:

`=B6:B14`

### – Cell Reference in a Function

In a function, cell references in Excel, like **C6:C14**, allow dynamic input. References in formulas make your calculations adapt automatically when you change the data, saving you from manual updates.

To find the **Total Sales** from a dataset with **Product **and **Sales** information, simply add up all the sales values in the dataset.

- Here we have used
**the SUM function**to write the formula below using cell reference**C6:C12**:

`=SUM(C6:C14)`

Or,

`=SUM(C6,C7,C8,C9,C10,C11,C12,C13,C14)`

### – Reference Text in Another Cell

Displaying text from one cell in another cell in Excel helps keep information connected. If you change the original text cell, the other cell automatically shows the updated text. It’s an easy way to link and update information in your spreadsheet.

In this section, we have a dataset of** Product **and **Sales** values. Here to refer to text in another cell, we have to refer to the **Product** corresponding to find the corresponding sales value.

- Here we used the formula below in the
**B16**cell:

**
**

`=B6`

### – Use Cell Reference in a Formula Using Worksheet Name

Using a cell reference in a formula with the worksheet name helps specify the location of the data in a different sheet. For example, **=Sheet2!A1** refers to cell **A1** in the **Sheet2** worksheet. This ensures accurate referencing across multiple sheets, enhancing organization and linking data between different parts of your Excel workbook.

In this section, we are going to find out** total sales** when sales data is in another worksheet **Jan**.

** **

Here we have used the formula in the** C5** cell.

**
**

`=SUM(Jan!C4:C12)`

The formula **=SUM(Jan!C4:C12)** in cell **C5** of the current worksheet calculates the sum of values in the range **C4 **to **C12** of another worksheet named **Jan**.

- Here we have used
**Jan**worksheet to get total sales.

### – Cell Reference Using OFFSET Function

The **OFFSET** function in Excel is used for dynamic referencing of cells. In Excel, the OFFSET function lets you refer to a cell by specifying how many rows and columns away it is from a starting cell.

To use the **OFFSET** function to refer to some cells, we have used the dataset of **prices **of different **products **in different **shops**. Here, using the **OFFSET** function, we will extract the whole row.

- Here we have used the formula below in the
**B17:**

**
**

`=OFFSET(B5,4,0,1,4)`

### – Clicking Cells to Refer

Here we deal with **Product** and **Sales **where we sum up individual product sales. In this example, we summed the sales of **TV** and **Oven** by clicking the sales of **TV** and** Oven**. After clicking the cell, press **=** sign to insert the formula in the formula bar and we have to select the cell, we want to refer to. Here we referred to **E6** and **E14 **to calculate total sales for **TV **and **Oven.**

## Types of Cell References and Their Particular Usage and Benefits

Here we will explain the three types of cell references and show some pros and cons of those cell references.

### 1. Relative Cell Reference & Its Application

A relative cell reference adjusts its position when copied to a new location. It refers to a cell’s position relative to the one containing the formula. For instance, if a formula, we are multiplying **B6*C6** and dragging the **Fill handle**, it will multiply **B7*C7**, **B8*C8,** and so on respectively.

Here we are going to mention some pros and cons of relative cell reference.

**Pros:**

**Ease of Copying**: Simplifies copying formulas to different cells.**Dynamic Formulas**: Formulas adjust to new locations, making them versatile.**Efficiency in Maintenance**: Changes in one cell often apply universally when copied, reducing manual effort.**Consistency in Patterns**: Formulas with relative references follow consistent patterns, aiding readability.

**Cons:**

**Potential Error Risk:**If not used carefully, relative references might lead to errors when formulas are copied.**Less Control Over Specific References:**You can’t lock specific cells in a formula to prevent them from changing when copied.**Complexity in Special Cases**: In certain complex spreadsheet scenarios, relative references might not be the ideal choice.**Dependency on Cell Layout:**Formulas depend on the layout of cells, so if cells are moved, the results might change unexpectedly.

In this section, we have given a dataset of the **Unit Price** of some **products **and the number of **Unit Sold. **We have multiplied **C6** and **D6**. When we drag and drop the **Fill Handle**, we will get respective multiplications such as **C7*D7**, **C8* D8,** and so on. The formula is given below:

**
**

`=C6*D6`

- Here we can see the last cell reference is
**C14*D14**. Cell reference has changed as you dragged the**fill handle**and**D**is changed according to the position of**C**.

### 2. Absolute Cell Reference & Its Application

An absolute cell reference refers to the cell absolutely. Mean, it does not change the referred cell when you drag down the **Fill Handle**. Unlike relative cell reference, If you choose** C6** as an absolute cell reference, the **C6** will be constant throughout the cells, after you drag the **Fill Handle**.

**Pros:**

**Control Over Specific References:**Allows you to “lock” specific cells in a formula, preventing them from changing when copied.**Stability in Formulas:**References remain fixed, providing stability in calculations even when formulas are copied.**Less Error-Prone:**Reduces the risk of errors caused by unexpected changes in references during copying.**Useful in Fixed Data Scenarios:**Ideal for scenarios where certain values or constants need to stay constant across multiple calculations.

**Cons:**

**Less Dynamic**: Formulas can be less dynamic and adaptable to changes in cell locations.**Increased Complexity**: Involves the use of dollar signs (**$**) which may add complexity, especially for beginners.**More Effort in Formula Maintenance**: Changing specific references requires manual adjustment in each formula.**Potential for Redundancy**: In some cases, absolute references might lead to redundant formulas when applied across different cells.

Here we have used commission percentage as an absolute cell reference in the dataset of **Product** and calculation of **Commission**. Now, we have to write the formula in the **F8** cell. Here Commission **$C$5 **is an absolute cell reference and** C5** cell will be fixed:

**
**

`=E8*$C$5`

- Unlike relative cell reference, we can see that
**E**has changed but**C5**did not change its position according to**E**.

### 3. Mixed Cell Reference & Its Application

In Excel, a mixed cell reference is a blend of fixed (absolute) and flexible (relative) references, providing partial stability in either the row or column. For example, with **$C6**, column **C** stays constant as you drag horizontally, while **C$6** keeps row **6** fixed when dragged vertically. It’s a handy way to control references partially in formulas.

**Pros:**

**Flexibility in Locking:**Allows for a mix of absolute and relative references, providing flexibility in controlling certain aspects of the formula.**Balanced Adaptability:**Offers a balance between the adaptability of relative references and the stability of absolute references.**Efficient Maintenance:**Easier formula maintenance compared to absolute references, as some references can adapt while others remain fixed.**Useful in Many Scenarios**: Well-suited for scenarios where some elements of the formula need to change dynamically while others stay constant.

**Cons:**

**Potential for Confusion:**Beginners might find the mix of absolute and relative references confusing.**Complexity in Formulas:**Depending on the mix used, formulas can still become complex, especially in intricate spreadsheet models.**Careful Management Required:**Requires careful management to ensure that the mix of references aligns with the desired behavior of the formula.**Not Universally Applicable:**Might not be the best choice in all scenarios; careful consideration is needed based on specific use cases.

In this section, we will use three different types of commission in three different tiers have three different commissions. So we have to make some rows absolute and some columns relative. That’s why mixed cell reference is necessary. Because mixed cell reference contains both absolute and relative cell reference.

- First, we have to write the formula below in the
**F9**:

**
**

`=$E9*F$6`

Here **column E** will not change and row 6 will not change because the **$** sign is given before them.

- Then, we fill the rest of the blank column by dragging and dropping the
**Fill Handle**.

## How to Switch Among Relative, Absolute, and Mixed References in Excel

In this section, we are going to interchange between mentioned references. To switch between references, we have to press** F4 **while referencing a cell. It will change your cells to relative, absolute, and mixed references as you want.

## Difference Between Absolute, and Relative Cell Reference in Excel

Here, we’ll learn the basic difference between absolute and relative cell reference. We have calculated the **Total** using both relative cell reference and absolute cell reference. When we use a relative cell reference then total revenue will change respectively but when using an absolute cell reference, total revenue will not change.

## How to Reference Entire Row or Column in Excel?

In Excel, referencing an entire row, like **A1:Z1**, means including all cells in that row for calculations, while referencing an entire column, like **A:A**, includes all cells in that column for operations. In this section, we will show how we can reference the entire column or row in Excel.

First, we’ll show how we can reference an entire column. Here, we referenced the whole** E** column, so If anyone wants to edit new data in the **E** column, it will automatically update the** Total Sales**.

- To use the entire column
**E**as a reference, we have used the formula below in**G**cell:

**
**

`=SUM(E:E)`

- Finally, we get the total in cell
**H5**.

- Now, we can see when you add new data, it will automatically update the total sales.

Now we will refer entire row in Excel. We’ll find the average price of a **TV **by referencing an entire row.

- We have used the formula below in cell
**C16**:

`=SUM(6:6)/3`

- Here’s the output entire 6th row is referenced. When you change the prices in this row, the average price will change automatically.

## How to Reference Entire Column Except for First Few Rows in Excel

In this section, we have used the dataset of **Product** and **Sales**. But finding out total sales we will ignore some rows. Here we reference from row **C8** to **C1048576** leaving **C6** and **C7**. We have referenced the whole column, except the **6th** (**TV**) and **7th** (**Fridge**) row.

- After using the formula below, we have used the formula below in the
**F5**cell:

**
**

`=SUM(C8:C1048576)`

## How to Use Variable Row & Column Numbers as Cell Reference in Excel

We have created a user-input model where you can give any row and column number, it will show what is contained in the respective cells. When we will give row and column numbers, we will get the corresponding value.

Here from the** Row No** and **Column No**, we find the **Product **according to the row and column.

- Here, we used the formula below in cell
**E17**:

`=INDIRECT(ADDRESS(B17,C17))`

## How to Use Cell Reference to Refer Another Worksheet or Workbook in Excel

Here will find out the total sales in March from another worksheet named **March**. We have used the formula below to refer to another worksheet **March** in the present worksheet.

**
**

`=SUM(March!C4:C12)`

- Now, we will learn how to refer to a workbook in a formula. We have another workbook named
**December.xlsx**and from the**Sales in****December**worksheet, we want to get the total sales and average sales. So we use the formula below to refer to another workbook:

`SUM('C:\Users\HP\Downloads\[December.xlsx] Sales in December'!$C$4:$C$12)`

## What Is the Difference Between A1 and R1C1 Reference Style in Excel?

Here we will see 2 types of cell references. The first one is **A1** which is regularly used another is the **R1C1** reference style.

In Excel, the **A1** reference style identifies cells by letters and numbers (e.g., **A1),** while **R1C1** reference style uses row and column numbers from the active cell (e.g., **R2C3** means two rows down and three columns to the right of the active cell).

Here, we calculated the **Total **price for the below dataset and showed the formula using both reference styles.

The formula with **A1 **reference style is as follows:

`=C6*D6`

Here,

**C6:**Refers to the cell in**column C**and**6th****D6:**Refers to the cell in the second**column D**and**6th**

The formula with **R1C1 **reference style is as follows:

`=RC[-2]*RC[-1]`

means

**RC**stands for**row, column**and it’s a way to reference cells in a relative manner.**[-2]**refers to two columns to the left of the current column, and**[-1]**refers to one column to the left.

- To enable
**R1C1**reference style press**Alt+F+T**>> Choose**Formulas**>> Check**R1C1 reference style**>> Press**OK**.

## How to Use Named Range to Refer Cells in Excel

In this discussion, we have named **E6**:**E12** so that we can easily use that range in the **SUM** function. Using that named range, we can easily refer to them in any circumstance as we have named the range and it will reduce the complexity of the formula.

- First, we named the whole column
**E6 to E14**as “**TotalSales”.**

- Next, we have used the defined name in the
**SUM**formula to get total sales:

`=SUM(TotalSales)`

**Read More:** Excel Use Cell Value as Worksheet Name in Formula Reference

## What is Circular Reference in Excel?

A circular reference in Excel happens when a cell’s formula refers to itself or creates a loop with other cells, causing calculation issues. Here **C16** refers to itself. It happens by mistake. Every time you open the worksheet, it will add the previous data and you will get an ever-increasing sum.

- For a new Excel workbook, it may return zero too:

`=SUM(C6:C16)`

- If you are creating a circular reference, you will find the warning.

## How Can We Use 3D Reference in Excel

A 3D reference in Excel is a way to combine information from the same cell or range of cells across multiple sheets in a workbook. It’s like looking at the data from different sheets all at once.

Here, we have extracted the corresponding sales columns of **May**, **June**, **July **worksheets and summed them up by using the formula below:

`=SUM(May!C4:C12,June!C4:C12,July!C4:C12)`

**Download Practice Workbook**

In Excel, cell references pinpoint specific locations in a worksheet, crucial for creating dynamic formulas. Absolute references stay fixed, relative adjust, and mixed combine both. Simple references point to individual cells, while ranges, cell names, and the **OFFSET **function enhance flexibility. Understanding these types is key to crafting accurate formulas. The article also touches on 3D references for combining data across sheets, offering a comprehensive guide to efficient data analysis in Excel.

## Cell Reference in Excel: Knowledge Hub

**<< Go Back to Excel Formulas | Learn Excel**