SINGLE function

The SINGLE function, returns a single value using logic known as implicit intersection. SINGLE may return a value, single cell range, or an error. 

When the supplied argument is a range, SINGLE will return the cell at the intersection of the row or column of the formula cell. Where there is no intersection, or more than 1 cell falls in the intersection, then SINGLE will return a #VALUE! error.

When the supplied argument is an array, SINGLE returns the first item (Row 1, Column 1).

In the examples below, SINGLE is supplied a range, and returns the values in D5 and C1 respectively. 

Vertical intersection

Horizontal intersection

SINGLE function example with =SINGLE(D1:D10)

SINGLE function example with =SINGLE(A1:E1)

Note: September 24, 2018: The SINGLE function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.







The value to evaluate using implicit intersection


  • The SINGLE function is primarily provided for backwards compatibility with implicit intersection formulas in earlier versions of Excel.

  • The latest version of Excel for Office 365 uses dynamic arrays. With this version, Excel no longer silently returns an implicit intersection of your formula. Instead, where it detects an implicit intersection, it will introduce the SINGLE function. This change was made to simplify formulas, and improve their readability.

  • Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

  • You can download a workbook with SINGLE function examples.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.