Shorten references with the hashtag symbol in Microsoft Excel

Sankt-Petersburg, Russia, June 2, 2018: Microsoft Excel application icon on Apple iPhone X screen close-up.  Microsoft Office Excel app icon.  Microsoft office on mobile phone.  Social media
Image: Aleksei/Adobe Stock

If you use Microsoft Excel in Microsoft 365, you may have noticed the hashtag symbol (#) or hash symbol in references. It’s a relatively new feature that is a bit short 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 explain how to use the hashtag in Excel references.

I’m using Microsoft 365 desktop on a 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 .xlsx demonstration file.

TO SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)

What is the # in Excel?

The hashtag or hash in a reference refers to an Excel leak range. An overflow range is the array returned by a dynamic array function. You can see when you’re working with an overflow range by the blue border around the resulting values.

Moreover, there is only one function and that is in the top left corner of the range, as shown in Image 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 dims the function.

Image A

Image: Susan Harkins/TechRepublic. Excel distinguishes the spill range with a blue border.

When you change the function, you only need to change one function, and the values ​​in the overflow range are automatically updated. For more information about Excel’s overflow range, read How to use the overflow range in Excel.

What # cannot do in Excel

Before we look at some examples of using #, let’s take a look at what you can’t do. The original data is formatted as a table object, so you might think you can refer to those columns with #. 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 is not part of an overflow range. If you enter =H3#, as shown in Figure Cthe # symbol refers to the entire waste range for the UNIQUE() function.

Figure B

Image: Susan Harkins/TechRepublic. You cannot directly reference a table with #.

Figure C

Image: Susan Harkins/TechRepublic. The # symbol returns the overflow range without referencing the column by name.

All you need to know is the first cell in the overflow range to return the entire overflow range. It’s simple and fast! 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 values ​​in the overflow range. In reality, it never gets harder to use, but knowing when to use it will make a huge difference in your credentials.

Now let’s return to the function shown in Image A=UNIQUE(Table Sales[Personnel]† In terms of structured reference, that reference is short. But the reference =H3# is much shorter. However, it is unlikely that you would create a leak range to duplicate it.

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

Figure D

Image: Susan Harkins/TechRepublic. Sort the results of your list.

Now let’s look at a practical example. In particular, let’s populate a data validation list with #:

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

Figure E

Image: Susan Harkins/TechRepublic. Refer to the sorted spill range in column K.

Figure F

Image: Susan Harkins/TechRepublic. Excel populates the list with the entire waste range.

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

Wondering what happens when 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 are updated. 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 sorted.

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

Figure G

Image: Susan Harkins/TechRepublic. The data validation control is dynamic and is updated when you change the original data in the table object.

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

Knowing what # means in a reference will help you maintain your workbook and troubleshoot if something goes wrong. If you apply the #shorthand a few times, you will find yourself using it often!