Refer to a cell on a different sheet in Excel

excel logoA basic ability of Excel is to be able to create a reference to the value or formula that is in a cell located on a different worksheet, or in a completely different spreadsheet file. If you are referring to a different spreadsheet (called a workbook by Excel) your reference is called a link. It's a lot easier than you would think. You can create either relative or absolute references (click here for more information) to other sheets. We'll cover referring to another sheet in the same workbook here.

The following examples all refer to Microsoft Excel 2003. If you are using a version that is different than that your screens will look slightly different, but the basic functionality should be the same.

Let's say you have a list of months located on the first sheet in your workbook (look here to see how to use the autofill feature to create this list):

 

excel ref diff sheet 01.gif

 

Now, click on the tab for Sheet2 at the bottom of the workbook:

 

excel ref diff sheet 02.gif

 

How about if we create references to those cells, starting in cell B4. The first thing to do is to click on the cell where you want to start:

 

excel ref diff sheet 03.gif

 

Now type an '=' sign in the cell, or in the formula bar:

 

excel ref diff sheet 04.gif

 

Then click on the tab for Sheet1:

 

excel ref diff sheet 05.gif

 

And then click on cell A1 containing the word January (notice the how the reference is being written in the formula bar):

 

excel ref diff sheet 06.gif

 

Now just hit the enter key, which will take you back to Sheet2:

 

excel ref diff sheet 07.gif

 

Finally, click on the expand button at the bottom right corner of cell B4, then drag down to expand the reference to pull in all of the months:

 

excel ref diff sheet 08.gif

 

Just like that.

Bytes: