Formula Errors

Fix #VALUE! Error When Referencing Excel Tables

Olivia Miller Sep 25, 2026 872 views

Question details

The user needs to resolve a #VALUE! error that occurs when referencing an entire Excel table on another worksheet using a simple formula.

How to Fix the #VALUE! Error When Referencing an Excel Table

Product: Microsoft Excel (2019 or earlier)
Device & OS: not provided
Scenario: Attempting to duplicate or reference an entire data table on a different worksheet by entering the table's name in a formula, such as =MyTable.
Observed behavior: Excel returns a #VALUE! error in the cell because older versions of the software do not natively support dynamic arrays.

Before you start

Verify your current version of Microsoft Excel. If you are using Excel 2019 or an older version, your software does not support dynamic arrays, which is the root cause of this specific formula error.

Solution 1Recommended

Enter the Table Reference as an Array Formula

Since Excel 2019 and earlier lack dynamic array support, you must enter the table reference as a traditional array formula using a specific keyboard shortcut.

In older versions of Excel, a single cell cannot automatically spill multiple values (like an entire table) into adjacent cells. To display the full table, you must manually designate the output area and apply an array formula.

Select the Destination Range

Highlight a blank range of cells on your current worksheet that exactly matches the row and column dimensions of your source table (for example, F1:I15).

Input the Table Formula

With the entire range selected, click into the formula bar at the top and type the formula referencing your table name, such as =MyTable.

Confirm as an Array Formula

Instead of pressing Enter, press Ctrl + Shift + Enter simultaneously. Excel will populate the selected range with your table data without showing the #VALUE! error.

Enter the Table Reference as an Array Formula

Automatic Curly Braces: Do not type the curly braces manually. Excel will automatically generate them (e.g., {=MyTable}) when you correctly press Ctrl+Shift+Enter.

Solution 2

Upgrade to a Version with Dynamic Arrays

If you frequently work with array formulas and table references, upgrading your software will natively resolve the #VALUE! error without needing manual array entry.

Free Microsoft Office alternative

Upgrade to WPS Office to Handle Spreadsheets Seamlessly

Tired of encountering formula errors due to outdated Excel versions? WPS Office provides a free, lightweight alternative with excellent format compatibility, a familiar interface, and comprehensive spreadsheet features.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office installer for your operating system.
2. Install the Software: Run the downloaded executable file and follow the quick on-screen instructions to install the suite.
3. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file to continue working with your data seamlessly.

Free to download and use with a highly lightweight installation.Highly compatible with Microsoft Excel (.xlsx) file formats and complex formulas.Familiar user interface requires no learning curve to get started.Efficiently handles large datasets, complex arrays, and table data.

microsoft office alternative - wps office

Frequently Asked Questions

Why does =MyTable work on my coworker's computer but returns #VALUE! for me?

Your coworker is likely using a newer version of Excel (like Microsoft 365 or Excel 2021) that supports dynamic arrays natively. If you are using Excel 2019 or older, the software cannot automatically spill the table into multiple cells, resulting in a #VALUE! error.

Can I fix the #VALUE! error without using Ctrl+Shift+Enter?

In Excel 2019 or earlier, referencing a whole table directly requires an array formula (Ctrl+Shift+Enter). Alternatively, you can use lookup functions like INDEX to pull individual cells one by one, but this is much more time-consuming.

How do I edit an array formula once it is created?

To edit an array formula, you must select the entire range of cells that the formula populates, make your changes in the formula bar, and press Ctrl+Shift+Enter again. You cannot change a single cell within an array range.

What does the #VALUE! error mean in Excel?

The #VALUE! error typically indicates that a formula has the wrong type of argument or operand. In this specific scenario, an older Excel version expects a single value for a cell but receives an array (an entire table) without the proper array syntax.

Excel & Data Problems