Summary
- Excel spilled range operator (#) allows formulas to automatically adjust to changes in the spilled range size.
- You need to be using Excel for Microsoft 365 on Windows or Mac to make use of this handy tool.
- Well-known functions like UNIQUE, COUNTIF, and SORTBY can be used with the spilled range operator to generate dynamic, sortable lists.
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.
Let’s imagine you run an animal sanctuary, and your spreadsheet contains a formatted Excel table called 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.
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 are not formatted 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 headers Animal and Count, respectively.
Now, in cell D2, type:
=UNIQUE(Animals_Admitted[Animal])
where UNIQUE is the Excel function that lists unique items in a range, Animals_Admitted is the name of the table where the original data sits, and [Animal] is the name assigned to the third column of that table.
Related
How to List and Sort Unique Values and Text in Microsoft Excel
Create a list of unique names, dates, or other data in your spreadsheet with a simple function.
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.
The result of the UNIQUE function is sorted according to the order in which each item first appeared in the original data.
Related
Now, it’s time to make Excel count how many of each animal there are in your original table, and this is where using the hash sign makes life a lot easier.
To do this, you’ll need to use the COUNTIF function. However, because you want Excel to count all the 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:
=COUNTIF(Animals_Admitted[Animal],D2#)
where COUNTIF is the function that counts the number of occurrences, Animals_Admitted[Animal] is the range containing each animal in your original table, and D2# 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 as structured references) and, where applicable, add the hash sign.
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.
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.
After typing the above headers into cells F1 and G1, in cell F2 type:
=SORTBY(D2#:E2#,E2#,-1)
where SORTBY is 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 -1 tells Excel to sort the data in descending order.
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 can rest assured, safe in the knowledge that your lists will expand and contract dynamically according to the data in your original table.
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
=COUNTA(D2#)
where COUNTA is 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.
Related
How to Count Cells in Microsoft Excel
For counting cells with numbers, text, or nothing at all, here are the COUNT functions you need.
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:
=Sheet2!A1#
Second, you can reference a spilled range in another workbook. Although Microsoft says that 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 of Excel’s formulas and functions, or you’ve applied for a job that requires Excel expertise, you can confidently say you know what the hash sign does in Microsoft Excel formulas!
