MVP #50: How To Link Sheets and Duplicate Sheets in Microsoft Excel

You sometimes have to pull data into a report from another report or Excel file, the most effective way to do this is to link the sheets. You will be mirroring the value in the source sheet in your destination sheet. 

An example is if there is a sheet that contains the internal revenue of all the states in Nigeria and you are doing a report on Kebbi state. You want to pull the values for Kebbi state from the sheet that has everything (source sheet), your best bet is to link the sheets. It is preferable to copying the values from the source sheet because if the source sheet is updated with new values your analysis sheet will not automatically update (will still be showing the now incorrect old values). 
So how do you link sheets?

It’s very easy. In the cells, in the analysis sheet, for the different months values you will type = and select the cell with the actual figure in the source sheet.

In the end you will have the following or similar:




The source sheet has the name Datasheet, hence the =Datasheet!B25 in January value cell in the analysis sheet. You don’t type the anything beyond = into the values cells in the analysis sheet, once you select the right cell in the source sheet, Excel will write everything you see in the cell.

Duplicating Sheets
Sometimes you will want an exact copy of a sheet to work with or email to someone (especially if the Excel file contains other sheets you don’t want the person to access). Excel has a nifty tool for duplicating sheets. And it’s very easy to use.

Right click on the name of the sheet you want to duplicate. Click on Move or Copy…


You’ll see a dialog box. Select new book and tick Create a Copy.


And the sheet will be duplicated in a new Excel file for you.