Permission & Access Issues

Link Password-Protected Excel Workbooks and Calculate Totals

Natalie TaylorNatalie Taylor Nov 12, 2026 868 views

Question details

Create links to values in password-protected Excel workbooks and calculate totals in a destination workbook.

Link Password-Protected Excel Workbooks and Calculate Totals
Product
Microsoft Excel workbooks and WPS Spreadsheet
Device & OS
not provided
Scenario
The user has access to protected source workbooks and needs to reference their cells or tables from another workbook.
Observed behavior
External links can work when source files are available and unlocked, but link refresh may be limited by file encryption, protection type, location, or missing credentials.
Before you start

Make sure you have permission and the correct password for every source workbook. Identify whether the password encrypts the file, protects a worksheet, or protects workbook structure, because these protections affect access differently.

Solution 1Recommended

Unlock source workbooks and create external references

Use this method when you can open each source workbook and need live formulas that total values from those files.

File encryption requires a password before the workbook can be opened. Worksheet protection and workbook structure protection may allow viewing data but can restrict editing, selecting cells, or adding sheets.

1
Open each protected source workbook

In Excel, select File > Open, choose each source workbook, and enter its password when prompted. If a worksheet is protected, go to Review > Unprotect Sheet and enter the worksheet password if editing or selecting restricted cells is required.

2
Open the destination workbook

Open the workbook where you want to show the linked values and select the destination cell for the first external reference.

3
Create a link to a source cell or range

Type =, switch to the unlocked source workbook, select the source cell, and press Enter. Excel creates an external reference such as ='[Source.xlsx]Sheet1'!$B$2.

4
Calculate the linked total

In the destination workbook, use a formula such as =SUM(B2:B10) to total imported linked values, or use =SUM('[Source1.xlsx]Sheet1'!$B$2,'[Source2.xlsx]Sheet1'!$B$2) to total values from separate workbooks.

5
Save and test the links

Save the destination workbook, close and reopen it, then choose Enable Content or Update Links if Excel asks whether to refresh external links. Keep source files in their expected folders so the saved paths remain valid.

Unlock source workbooks and create external references
Protection can remain enabled: After creating the references, you can save and close the source workbooks with their protections still enabled. The destination workbook may still require access to the source files to refresh values.
Work with linked spreadsheet totals

Create linked workbook totals in WPS Spreadsheet

WPS Spreadsheet lets you open compatible Excel workbooks, create formulas that reference cells in other workbooks, and calculate totals in a destination sheet. Use authorized passwords to open protected files, then keep source files in stable locations for reliable link updates.

  1. 1. Open the authorized source workbook: In WPS Spreadsheet, click Menu > Open and select the source Excel workbook. Enter the password when the file requires one.
  2. 2. Open the destination spreadsheet: Click Menu > Open to open the workbook that will display the linked values, then select the target cell.
  3. 3. Insert an external reference: Type = in the target cell, switch to the open source workbook, select the needed cell, and press Enter to create the reference.
  4. 4. Add a total formula: Select the total cell and enter a formula such as =SUM(B2:B10), then press Enter to calculate the linked values.
  5. 5. Save files in consistent locations: Use Menu > Save for both workbooks and avoid moving or renaming source files if you want existing external references to remain available.
Create external cell references and SUM formulas for workbook totals.Open and save Microsoft Excel .xlsx, .xls, and .xlsm workbook formats.Use a familiar spreadsheet interface for formulas, sheets, and file management.Work with lightweight spreadsheet tools for everyday reporting and calculations.
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel link to a workbook that has a password to open?

Yes, if you are authorized to open the source workbook and provide the correct file password. Create the external reference after opening the source file; later refresh behavior depends on whether Excel can access the protected source file and its location.

Does worksheet protection prevent external formulas from reading cells?

Not always. Worksheet protection mainly controls actions inside the sheet, such as editing locked cells. However, protected sheet settings, hidden content, and access restrictions can affect what you can select or modify when creating a link.

Why do my linked workbook totals show old values?

The source workbook may be unavailable, moved, renamed, closed behind restricted access, or waiting for an update prompt. In Excel, use Data > Refresh All or enable updates when prompted, then verify the source file path and permissions.

Can I use Power Query to bypass a workbook password?

No. Power Query does not bypass encryption, passwords, or organization permissions. The person or account refreshing the query must have authorized access to the source workbook.

What formula totals values from multiple linked workbooks?

Reference each authorized source cell in a SUM formula, for example =SUM('[North.xlsx]Data'!$B$2,'[South.xlsx]Data'!$B$2). You can also place linked values in a destination range and use =SUM(B2:B10).