Home >Tips >Excel values between files kalmstrom.com site map icon Site map  

Fetch values from other Excel files

An Excel tutorial by Peter Kalmstrom

Excel iconIn the demo below Peter Kalmstrom, Microsoft certified Excel expert and trainer, shows how to tell Excel to fetch a value from another Excel file to the current sheet.

This is the process:
  • Open the two Excel sheets and put them side by side on the screen. This is easily done if you use the Windows button + the left and right arrow keys.

  • Select the cell where you want to place the value that should be fetched from another file.

  • Write an equals sign in the formula bar.

  • Click on the cell that has the value you want to fetch.

  • Now Excel will create a formula that fetches the value in the cell you clicked on to the selected cell in the other Excel file.

    Excel formula bar

  • Press Enter to see the fetched value in the selected cell, instead of the formula.

Each time the value in the fetched cell is changed, the value in the cell you selected will be changed too.

Peter uses Excel 2013 for his demo, but you can do the same with earlier versions of Excel.

Excel formula barNote: when you add a new line in the source file, the row number of the cell you fetch a value from will be changed. Normally Excel handles this and updates the formula automatically, but this is NOT done when you refer between files.

This problem is avoided if you give the source cell a name instead of using the cell coordinates. Right click on the cell that has the value you want to fetch and select "Define name". Give a unique name to the cell, and then follow the instructions in the demo.

Now you can modify the other cells as you wish. The source cell will always have the same name, and the value of that cell will be fetched to the new file.