This one Excel symbol will change how you write formulas

If you've ever had an Excel formula break because you added a few rows of data, you know how frustrating it can be to go back and manually update every cell reference. The # symbol (also called the spill range operator) solves this problem by automatically adjusting your formulas as your data grows or shrinks.

Instead of typing static ranges like A2:A10, you can reference an entire dynamic range with something like A2#. Excel then tracks any data that spills from that starting cell, regardless of the number of rows it includes. This makes your Excel formulas more flexible and reliable, and a lot less likely to break when your spreadsheet changes.

The # symbol automatically tracks your array ranges

How the spill range operator works

The # symbol references every cell in a dynamic array, starting from the cell you specify. If you type =G2#, Excel includes G2 and every cell below it that contains spilled data—whether that's five rows or 500.

It works well with Excel functions, such as SORT, FILTER, and UNIQUE, to end the pain of resizing tables. When you reference the spilled range with a hash (#), Excel tracks the entire output without requiring you to count rows.

But there's a catch. The # symbol only works when referencing a spilled array, not static data. If A2 just contains the word "Product," you can't use A2#. You need to make your data spill first.

The best way to do this is by converting your data to an Excel table. Select your data range (A1:E11), press Ctrl + T, and click OK. Excel names it something like "Table1"; in my case, it's Table3, and it automatically expands when new rows are added.

Now in cell G2, enter:

=Table3

It creates a spilled copy of your entire table. The data spills from G2 down through all rows and across all columns. When you add rows to Table3, the spilled range in G2 updates automatically.

With that spilled array in place, you can use the following formula anywhere else:

=SORT(G2#, 4, -1)

The G2# reference includes every row and column that spilled from G2. The 4 sorts by the fourth column (Price), and -1 means descending order. Now, when you add products to your table, the SORT formula instantly picks them up.

The # symbol is handy in chained formulas. Put the formula in M3 to create a sorted spilled array. Then, in another cell, use the following formula to filter that sorted data:

=FILTER(M3#, INDEX(M3#, , 2)="Electronics")

If you change the SORT formula, the FILTER formula updates automatically—you don't need to make any changes.

Understanding #SPILL! errors and how to fix them

When Excel can't spill results

When you see #SPILL! in a cell, it means Excel tried to return multiple results but couldn't because something is blocking the spill range. The formula works—it just has nowhere to put the output.

It happens when there's data, formatting, or merged cells in the path where Excel needs to spill results. Even a single cell with a space in it will trigger an error.

Let's say you've put =Table3 in G2, and now you want to list all unique categories. You use the following formula in cell M2 to extract unique values from the second column (Category). Excel determines it needs to return four results: Electronics, Furniture, and any other categories.

=UNIQUE(INDEX(G2#, , 2))

If cell M3 or M4 contains any data—even just a period or a space—the formula can't spill past M2. Now, you can click the #SPILL! error, and Excel will show you exactly what's blocking it. You'll see a dashed border around the cells where results should appear, with the blocking cell highlighted.

However, the fix is easy for such common Excel errors. Clear the blocking cells. Select the range Excel is trying to fill in and press Delete. Once those cells are empty, the formula spills immediately.

If you can't clear those cells because they contain data you need, move your formula to a different location. Put it in column P instead of column M, or start it a few rows lower where there's empty space.

Merged cells also cause #SPILL! errors. If you've merged cells in the spill path—even if they look empty—you'll need to unmerge them. To do that, go to Home > Merge & Center and click it to unmerge the text.

Not everyone has access yet—but it's worth the upgrade

Check your Excel version

The # symbol only works with Microsoft 365 or Excel 2021 and later. If you're using Excel 2019 or older, dynamic arrays and the spill range operator aren't available—your formulas will return errors if you try to use them.

You can check your version by going to File > Account > About Excel. If it shows Excel 2019 or earlier, you'll need to upgrade to access these features.

But if you're a Microsoft 365 subscriber, the # symbol is already enabled. You don't need to activate anything or change settings—it just works once you start using dynamic array functions such as SORT, FILTER, or UNIQUE.