If you’ve ever seen a formula in Excel that ends with a # symbol and wondered what it means — you’re about to discover one of Excel’s most powerful dynamic formula features introduced in recent versions of Excel, especially in Office 365 and Excel 2021+.
Table of Contents
🧠 What is the # Hash Sign in Excel?
The # symbol is used to refer to the entire spill range of a dynamic array formula. When a formula spills results into multiple cells (like SEQUENCE, FILTER, SORT), adding # allows other formulas to reference the entire output, even if the size changes.
📘 Example 1: SEQUENCE with
=SEQUENCE(5)
This will return a vertical list of numbers from 1 to 5.Now in another cell:
=A1#
If your SEQUENCE is in cell A1, typing A1#
will automatically reference the entire spilled range of that formula, even if the SEQUENCE is changed to 10 later.📘 Example 2: FILTER with
Suppose you filter a list:
=FILTER(A2:A10, B2:B10="India")
This formula returns only the names where the country is India.If you want to refer to the entire result somewhere else:
=D2#
It ensures your references always update if the number of matching items changes.🛠 Why Use # in Excel?
- ✅ Automatically adjusts when the formula spill range changes
- ✅ Reduces the need for manually updating ranges
- ✅ Cleaner, dynamic references for dashboards and linked formulas
🧪 Bonus Example: SUM of Spill Range
=SUM(D2#)
Instead of manually typing a range like D2:D6, using D2# sums the entire dynamic output of D2.
