Summary

Using a hash symbol (#)—also known as a spilled range operator—in an Excel formula is a way to tell the program to consider all results in a spilled range. As a result, even if the spilled range grows or shrinks, the formula containing the # will automatically reflect this change.

You can only take advantage of Excel’s spilled range operator if you’re using Excel for Microsoft 365 on Windows or Mac.

An Excel table with dates in column A, names in column B, and animals in column C.

Let’s imagine you run an animal sanctuary, and your spreadsheet contains aformatted Excel tablecalled Animals_Admitted, which shows the animals currently under your care.

So that you can make the best use of the space you have at the sanctuary, you need to know how many of each type of animal you have, and how many different types of animals you have overall.

The column heading Animal is typed into cell D1, and the column heading Count is typed into cell E1.

Because the functions you’re about to use produce spilled arrays, and spilled arrays don’t work in formatted Excel tables, you need to type the formulas in areas of your spreadsheet that arenotformatted as an Excel table.

To see how many of each animal are currently in your sanctuary, in cells D1 and E1 (the cells above where your first spilled arrays will go), type the column headersAnimalandCount, respectively.

A spilled array in Excel that list unique animals in the original data.

Now, in cell D2, type:

whereUNIQUEis the Excel function that lists unique items in a range,Animals_Admittedis the name of the table where the original data sits, and[Animal]is the name assigned to the third column of that table.

When you press Enter, you get a spilled array that lists each unique item in the Animal column. You know this is a spilled array because a blue line surrounds the result whenever you select one of the affected cells.

A spilled array in Excel that uses the COUNTIF function to count the number of times each animal appears in the original table.

The result of the UNIQUE function is sorted according to the order in which each item first appeared in the original data.

Now, it’s time to make Excel count how many of each animal there are in your original table, and this is where using thehashsign makes life a lot easier.

An animal sanctuary list in Excel, with a hedgehog named Sonic added to the bottom of the list.

To do this, you’ll need to usethe COUNTIF function. However, because you want Excel to countallthe animals returned by the UNIQUE function in column D—even if certain animals are added to or removed from this list later on—you need to add a hash sign after the criteria reference.

So, in cell E2, type:

whereCOUNTIFis the function that counts the number of occurrences,Animals_Admitted[Animal]is the range containing each animal in your original table, andD2#tells Excel that the criteria for the COUNTIF function are a spilled array starting in cell D2 and, thus, may change size.

Instead of typing the formula manually, if you use your mouse to select the cells for each argument, the formula will automatically adopt the column names (also known asstructured references) and, where applicable, add the hash sign.

Spilled arrays in Excel that have picked up a new entry to the original data.

Now, let’s imagine that a hedgehog is brought into your sanctuary, and this hedgehog will be the only one currently under your care.

To add an extra row to a formatted table, click and drag the handle in the bottom-right corner downwards.

An Excel sheet with two empty columns headed Animal and Sorted Count.

Because you referenced a formatted table heading in column D and used the spilled range operator in your COUNTIF formula in column E, the hedgehog is automatically added to the list in column D, and the hedgehog count in column E correctly shows as “1.”

Now, you want to create a list that sorts the animals by count.

A dynamic spilled array based on the use of the SORTBY funciton in Excel.

After typing the above headers into cells F1 and G1, in cell F2 type:

whereSORTBYis the Excel function that sorts a range according to values in another range or array,D2#:E2#tells Excel that the array—which occupies columns D and E—contains two columns of spilled arrays starting at cells D2 and E2,E2#is the spilled array to sort on, and-1tells Excel to sort the data in descending order.

The COUNTA function being used with a spilled range operator to return the number of unique animals in the sanctuary.

The result of the SORTBY function is first sorted according to the order you specified in the SORTBY formula. However, if any variables have the same values after this initial sort, they’ll then be sorted according to the order in which each item first appeared in the original data.

Because you used those hash signs in the formula, you may rest assured, safe in the knowledge that your lists will expand and contract dynamically according to the data in your original table.

An Excel sheet containinig various dynamic array lists and other functions that show the effective use of the spilled range operator.

Finally, you also need a basic count of the total number of animal types in your sanctuary.

To do this, after typing a relevant header in cell H1, in H2, type

whereCOUNTAis the Excel formula that counts the number of cells in a range that are not empty, and D2# tells Excel that the range is a spilled array. In other words, we’re telling Excel to count the number of animals the UNIQUE function returned in the spilled array starting in cell D2, and that this number may change.

As a final test, let’s assume London the turtle is released. After removing London from the original data, since he was the only turtle in the sanctuary, cell H2 correctly tells you that there are now only 18 unique animals under your care, and “Turtle” no longer appears in the other lists you created.

Spilled Range Operators (#): Final Pointers

Before you get started, there are a few extra points to note.

First, as well as referencing a spilled range in the same worksheet, you can also reference a spilled range in another sheet. For example, to reference a spilled array starting in cell A2 on sheet 2, you could type:

Second, you can reference a spilled range in another workbook. Although Microsoftsaysthat the referenced workbook must be open for this not to return the #REF! error, when I tested it with a closed workbook, the reference continued to work. Just make sure you save both workbooks and have them both connected to the same OneDrive account to ensure you don’t lose any data.

Finally, to avoid your formulas returning the #SPILL! error, make sure all cells in which the array is due to spill are clear of other data and unmerged.

Now, whether you’re aiming to simply expand your knowledge ofExcel’s formulas and functions, or you’ve applied for ajob that requires Excel expertise, you can confidently say you know what the hash sign does in Microsoft Excel formulas!