Excel Custom Number Format

How to Customize Number Formats In Excel

Looks matter both in life and data visualization—you never get a second chance to make a first impression. Numbers in excel constitute the largest data type and knowing how to format them either on Charts or on tables is a big plus towards quicker decision making.

Below are some tips on custom formatting;

Custom Number Format on tables

For example how do you display decimals and change font color when a number is less than 1 in table below?

custom number format

How to go about it:

  1. Highlight the numbers and press CTRL+1 on your Keyboard
  2. Go to—Number tab, select category as “Custom”
  3. Write your format

    [Blue][>1]##0;[Red]##0.00

Custom number Format

Just that and you have a better looking table

Custom number Format

quiz

►Try below Custom format and see the results

[>1]”Ksh” ###0;””

Custom formatting number on charts

What if you had a chart like below and you need to format Y axis so that positive numbers are colored Green, negatives red, and keep zeros in their default?

Custom format

How to go about it:

  1. Right-click axis area and Format axis
  2. Go to Numbers, then Custom and Paste below format on Format code–then click Add

             [Green][>1]#, ##0; [Red] (#, ##0); #, ##0

Format Axis

Just like that you have beautiful looking chart

Custom Chart2

What if you want to change font color for numbers from 4000 and above?

             [Green][>=4000]#, ##0; [Red] (#, ##0); #, ##0

Custom Chart

The same trick can be used to format Series data labels to show Decrease▼  Increase▲or Constant ◄►

                     [Blue] 0.00▲”%”;[Red] 0.00▼”%”;[Green]#0.00◄►”%”

format excel chart

or in a dashboard

excel dashboard format

Download workbook for more practice

quiz

►List the font colors that can be included custom format?

Leave a comment and teach someone

Further reading:

http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/

To learn more on how to create characters http://www.alt-codes.net/

Leave a Comment

6 thoughts on “How to Customize Number Formats In Excel”

  1. Hugh says:

    yоu’re in reality a just right webmaster.
    The web site loading speed is аmazing. It sort of feels that you’re ⅾօing any unique
    trick. In addition, The contents are masterwork.
    you’ve done a wonderful job in this matteг!

  2. Damon says:

    І am extremely impressed with your writing ѕkills and also with the layout on your blog.
    Is this a paid theme or did you moɗіfy it yourself? Either way қeep up
    the nice quality writing, it is гare to see a great ƅloɡ like this one today.

  3. Tamela says:

    Generally ӏ don’t ⅼearn post on blogs, however Ӏ wish to say that this write-up very compelled mе to check out and Ԁo it!
    Your writing style has been surрrised me. Thank you, very
    nice post.

    1. Thanks Tamela. Hope you keep learning more

Leave a Comment:

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