What hash sign means in Excel and how to remove #### from cell

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 # symbol means in Excel
• Remove #### error from cell
• Using # in custom number formats
• Using # as spill range operator

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.

How to remove #### in Excel

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.

How to fix #### shown instead of negative dates or times

When ##### appears in a cell because of a negative date or time value, try these troubleshooting tips:

• Make sure dates and times are positive values when using the 1900 date system. Or change to the 1904 date system to display negative date and time values normally: File > Options > Advanced > When calculating this workbook > Use 1904 date system.
• Use a correct formula to add/subtract dates or add/subtract times to avoid negative results.
• Apply a special format for negative dates/times to display a blank cell or error message instead of negative values.
• Use this formula to calculate an absolute value of negative times and convert to an appropriate format to show negative times correctly.

For more information, see How to calculate and show negative times in Excel.

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	Displayas
#.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)

The above is the detailed content of What hash sign means in Excel and how to remove #### from cell. For more information, please follow other related articles on the PHP Chinese website!