MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Use # "The Spiller" to Refer to All Array Results


September 23, 2020 - by Bill Jelen

Excel Use #

In this screenshot, the UNIQUE function is in cell F5.

=UNIQUE(D4:D6132) returns a list of genres such as Rock, Easy, Vocal, Pop, R&B, Country, Folk

You never really know how many results that formula will return.


To refer to "the entire array returned by the formula in F5", you would write F5#. There is no official name for this notation, so I am using an idiom coined by Excel MVP Ingeborg Hawighorst: The Spiller.

Title Photo: Saffu at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.