How to use an absolute cell reference in excel

If you are curious how to use an absolute cell reference in excel, no need to worry now because you have come to the right page. But before going in detail, let’s see what this function does.

The absolute cell reference locks, the row, or column or even both row and column. This is very useful in calculation or referencing a specific value or text. In this article, you will know with pictures how to lock row, column, or both together and eventually your question how to use an absolute cell reference in excel will be answered. So, let’s begin

Use Absolute cell reference to lock row and column in excel

1. Open your file in which you need to use the function. This function contains test numbers and multiplication numbers. We need to find multiplied values using absolute cell referencing.

2. Click on the cell E7.

3. To use cell reference, it is required to insert some operation between two values. We will add multiplication operation in this case. To do this, click cell D7 and type “=”.

4. Now write multiplication function and click H7. This will do multiplication between both values

5. If you want to lock this cell, click on the cell H7 in the function tab. Then you can press Enter key.

6. Now press press F4 function key on the keyboard. Notice the $ sign appears before cell and column name. The cell is locked now.

7. Now press Enter key. The multiplication is done using cell referencing

8. Now again click E7 and put your mouse to the bottom right corner of the cell. Cursor will change into + sign.

9. Click the left mouse, hold it and drag down to E12 and release. The multiplication is done on all values.

10. When you click on any tab and you will see function containing values of D column  multiplied by cell H7 values because it was locked in the formula

Use Absolute cell reference to lock row in excel

1. Open your excel file. This file contains test numbers and multiplication numbers. We have to find multiplied values using absolute cell referencing.

2. Click on the cell E7.

3. To use cell reference, it is necessary to insert some operation between two values. In this case the operation will be multiplication. To do this, type “=” and click cell D7

4. Now insert multiplication function and click H7. This will do multiplication between both values

5. Now before pressing Enter key, click on the cell H7 in the function tab if you want to lock this cell.

6. Now type press F4 function key on the keyboard. Notice the $ sign appears before cell and column name. The cell is locked now.

7. Press F4 again, the function only shows $ sign in front on row only.

8. Now press Enter key. The mulitplication is done using cell referencing and row is locked

9. Now again click E7 and hover your mouse to the bottom right corner. This will change cursor into + sign

10. Click the left mouse, hold it and drag down to E12 and release. The multiplication is done on all values.

11. Click on any tab and notice in the function that the values in the D column are all multiplied by cell H7 again. The row is same in the function so the result will also be the same as locking row and column both

Use Absolute cell reference to lock column in excel

1. Open your file you wish to use the function. This function contains test numbers and mulitplication numbers. It is requried to find multiplied values using absolute cell referencing.

2. Click on the cell E7.

3. In order to use cell reference, it is required to insert some operation between two values. We will add multiplication operation in this case. To do this click cell D7 and type “=”.

4. Now insert multiplication function and click H7. This will do multiplication between both values

5. Now before pressing Enter key, click on the cell H7 in the function tab if you want to lock this cell.

6. Now type press F4 function key on the keyboard. Notice the $ sign appears before cell and column name. The cell is locked now.

7. Press F4 again, the function only shows $ sign in front on row only. Again press the F4 which will show the $ sign in front of column H only.

8. Now press Enter key. The mulitplication is done using cell referencing and row is locked

9. Now again click E7 and hover your mouse to the bottom right corner. This will change cursor into + sign

10. Click the left mouse, hold it and drag down to E12 and release. The multiplication is done on all values.

11. Click on any tab and notice in the function that the values in the D column are all multiplied by different cell values but same column head. This is because we only locked the column and not row.

So this is how to use an absolute cell reference in excel. Need to edit Word/Excel/PPT file free of charge? download WPS Office edit files like without any cost. Download now! to get enjoyable working experience.