February 25, 2014

Accessing data from another workbook in Excel

One of the questions that is very regularly asked during training sessions: how can I access data from another workbook in Excel. Let’s say we have the data ‘Salaries’ in Book1. Now we open another workbook. How can we do calculations in the new workbook using the data in book1.
If the the workbook book1 is open, we can access it through the path and use the formula :”=sum([mybook1.xlsx]Sheet1A2:A4)”. ‘.xlsx’ is the file extension name in Excel 2007.
If the workbook1 is closed then we have to use the complete path including the hard-disk as shown “:=SUM(‘C:\Documents and Settings\Dinu\My Documents\[mybook1.xlsx]Sheet1’!A2:A4)”.
If you had named the range ‘A2:A4’ in Book1.xlsx as ‘salaries’ then the access path would have been ‘=SUM(Book1.xlsx!salaries)

What are the areas where you can use external references as shown above effectively?

  • You can merge data from several workbooks: You can create a summary workbook from many external workbooks of different departments in your office. Now when people change the data in the linked or referenced workbooks, the summary workbook automatically gets updated.
  • You can create different views of your report: Create a new workbook by linking to relevant data in other workbooks and then create a pivot table report, for example. This report can be updated as and when the external data changes.
  • You can handle large complex data better: Let’s say you have a large finance model You can break it down into many small interlinked workbooks. These small interdependent workbooks can be easily manipulated and save on memory usage and increase the speed of calculations. The more important and pertinent calculations can be kept in another interlinked workbook!