How to shorten a reference with the hashtag symbol # in Microsoft Excel

Referencing can get tricky, especially if you’re using a structured reference. Instead, reference the overflow range so you can use the hashtag shortcut.

If you are using Microsoft Excel In Microsoft 365, you may have noticed the hashtag symbol # (#), or the pound sign, in the references. It’s a relatively new feature that’s a bit off-putting in some situations. It’s faster and certainly makes referencing easier, but unless you know how to use it, it’s confusing. In this tutorial, I will explain how to use hashtags in Excel references.

I am using Microsoft 365 desktop on Windows 10 64-bit system. This capability is only available in Microsoft 365 and Excel for the web. For your convenience, you can download the demonstration .xlsx file.

UNDERSTAND: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

# What is in Excel?

The hashtag or pound sign in the reference refers to an overflow range in Excel. The overflow range is the array returned by a dynamic array function. You can tell when you are working with overflow ranges by the blue border around the resulting values.

Also, there is only one function and that is in the top left corner of the range, as shown in Picture A. In this case, the UNIQUE() function in H3 returns an overflow range through H8. If you select a cell in the overflow range, instead of H3, Excel will blur the function.

Picture A

When modifying a function, you simply modify a function and the values ​​in the overflow range will update automatically. To learn more about Excel overflow ranges, you can read How to use overflow range in Excel.

What # can’t do in Excel

Before looking at some examples of using #, let’s see what you can’t do. The original data is formatted as a Table object, so you might think you can reference those columns using #. Try that and see what happens. Type =C3# to return the entries in the Value column. As you can see in Figure BUT, it doesn’t work because column C is not part of the overflow range. If you enter =H3#, as shown in SIZEthe # symbol refers to the entire overflow range for the UNIQUE() function.

Figure BUT

SIZE

All you need to know is the first cell in the overflow range to return the full overflow range. It’s simple and fast! Now let’s use it with some real life examples.

How to use # in Excel

You just saw a simple example of #in SIZE. That simple expression returns all values ​​in the overflow range. In fact, it never gets any harder to use, but knowing when to use it will make a big difference in your references.

Now let’s go back to the function shown in Picture A= UNIQUE (TableSales[Personnel]). In terms of structured references, that reference is concise. But reference=H3# is much shorter. Even so, it’s unlikely that you would create an overflow just to duplicate it.

Enter = SORT (J3 #) to sort the results of the list in column J, as shown in Visualization. You don’t need to highlight the entire overflow range, just select J3 and enter #), to complete the expression. To get the same single sorted list that refer to the Table you need a longer structured reference, =SORT(UNIQUE(TableSales[Personnel])).

Visualization

Now, let’s look at a practical example. Specifically, populate the data validation list using #:

1. Select L3.
2. Click the Data tab and then click Data Validation in the Data Tools group.
3. From the drop-down menu, select Data validation.
4. In the dialog box that appears, select List from the Allow drop-down menu.
5. In Source control, enter =K3#, as shown in Figure E.
6. Click OK.

Figure E

Figure F

As you can see in Figure F, Excel populates the list with a single sorted list of personnel. You can do the same thing with a much longer structured reference, but the hashtag makes it much easier.

Are you wondering what happens if you add a new person to the original data? Let’s find out together. Select F13 and press tab to insert a new record into TableSales (Table object). Switch to D14 and enter Alice. Immediately, the list in columns J and K updates. Excel adds Alice to the end of the list in column J because it is not a sorted list. Excel adds Alice to the top of the list in column K because it is already sorted.

Now click on the drop-down menu to see how to control the new item management data validation. As you can see in WOOD Figurethe list not only adds the item, but also sorts it.

WOOD Figure

Again, you can do the same thing with a long structured reference, but not only is this easier, it’s a good example of the value of #.

Knowing what the # in the reference means will help you maintain your workbook and troubleshoot when something goes wrong. After applying the shorthand # a few times, you’ll find yourself using it often!