How to cross reference between Excel spreadsheets

Written by stephen byron cooper Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to cross reference between Excel spreadsheets
Excel is part of Microsoft Office. (Getty Thinkstock)

Excel gives the ability to refer to values in other cells in a spreadsheet by putting in a cell identifier instead of a value. These cell identifiers can also substitute for absolute values in formulas. Using this method, the figures in cells automatically update when the figures in the source cells change. This facility not only allows the use of other cells in the same sheet, but also cells in other sheets in the same workbook, or sheets in other files.

Other People Are Reading

Other sheets

Label all the sheets in your workbook, giving each a unique and meaningful name. To do this, hover the mouse pointer over the tab for a sheet at the bottom of the screen. Click the right mouse button to get the “right-click” menu. Select “Rename” from this menu and enter a new name for the sheet.

Formula character

Choose a standard indicator to signify references. There are two methods that you can use. You can either put a plus (“+”) in front of it, or an equals sign (“=”). This symbol lets Excel know that the next text until the next mathematical operator would not be treated as text, but as a reference. Assume “=” is the indicator for the purposes of this exercise.

Referencing

Type the reference to a cell in the cell where you want to pull in a value from another sheet. If you were in cell B2 and wanted to reference cell A1 in the same sheet, you would write “=A1” (without the quotes). To access cell A1 in another sheet, and call this sheet “OtherSheet” for this exercise, type “=OtherSheet!A1” and press enter. The value held in cell A1 in the OtherSheet will appear in cell B2 of the current sheet.

Other files

Save the workbook you want to reference. Make a note of the name of the file and the worksheet in that file that has the cell you want to reference. Open the new workbook, then also open the workbook containing the cell you want to reference. For this exercise, call the source file “OtherBook” and put the cell you want to reference in a sheet called “OtherSheet”.

Change view

Click on “View” in the top menu and select the “View Side by Side” option. You will see the new workbook in the top half of the screen and “OtherBook” in the bottom half of the screen.

Create link

Click in the cell where you want to use the referenced value. Type “=” and then click on the cell in OtherBook that you want to reference. Press “Enter” to get the reference written into the new workbook. If you pressed on cell A1 in OtherBook, which is in “OtherSheet” then the text in the cell in your new workbook will read “=[OtherBook.xlsx]OtherSheet!A1” when you enter the cell. However, when the referencing cell is not the currently active cell, it will display the value that appears in cell A1 in OtherSheet in OtherBook.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.