Question

describe the difference between a relative cell reference, absolute cell reference, and a mixed cell reference in a formula? In or after your explanation, please provide examples either in screenshots or in an attached Excel file that show the practical uses for one or all of these.

Answer 1

Ans: -

In excel the cells are very important part because every single piece of information and data in excel is represented using these cells.

So, the Cell reference is the technique used in excel to refer or addressing the cells using the reference formulas. Every cell of the excel sheet has the unique address that consists of rows and columns.

In Excel there are three techniques for referring or addressing a cell which are as follows:

1. Relative Reference
2. Absolute Reference
3. Mixed Reference

1. Relative Cell Reference: - In relative cell reference, the formula that we apply on cells gets change for every cell value by just dragging the plus icon that appear on the cell.

Let us take and example for the clear understanding.

Here we are calculating the sum for the A and B cells in the C cell by applying Sum function. But instead of using formula for calculating each value we can use Relative reference method by just dragging the mouse. It is given below as: -

So, here is the advantage of the relative cell reference that we do not need to calculate value for every cell. By just dragging the + icon using the mouse we can do the same.

Relative cell references are very useful when you create a formula for the different cells and we need the same formula to be referred other cells than it can use relative cell reference.

2. Absolute Cell Reference: -

The absolute reference is different from the relative cell reference because it does not provide the copy paste of the same formula for the different cells. We use $ sign to make absolute cell in the excel sheet.

Let us take an example to understand the Absolute cell Reference technique: -

Here we are trying to multiply our resulted sum with the value 20 in the F1 column. But if we will drag the + icon then other cell values will get result 0 as shown below: -

Here the absolute reference comes into play. If we add $ sign i.e. = C1* $F$1 then it will become absolute reference and we will be able to multiply other values with 20 by just dragging the + icon.

Here is the screenshot of the demonstration: -

Absolute reference is useful for calculating the tax rate and commissions etc. when you want to use formula in specified cells.

3.Mixed Reference: - In this reference the formula can be applied either on row or column instead of applying on the whole cell. It has two different conditions:

The row remains fixed while formula is copied and column changes or column is fixed while applying the formula and row changes.

Example: -

Here we are having combination of three rows and columns in which we are performing SUM function.

You can look on the formula in the below screenshot that there is a $ sign in front of the row number but not in the column letter. Which means it will update the column letters when formula will be copied but for the row numbers the value will remain same if we copy paste the formula.

Here are the screenshots for the same: -

So, these were the differences between the Relative reference, absolute reference and the mixed references in Excel