by In this article, we will explore various ways in which the number sign can be used in Excel and provide solutions for the #### error.
The # symbol, also known as the number, hash or pound sign, is a versatile character with multiple meanings in different fields and contexts - from representing numbers in mathematics to indicating color codes in graphic design. In Excel, the # symbol is commonly associated with errors, but it also has several legitimate uses such as a spill range reference or a digit placeholder. The aim of this article is to help you understand all these different meanings and efficiently apply them where appropriate.
What does # or #### mean in Excel?
The #symbol in Excel has various meanings depending on the context:
- #### error. A sequence of # characters in a cell usually indicates that the cell is not wide enough to display the value in it. Besides, ##### can appear in cells containing negative date or time values.
- Custom number formats. The number sign is also used as a digit placeholder in Excel number formats. For instance, the # ##0.00 format code will display numbers with two decimal places and a space separating thousands.
- Spill range operator. In dynamic array formulas that return multiple values, the # sign is used to construct a spill range reference that refers to the entire range returned by the formula, for example A2#.
In the following sections, we will explore each use case in more detail, and provide tips and tricks to help you master the use of the # sign in Excel.
#### error in Excel cell
When you see ##### in Excel, usually it means that the data is too large to fit into a cell. Or looking from another angle, the cell is not wide enough to display the entire value :) In other words, Excel shows #### to let you know that the actual data is not visible.
The most common cause of the ### error is when you enter a very large number or a date in the format that is wider than the cell.
There are several ways to fix the ### error in Excel. The fastest one is to adjust the cell's width to accommodate the data. For this, just double-click the right edge of the column header to autofit the column or drag it to the desired width manually.
If the above method does not work, try one of the following solutions: Change format. If a date value is too long, you can apply a shorter date/time format. The detailed instructions are here: how to change date format in Excel. Show fewer decimal places. If numbers have too many decimal places, you can show fewer places by clicking the Decrease Decimal button on the ribbon. See Excel formatting options for more details. Decrease font size. If the font size of the cell is too large, you can make it smaller for the data to fit into the cell. When ##### appears in a cell because of a negative date or time value, try these troubleshooting tips: For more information, see How to calculate and show negative times in Excel.
How to remove #### in Excel
How to fix #### shown instead of negative dates or times
The # placeholder in Excel number formats
In Excel number formatting, the # character is the digit placeholder that only displays significant digits, without extra zeros. The table below provides a few examples of custom number formats:
| Format | Description | Input value | Display as |
|---|---|---|---|
| #.00 | Always display 2 decimal places. | 5 5.5 0.555 |
5.00 5.50 .56 |
| #.## | Shows up to 2 decimal places, but without insignificant zeros. | 5 5.5 0.555 |
5. 5.5 .56 |
| ### ###
or # ### |
Rounds numbers to the nearest integer (no decimal places) and separates into groups of three digits leaving a space between each group. | 1000 10000 10000.5 |
1 000 10 000 10 001 |
| #,##0.00 | Displays two decimal places and a comma as a thousand separator and. | 1000.5 10000.555 |
1,000.50 10,000.56 |
| # ##0.00 | Displays two decimal places and a space as a thousand separator. | 1000.5 10000.555 |
1 000.50 10 000.56 |
| # ##0_); [Red](# ##0) | Rounds numbers to the nearest integer and uses a space to separate thousands. Negative numbers are displayed in parentheses and in red font color. Moves (indents) positive numbers and zeros from the right border by one character width. | 10000 10000.5 -10000.5 |
10 000 10 001 (10 001) |
The screenshot below shows these custom number formats in action:
For more information, please see How to use custom Excel number format.
The # symbol as a spill range operator
In addition to its use in number formatting, the number symbol # can also act as a spill range operator in Excel formulas. This is a new feature introduced in Excel 365 that allows referring to an entire range of cells returned by a dynamic array formula.
To make a spill range reference, you put the # sign after the address of the upper-left cell in the returned range, for instance A2#. This tells Excel to process all the cells in the spill range.
For example, if you have a FILTER formula in E6, which returns multiple results in E6:G9, you can use the spill range reference to return the filtered values into another location on the same sheet or in a different sheet:
=E6#
=Sheet1!E6#
To find how many records (rows) are returned by the FILTER formula, supply the spill range reference to the ROWS function:
=ROWS(E6#)
Unlike regular ranges, spill ranges are dynamic by nature and automatically adjust as you add or remove data from the source range. This means that you don't need to manually update references in your formulas every time your source data changes, which can save you time and reduce errors.
For more information, please see # operator in Excel formulas.
Now you know how to effectively use the number sign in Excel and how to troubleshoot any #### errors that may occur. Thank you for reading!
Practice workbook for download
# symbol in Excel - examples (.xlsx file)
5 comments
To allow large numbers to go beyond the border of a cell, you can also convert it to text by placing an apostrophe in front of your number. E.g. 123456 would now be '123456 and show the full number regardless of cell width.
Thanks so much! This cleared up a lot of stress very quickly.
When using this with a FILTER function and COUNTA as in =COUNTA(C7#) I get a count of 1 when the resulting filter shows no results. Using it in reference to any empty cell always shows 1.
Hi! Your formula =COUNTA(C7#) refers to the spill range. Therefore, Excel assumes that cell C7 contains the spill range, which means it is not empty. Read more: Excel spill range explained.
This is very helpful, thank you!