Excel - Number Formatting Secrets - Episode 2044
1,972 views · Published 9 October 2016 · 18:38 · Indexed 23 October 2026
Channel: MrExcel.com · 2016 · Education
Microsoft Excel Tutorial - Number Formatting Secrets. This video covers everything you need to know about custom number formatting in Excel. Number formatting is a façade: Excel stores one thing, shows us another. For example: use decrease decimals to show a rounded version of the number. Excel still stores all of the decimals, but simplifies the display. There are 11 number formats in the drop-down on the home tab. Click the dialog launcher to get to Format Cells dialog. Today, we are talking about the formatting codes in the Custom group. I don't have all the knowledge here today, so if you have better tips, put them in the YouTube comments. A zero in the format code says Excel must display the digit. A # sign says they may display the digit if there is enough precision, but they don't have to when there is not. A ? will leave space for the digit to keep the decimal place lined up. There can be 1, 2, 3, or 4 formats, separated by a semi-colon. When there are 2 formats, the first is for zero and up. The second is for negative. When there are 3 formats, the third if for zero. 4th format is for text. Using the number format to display text with number or to display text instead of number. Specifying a color for a zone. To change [Color 5] visit Excel options. *x to repeat x up to the numbers , at end to divide by 1000 ,, divides by millions Using conditions in the zone _x will leave space the size of "x" m mm mmm mmmm mmmmm d dd ddd dddd [h]:mm This video answers these common search terms: force leading zeroes in excel using a hash in custom number format using a question mark in custom number format four zones for number formats adding words to a number in excel replacing number with words in excel summing words in excel! legal characters in custom number format separate digits with dashes in excel display numbers in thousands in excel font color in excel custom number format changing the 56 colors in excel hide all numbers using number formatting in excel fill excel cell with * like a check writer numbers in thousands or millions forcing a number to be entered in excel leaving whitespace in custom number format dates in excel custom number formatting dates as jfmamjjasond weekday name from custom number format display over 24 hours in excel using number formatting square brackets in excel custom number formatting decoding the accounting number format #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelhacks #evergreen #excelformatting #numberformatting Table of Contents (00:00) Number Formatting in Excel (00:25) Decrease Decimal in Excel (01:03) Custom Number Formatting in Excel (01:20) Force leading zeroes in Excel (02:03) Using a Hash in Custom Number Format (02:28) Using a Question Mark in Custom Number Format (03:17) Four Zones for Number Formats (04:24) Adding words to a Number in Excel (04:37) Replacing Number with Words in Excel (05:15) Summing Words in Excel! (05:34) Legal characters in Custom Number Format (06:06) Separate digits with dashes in Excel (06:22) Display numbers in thousands in Excel (06:40) Font color in Excel custom number format (07:26) Changing the 56 Colors in Excel (07:49) Hide all numbers using number formatting in Excel (08:12) Fill Excel cell with * like a check writer (08:53) Numbers in Thousands or Millions (09:28) Forcing a number to be entered in Excel (10:09) Leaving whitespace in custom number format (10:49) Dates in Excel Custom Number Formatting (11:16) Dates as JFMAMJJASOND (12:01) Weekday name from Custom Number Format (12:56) Display over 24 hours in Excel using Number Formatting (14:20) Square brackets in Excel custom number formatting (14:31) Decoding the Accounting Number Format (16:15) Buy the book! (16:30) Clicking Like really helps the algorithm Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153871/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920