Forgot your password?

Reference Cells in Another Worksheet in Excel

By Edited Nov 13, 2013 0 1

It is possible to have several worksheets in a Microsoft Excel workbook (document) and reference cell data between the worksheets.  The function is actually pretty straight forward, but there are a few tricks to keep in mind.

The Function

If you're already comfortable using Excel and you're just looking for a reference, here is the function format:



Entering the Function into a Cell (basics)

1. To apply this function, start by typing an equal sign (=) into the cell where you want the data to appear.  This tells Excel that what you are entering here will not be displayed ver batim, but you are referencing other data, or entering a function to compute some sort of value.

2. Enter the worksheet name you want to collect the data from.  For example, by default the first worksheet in Excel is called Sheet1 (screenshot below).

Excel Worksheet Screenshot

So far, the text in your cell should look like this:


3. Next, type in an exclamation mark to let Excel know that you're referencing a cell within that worksheet.  You're not done yet.  Now we have this:


4. Now we want to type in the address of the cell that we're accessing in the other worksheet.  For example: "C5" (without the quotation marks).  You should now have something similar to this:


Here is what it looks like in context:

Excel Function Screenshot

5. Hit "Enter" (or return) and your data should apply to the currently active cell.

Worksheet Names With Spaces

Excel Worksheet with Spaces Screenshot
If your worksheet name has a space in it you can surround the worksheet name with single quotes, like this:

='My Worksheet'!C5

A cool feature of Microsoft Excel is that if you reference a worksheet like this and then decide to change the name of the worksheet later, you DO NOT have to change your formula.  Excel will update your forumulas for you when worksheet names change.  It will not require any further modifications by you.  Cool, huh?


Here is an overview showing how this is completed:

Excel Example Screenshot


Mar 16, 2013 5:32pm
....or, you could just type in '=', click on the cell in the other worksheet, then hit enter. Pretty sure the click method works for referencing cells in other notebooks too, although you may have issues with updating if you don't open all relevant notebooks at the same time.
Add a new comment - No HTML
You must be logged in and verified to post a comment. Please log in or sign up to comment.

Explore InfoBarrel

Auto Business & Money Entertainment Environment Health History Home & Garden InfoBarrel University Lifestyle Sports Technology Travel & Places
© Copyright 2008 - 2016 by Hinzie Media Inc. Terms of Service Privacy Policy XML Sitemap

Follow IB Technology