Range Union - Excel Glossary

Range Union is a fundamental Excel technique for working with multiple non-adjacent areas. In professional spreadsheets, data often resides in separate columns or sections—union syntax lets you reference all these areas at once. The syntax varies by locale: North American Excel uses spaces (A1:A5 C1:C5), while European versions use semicolons (A1:A5;C1:C5). This approach streamlines SUM, COUNT, AVERAGE, and formatting operations, reducing formula complexity and improving readability. Union references work seamlessly with conditional formatting, data validation, and pivot tables.

Definition

A Range Union combines two or more non-contiguous cell ranges into a single reference using the union operator (space or semicolon). It allows formulas to perform calculations or formatting across multiple separate areas simultaneously, eliminating the need for complex nested functions.

Key Points

1Union operator varies by region: space (US/UK) or semicolon (Europe/France)
2Enables calculations across non-contiguous ranges in a single formula
3Works with most functions: SUM, COUNT, AVERAGE, MAX, MIN, and formatting rules

Practical Examples

→=SUM(A1:A10 C1:C10) adds values from column A and column C, skipping column B entirely
→Applying conditional formatting to A2:A10 D2:D10 highlights alternating product columns simultaneously

Detailed Examples

Multi-department budget summation

A finance manager uses =SUM(B2:B20 E2:E20 H2:H20) to total sales, marketing, and operations budgets from separate sheet sections. This eliminates three individual SUM functions and reduces formula clutter significantly.

Conditional formatting across scattered KPIs

HR applies conditional formatting to ranges A5:A15 C5:C15 E5:E15 to highlight employee performance metrics above target across non-adjacent columns. The union reference ensures consistency without creating separate rules for each range.

Best Practices

✓Always verify your regional syntax (space vs. semicolon) before writing union formulas to avoid errors.
✓Use consistent range sizes within unions to simplify auditing and reduce calculation errors.
✓Combine unions with named ranges for improved readability and easier formula maintenance across large spreadsheets.

Tips

✓Test union formulas in a simple cell first before applying them to complex spreadsheet logic.
✓Use the Name Manager (Formulas > Define Name) to create named unions for frequently-used range combinations.
✓Remember that union operators cannot be mixed—stay consistent with either spaces or semicolons throughout.

Related Excel Functions

SUM COUNT AVERAGE MAX MIN SUMIF COUNTIF

Frequently Asked Questions

What's the difference between union and intersection operators?

Union (space/semicolon) combines multiple ranges; intersection (single space in some versions) returns only cells present in ALL referenced ranges. Union is for separate areas, intersection for overlapping cells.

Can I use union operators in pivot tables?

Union syntax works in pivot table source data definitions and conditional formatting rules, but pivot tables themselves cannot directly reference non-contiguous ranges due to their structure.

Why does my union formula show #NAME! error?

This typically indicates incorrect regional syntax (using space in a semicolon locale or vice versa). Check your Excel language settings and adjust your formula operator accordingly.