Share via

Why cant I use hash operator?

Anonymous
2025-01-30T10:45:21+00:00

I recently discovered the Hash operation in Excel through a YouTube video, but when I tried it, it didn’t work for me. I tried it on Windows, but it didn’t work either.

Microsoft 365 and Office | Excel | Other | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

riny 20,870 Reputation points Volunteer Moderator
2025-02-01T07:21:47+00:00

Well, those videos are very basic explanations on how dynamic arrays (DA) work and how you can use the # operator. You may have overlooked a key element in it and that is that the data needs to be in a structured table to begin with. Then you can use any of the DA functions referencing the table/column name. These will automatically expand/shrink with the table.

Once such a DA function has spilled a result you can use the # operator to address the entire spilled array by referencing the top left cell of it in other formula.

Now, change data in the structured table. The spilled array will dynamically update as will the formulas that reference that spilled array.

Was this answer helpful?

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2025-02-01T04:51:46+00:00

    Then B48 doesn't contain a (spilled) dynamic array.

    Image

    I had a basic idea of how to do it, but I’m curious about the new ‘Hash’ operator. It seems to make things a lot easier when you want to add more data below. For instance, if we use ‘A1:A3’, we have to keep expanding the range every time we add more data, or we have to insert rows instead of just adding the data below. But what the Hash operator does is automatically expand what’s below when we add a new data.

    Try watching these videos for more information

    Was this answer helpful?

    0 comments
  3. riny 20,870 Reputation points Volunteer Moderator
    2025-01-30T11:01:30+00:00

    Then B48 doesn't contain a (spilled) dynamic array.

    Was this answer helpful?

    0 comments