TYPE OF REFERENCE IN EXCEL | RELATIVE | ABSOLUTE | MIXED REFERENCE | Beginners Tutorial | IN HINDI.
Introduction
Please Like & Subscribe our Channel _
• CorelDraw 2020
A worksheet in Excel is made up of cells. These cells can be referenced by specifying the row value and the column value. In Excel, a cell reference is a technique to signify any cell's identity and location by combining column names and row numbers on a worksheet. For example, suppose we say A5, then it expands as a column A and 5th Row. Similarly, we can also declare or define cell references to any location in the worksheet. We may also activate R1C1 from Excel options, which is another means of referencing cells, with R1 denoting Row 1 and C1 denoting Column 1.
Types of Cell Reference in Excel
The following are the three types of references in Excel:
Relative cell references
Absolute cell references
Mixed cell references in Excel
#relative references:
1. Relative Cell References
In Excel, a relative reference is a form of a cell reference. By default, all cell references are relative references. Relative references are changed when copied across different cells based on the relative positions of rows and columns. For example, suppose we copy the formula =B1*C1 from row 1 to row 2, the formula will become=B2*C2. When we have to repeat a calculation across numerous rows or columns, relative references are extremely useful.
By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.
#Absolute references:
2. Absolute Cell References
In Excel, an absolute cell reference is one of the cell reference types in which the cells being referred to do not alter like they did in a relative reference. We utilize the $ sign by pressing f4 to create a formula for absolute referencing. The $ sign means lock, and it locks the cell reference for all of the formulas, ensuring that the same cell is referred to all of them.
There may be times when you do not want a cell reference to change when filling cells. Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant.
An absolute reference is designated in a formula by the addition of a dollar sign ($) before the column and row. If it precedes the column or row (but not both), it's known as a mixed reference.
3.Mixed Cell Reference in Excel:
A Mixed cell reference is a mixture of both relative and absolute cell reference. In mixed cell reference, dollar signs are attached to either the letter or the number. For example, $B2 or B$4. It's a mix of relative as well as absolute reference.
Let's take a closer look at each of the cell references:
Using cell references with multiple worksheets
Most spreadsheet programs allow you to refer to any cell on any worksheet, which can be especially helpful if you want to reference a specific value from one worksheet to another. To do this, you'll simply need to begin the cell reference with the worksheet name followed by an exclamation point (!). For example, if you wanted to reference cell A1 on Sheet1, its cell reference would be Sheet1!A1.
#What does the Dollar ($) sign do?
When the dollar symbol is placed in front of the row and column number, the cell becomes absolute (means it stops the row and column number from changing when copied to other cells).
For example, in the above case, if we copy the formula form E2 to E3, it modifies from =D2*$F$1 to =D3*$F$1.
It's worth noting that, while D2 changes to D3, $F$1 remains unchanged.
Because we put a dollar symbol in front of 'F' and '1' in F1, the cell reference would not be changed when we copied it.
As a result, the cell reference is now absolute.
When to Use Absolute Cell References in Excel?
We used absolute cell reference when we don't need the cell reference to modify when copying the formulas from one cell to another. This could be the situation when we have a fixed value which we have to utilize in the formula (like commission rate, tax rate, number of months, etc.).
While we could hard code this amount in the formula (e.g., $F$2 instead of 30%), putting it in a cell and then utilising the cell reference allows us to alter it afterward.
To reference cells across worksheets:
In our example below, we'll refer to a cell with a calculated value between two worksheets. This will allow us to use the exact same value on two different worksheets without rewriting the formula or copying data between worksheets.
Thanks For watching
Please Like & Subscribe our Channel _
• CorelDraw 2020