Ravindra BagaleCourses & study guides मराठी Track your progress

23. Formulas Across Sheets and Workbooks

23.6 Links to Another Workbook (External References)

By Last updated:

Example first. Each city lead sends a file: Pune_Nov.xlsx, Nashik_Nov.xlsx and Nagpur_Nov.xlsx. Each file has a Summary sheet with delivered sales in B2 and orders in B3. Raja keeps a Region.xlsx file whose formulas read those cells directly. Region total: ₹9,11,000.

External workbook reference — in 30 seconds

What: A formula can read a cell in another file. While the file is open it looks like =[Pune_Nov.xlsx]Summary!$B$2. When the file is closed Excel adds the full path.

Why: Many teams keep one file per city or per month. External links build the regional view without copy-paste, and they update when the source files change.

Tiny example: =[Pune_Nov.xlsx]Summary!$B$2 + =[Nashik_Nov.xlsx]Summary!$B$2 + =[Nagpur_Nov.xlsx]Summary!$B$2 = ₹9,11,000.

First look at the data (before → after):

External workbook reference: before and after tables, fictional Blinkit Maharashtra data

Expected result — Region.xlsx

City Sales (₹) Orders AOV (₹)
Pune 4,12,000 1430 288.11
Nashik 2,68,000 980 273.47
Nagpur 2,31,000 905 255.25
Total 9,11,000 3315 274.81
Before table and formula as text

Before — Three source files (sheet Summary in each)

File B2: Delivered sales (₹) B3: Orders
Pune_Nov.xlsx 4,12,000 1430
Nashik_Nov.xlsx 2,68,000 980
Nagpur_Nov.xlsx 2,31,000 905

Formula / click path

Region!B2: =[Pune_Nov.xlsx]Summary!$B$2      (source open)
Region!B2: ='C:\Reports\Nov\[Pune_Nov.xlsx]Summary'!$B$2   (source closed)
Region!B5: =SUM(B2:B4)   C5: =SUM(C2:C4)   D5: =B5/C5

Do it step by step:

  1. Open Region.xlsx and Pune_Nov.xlsx together.
  2. In Region.xlsx click B2 and type =.
  3. Click View › Switch Windows › Pune_Nov.xlsx, click the Summary sheet and cell B2, then press Enter.
  4. Back in Region.xlsx, the cell shows =[Pune_Nov.xlsx]Summary!$B$2. Repeat for Nashik and Nagpur.
  5. Add the totals and AOV =B5/C5.
  6. Close Pune_Nov.xlsx and look at the formula again: it now shows the full folder path.

What happens when you open Region.xlsx next week. Excel shows a yellow bar: Security Warning: Automatic update of links has been disabled. Click Enable Content only if you trust the source files. The values shown before you click are the ones saved last time.

Good habits for external links

  1. Keep all linked files in one folder and do not rename them after linking.
  2. Link to one summary cell per file (like B2 and B3), not to hundreds of cells.
  3. Note the source files and the folder on the Region sheet, so the next person knows where the numbers come from.
  4. For more than a handful of files, or files that arrive every month, use Power Query › Get Data › From Folder (Module 10). It is easier to refresh and does not break when one file is missing.

Practice task (try it before you open the answer):

  1. Create the three small source files and Region.xlsx.
  2. Link Sales and Orders for all three cities.
  3. In Nashik_Nov.xlsx change B2 to 300000, save, and look at Region.xlsx.
Answer

Region total becomes ₹9,43,000 and the AOV 284.46. If Region.xlsx was open at the same time, it updates at once; if not, click Enable Content when you open it.

Ravindra Bagale's Tip

Before you email a file that links to other files, break the links or paste values. The person who receives it does not have your C:\Reports folder and will see only old numbers or errors.