Function Problems

How to Use the @ Reference Syntax with Named Ranges in Excel

WPS Editor Oct 10, 2026 869 views

Question details

The user wants to understand how to use the @ syntax to reference a value on the same row within a named range, such as @interest_rates.

Using the @ Reference Syntax with Named Ranges in Excel

Product: Spreadsheet
Device & OS: not provided
Scenario: Referencing specific row values within a named range or structured Excel table to perform calculations.
Observed behavior: The @ symbol acts as an implicit intersection operator or structured reference, returning the single value located on the same row as the formula.

Before you start

Ensure your data is either properly formatted as a Table if you plan to use structured references, or verify that your named ranges are correctly defined in the Name Manager before applying the formula.

Solution 1Recommended

Using the @ Symbol for Implicit Intersection in Named Ranges

Use the @ symbol directly before a named range to extract only the value on the current row.

In modern spreadsheet software, the @ symbol is used as the implicit intersection operator. When applied to a named range that contains multiple values, placing @ before the name forces the formula to return only the single value on the same row as the formula itself, preventing the entire array from spilling.

Define the Named Range

Highlight your data array (e.g., C2:C10), navigate to the Formulas tab, and click 'Define Name'. Enter a descriptive name like 'interest_rates'.

Enter the Formula with the @ Symbol

Click on an adjacent cell in the same row where you want to calculate the data. Type your formula using the @ symbol, for example: =A2 * @interest_rates.

Evaluate the Result

Press Enter. The formula will automatically extract the single value from the 'interest_rates' array that corresponds to that specific row.

Using the @ Symbol for Implicit Intersection in Named Ranges

Modern Formula Syntax: The @ symbol seamlessly replaces older implicit intersection methods, making your formulas significantly easier to read and troubleshoot.

Solution 2

Applying Structured References in Tables

Convert your dataset into a Table to fully utilize the @ structured reference syntax automatically.

Master Structured References with WPS Spreadsheet

WPS Spreadsheet fully supports modern formula syntaxes, including named ranges, dynamic arrays, and structured references in tables. You can easily manage complex data calculations with an intuitive interface and high format compatibility.

1. Download and Install: Download and open WPS Office, then launch a new WPS Spreadsheet document.
2. Insert a Table: Highlight your dataset and press Ctrl+T to quickly format it as a smart Table.
3. Use Structured References: Type your formula using the =[@ColumnName] syntax to effortlessly calculate row-by-row data without complex cell coordinates.

Seamlessly handles the @ structured reference syntax for tables and named ranges.100% compatible with Microsoft Excel formulas, functions, and structured data formats.Free to use with a lightweight, fast, and user-friendly interface.Advanced Name Manager for easily organizing and tracking all your named ranges.

microsoft office alternative - wps office

Frequently Asked Questions

What does the @ symbol mean in a spreadsheet formula?

The @ symbol denotes implicit intersection. It forces a formula to return a single value—specifically the one on the exact same row or column as the formula—from a larger array, table column, or named range.

Why does my spreadsheet automatically add the @ symbol to my older formulas?

When opening older workbooks in newer versions of software that support dynamic arrays, the @ symbol is automatically inserted into formulas that previously relied on silent implicit intersection. This ensures your legacy formulas calculate identically without spilling over.

Can I use the @ symbol without formatting my data as a table?

Yes. While originally introduced for structured references in Tables, the @ symbol is now universally used as an implicit intersection operator. This means it works perfectly with standard named ranges and custom arrays outside of formatted tables.

Excel & Data Problems