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?

FORMAT NUMBERS IN EXCEL

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

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 NUMBERS IN EXCEL

Just like that, you have beautiful looking chart

FORMAT NUMBERS IN EXCEL

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

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

FORMAT NUMBERS IN EXCEL

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 NUMBERS IN EXCEL

or in a dashboard

FORMAT NUMBERS IN EXCEL

Custom Number Formatting Worksheet

Further reading:

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

To learn more about how to create characters https://www.alt-codes.net/

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.