Cell References In Excel 3 Types Example How To Do

Explained

Excel worksheet is made up of cells. Each cell has a cell reference.Cell reference contains one or more letters or the alphabet followed by a number where the letter or alphabet indicates the column and the number represents the row.Each cell can be located or identified by its cell reference or address, e.g., B5.Each cell in an Excel worksheet has a unique address. The address of each cell is defined by its location on the grid. g. In the below-mentioned screenshot, the address “B5” refers to the cell in the fifth row of column B.

Even if we enter the cell address directly in the grid or name window, it will go to that cell location in the worksheet. Cell references can refer to either one cell, a range of cells, or entire rows and columns.

When a cell reference refers to more than one cell, it is called “range.” E.g., A1:A8 indicates the first 8 cells in column A. In between, a colon is used.

Types of Cell Reference in Excel

Now, let us discuss each cell reference in detail –

#1 How to Use Relative Cell Reference?

The below-mentioned pharma sales table below contains medicine “Products” in column C (C10:C16), “Quantity Sold” in column D (D10:D16), and “Total_Sales_Value” in column F, which we need to find out.

To calculate the total sales for each item, we need to multiply the price of each item by its quantity of that.

Let us check out the first item; for the first item, the formula in cell F10 would be multiplication in ExcelMultiplication In ExcelMultiplication in excel is performed by entering the comparison operator “equal to” (=), followed by the first number, the “asterisk” (), and the second number.read more – D10E10.

It returns the total sales value.

Instead of entering the formula for all the cells, we can apply a formula to the entire range. To copy the formula down the column, click inside cell F10 to see that the cell is selected. Then, select the cells till F16. So, that column range will get selected. Then, we will click “Ctrl+D” to apply the formula to the entire range.

Here, when you copy or move a formula with a relative cell reference to another row. Automatically, row references will change (similarly for columns also)

We can notice that the cell reference automatically adjusts to the corresponding row.

To check a relative reference, we must select any of the “Total Sales Value” cells in column F, and we can view the formula in the formula bar. E.g., In cell F14, we can observe that the formula has been changed from D10E10 to D14E14.

#2 How to Use Absolute Cell Reference?

The below-mentioned pharma product table contains medicine “Products” in column H (H6:H12), and it’s “Old_Price” in column I (I6:I12), and “New_Price” in column J, which we need to find out with the help of absolute cell reference.

We can see that the rate for each product is increased by 5% effective from Jan 2019 and is listed in cell “K3”.

To calculate the “New_Price” for each item, we need to multiply the old price of each item by the percentage price increase (5%) and add the “Old_Price” to it.

Let us check out the first item. For the first item, the formula in cell J6 would be =I6*$K$3+I6, which returns the new price.

Here, the percentage rate increase for each product is 5%, a common factor. Therefore, we must add a dollar symbol in front of the row and column number for the cell “K3” to make it an absolute reference, i.e., $K$3. We can add it by clicking the “function+F4” key once.

Here the dollar sign for the cell “K3” fixes the reference to a given cell, where it remains unchanged no matter when you copy or apply a formula to other cells.

Here, $K$3 is an absolute cell reference, whereas “I6” is a relative cell reference. It changes when you apply it to the next cell.

Instead of entering the formula for all the cells, we can apply a formula to the entire range. To copy the formula down the column, click inside cell J6 to see that the cell has been selected. Then, we must select the cells till J12. So, that column range will get selected. Then click “Ctrl+D” so the formula is applied to the entire range.

#3 How to Use Mixed Cell Reference?

In the below-mentioned table, we have values in each row (D22, D23, and D24) and columns (E21, F21, and G21). Here, we have to multiply each column with each row with the help of a mixed cell reference.

We can use two mixed-cell references here to get the desired output.

Let us apply two types of mixed references below in cell “E22.”

The formula would be =$D22*E$21

#1 – $D22: Absolute column and Relative row

The dollar sign before column D indicates that only the row number can change. At the same time, the column letter D is fixed. It does not change.

When we copy the formula to the right side, the reference will not change because it is locked, but When you copy it down, the row number will change because it is not locked.

#2 – E$21: Absolute row and Relative column

The dollar sign right before the row number indicates only the column letter E can change. Whereas the row number is fixed; it does not change.

The row number will not change when copying the formula because it is locked. But when we copy the formula to the right side, the column alphabet will change because it is not locked.

Instead of entering the formula for all the cells, we can apply a formula to the entire range. Now, we will click inside cell E22. As a result, first, we will see that the cell is selected. Then, we will select the cells until G24 so that the entire range will get selected. Next, we will click on the “Ctrl+D” key and, later, “Ctrl+R.”

Things to Remember

The cell reference is a key element of formula or excels functions.Cell references are used in Excel functions, formulas, charts, and various other Excel commandsExcel CommandsVLOOKUP function, IF condition, CONCATENATE function, find MAXIMUM and MINIMUM values are the most commonly used excel commands.read more.Mixed reference locks either of one. It may be a row or column, but not both.

Explained#

Types of Cell Reference in Excel#

#1 How to Use Relative Cell Reference?#

#2 How to Use Absolute Cell Reference?#

#3 How to Use Mixed Cell Reference?#

Things to Remember#

Recommended Articles#