Formula Errors

How to Fix the Excel Comma Union Operator #VALUE! Error

Steve K Sep 28, 2026 869 views

Question details

The user needs to fix a #VALUE! error that occurs when attempting to combine separate Excel table columns using the comma union operator.

How to Fix the Excel Comma Union Operator #VALUE! Error

Product: Excel
Device & OS: not provided
Scenario: Referencing multiple, separate table columns in an Excel formula using a comma without an accompanying function to process the combined ranges.
Observed behavior: The formula returns a #VALUE! error because Excel cannot evaluate the standalone comma union operator into a combined array without a proper calculation or array function.

Before you start

Ensure you are using a spreadsheet version that supports dynamic array functions, as utilizing these functions is the most efficient way to combine separate columns without errors.

Solution 1Recommended

Use the HSTACK Function to Combine Columns

Replace the standard comma union operator with the HSTACK function, which correctly processes the ranges and appends the arrays horizontally.

The comma acts as a union operator in Excel, but unless it is wrapped in a function that inherently processes multiple distinct ranges (like SUM), Excel doesn't know how to display the combined raw data. The HSTACK function is specifically designed to combine arrays side-by-side.

Select the destination cell

Click on the cell where you want the upper-left corner of your combined table columns to appear.

Enter the HSTACK function

Type =HSTACK( in the formula bar to begin the array combination function.

Input the table column references

Add your first column reference, type a comma, and add your second column reference. For example: =HSTACK(DeptSales[Sales Amount], DeptSales[Commission Amount]).

Execute the formula

Close the parentheses and press Enter. The combined data will now spill dynamically into the adjacent columns without returning a #VALUE! error.

Use the HSTACK Function to Combine Columns

Dynamic Array Spilling: Because HSTACK is a dynamic array function, ensure there are empty cells to the right and below your target cell, or you may encounter a #SPILL! error.

Advanced Formula Support

Combine Table Columns Seamlessly in WPS Office

WPS Spreadsheets provides robust support for modern array formulas, allowing you to manipulate, stack, and combine data columns effortlessly while avoiding complex syntax errors.

1. Open your workbook: Launch WPS Spreadsheets and open the file containing your separate data columns.
2. Select the target cell: Click on the blank cell where you wish to output the combined data.
3. Enter the array formula: Type the formula using supported array functions, such as =HSTACK(Table1[ColumnA], Table1[ColumnB]).
4. Calculate and spill: Press Enter to execute the formula. WPS Spreadsheets will instantly calculate and display the combined arrays.

Fully compatible with Microsoft Excel formulas and .xlsx formatsAdvanced support for dynamic array functionsLightweight application with quick calculation speedsBuilt-in error checking to help troubleshoot formula issues

microsoft office alternative - wps office

Frequently Asked Questions

Why does the comma operator cause a #VALUE! error in Excel?

The comma is a union operator meant to pass multiple ranges to a function (like SUM or AVERAGE). When used on its own or outside a function that can interpret unionized arrays, Excel cannot resolve it into a single displayable value, triggering a #VALUE! error.

What is the difference between HSTACK and VSTACK?

HSTACK appends arrays horizontally (side-by-side into columns), while VSTACK appends them vertically (one below the other into rows). If you want your separate table columns to stack on top of each other, use VSTACK instead.

How do I fix a #SPILL! error after using HSTACK?

A #SPILL! error occurs when the dynamic array function does not have enough blank cells to display all the data. To fix this, clear any text, numbers, or hidden characters in the cells directly adjacent to your formula so the array has room to expand.

Excel & Data Problems