Excel is a powerful spreadsheet program. Sometimes it throws error messages to help users understand why a certain action didn’t work as intended. In most cases, the errors are associated with functions but there are others that cut across all features. One of the most common errors in Excel is the #####. If you are new to Excel this may confuse you. Luckily it’s the easiest error to fix.
In this tutorial, I will show you how to solve the #### error in Excel.
What is the ##### Error in Excel?
##### in Excel is caused by having contents that occupy more space than the cell width. Mostly occurs when you type dates or numerical values. This error is common in all Excel versions. The number of ### also varies depending on the size of the Excel cell.
How To Solve ##### Error in Excel?
There are a few ways in which you can solve this problem.
1. Adjusting Cell Width Manually
You can easily solve this error by adjusting the Excel cell width. To adjust an Excel cell width then you need to do it in the column level.
1. Just place the cursor on the column border at the top.
2. Then click on the border and drag it towards the right side.
3. Release the cursor. You will realize that all the cells under that column have been adjusted
2. VBA Code to Adjujst Cell Width Automatically
If you are working with a lot of data then you can adjust the width of the cells automatically. The best way to achieve this is by using VBA code. Here are the steps.
1. Open the VBA editor by using the shortcut ALT+F11 or Opt + F11 or Fn + Opt + F11 on Mac
2. Click on Insert then select module
3. Copy and paste the following code
Sub AdjustColumnWidth()
Dim ws As Worksheet
Dim col As Integer
Dim lastRow As Long
Dim maxLen As Double
' Iterate through all worksheets
For Each ws In ThisWorkbook.Sheets
' Iterate through all columns
For col = 1 To ws.Columns.Count
' Find the last row with data in the current column
lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
' Find the maximum content length in the column
If lastRow > 1 Then
maxLen = Application.WorksheetFunction.Max(ws.Cells(1, col).Resize(lastRow).Cells)
' Adjust column width based on the maximum content length
ws.Columns(col).ColumnWidth = maxLen + 2 ' Add some extra width for padding
End If
Next col
Next ws
End Sub
4. Exit the editor and click the developer’s tab then run the macro
5. Continue typing on your spreadsheet as usual. You will notice that the cell adjusts automatically according to the length of the data that you are typing.
3. Using short date
If you are facing this error on a date then you can use shorter versions of dates. Here is how to format your dates to become shorter. Learn more about how to change the date format in this article.
1. Highlight the cells containing the dates or that will contain dates
2. Then right-click on the cells and select Cell Formatting
3. Under the number tab click on dates then select the short date format
4. Click okay
4. Shrinking cell contents
Another common way of getting rid of the #### error is by shrinking the cell contents. Here are the simple steps to do that.
1. Under the home tab click on the alignment settings drop-down
2. Navigate to text control then check the option shrink to fit
3. Click okay to apply the changes
The text has been shrunk to fit the space available in the cells.
5. Decrease Decimals
Too many decimals can cause this error too. However, you can easily fix the problem by reducing them to fit the cells. The best way to reduce decimals is by rounding them off. Learn more about rounding off numbers in this tutorial.
Decreasing the decimals is easy with the following steps
1. Highlight or select the cells containing numbers with many decimals.
2. Click on the home tab
3. Navigate to the number group then click the option for reducing decimals as illustrated on the image below.
As you can see from the image below the decimals have been reduced hence solving the ##### error.
Read More: How to Solve Common Excel Errors
Final Thoughts
The ##### error can be nagging especially to the new Excel users. Luckily it’s a simple error message that can easily be fixed by trying the above simple solutions. My favorite fix is automatically adjusting the cell width using VBA to prevent the error.