Displaying the Format Cells Dialog Box

 

Format Cells is the most used feature of excel when it comes to formatting a cell value from General format to Number, Currency, Accounting, Date & so on. The shortcut to launch Format cells dialog box is “CTRL + 1”; there’s no need to use mouse; hence, you can save your precious time. The other shortcut is “SHIFT + F10” keys and will work as right clicking on mouse does & then you can simply select Format Cells from the shortcut menu.

There are six tabs in the Format Cells dialog box:

1. Number
2. Alignment
3. Font
4. Border
5. Patterns
6. Protection

To know more about each tab individually, click on the link:

 

Question: Sometimes, excel cell displays a series of ##### characters instead of value or text.

There can be multiple answers to this problem. The possible reasons are:

  • Cell width
  • Dates accepted range (1/1/1900 through 12/31/9999)
  • Formatting of dates is too large
  • Font size

 

Cell width

First one is that the cell width is not wide enough to display the content of the cell.

Example: the cell contains a date value & the cell width is narrow.

 

img1

 

In above image, the cell value contains date as 7/14/2015 which is getting displayed in Formula Bar box. All we need to do is increase the width of the cell by mouse or keyboard shortcut “ALT + O + C + A”

 

Dates accepted range (1/1/1900 through 12/31/9999)

Excel is designed to contain dates within range 1/1/1900 through 12/31/9999; if user is trying to enter dates outside this range then # error will get displayed.

 

Formatting of dates is too large

Sometimes, the date format is set in a lengthy format, like “October 1, 2015”. You can refer to below image in which cell A2 & B2 both contains same date with same formats but one cell is visible while the other one is not getting displayed.

 

img2

 

You can check the format settings; refer below image:

 

img3

 

Font size

One should also keep in mind that excel shows #### sign instead of text due to increase in font size of the cell. Changing the font to large size excel may not be able to display the value of the cell & hence shows hash error in excel. You need to increase the column width to view the content of the cell.

 

Conclusion: The above four detailed explanations are the possible reasons for showing hash error instead of text or numbers.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 



Example:


6 thoughts on “Displaying the Format Cells Dialog Box

  1. I have a 50 worksheets with 15 individual cells that I want to link to a one spreadsheet. Is there an easy way to link (if I copy the link the refernce cell does not change) any ideas.

  2. In my version of Excel 2000, to turn a number into a date you merely press Ctrl + # (no shift key). This produces the date in the following format: 25-Apr-03. The same key combination will also change any other pre-existing date format to this format.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>