A common thing to do in Excel is to use one sheet for collecting raw data, such as sales totals, and then to use another tab, or even a workbook to create summations or reports; the thing is though, doing so requires you link the cells first. Fortunately, this is a lot easier than it sounds.
To see how it works, first create or open a sheet that has some data in it; here, we’ll use the data sheet shown below:
Highlight and copy the cells you want to link, in this case, the YTD totals for each of the prior ten years. Next, open a new Tab by clicking one of the tabs at the bottom of the workbook or by clicking on the little plus icon.
Then, move over to the place in the new tab where you want to place your link, and highlight the same number of cells that you started with on the first tab. It’s worth noting that in newer versions of Excel, you can simply right click anywhere and choose Paste Link rather than selecting an area that has the same number of cells as the original selection.
Then right click in the highlighted area, you should see this popup menu:
Look where it says Paste Options: there are six icons to choose from, they represent, from left to right: Paste, Values, Formulas, Transpose, Formatting, and Paste Link.
To paste the data from your other tab, choose Paste Link.
Note: Excel 2010 unlike prior versions of Excel lets you see what will be pasted into your sheet when you hover over the options.
To see what happened when you pasted your link, move to one of the cells that was copied over and note what it says in the cell display box:
In the example, “Raw Sales Numbers” refers to the name of the original tab, while the exclamation point is known as a pointer, in this case because it’s pointing to the cell to follow (P7). Thus, in this sheet, the content of cell E5 in the new tab is whatever is in cell P7 on the tab called Raw Sales Numbers.
The process for linking between workbooks is virtually identical; the exception being, instead of pasting to a new tab, you instead paste to cells in a whole different spreadsheet.
The address for one of the pasted cells now looks like this:
In this case, the cell content shows first that it’s an external Excel sheet, then the path address of the file, then the sheet name, and finally the range name that was created automatically for the range of cells that was specified.
Also, you can create any number of links to the same set of cells as you please. Whenever you update the original cells, the data will be updated in all the locations where you created a link.
Linking between tabs or workbooks is a useful thing to do when you want to have raw data in once place and results in another. This is particularly true of large sheets where it can become difficult to see what is what; grouping results on a new tab helps to clarify your spreadsheets and makes them far easier to read and understand.
