You are currently viewing Excel Hash Sign (#) in Excel Formulas and Functions

Excel Hash Sign (#) in Excel Formulas and Functions

In this article, we will learn about the Hash sign (#) you often see people use in some Excel functions and formulas.

Requirements and availability

You would need to have Microsoft Excel installed on your computer and have a Microsoft 365 license

What is the hash sign?

The hash sign (#) is actually called octothorpe; however, most know it as the pound or hash sign. It is used in your formula as a spill operator in dynamic array functions with spilled array behaviour.

When can I use the hash sign?

You should use it when referencing a spill range. Thus, the hash sign is used to call your spill range results. You should also know that the hash sign automatically updates when your spill range results change. This is very useful when feeding the spill array to a formula.

How do you create a spill range? 

Spill ranges are created when you use one of the dynamic excel formulas. The formula used to create them is written in one cell, and the results get spilled into neighbouring cells. Some examples of the Dynamic excel function are the UNIQUE, SORT, and FILTER functions.

To learn more about dynamic excel formulas. You should check out our session about the new dynamic Excel array functions with Salif Sawadogo, or this customized video playlist.

How can I use the hash sign?

1. When referencing a spill range: Using Excel UNIQUE formula

Let’s take this movie dataset. It is a modification of this GitHub gist.

We will use the UNIQUE function in excel to find the unique genre in this data. To do this:

  1. We would write out =UNIQUE( on a cell
  2. Then we click on the first value in our genre column down to the last value. 
  3. Close the bracket and press enter on your keyboard. This return all unique genre.
  4. To use the hash sign, we will write “=” in another cell, followed by the cell reference of the first value in our spill array.
  5. As soon as we select the last value of this spill array, Excel will automatically change the reference syntax to the cell reference and the # sign. 
  6. Press enter. This return the spill reference.

Try changing one of the comedy values in your genre column to horror, you will see everything was updated. 

To learn more about the UNIQUE function, check out this Video.

2. When referencing a spill range: Using Excel SORT formula

Let’s sort the Worldwide Gross Column. 

To do that, I will follow the same steps I described for Excel UNIQUE formula. However, let’s try something different.  

  1. we would write out =SORT( on a cell
  2. Then we would give it the required argument.
  3. This returns our results.
  4. We would go to another cell so we can reference this spill array.
  5. To do that, write “=” followed by the cell reference of the first value in our spill array.
  6. Then write #.
  7. Press enter. This return the spill reference.

When using your spilled range in a formula

We can try to count the number of distinct values of our Genre column. There are multiple ways to do this, but let’s use the hash # sign.

  1. In another cell, write out the COUNTA(
  2. Then reference the spill array from the UNIQUE formula in it.

And that’s it!

More of a visual person?

YouTube player

To learn more about Power BI, you should sign up for our classes, keep up with the blog, and subscribe to our YouTube channel.

Leave a Reply