What's the fuss about? There are so many ###s in Excel that no matter how wide the cell fills up, is there a rut? Case Study: Solution 1: Solution 2: Solution 3:

Have you ever encountered this situation: a good cell, how to enter the data inexplicably become a long list of "#######"? Is this a bug or is it correct? How should it be solved?

In Figures 1 through 3 below, each column has a "#####" symbol, and the reasons and solutions for these three scenarios are completely different.

Let's look at the detailed steps separately.

1. Enter the number "1" in cell a1, and a series of "##" appear in the cell after entering.

2. Entering "123" in the a2 cell also becomes a string of "#####".

3. If you still don't believe in evil, enter "12345" in cell a3, and the situation remains the same.

What exactly is causing this? If we select cell a1, you can see from the formula bar that the number you just entered automatically becomes a date.

Originally, the cell format for column a was formatted as a date.

For this kind of "#" solution is relatively simple, there are usually the following two.

Increase the column width appropriately so that the date can be displayed in its entirety.

Select column a, press ctrl+1 --> select the Number tab in the dialog that pops up --> General --> Click OK

The format of the a column becomes a regular numeric value, and the annoying "#" naturally disappears.

1. Enter "1234512345" in cell b1, and the carriage return becomes a long string of "#######".

What's going on here?

Before answering this question, let's enter other values to try.

2. Enter "0" in cell b2, and instead of entering, a "#" appears, but becomes a date value.

Originally, column b was also formatted as a date.

3. Enter "0.001" in cell b3, and enter the same value as cell b2.

4. Enter "-1" in cell b4 and the annoying "####" comes again.

Even if you pull the b column wider, it doesn't help, the "#" sign in the cell will automatically increase with the column width, always filling the entire cell width.

At this point, I can explain to you the reason why the "#" in this column appears.

Column b is formatted as a date, and the values in cells b1 and b4 exceed the top and lower limits of the date, respectively;

The upper limit of the date format is 12/31/9999, converted to a number is 2958465, as long as it is greater than this value, it will be displayed as a "#" sign that fills the entire cell;

While dates cannot be negative, entering negative numbers will also be displayed as a "#" sign.

In this case, either re-enter the numeric value in the correct date range or modify the cell format:

Select column b, press ctrl+1 --> select the Number tab in the pop-up dialog box, you can see that it is now the date format --> Select "General" --> Click "OK"

The date format coat has been faded, and now the values of column b are displayed normally.

Regarding the third scenario, I will not sell Guanzi, and I will show you the reason directly.

1. Select column c, press ctrl+1 --> select Number in the pop-up dialog box -- > Custom -- > Enter the following type in the Type area --> Click OK:

[=1] "Qualified"; [=0] "Failed"

According to the setup rules, enter "1" and "0" in column c, which will be displayed as "Pass" and "Fail", respectively.

If you enter any data other than "1" and "0", the cells display an endless "#".

Increase the column width, and the "#" will also become more.

The reason for this situation is that you have entered a non-compliant value, so you can enter the correct value as specified by the cell setting.

Many students will feel that the explanation of a single case of excel is somewhat fragmented, and beginners may not be able to fully understand and master it. Many students hope to have a complete set of graphic teaching, starting from the most basic concepts, step by step from simple to complex, from beginner to proficient, systematically explain the various knowledge points of Excel.

Now finally, the following column, starting from the most basic operations and concepts, with vivid and interesting cases to take you to master the operation skills of excel one by one, shortcut keys, function formulas, pivot tables, charts, printing skills, etc. Learn the whole book, and you can become an excel master.