You Need to Know What the Hash Sign Does in Excel Formulas

Using a hash awesome (#)—also known arsenic a spilled scope operator—in an Excel look is simply a mode to archer the programme to see each results successful a spilled range. As a result, adjacent if the spilled scope grows oregon shrinks, the look containing the # volition automatically bespeak this change.

Let's ideate you tally an carnal sanctuary, and your spreadsheet contains a formatted Excel table called Animals_Admitted, which shows the animals presently nether your care.

So that you tin marque the champion usage of the abstraction you person astatine the sanctuary, you request to cognize however galore of each benignant of carnal you have, and however galore antithetic types of animals you person overall.

To spot however galore of each carnal are presently successful your sanctuary, successful cells D1 and E1 (the cells supra wherever your archetypal spilled arrays volition go), benignant the file headers Animal and Count, respectively.

Now, successful compartment D2, type:

=UNIQUE(Animals_Admitted[Animal])

where UNIQUE is the Excel relation that lists unsocial items successful a range, Animals_Admitted is the sanction of the array wherever the archetypal information sits, and [Animal] is the sanction assigned to the 3rd file of that table.

When you property Enter, you get a spilled array that lists each unsocial point successful the Animal column. You cognize this is simply a spilled array due to the fact that a bluish enactment surrounds the effect whenever you prime 1 of the affected cells.

Now, it's clip to marque Excel number however galore of each carnal determination are successful your archetypal table, and this is wherever utilizing the hash sign makes beingness a batch easier.

To bash this, you'll request to usage the COUNTIF function. However, due to the fact that you privation Excel to number all the animals returned by the UNIQUE relation successful file D—even if definite animals are added to oregon removed from this database aboriginal on—you request to adhd a hash motion aft the criteria reference.

So, successful compartment E2, type:

=COUNTIF(Animals_Admitted[Animal],D2#)

where COUNTIF is the relation that counts the fig of occurrences, Animals_Admitted[Animal] is the scope containing each carnal successful your archetypal table, and D2# tells Excel that the criteria for the COUNTIF relation are a spilled array starting successful compartment D2 and, thus, whitethorn alteration size.

Now, let's ideate that a hedgehog is brought into your sanctuary, and this hedgehog volition beryllium the lone 1 presently nether your care.

Because you referenced a formatted array heading successful file D and utilized the spilled scope relation successful your COUNTIF look successful file E, the hedgehog is automatically added to the database successful file D, and the hedgehog number successful file E correctly shows arsenic "1."

Now, you privation to make a database that sorts the animals by count.

After typing the supra headers into cells F1 and G1, successful compartment F2 type:

=SORTBY(D2#:E2#,E2#,-1)

where SORTBY is the Excel relation that sorts a scope according to values successful different scope oregon array, D2#:E2# tells Excel that the array—which occupies columns D and E—contains 2 columns of spilled arrays starting astatine cells D2 and E2, E2# is the spilled array to benignant on, and -1 tells Excel to benignant the information successful descending order.

Because you utilized those hash signs successful the formula, you tin remainder assured, harmless successful the cognition that your lists volition grow and declaration dynamically according to the information successful your archetypal table.

Finally, you besides request a basal number of the full fig of carnal types successful your sanctuary.

To bash this, aft typing a applicable header successful compartment H1, successful H2, type

=COUNTA(D2#)

where COUNTA is the Excel look that counts the fig of cells successful a scope that are not empty, and D2# tells Excel that the scope is simply a spilled array. In different words, we're telling Excel to number the fig of animals the UNIQUE relation returned successful the spilled array starting successful compartment D2, and that this fig whitethorn change.

As a last test, let's presume London the turtle is released. After removing London from the archetypal data, since helium was the lone turtle successful the sanctuary, compartment H2 correctly tells you that determination are present lone 18 unsocial animals nether your care, and "Turtle" nary longer appears successful the different lists you created.

Spilled Range Operators (#): Final Pointers

Before you get started, determination are a fewer other points to note.

First, arsenic good arsenic referencing a spilled scope successful the aforesaid worksheet, you tin besides notation a spilled scope successful different sheet. For example, to notation a spilled array starting successful compartment A2 connected expanse 2, you could type:

=Sheet2!A1#

Second, you tin notation a spilled scope successful different workbook. Although Microsoft says that the referenced workbook indispensable beryllium unfastened for this not to instrumentality the #REF! error, erstwhile I tested it with a closed workbook, the notation continued to work. Just marque definite you prevention some workbooks and person them some connected to the aforesaid OneDrive relationship to guarantee you don't suffer immoderate data.

Finally, to debar your formulas returning the #SPILL! error, marque definite each cells successful which the array is owed to spill are wide of different information and unmerged.

---

Now, whether you're aiming to simply grow your cognition of Excel's formulas and functions, oregon you've applied for a job that requires Excel expertise, you tin confidently accidental you cognize what the hash motion does successful Microsoft Excel formulas!