Spreadsheet Super Hero

Despite a 3 year retirement from Excel, I'm better at Excel now more than ever!

I really haven't worked in Excel since I started coding in 2019. I returned to Excel (please pray for me) this week to clean up some legacy spreadsheets.

I used to have to write complicated formulas to generate summary tables. But now, there are two new Excel features which largely automate that work. I wanted to share, since I suspect many of my friends do not know about these features.

1) Dynamic Array Formulas - I specifically use Unique to get a list of unique identifiers in a dataset. Once I have a list of identifiers, I can write all the calculations and lookups I need. This is very handy to build "pro pivot tables." On the left you can see an example, where I get only the unique values from a list. This is the article I read to learn Dynamic Arrays: https://exceljet.net/dynamic-array-formulas-in-excel

2) Using "#" - I don't even know what this is called in Excel. But it is a newly introduced feature critical for my new super power! Once I have a dynamic array formula in A1 such as =unique(LIST), the length of column A will automatically expand and contract based on the number of unique values in the list. To make all my fancy lookups and calculations expand and contract with the array automatically, I simply need to use # after cell references.

Using # and a dynamic array turns two formulas into an entire table.

Once I press enter, you can see creates a blue box the length of the dynamic array.

This completely eliminates having to drag formulas down when datasets expand.

Just remember, don't tell your boss about this. You've just earned yourself some down time with this new super power!