How to link cells in Excel
The concept of linking cells is probably the most important aspect of spreadsheets, and simply something you must understand.
With that in mind, let’s say that we want to link two cells in Excel. How can we do this? Well, let’s illustrate this through an example. First, take a look at the screenshot below – pay special attention to the fact that inside the formula bar for cell B2, there is some text that says “=A1”. The formula bar is the long bar after the letters “fx” :
You can see in the image above we have a very simple spreadsheet in which cell A1 has the text “ProgrammerInterview.com”. Now, let’s say that we want to link cells A1 and B2 – so that B2 will always display whatever value is inside cell A1. In order to link the two cells, all we have to do in cell B2 is simply go to the formula bar and type “=A1”. This tells the spreadsheet to put the value in cell A1 into cell B2. Think of it as saying “set the value in this cell equal to the value in cell A1”, which explains why there is an equal sign in “=A1”.
So, can you guess what value will now show up in cell B2 once we hit “Enter”? That’s easy – it’s “ProgrammerInterview.com”, the value in cell A1 – as you can see here:
What happens to the referencing cell if the source cell is updated or changed?
In our example, because cell B2 uses the value in cell A1, we consider cell A1 the “source” cell – since it is the source of the value, and cell B2 is considered the referencing cell. If the source cell (cell A1) is changed, then the referencing cell (B2) will have the same changes applied to it as well. So, let’s say we change the text in cell A1 to say “ProgrammerInterview.com is great!”, then you’ll notice the same change in cell B2 as soon as you press Enter. Here’s a screenshot illustrating this concept:
If the formatting of the source cell is changed, will it change the referencing cell as well?
No, if the formatting of the source cell (A1 in this case) is changed then that change is not also made to the referencing cell (B2). Take a look at this image to confirm that fact – note that we changed the formatting in cell A1 to have a green background. But, as you can see, this change has no effect on cell B2 even though it still references cell A1:
What happens to the referencing cell if the source cell is moved?
If the “source” cell (cell A1) is moved somewhere else on the spreadsheet, then the referencing cell (B2) will still be linked to the original. What is interesting is that if cell A1 is moved to a different location – like cell A7 – then the formula reference in cell B2 will change to point to A7. So, the value in the formula bar for B2 will automatically update to A7. Here’s a picture illustrating that concept – note that the value in the formula bar for cell B2 has automatically changed to point to A7 without the user having to do anything.
Because the cell B2 references cell A1, Excel assumes that the user would like to continue to have the cells be linked even after cell A1 is moved to cell A7. Unlinking the cells is easy enough – you just have to remove the cell reference in the formula bar.