Cell Reference in Excel (Absolute, Relative, and Mixed)

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:

– 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:

– 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:

Or,

– 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:

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

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:

– 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:

• 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:

• 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:

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:

• 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:

• 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:

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:

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.

• 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:

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:

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:

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:

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:

• 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:

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