Amazing Cell Reference in Excel

While teaching Excel to students in Universities, I have observed that they find it difficult to understand and apply reference in Excel formulas. Though initially it looks bit confusing but if understood patiently it is not only simple but also super useful and necessary in getting formula-based outputs in multiple cells in Excel.

Today here I am going to explain reference in Excel for the benefit of Excel enthusiasts.

We know that a worksheet in Excel is made up of cells and these cells can be referenced by specifying the row value and the column value. The columns in Excel are represented by alphabets A, B, C…. and rows in Excel are represented by numbers 1, 2, 3, …. and so on. For example, the very first cell is referred as A1 where A is the first column and 1 is the first row.

What makes Excel Powerful is that while creating formulas in Excel we can use these cell references in other cells.

There are three kinds of cell references that we can use in Excel:

· Relative Cell References

· Absolute Cell References

· Mixed Cell References

Understanding these different types of cell references will help us work with formulas and save lot of time.

Understanding these different types of cell references will help us work with formulas and save lot of time.

Here is a video you can watch to understand cell reference

File used in the video is attached here for practice

Reference in Excel
Download XLSX • 191KB

Relative Cell Reference

Let’s first understand Relative Cell References in Excel which is the simplest one. Let’s take an example-

To calculate the net income for each month, we need to subtract the expense of each month from revenue of each month. For the first item, the formula in cell D3 would be B3- C3 (as shown here):

Now, instead of entering the formula for all the cells one by one, you can simply copy cell D2 and paste it into all the other cells (D3:D14). When you do it, you will notice that the cell reference automatically adjusts to refer to the corresponding row. For example, the formula in cell D4 becomes B4-C4 and the formula in D6 becomes B6-C6.

The references which adjust automatically by default, when formulas are copied in Excel are called relative cell reference in Excel.

Whenever a formula is created the and the cells on which formula is dependent need to change with the cells in which formula is copied, we use relative reference and just copy and paste the formula, without making any changes.

You may be familiar with what all has been told till now, but you are requested to be patient for some time and understand the next two references which will help you appreciate the difference and efficiency it will bring in the way you use formulas.

Absolute Cell Reference

Let’s now understand Absolute Cell References in Excel. Unlike relative cell references, absolute cell references don’t change when you copy the formula to other cells. Let’s take an example-

In the table below we have marks of some students out of 100 and we need to calculate the percentage for each student.

The formula to be entered in cell C3 is =B3/B12 and we get the percentage. Now as we did in previous example, if we copy the formula in cell C3 and paste in cells C4 to C10, we will get absurd results.

Now what is goung wrong here. If we go to formulas tab and click on show formula in formula auditing group, we can see all the formulas applied as below-

Now if we look carefully, we will see that, relative reference has been applied and B12 cell which should be fixed and should not change is also changing to B13, B14…. as the formula moves down to C4, C5… and so on. To solve this problem absolute reference comes into picture and we need to apply $ sign and write B12 as $B$12 (Shortcut for this is function key F4), Once we do this the cell B12 gets fixed and will not change even if the formula is copied to cells C4 and C10.

And if we use show formulas, we can see that formula has denominator fixed as $B$12 and it is not changing as we want the max marks to be same for all students and this is called as giving absolute reference.

Mixed Cell Reference

Next, we will talk about Mixed Reference. This one is a bit trickier, but very powerful and useful.

There can be two types of mixed cell references:

  • The row is locked while the column changes when the formula is copied.

  • The column is locked while the row changes when the formula is copied.

Let’s see how it works using a simple example. We are creating here a multiplication table and this can take few minutes to complete if we are not using mixed reference. But if we use mixed reference this table can be populated in few seconds.

In cell B5 a formula is applied =B4*A5 which is 1X1=1. If se drag this formula with relative reference, it will give absurd outputs. You can try in the practice excel which is given here for practice.

If we apply absolute reference by freezing both the cells, we will get 1 in all the cells as output.

To get the correct out put we write the formula in cell B5 as =B$4*$A5 and copy and paste it to all the cells and we get the output as-

Its important to understand, why has it been done and when to apply dollar in front of alphabet and when in front of number.

If we care fully look at the formula =B$4*$A5, we will observe that number 1 to 12 written in row 4 are used in our formula. In this row when we move from left to right the cell address changes from B4 to C4, D4, E4 and so on. We observe that the alphabets are changing while the number 4 representing the row remains constant and thus, we freeze the row and to do this we apply dollar in front of number 4.

Similarly, when we look at the second cell reference in our formula, it is A5 and as we move downwards the cell reference changes to A6, A7, A8 and so on. Here the row numbers are changing but the column A remains constant and hence we apply $ in front of A.

Another example is given in Excel sheet, where budget is being prepared and to accomplish our task in few minutes, mixed reference is used.

To solve this you can watch the video and solve in the Excel file attached.

Reference in Excel
Download XLSX • 191KB

1 view0 comments

Recent Posts

See All