Format numbers in Excel

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 the 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

Custom number Format

Just that and you have a better-looking table

Custom number Format


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 to 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 the 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

Further reading:

To learn more about how to create characters

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 *

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