Cell Reference in Excel and Types

-
Microsoft Excel has widely used spreadsheet software which gives us an ability to work with small, medium or huge database. To work in this software we need to use its cells and how we are using this cell that method is called Cell Reference Type and the address of that cell is called Cell Reference.


 Cell Reference only can be used at the time of formula or function otherwise excel will not allow users to use a cell reference also we can use it for navigation purpose but most of the time it is used in a formula or a function.

By default whenever we want to use a cell reference excel will automatically capture its address just by clicking on it. Though it's an automatic process if we want we can type it manually also. Let's see how;

To Create a Cell Reference we need to combine two things one is Column Number and another one is Row Number, For Example, we need to create a cell reference which is situated in column no "D" and row no "15" then the reference should be "D15".


 So now we understand what is Cell Reference and how to create it, Let's understand Why we should learn it and use it in Excel?

See basically cell reference is widely used and every time we use it for its dynamic ability. Dynamic in the sense it is auto updatable whenever any changes made on a particular cell. This is the one and only but very important reason to use it.

As we discussed previously to use a cell reference excel will automatically capture its address just by clicking on it. Though it's an automatic process if we want we can type it manually also. Let's see what are the types of cell reference available in excel. Basically, there are 5 types of cell reference available in excel. They are;

  • Relative Cell Reference
  • Absolute Cell Reference
  • Mixed Cell Reference
  • Circular Cell Reference
  • 3D Cell Reference

Relative Cell Reference
Relative Cell Reference - This is the basic type of cell reference and this cell reference will be changed to the same direction on which a cell is being dragged.
That means if we drag any cell in the downward direction then it's also changed to next cell of that direction. It looks like a simple cell reference - D15.


Absolute Cell Reference
Absolute Cell Reference - This is a fixed type of cell reference and this cell reference will never change irrespective of any direction. That means whatever direction we drag a cell it will remain the same. Basically, the cell reference comes with a "$" sign before column number and row number which indicates that it is an "Absolute or Fixed type of cell reference".Like - $D$15.

Mixed Cell Reference
Mixed Cell Reference
Mixed Cell Reference - This cell reference is a mixture of Absolute and Relative cell reference. That means it will be changed based on the position of "$" sign in the cell reference. See our Video for better understanding. This is the most useful and most powerful cell reference in excel. It's used in most of the bigger and complicated calculations. It looks like - $D15 or D$15.





Circular Cell Reference
Circular Cell Reference - It's a cell reference which includes other cell references in within which the same cell also linked that means the calculation is circulated between those cells. It's rarely used cell references. Like - In D15 cell we link E16 and in E16 cell we link D15.



3D Cell Reference
3D Cell Reference - It's a smart cell reference. It's basically used for external connections and other worksheet connections. Like we want to use data which is present in another excel file of your computer or in the same excel file but in another sheet. It looks like - "SheetName!D15" (When the data is in the same file but another sheet), "FilePath!FileName!SheetName!D15" (When the data in another file).
3D Cell Reference
Check Out Videos for better understanding -

Need a Sample file for better understanding or practices?


Need a practice file for better practices? Don't worry we also have it for you

You can download the Practice file for Relative Cell Reference from here -https://drive.google.com/file/d/1U15ortCsibCC7DMtsKFlOFKhHc8nVKCH/view?usp=sharing



You can download the Practice file for Absolute Cell Reference from here -https://drive.google.com/file/d/1efAtVa63yS_k6fEHuoqG9xYtu0Sq8Am4/view?usp=sharing


If you want to learn more about excel subscribe our YouTube channel - SD Creations

Click Here to go to YouTube.

If you like this post then hit the like button and share it. If you have any query then comment it out.

You can also follow us on other social media,

Facebook: https://www.facebook.com/sdcreations41198/

Insta: https://www.instagram.com/s_d_creations41198/


Comments

Post a Comment