Structure data using table references to ensure formulas always reference the correct range. Key dynamic formulas include Spill Reference Operators, BYROW, LAMBDA, and Array Boolean Logic.

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!

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.

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 ...

If the table does not have headers, be sure to uncheck the My Table Has Headers option. Click OK and Excel will format the data range as a table. Any chart you build on the table will be dynamic.