What are hashtags in Excel formulas?
Unlocking Excel's Power: Demystifying Hashtags in Formulas
If you are using the latest version of Excel, I am sure you would have encountered the # symbol in some formulas.
And if you are still unsure about what it is and how to use it in your formulas then this blog is exactly what you are looking for.
As mentioned in my previous post, Excel has a bunch of special formulas that instead of throwing one single answer will throw multiple cells as the final output.
Now, if you were to refer to that dynamic array anywhere else in the workbook then you can refer to them using the # symbol and Excel will refer to the entire set of cells automatically.
For example, I have a dynamic array formula in cell B2 in the screenshot below.
Now let’s say I want to calculate the sum of all these cells. One way would be to write the formula as below.
=SUM(B2:C11)This works fine and gives you the right result. However, there is a better way of writing the same formula which is :
=SUM(B2#)The formula is not only crisp but also has the ability to refer to the entire set of cells without explicitly mentioning the range.
In other words, if tomorrow this range were to expand or trim down then the formula with # will be able to accommodate accordingly and calculate the sum of only relevant cells and ignoring any other cells.
This allows the users to write the formula once and forget about it in case the number of cells changes in the dynamic array.
For example, let’s say for some reason the dynamic array in cell B2 now produces the following set of cells.
Then using the traditional formula would not give us the correct result.
However, using the # formula would allow Excel to refer to the entire set of cells (including the new cells) and produce the correct result.
I strongly would advocate all Excel developers to use dynamic arrays as much as possible and use # with them as they make life easier and make the formulas scalable.
Do remember that # only works in formulas while referring to only dynamic arrays.
If you have any other questions related to hashtags in Excel formulas, comment below; I would be happy to help.