What’s the #### in My Worksheet?
Have you ever found yourself happily working in MS Excel only to be befuddled by the sudden appearance of #### in a cell where there should be a number?
Or maybe something along the lines of 2E+15 appeared?
Now, what is that?
Good question, and believe it or not, there’s a good answer. (You may not like the answer, but it’s a valid one just the same).
The deal with the #### is that your number is too long to fit in the cell width you’ve set.
I’ve found that as long as I haven’t previously made manual changes to the cell width, it will automatically adjust to a numbers width.
But, if I’ve been messing with the width setting, it just leaves it where it’s at. So, when the number is too long to fit in the space, it’s letting you know that by displaying the ####.
And what about the 2E+15, you ask?
Well, that’s your number put into scientific notation.
I find that Excel puts my data into scientific notation if the cell has the default cell format of general and the number is too long for the cell, but still long enough to get this in.
If the cell is specifically formatted some other way, you may not see this one, but Excel is trying to display some sort of value and that’s often the shortest way to display a large number.
Looking for a solution to the problem(s)?
It’s simple. Just make the cell width larger.
There are many ways you can accomplish this task. A couple of the fastest have to do with the column label letter.
At the top of the column, you’ll find the letter label. Each letter is separated from the next column by a vertical line.
One way to change the column width using this divider is to place your mouse pointer over the divider to the right of the letter. When it turns into a double-sided arrow, click the left mouse button and hold it.
Now, simply drag the divider to the right and release the mouse button when you feel you’ve made the column wide enough. (You’ll know if it’s big enough immediately, because the number will be completely displayed).
Another way is to get the program to AutoFit to the longest piece of data in the column.
The fastest way to accomplish this is to go back to the column label divider to the right on the column.
This time, instead of the old “click and hold” thing, double click on the divider.
Instantly, you’ll see the column jump to just the right width to accommodate your data.
By the way, you can also change column width with the Format menu, Column submenu.
Manual adjustments can be completed through the Width choice. AutoFit Selection will expand all columns currently selected. (If only one cell is selected, it works just on that column).
Whatever your method choice, they all remove the #### blues!
~ April