Number Formatting Excel

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.


Number Formatting Excel






How to Use Number Formatting in Excel

 

Excel provides many options for displaying numbers as percentages, currency, dates, and so on. If these built-in formats do not meet your needs, you can customize a built-in number format to create your own. To learn more about how to change number format codes, you may want to review the guidelines for customizing a number format before you get started.

Review guidelines for customizing a number format

To create a custom number format, you start by selecting one of the built-in number formats as a starting point. You can then change any one of the code sections of that format to create your own custom number format.

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

For example, you can use these code sections to create the following custom format:

[Blue]#,##0.00_);[Red](#,##0.00);0.00;”sales “@

You do not have to include all code sections in your custom number format. If you specify only two code sections for your custom number format, the first section is used for positive numbers and zeros, and the second section is used for negative numbers. If you specify only one code section, it is used for all numbers. If you want to skip a code section and include a code section that follows it, you must include the ending semicolon for the section that you skip.

The following guidelines should be helpful for customizing any of these number format code sections.

Guidelines for including text and adding spacing

  • Display both text and numbers    To display both text and numbers in a cell, enclose the text characters in double quotation marks (” “) or precede a single character with a backslash (\). Include the characters in the appropriate section of the format codes. For example, type the format $0.00″ Surplus”;$-0.00″ Shortage” to display a positive amount as “$125.74 Surplus” and a negative amount as “$-125.74 Shortage.” Note that there is one space character before both “Surplus” and “Shortage” in each code section.

    The following characters are displayed without the use of quotation marks.

$ Dollar sign
+ Plus sign
( Left parenthesis
: Colon
^ Circumflex accent (caret)
Apostrophe
{ Left curly bracket
< Less-than sign
= Equal sign
Minus sign
/ Slash mark
) Right parenthesis
! Exclamation point
& Ampersand
~ Tilde
} Right curly bracket
> Greater-than sign
Space character
  • Include a section for text entry    If included, a text section is always the last section in the number format. Include an “at” character (@) in the section where you want to display any text that you type in the cell. If the @ character is omitted from the text section, text that you type will not be displayed. If you want to always display specific text characters with the typed text, enclose the additional text in double quotation marks (” “). For example, “gross receipts for “@

    If the format does not include a text section, any non-numeric value that you type in a cell with that format applied is not affected by the format. In addition, the entire cell is converted to text.

  • Add spaces    To create a space that is the width of a character in a number format, include an underscore character (_), followed by the character that you want to use. For example, when you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.
  • Repeat characters    To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format. For example, type 0*- to include enough dashes after a number to fill the cell, or type *0 before any format to include leading zeros.

Guidelines for using decimal places, spaces, colors, and conditions

  • Include decimal places and significant digits    To format fractions or numbers that contain decimal points, include the following digit placeholders, decimal points, and thousand separators in a section.
0 (zero) This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.
# This digit placeholder follows the same rules as the 0 (zero). However, Excel does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed.
? This digit placeholder follows the same rules as the 0 (zero). However, Excel adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.
. (period) This digit placeholder displays the decimal point in a number.
  • If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47.
To display As Use this code
1234.59 1234.6 ####.#
8.9 8.900 #.000
.631 0.6 0.#
12
1234.568
12.0
1234.57
#.0#
44.398
102.65
2.8
  44.398
102.65
2.8
(with aligned decimals)
???.???
5.25
5.3
5 1/4
5 3/10
(with aligned fractions)
# ???/???
  • Display a thousands separator    To display a comma as a thousands separator or to scale a number by a multiple of 1,000, include the following separator in the number format.
