How to correct "###" in cell display Excel (Excel)

When dealing with numbers in Excel, it often happens that the sharpness of the title "######" is consecutive and the value is unreadable.

To sum up, the reason for the "#####" is that the length of the number is larger than the size of the cell, so it cannot be displayed. There is no need to panic, as the calculation itself is working fine.

However, this is a problem if the invoice amount is like "####" yen because it will be printed as # when printed in this state.

In this article, we will show you how to correct such a display.

Reproduce "#####" display

First, let's try to reproduce the phenomenon that causes this kind of display.

From top to bottom, they are "Amount Display with Comma", "Date Display", "List of Numbers", and "Time Display".

In this state, the width of "column B" is gradually reduced.

Image (1), ,Image (2)The display content could not fit in the column width until "###" was changed to "###".

Image (iii)Now the number has changed from "12345678" to "1.2E+07" instead of "####". This display means 1.2 x 10000000 (seven zeros), which is the same as 12000000. The digits are omitted after the third digit from the top to keep the number within the column width.

Image (4)wellImage (iii)display did not fit in the column width and changed to "#####".

Image (5)The "time display," which was the smallest in size in the "##," has also been changed to "##.

Measure 1, widen cell column widths

The easiest solution is to increase the column width of the cell.

If you move the mouse cursor to the right side of column "B" in the column heading, the shape of the mouse cursor changes as shown in the above figure. If you "double-click" in this state, you will see

The "#####" display could be eliminated by increasing the column width to match the cell with the largest column width.

Countermeasure 2, reduce the font size.

The method of measure 1 cannot accommodate a smaller column width.

In such cases, the text can be displayed by reducing the font size.

First, select the cells you do not want to display "###" and right-click, then click "Format Cells".

In the "Format Cells" menu, go to the "Alignment" tab and check the "Shrink to Show Whole" checkbox.

Then the "#####" display disappeared as shown in the image below.

Instead of the "#####" display, the text is shown in a smaller size, but it is indeed too small to read. So please adjust the size to your preference.

This allowed cells that were wide enough to be displayed in normal font size, while cells that might overflow were able to display values with smaller text.

Countermeasure 3, Convert to string and display overflow

Cells containing numbers will be "#####" if the cell width is small, but cells containing text strings will be displayed overflowing the cell.

This method is useful if you wish the cells containing numerical values to overflow in the same way.

Cell width overflow with numerical display

Next, use the "TEXT" function to convert to TEXT while specifying the format of each display item.

Amount (C2 cell)
=TEXT(B2, "#,###")

Date (C3 cell)
=TEXT(B3, "yyyy/m/d")

Numerical value (C4 cell)
=TEXT(B4, "0")

Time (C5 cell)
=TEXT(B5, "h:mm")

The above information is entered into each cell as shown in the figure below.

The "C" column remains narrow, but I was able to overflow the cell and get it to display the correct formatting.

Convert from cell formatting to string

Incidentally, there is also a way to convert it to a string by formatting the cells, but this does not work. Let's try to see what exactly happens.

Select the cell with the "##" display and click on the "Home" tab, then "Display Format Selection Area", then "Text String".

However, this alone will not immediately convert the cell to a string, so once converted to a string, the cell is converted to a string by activating the cell and pressing the "Enter" key.

However, as shown in the figure above, this caused the comma display for the amount to disappear and the date and time to change to "serial values".

Conclusion

If you get the "###" indication, there are three ways to get around it

1. Widen cell column widths
2. Reduce text size
3. Convert to string and display overflow

There are two ways to convert "3." into a string: using the "TEXT" function or from formatting, but using the "TEXT" function is recommended.