Well-known functions like UNIQUE, COUNTIF, and SORTBY can be used with the spilled range operator to generate dynamic, sortable lists. Using a hash symbol (#)—also known as a spilled range ...

TL;DR Key Takeaways : Excel tables handle structured references inconsistently, behaving as absolute when copied and as relative when dragged, which can lead to formula errors.

This newish function is available in Microsoft 365 and Excel 2021, and Excel for the web. For your convenience, you can download the demonstration .xlsx file .

The original function referenced H8, but after converting the data range to a Table, the function references the last row in the Table, row 9–you didn’t have to update the functions in column E!

The new `TRIMRANGE` function in Excel 365 removes leading and trailing blank cells from ranges or arrays, improving formula reliability and automating data adjustments for dynamic datasets.