, (comma) Displays the thousands separator in a number. Excel separates thousands by commas if the format contains a comma that is enclosed by number signs (#) or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.200.0 is displayed.
To display As Use this code
12000 12,000 #,###
12000 12 #,
12200000 12.2 0.0,,
  • Specify colors    To specify the color for a section of the format, type the name of one of the following eight colors enclosed in square brackets in the section. The color code must be the first item in the section.
[Black]
[Green]
[White]
[Blue]
[Magenta]
[Yellow]
[Cyan]
[Red]
  • Specify conditions    To specify number formats that will be applied only if a number meets a condition that you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, the following format displays numbers that are less than or equal to 100 in a red font and numbers that are greater than 100 in a blue font.

    [Red][<=100];[Blue][>100]

    To apply conditional formats to cells (for example, color shading that depends on the value of a cell), on the Home tab, in the Styles group, click Conditional Formatting.

Guidelines for currency, percentages, and scientific notation format

  • Include currency symbols    To type one of the following currency symbols in a number format, press NUM LOCK and use the numeric keypad to type the ANSI code for the symbol.
To enter Press this code
¢ ALT+0162
£ ALT+0163
¥ ALT+0165
euro ALT+0128
  • Note: Custom formats are saved with the workbook. To have Excel always use a specific currency symbol, you must change the currency symbol that is selected in the Regional Options in Control Panel before you start Excel.

  • Display percentages    To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% — include the percent sign (%) in the number format.
  • Display scientific notations    To display numbers in scientific (exponential) format, use the following exponent codes in a section.
E (E-, E+, e-, e+) Displays a number in scientific (exponential) format. Excel displays a number to the right of the “E” or “e” that corresponds to the number of places that the decimal point was moved. For example, if the format is 0.00E+00, and you type 12,200,000 in the cell, the number 1.22E+07 is displayed. If you change the number format to #0.0E+0, the number 12.2E+6 is displayed.

Guidelines for date and time formats

  • Display days, months, and years    To display numbers as date formats (such as days, months, and years), use the following codes in a section.
m Displays the month as a number without a leading zero.
mm Displays the month as a number with a leading zero when appropriate.
mmm Displays the month as an abbreviation (Jan to Dec).
mmmm Displays the month as a full name (January to December).
mmmmm Displays the month as a single letter (J to D).
d Displays the day as a number without a leading zero.
dd Displays the day as a number with a leading zero when appropriate.
ddd Displays the day as an abbreviation (Sun to Sat).
dddd Displays the day as a full name (Sunday to Saturday).
yy Displays the year as a two-digit number.
yyyy Displays the year as a four-digit number.
To display As Use this code
Months 1–12 m
Months 01–12 mm
Months Jan–Dec mmm
Months January–December mmmm
Months J–D mmmmm
Days 1–31 d
Days 01–31 dd
Days Sun–Sat ddd
Days Sunday–Saturday dddd
Years 00–99 yy
Years 1900–9999 yyyy
  • Display hours, minutes, and seconds    To display time formats (such as hours, minutes, and seconds), use the following codes in a section.
h Displays the hour as a number without a leading zero.
[h] Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss.
hh Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock.
m Displays the minute as a number without a leading zero.

Note: The m or mm code must appear immediately after the h or hh code or immediately before the sscode; otherwise, Excel displays the month instead of minutes.

[m] Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss.
mm Displays the minute as a number with a leading zero when appropriate.

Note: The m or mm code must appear immediately after the h or hh code or immediately before the sscode; otherwise, Excel displays the month instead of minutes.

s Displays the second as a number without a leading zero.
[s] Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss].
ss Displays the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00.
AM/PM, am/pm, A/P, a/p Displays the hour using a 12-hour clock. Excel displays AMamA, or a for times from midnight until noon and PMpmP, or p for times from noon until midnight.
To display As Use this code
Hours 0–23 h
Hours 00–23 hh
Minutes 0–59 m
Minutes 00–59 mm
Seconds 0–59 s
Seconds 00–59 ss
Time 4 AM h AM/PM
Time 4:36 PM h:mm AM/PM
Time 4:36:03 P h:mm:ss A/P
Time 4:36:03.75 h:mm:ss.00
Elapsed time (hours and minutes) 1:02 [h]:mm
Elapsed time (minutes and seconds) 62:16 [mm]:ss
Elapsed time (seconds and hundredths) 3735.80 [ss].00

 

Create a custom number format

  1. Open the workbook in which you want to create and store a custom number format.
  2. On the Home tab, click the Dialog Box Launcher next to Number.

    Dialog Box Launcher in Number group

  3. In the Category box, click Custom.
  4. In the Type list, select the number format that you want to customize.

    The number format that you select appears in the Type box above the Type list.

    Note: When you select a built-in number format in the Type list, Excel creates a copy of that number format that you can then customize. The original number format in the Type list cannot be changed or deleted.

  5. In the Type box, make the necessary changes to the selected number format.

    Tip: For more information about the changes that you can make, see Review guidelines for customizing a number format in this article.

Note: A custom number format is stored in the workbook in which it was created and will not be available in any other workbooks. To use a custom format in a new workbook, you can save the current workbook as an Excel template that you can use as the basis for the new workbook.

 

Delete a custom number format

  1. Open the workbook that contains the custom number format that you want to delete.
  2. On the Home tab, click the Dialog Box Launcher next to Number.

    Dialog Box Launcher in Number group

  3. In the Category box, click Custom.
  4. In the Type list, select the custom number format that you want to delete.

    Note: Built-in number formats in the Type list cannot be deleted.

  5. Click Delete.

    Note: Any cells in the workbook that were formatted with the deleted custom format will be displayed in the default General format.

 

See how to format cells and ranges in Excel This Excel tutorial has been created to help you learn microsoft excel online. On http://www.myelesson.org you get excel help and online file download facitity. This video lesson covers the entire topic in simple and easy to understand step by step approach.








Our Other Services




Read Free Ebooks

myeboook.com

Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!



Buy Excel Course

myelesson.org/product

Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788



Create Best Looking Resume

http://www.makecv.org/

At Makecv.org you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!


Download Excel Crash Course


This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.



Learn Photoshop Web Designing


You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .



Learn HTML CSS


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .


Download Full list of Excel Shortcuts


This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.




Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on www.Myelesson.org




Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from myelesson.org .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.




List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.





Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok
Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok