How to shorten references using the hashtag symbol in Microsoft Excel

Referencing can become difficult, especially if you’re using structured referencing. Instead, reference a spill range so you can use the hashtag shortcut.

If you’re using Microsoft Excel in Microsoft 365, you might have noticed the hashtag symbol (#), or the pound sign, in references. It’s a relatively new feature that’s a bit of shorthand in some situations. It’s quicker and definitely makes referencing easier, but unless you know how to use it, it’s confusing. In this tutorial, I’ll explain how to use the hashtag in Excel referencing.

I’m using Microsoft 365 desktop on a Windows 10 64-bit system. This capability is available only in Microsoft 365 and Excel for the web.

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

What is the # in Excel?

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

In addition, there’s only one function and that’s in the top-left corner of the range, as shown in Figure A. In this case, the UNIQUE() function in H3 returns a spill range through H8. If you select a cell in the spill range, instead of H3, Excel dims the function.

Figure A

When modifying the function, you need modify only the one function, and the values in the spill range will update automatically. To learn more about Excel’s spill range, you can read How to use the spill range in Excel.

What # can’t do in Excel

Before we look at some examples of using #, let’s look at what you can’t do. The original data is formatted as a Table object, so you might think you can reference those columns using #. Let’s try that and see what happens. Enter =C3# to return the items in the Value column. As you can see in Figure B, it doesn’t work because column C isn’t part of a spill range. If you enter =H3#, as shown in Figure C, the # symbol references the entire spill range for the UNIQUE() function.

Figure B

Figure C

All you need to know is the first cell in the spill range to return the entire spill range. It’s simple and quick! Now, let’s use it with some real examples.

How to use # in Excel

You just saw a simple example of # in Figure C. That simple expression returns all the values in the spill range. In truth, it never gets any more difficult to use, but knowing when to use it will make a big difference in your references.

Now, let’s return to the function shown in Figure A, =UNIQUE(TableSales[Personnel]). In terms of structured referencing, that reference is short. But the reference =H3# is much shorter. It’s unlikely that you’d create a spill range just to duplicate it though.

Enter =SORT(J3#) to sort the results of the list in column J, as shown in Figure D. You don’t have to highlight the entire spill range, simply select J3 and enter #), to complete the expression. To get the same sorted unique list referencing the Table, you’d need the longer structured reference, =SORT(UNIQUE(TableSales[Personnel])).

Figure D

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

1. Select L3.
2. Click the Data tab and then click Data Validation in the Data Tools group.
3. From the dropdown, select Data Validation.
4. In the resulting dialog, choose List from the Allow dropdown.
5. In the 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 sorted unique 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. Select F13 and press tab to insert a new record into TableSales (the Table object). Tab over to D14 and enter Alice. Immediately, the lists in columns J and K update. Excel adds Alice to the end of the list in column J because it isn’t a sorted list. Excel adds Alice to the top of the list in column K because it is sorted.

Now, click the dropdown to see how the data validation control managed the new item. As you can see in Figure G, the list not only adds the item, but also sorts it.

Figure G

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

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