Excel : Custom Number Formats Examples

In this tutorial, you will learn some useful custom formats in excel.

1. Add prefix "X" to cells.
Custom Format : "X"#
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.
Custom Format in Excel

2. Add suffix "X" to cells.
Custom Format : #"X"
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

3. Round the number to nearest whole number.
Custom Format : #
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

4. Color numeric value as red and text as green.
Custom Format : [Red]#;[Green]@
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

5. Number >= 100 colored as green else colored as red
Custom Format : [Green][>=100]#;[Red][<100]#
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.


6. Amount showed in whole number and percent in percent format
Custom Format : [>=1] #,##0;[>0] 0%;0
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

7. Large Numbers showed in millions
Custom Format : 0.0,," millions"
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

8. Large Numbers showed in thousands
Custom Format : 0.0," K"
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

9. Format Number in thousands only when they are >= 1000
Custom Format : [>=1000]#,##0,"K";0
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

10. Format number (20152210) as date (2015-22-10)
Custom Format : #-##-##
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

11. Format number 22102015 as date (22-10-2015)
Custom Format : ##-##-####
How to Use : Press CTRL 1 and Select Custom Tab under Number Category and type the above custom format.

12. Convert a number to real date value format

Suppose 20111208 is entered in cell A1. Enter the formula =--TEXT(A1,"0000\-00\-00")
It will return 40885 and change the format to Date.

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "Excel : Custom Number Formats Examples"

Post a Comment

Next → ← Prev