When Should You Use the Hash Sign in Excel Formulas?

today we're going to talk about the hash reference in excel a common question i get is what is that hash sign i keep using in my videos when i'm referencing cells well if you have microsoft 365 you already have that hash sign and you should be using it because it's going to make your life a lot simpler let me show you how here i have some sample data which is not formatted as an excel table first of all let me show you where you can't use the hash sign so you can just go type in an equals reference a3 put in hash and expect your results to spill and pick up the range from a3 to d18 that's not gonna happen and the reason it doesn't happen is that this is not a spilled range you can only use the hash sign when you reference arrange that spilling now how do you recognize this build range well let me show you first of all let's create a spilled range how do you do that well spilled ranges are generally created by using formulas that spill so for example if you use the sort function or the unique function these are functions that spill if you're curious to find out more about these functions i have a separate video about this and i'm going to add the link to it to the description of this video i also have a complete course that covers all the new functions in excel 365. i'll add the link to that as well the only thing we need for the unique is our range so let's say i want to get the unique combination for division and region i'm going to reference this range close bracket press enter and this now is a spilled range i wrote the formula once i pressed enter and the results spilled to the next cells now here's how you can recognize