When to use $ sign in formula? Cell Reference in Excel

Cell Reference in Excel: Cell reference is an important Excel tip that is important to understand properly to use Excel properly.

In this blog, we will talk about the importance, types, and uses of Cell Reference in Excel, and learn all about it with some examples.

Table of Contents

1.  What is Cell Reference in Excel?

2.  What is the use of Cell Reference?

3.  Types of cell reference Types of Cell Reference

001.                         Relative Cell Reference

002.                         Absolute Cell Reference

003.            Mixed Cell Reference

4.EXCEL BASIC TO ADVANCE LEVEL COURSE IN ENGLISH

5.Conclusion

What is CELL REFERENCE in EXCEL?

Cell reference in Excel refers to a specific cell that we select to use with other cells.
It can be understood as the address of a cell which guides us to the location of a specific cell.

What is the use of CELL REFERENCE?

Cell references are used when working with data, allowing us to move to specific cells and retrieve information.

Its importance increases when we arrange data and create formulas.

Types of cell reference TYPES OF CELL REFERENCE

1. RELATIVE CELL REFERENCE

In this type of cell reference, the cell address is relative, so it can be used with any other cell and moved as well.

Relative Cell Reference includes column letter and row number. When you copy and paste a Relative Cell Reference, Excel automatically adjusts the column letter and row number.

Example:

Suppose cell A1 contains the value 10 and cell B1 contains the value 20. If you enter the following formula in cell C1:

=A1+B1

This will return the value of 30 in cell C1. If you now copy cell C1 and paste it into cell C2,

So Excel will automatically change the formula to =A2+B2. This will return a value of 40 in cell C2.

When to use Relative Cell Reference?

Relative Cell Reference is used when you want the formula to be relative to the surrounding cells.

For example, if you want to add data to a column, you can enter a formula in the cell at the top of the column and then copy that formula to the cells below the column.

Excel will automatically adjust the formula in each cell so that it references the correct cell.

Benefit of Relative Cell Reference

There are several benefits of using Relative Cell References, including:

• They can be easily copied and pasted.
• They can be adjusted easily. 
• They make the formula more flexible.

Examples of Relative Cell Reference

• =A1+B1
• =SUM(A1:A10)
• =AVERAGE(B2:B100)
2. ABSOLUTE CELL REFERENCE

In this type of cell reference, the cell address is related to the position in Excel, which means that the cell can be used from any position without changing it.

Absolute Cell Reference includes a dollar sign ($) in front of the column letter and row number.

When you copy and paste an Absolute Cell Reference, Excel does not adjust the column letter and row number.

Example

Suppose cell A1 contains the value 10 and cell B1 contains the value 20. If you enter the following formula in cell C1:

=$A$1+$B$1

This will return the value of 30 in cell C1. If you now copy cell C1 and paste it into cell C2,

So Excel will not change the formula. The value 30 will also be displayed in cell C2.

When to use Absolute Cell Reference?

Coordinate cell reference is used when you want the formula to reference a specific cell, regardless of where the formula is copied and pasted.

For example, if you want to reference data in a specific cell, you can use a coordinate cell reference.
Benefit of Absolute Cell Reference
There are several benefits of using coordinate cell references, including:

• They ensure that the formula will always reference a specific cell.
• They make the formula more accurate.
• They make formulas more easily readable.

• 

Examples of Absolute Cell Reference

• =$A$1+$B$1
• =SUM($A$1:$A$10)
• =AVERAGE($B$2:$B$100)

3. MIXED CELL REFERENCE

In this type of cell reference, some parts are relative, and some are absolute, which helps in creating different types of formulas in Excel.
Mixed cell reference includes a dollar sign ($) in front of the column letter or row number.

When you copy and paste a mixed cell reference, Excel adjusts either the column letter or the row number, but not the other.

Example

Suppose cell A1 contains the value 10 and cell B1 contains the value 20. If you enter the following formula in cell C1:
=$A1+B$1

This will return the value of 30 in cell C1. If you now copy cell C1 and paste it into cell C2,

So Excel will adjust the column letter, but will not change the row number. The value 40 will display in cell C2.

When to use mixed cell reference?

Mixed cell reference is used when you want the formula to reference one aspect of a specific cell, but keep another aspect relative to surrounding cells.

For example, if you want to add data to a column, but you want to always reference the cell at the top of the column, you can use Mixed cell reference.

Benefits of mixed cell reference

There are several benefits to using a mixed cell reference, including:

They allow you to reference one aspect of a specific cell and position another aspect relative to surrounding cells.

• They make the formula more flexible.
• They make formulas more easily readable.

Examples of mixed cell references

• =$A1+B$1
• =SUM($A1:A10)
• =AVERAGE(B$2:B100)

EXCEL BASIC TO ADVANCE LEVEL COURSE IN ENGLISH

Apart from this, let me tell you if you are a beginner in Excel or you have just started learning Excel or you are learning Excel and you want to become an expert in Excel.

And if you want to learn every formula, functions and options, features of Excel up to advanced level, then I recommend you to visit our learning platform Skill Course or our Android App Learn More.

Where you will get an Excel Basic to Advance Level course of Excel. This is a very advanced level course in which you learn each and every feature of Excel in detail and each and every formula function with practice files.

Currently, this course is running at a discount, so you must go to the website and check the Excel Basic Advanced course.

Currently, this course is running at a discount, so you must go to the website and check the Excel Basic Advanced course.

CONCLUSION

Cell reference is an important concept in Excel. They allow you to refer to specific cells or cell ranges in formulas. This makes it possible to perform complex calculations and create dynamic spreadsheets.

There are three types of cell references: Relative, Absolute, and Mixed. Relative cell references are the most common type. When you copy and paste formulas, they are adjusted. Absolute cell references always refer to the same cell, even if you copy and paste formulas. Mixed cell references are a combination of relative and absolute cell references.

Cell referencing is a powerful tool that can be used to save time and improve the accuracy of your spreadsheet. By understanding how to use cell references, you can create more efficient and effective spreadsheets.

In conclusion, cell references are an essential part of Excel. They allow you to create complex formulas and dynamic spreadsheets. By understanding the different types of cell references, you can use them to your advantage to save time and improve the accuracy of your work.