Formula Errors

How to Fix Excel AVERAGE Formula Error for External Workbooks

Amos GikundaAmos Gikunda Oct 13, 2026 869 views

Question details

The user needs to properly format an AVERAGE function that calculates data from a different, external Excel workbook.

How to Fix Excel AVERAGE Formula Error for External Workbooks
Product
Microsoft Excel
Device & OS
not provided
Scenario
Attempting to calculate the average of a specific cell range located in an external workbook, such as a sales report file.
Observed behavior
The formula returns a syntax error because it lacks proper function structure, specifically missing the opening parenthesis between the function name and the external workbook reference.
Before you start

Ensure that you know the exact file path, workbook name, and sheet name of your external data source. It is highly recommended to keep the source workbook open while building your formula to prevent link update errors and simplify the selection process.

Solution 1Recommended

Add Parentheses and Verify External Reference Syntax

Fix the formula error by completely enclosing the external workbook path and cell range within the AVERAGE function's parentheses.

In spreadsheet software, every function requires opening and closing parentheses to contain its arguments. A common mistake when linking external workbooks is placing the file path immediately after the function name without parentheses, like =AVERAGE'https://...'. This immediately breaks the formula calculation.

1
Select the destination cell

Click the cell in your current workbook where you want the calculated average to appear.

2
Type the function name with a parenthesis

Enter =AVERAGE( to begin the formula. Do not forget the opening parenthesis.

3
Add the external reference

Type or paste the external link format inside the parentheses. If it is a web path, include the full URL. It should look similar to: 'https://d.docs.live.net/.../[Bike_Sales_2021.xlsx]Sheet1'!$Q$2:$Q$14

4
Close the parenthesis and calculate

Type the closing parenthesis ) at the end of the formula and press Enter. The complete correct formula is: =AVERAGE('https://d.docs.live.net/.../[Bike_Sales_2021.xlsx]Sheet1'!$Q$2:$Q$14).

Add Parentheses and Verify External Reference Syntax
Single Quotes Requirement: If your workbook name, file path, or sheet name contains spaces or special characters, you must enclose the entire path and sheet name in single quotes before the exclamation mark (!).

Calculate External Data Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers full support for cross-workbook references and advanced formulas like AVERAGE. By using WPS Office's tabbed workspace, you can easily click between open workbooks to automatically generate complex formula syntax, ensuring accurate calculations without syntax errors.

  1. 1. Open your files in WPS: Open both your destination file and source file in WPS Office's convenient tabbed workspace.
  2. 2. Initiate the function: Type =AVERAGE( in your target cell to start the formula.
  3. 3. Select and auto-generate: Click on the source workbook tab at the top, highlight your data range, and press Enter to let WPS auto-generate the perfect formula.
100% compatibility with Microsoft Excel formulas, functions, and external workbook links (.xlsx).Intelligent formula auto-complete prevents missing parentheses and common syntax errors.Lightweight tabbed interface makes it incredibly easy to switch between multiple open workbooks.Free to use with comprehensive data analysis and visualization tools.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my external reference formula show a #REF! error?

A #REF! error usually occurs if the source workbook was deleted, moved, or renamed. It also happens if the specific worksheet or cell range referenced in your formula was deleted from the source file.

Do I need to keep the source workbook open for the AVERAGE formula to work?

Generally, simple calculation functions like AVERAGE will retrieve data from a closed external workbook as long as the file path in the formula is fully defined and accurate. However, keeping the file open helps prevent reference update warnings.

How do I update the external workbook links in my spreadsheet?

Navigate to the Data tab on your ribbon and click on 'Edit Links' or 'Connections'. Select your source workbook from the list and click 'Update Values' to fetch the latest data for your formula.

What is the correct syntax for referencing a closed workbook locally?

For a closed local workbook, the syntax must include the full drive path. For example: =AVERAGE('C:\Documents\Reports\[Bike_Sales_2021.xlsx]Sheet1'!$Q$2:$Q$14).