Excel is both brilliant and the most frustrating software on Earth. For example, when it inserts #### in your Excel spreadsheets without any explanation. Thankfully, there’s normally an easy solution to this problem. Here’s how to deal with it.
Why are there #### signs in an Excel spreadsheet?
Here’s an example of where you might see ##### (often called hashes or pound symbols, depending on where you come from) in an Excel spreadsheet:
You’ve created a formula to calculate the average value of the ‘Widgets’ and ‘Wodges’ over six months, but the relevant cells are showing nothing but ##### symbols.
What are the hash signs trying to tell you? You’ve made an error in the formulae? No. The hash signs are appearing purely because the cell isn’t wide enough to fit in the contents. It’s that simple.
How to stop #### appearing in Excel spreadsheets
There are a few simple ways to stop this occurring.
The easiest way is to manually widen the cell. Go to the top of the column, hover your mouse on the line between the two cells you wish to widen, and wait for the cursor to turn into two arrows with a line between them. Then click and drag the cell until it’s wide enough for the contents of the problem cell. Watch the the short video below to see this in action:
If you don’t want to widen the cells, you can right click on the cell with the #### marks, select Format Cells, click on the Alignment tab in the window that appears and tick Shrink to Fit before clicking OK.
This will shrink the size of the font, so that the contents of the cell now fit in the cell width. I prefer not to do that, as it can make a spreadsheet look ragged with different sized fonts everywhere.
If, as in the example above, the problem is simply that you have numbers with lots of decimal places that you don’t actually need to display, you can also solve the issue by clicking on the problem cell and then clicking the Decrease decimal icon you’ll find under the Home tab at the top of the screen. It looks like three zeroes with an arrow pointing to the right. You may need to press it multiple times to reduce the number of decimal points being shown in the cell. It won’t affect any calculations, just the number of decimal points being displayed. You can also apply that setting across the entire spreadsheet to keep everything looking consistent.
There are some circumstances where Excel will display #### and it’s not a content width problem, but another error, such as entering a negative value for a time or date. Check your formulae or cell values carefully if adjusting the cell width or the other measures above make no difference.