In Excel, it's easy to collect the value of another cell to reuse it. Let's see different situations in the same worksheet or in another worksheet.
What is the cell reference?
In Excel, a cell is often called by its reference. In other words, it is the intersection of a row and a column, like A1, B5, D8,... For instance, here, the active cell is the cell B3
If you are unfamiliar with the Excel keywords, you can do this QUIZ
Reusing the value of a cell in the same worksheet
In Excel, it's easy to reuse the content of another cell.
- Select any empty cell in your worksheet
- Press the =sign
- Select the cell to link (here A1)
- The complete formula is displayed below (easy to understand 😉)
Remarks. When you refer to another cell
- The content is duplicated, whatever the type of data, Text or Number
- The format of the cell selected is not repro
Why it's important?
Creating such a reference is not complicated, and it's the best way to work with Excel. Because now, each time you update the value in the source cell, the linked cell is automatically updated
Reusing the value of a cell in another worksheet
If you want to link a cell from another worksheet, the steps are the same
- Select any cell in another sheet
- Press the =sign (this activates the edit mode)
- Return to the worksheet with the cell that you want to link
- Select the cell with the content to link
The formula is now:
The delimiter between the Sheet name and the cell reference is ALWAYS an exclamation mark (!)
What happens if you change the Sheet's name?
If you change the Sheet's name, Excel will automatically change the reference in the destination cell.
Copy a formula with references
When you copy a formula that contains references to other cells, the action to copy will change the references according to the direction of the copy.
For example, let's take the formula in cell C2 for calculating the total.
When you copy down this formula, all the references will be changed as follows:
- The reference of the row number changes
- The reference of the column letter remains the same
Copy a formula containing a reference to the right
Now, if we copy a formula to the right:
- The reference of the column letter changes
- The reference of the row number remains the same
This example shows a cumulative sum of sales from month to month. C4 contains the formula:
Now, by copying this formula to the right, all column references change but not those of the rows.
As you can see, depending on the direction in which you copy your formula, the cell references will be modified according to the direction of the copy. It's very clever😊
The absolute or relative reference
As you can see, each time we copy a formula with references, the references change. It's because we have written relative references.
But we can avoid that by fixing the references. We call them absolute references.
In this article, we will see how to use them.