The purpose of the HASH (#) symbol is to refer to a Spill Range.
If you are not familiar with Spill Ranges, check out my video in the #SPILL! error message.
We use the # to instruct a formula’s cell reference to obtain all the entries produced by the Spill Range starting at a defined cell address.
Let’s look at a simple example. Suppose we have a list of apps in cells A1:A19 and we want to derive a unique list of apps. We write the following Dynamic Array formula using the UNIQUE function to generate the unique list starting in cell C1.
=UNIQUE(A1:A19)
The results spill in the C1:C11 range of cells.
Now we want to sort the list that starts in cell C1. We place the following formula in cell E1…
=SORT(C1)This produces a list consisting of one app.
This seems reasonable. After all, we only gave it one cell to sort.
But we want to sort ALL the apps returned by the UNIQUE function. We can modify the SORT formula to include ALL apps by adding a HASH (#) symbol after the C1 cell reference.
=SORT(C1#)The results are what we desired.
The # at the end of the cell reference tells Excel to include ALL results from the Spill Range.
Where We CAN’T Use the Hash (#) Symbol
It’s important to understand the limitations of #.
If we have a table of data that starts in cell A1, we can’t write a formula that references cell A2 and simply add a # to the end of the reference to return the entire table.
The reason this fails is that the data starting in cell A2 is not a Spilled Range; it’s just typed data.
The HASH (#) reference ONLY works with Spilled Ranges.
Recognizing a Spilled Range
Spilled Ranges are created by using formulas that spill; ex: UNIQUE, SORT, and SEQUENCE among others.
NOTE: If you are curious to learn about these and other powerful Dynamic Array functions, check out my “Excel 365 Functions for 2021” post. I also have a complete course that explains all of the features and operations of Dynamic Array functions called “Master Excel Functions in Office 365: Excel Dynamic Arrays”.
Spilled Range results are identified by the thin, blue border that surrounds cells when you select any cell in the Spill Range.
Another clue that data is the result of a Spill Range is that when you select a cell in the spilled area (not the cell holding the formula), the formula in the Formula Bar will have a light gray font color.
The formula can ONLY be updated or deleted from the original cell it was created (i.e., upper-left of the Spill Range.)
Breaking a Spill Range
If you type data into a cell that contains part of the results of a Spill Range, the Spill Range will result in a #SPILL! error.
Another Example of Spill Ranges
Suppose we want to generate a unique list of Division/Region combinations from the following table.
We write the following formula in cell G2:
=UNIQUE(A2:B17)The results are as follows.
Referencing a Spill Range
Now that we know how to create Spill Ranges, how do we reference a Spill Range in a dependent formula?
If we wish to reference the entire results of the previous UNIQUE example, we can test the # reference feature by typing the following in an adjacent cell:
=G2#This displays the entire set of results from the UNIQUE function in cell G2.
This example is a bit contrived. It’s unlikely you would spill the results of a formula just to spill them again in another location. What this demonstrates is that when the # is used in a formula, the reference is not limited to the single declared cell, rather all the cells occupying the Spill Range.
Automatic Hash References
When you are referencing a Spill Range in a formula, you can select the upper-left corner of the Spill Range (the cell that holds the formula), or you can highlight the entire Spill Range.
If a Spill Range were to occupy cells A1:D10, you could either type A1# or you can highlight the A1:D10 range and Excel will automatically replace the traditional A1:D10 range reference with an A1# Spill Range reference.
Advantage of Spill References
The advantage to using the # in our references is that when the sheet recalculates, if the dimensions of the Spill Range changes, the reference to that Spill Range changes dimensions as well.
Using Excel Tables with Spill Ranges
Notice in the below formula, I’m referencing a static cell range.
=UNIQUE(A2:A17)If data is added beyond row 17, the UNIQUE formula will not incorporate the additional rows of data.
A solution to this issue of tables that change in dimensions is to “upgrade” the table to a proper Excel Table.
If we select a cell in the data and press CTRL-T, we can imbue the “plain table” with all the features and benefits of a proper Excel Table.
If we update the UNIQUE formula to reference the Division and Region columns, the height of the table will be dynamically calculated.
=UNIQUE(Table[ [Division]:[Region] ] )
When new records are added to the table, the UNIQUE function (and the subsequent =G2# Spill Range reference) will adjust to the new table dimensions.
A Practical Example of Spill Ranges
As the above example was to demonstrate the syntax and behavior of Spill Range references, let’s look at a practical example of this ability.
We have a table that occupies cells A1 through D20 and consists of the following data:
NOTE: To keep the syntax to a minimum, we will not “upgrade” this table to a proper Excel Table. In the real world, converting this to a proper Excel Table would be considered a Best Practice.
We have provided the user with a drop-down list (via Data Validation) that allows them to choose between “Division”, “Region”, and “App.
When they select from the drop-down list, we use that choice to select one of the table’s columns and run a UNIQUE operation on that column’s data.
If you are interested in the formula used to build the list of unique values based on the drop-down selection, here is the formula.
=SORT(UNIQUE(INDEX(A2:C20, , XMATCH(G1, A1:C1) ) ) )This formula uses the SORT, UNIQUE, INDEX, and XMATCH functions to generate a unique, sorted list.
XMATCH is a new function that is intended to replace the legacy MATCH function.
Create a Spill Range Reference with #
The practicality of # can be seen when we generate a list of row numbers for our list of user-selected items.
=SEQUENCE(COUNTA(G2#) )
When we change the drop-down selection, the G2# spill reference counts the number of returned items and creates a list of numbers of the same length.
Using Spill Ranges in Excel Tables
Spilled array formulas are not supported in Excel Tables.
Spilled formulas should only exist in a single cell. Excel Tables will repeat the formula to every cell in the table’s column. This creates catastrophic interference between every cell in the column.
If you need to use the formula as a spilled array formula, you will need to revert the Excel table to a “plain table” using the Table Design (tab) -> Tools (group) -> Convert to Range option.
Referencing a Portion of a Spilled Range
If you need to reference a Spilled Range but you only want to return part of the Spilled Range, you can use the INDEX function to solve this task.
Returning All Items From a Specific Column
If we were to reference a Spill Range that starts in cell J2, and we only want the 2nd column of items, we can write the following formula.
=INDEX(J2#, 2)
Returning All Items From a Specific Cell
If we were to reference a Spill Range that starts in cell J2, and we only want the item located on the 4th row and the 2nd column, we can write the following formula.
=INDEX(J2#, 4, 2)
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
