Select Page

A client recently sent me an Excel file that someone else had created. That someone else had left the company and the spreadsheet needed amending.

But there was an odd-looking formula in there. It had a hash sign (#) in it. My client had never seen a hash sign in a formula before and wanted me to explain what it meant.

So I thought…well if that person doesn’t know what a hash sign in a formula means, there’s a good chance others don’t either, and that’s what gave me the idea for this week’s YouTube video.

In order to understand the use of the hash sign in a formula, you need to understand Dynamic Arrays and Spilling, two concepts that were introduced into Excel in 2020.

In the video, using hands-on examples, I’ll take you through how Dynamic Arrays, Spilling, and the critical role of the Hash sign seamlessly work together to streamline your Excel workflows.

Note that this functionality is only available to Excel 365 and Excel 2021 users.

Formulas used in the demos:

=B2+1000

=B2:B7 + 1000

=SUM(C2#)

=DAYS(C2,B2)

=AVERAGE(D2:D24)

=DAYS(C2:C24,B2:B24)

=AVERAGE(D2#)

=CHOOSECOLS(FILTER(Transactions,Transactions`[Type]=`“CR”),1,2)

=SUM(CHOOSECOLS(E5#,2))