Referencing can grow to be tough, particularly should you’re utilizing structured referencing. As an alternative, reference a spill vary so you should utilize the hashtag shortcut.
Should you’re utilizing Microsoft Excel in Microsoft 365you may need seen the hashtag image (#), or the pound signal, in references. It’s a comparatively new function that’s a little bit of shorthand in some conditions. It’s faster and undoubtedly makes referencing simpler, however until you understand how to make use of it, it’s complicated. On this tutorial, I’ll clarify how one can use the hashtag in Excel referencing.
I’m utilizing Microsoft 365 desktop on a Home windows 10 64-bit system. This functionality is accessible solely in Microsoft 365 and Excel for the online.
SEE: Home windows, Linux, and Mac instructions everybody must know (free PDF) (TechRepublic)
What’s the # in Excel?
The hashtag or pound check in a reference refers to an Excel spill vary. A spill vary is the returned array by a dynamic array perform. You possibly can inform if you’re working with a spill vary by the blue border across the ensuing values.
As well as, there’s just one perform and that’s within the top-left nook of the vary, as proven in Determine A. On this case, the UNIQUE() perform in H3 returns a spill vary by means of H8. If you choose a cell within the spill vary, as an alternative of H3, Excel dims the perform.
When modifying the perform, you want modify solely the one perform, and the values within the spill vary will replace robotically. To study extra about Excel’s spill vary, you possibly can learn use the spill vary in Excel.
What # can’t do in Excel
Earlier than we take a look at some examples of utilizing #, let’s take a look at what you possibly can’t do. The unique information is formatted as a Desk object, so that you would possibly assume you possibly can reference these columns utilizing #. Let’s strive that and see what occurs. Enter =C3# to return the gadgets within the Worth column. As you possibly can see in Determine Bit doesn’t work as a result of column C isn’t a part of a spill vary. Should you enter =H3#, as proven in Determine Cthe # image references the complete spill vary for the UNIQUE() perform.
All you want to know is the primary cell within the spill vary to return the complete spill vary. It’s easy and fast! Now, let’s use it with some actual examples.
use # in Excel
You simply noticed a easy instance of # in Determine C. That easy expression returns all of the values within the spill vary. In reality, it by no means will get any tougher to make use of, however figuring out when to make use of it should make a giant distinction in your references.
Now, let’s return to the perform proven in Determine A=UNIQUE(TableSales[Personnel]). By way of structured referencing, that reference is brief. However the reference =H3# is way shorter. It’s unlikely that you just’d create a spill vary simply to duplicate it although.
Enter =SORT(J3#) to type the outcomes of the checklist in column J, as proven in Determine D. You don’t have to spotlight the complete spill vary, merely choose J3 and enter #), to finish the expression. To get the identical sorted distinctive checklist referencing the Desk, you’d want the longer structured reference, =SORT(UNIQUE(TableSales[Personnel])).
Now, let’s take a look at a sensible instance. Particularly, let’s populate a knowledge validation lists utilizing #:
- Choose L3.
- Click on the Knowledge tab after which click on Knowledge Validation within the Knowledge Instruments group.
- From the dropdown, choose Knowledge Validation.
- Within the ensuing dialog, select Listing from the Enable dropdown.
- Within the Supply management, enter =K3#, as proven in Determine E.
- Click on OK.
As you possibly can see in Determine FExcel populates the checklist with a sorted distinctive checklist of personnel. You are able to do the identical factor with a for much longer structured reference, however the hashtag makes it a lot simpler.
Are you questioning what occurs should you add a brand new particular person to the unique information? Let’s discover out. Choose F13 and press tab to insert a brand new file into TableSales (the Desk object). Tab over to D14 and enter Alice. Instantly, the lists in columns J and Okay replace. Excel provides Alice to the tip of the checklist in column J as a result of it isn’t a sorted checklist. Excel provides Alice to the highest of the checklist in column Okay as a result of it’s sorted.
Now, click on the dropdown to see how the info validation management managed the brand new merchandise. As you possibly can see in Determine Gthe checklist not solely provides the merchandise, but in addition kinds it.
Once more, you may do the identical factor with a protracted structured reference, however not solely is that this simpler, it’s instance of how helpful # is.
Realizing what # in a reference means will provide help to preserve your workbook and troubleshoot when one thing goes incorrect. When you apply the # shorthand a number of instances, you’ll end up utilizing it incessantly!