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.

Download the demo file: https://app.box.com/s/2uu6zo5hzb2kz1o64eqk2c1xfv6eo2c4

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