# When to Use the Hash Sign (#) in Excel Formulas

“What is that **HASH** symbol (#) you keep using in your videos when referencing cells?”

This is a question I frequently receive.

If you are a subscriber to **Microsoft 365** (*formerly known as Office 365*) you already have access to the

**HASH**symbol.

If you’re not using it in Excel, now is the day to start. The **HASH** symbol will make using Excel much easier.

Let’s discover what the **HASH** can do for us and witness it in action.

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.

# 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 **2 ^{nd}** 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 **4 ^{th} **row and the

**2**column, we can write the following formula.

^{nd}=INDEX(J2#, 4, 2)

## Practice Workbook

Feel free to Download the Workbook HERE.

Get the ULTIMATE Excel Power Query Course

## Use Power Query Like an EXPERT From the Start

Learn anytime that fits your schedule.

Download files. Practice. Apply.