Excel number formats can seem quite scary. This topic will help you better understand the way Excel number formatting works. You will be able to put this to good use in your own Excel workbooks and spreadsheets.

Your computer generally is set to the country that you live in either by you, the sales or support person. This is the overall settings that control many of the formatting apps and features. There are many reasons that number formats need changing, it could be personal or business specific.

Let’s begin…

You are already familiar with Excel number formats, now we need to understand how to customise them to suit. Instead of starting to create your own format which is also okay, there are already an extensive list of pre-set number formats. We are going to learn how to manage some of these to better represent your data.

These sample formats can be edited to the format you want. In the category list click Custom then select the number format from the list which is displayed in the Type box ie.. # ##0;[Red]-# ##0. In the Type box edit the number format to suit.

The sample number format explained:

  • There is no financial symbol ie. $ sign
  • The hash signs # represent the number of digits ie. Hundreds, thousands, millions irrespective, the number will be correctly displayed
  • The 0 (zero) tells Excel that it’s a number.
  • The first part of the number is the positive number format
  • The word in Red in square brackets [Red] rules the negative number format, at the back.
  • Whatever changes you make to the positive number format do the same for the negative number format. You will need to choose whether to put a – (minus) or brackets () to indicate the negative number format
  • For example, $#,##0;[Red]($#,##0) – the result will look like this (positive)  $10,500 or (negative) ($4,500)

Do share your comments!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.