{"id":1929,"date":"2024-04-23T16:49:40","date_gmt":"2024-04-23T08:49:40","guid":{"rendered":"https:\/\/www.fyndpro.com\/?p=1929"},"modified":"2024-09-11T10:43:29","modified_gmt":"2024-09-11T02:43:29","slug":"master-excel-custom-formats-symbols-and-their-uses","status":"publish","type":"post","link":"https:\/\/www.fyndpro.com\/cn\/master-excel-custom-formats-symbols-and-their-uses\/","title":{"rendered":"Master Excel Custom Formats: Symbols and Their Uses"},"content":{"rendered":"<p>In Excel&#8217;s custom format codes, special symbols can be used to control how data is displayed more effectively. Below are some common symbols and their meanings:<\/p>\n<h4>1. The \u201c#\u201d Symbol<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Displays the number in the cell but does not show insignificant zeros.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>####.00<\/code>\u00a0displays numbers with two decimal places.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>20<\/td>\n<td>####.00<\/td>\n<td>20.00<\/td>\n<\/tr>\n<tr>\n<td>424894<\/td>\n<td>####.00<\/td>\n<td>424894.00<\/td>\n<\/tr>\n<tr>\n<td>166<\/td>\n<td>####.00<\/td>\n<td>166.00<\/td>\n<\/tr>\n<tr>\n<td>326.89<\/td>\n<td>####.00<\/td>\n<td>326.89<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>2. The \u201c0\u201d Symbol<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Displays the number in the cell and pads with zeros if the number of digits is less than specified.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>00000<\/code>\u00a0displays the number as five digits.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>20<\/td>\n<td>00000<\/td>\n<td>00020<\/td>\n<\/tr>\n<tr>\n<td>424894<\/td>\n<td>00000<\/td>\n<td>424894<\/td>\n<\/tr>\n<tr>\n<td>166<\/td>\n<td>00000<\/td>\n<td>00166<\/td>\n<\/tr>\n<tr>\n<td>326.89<\/td>\n<td>00000<\/td>\n<td>00327<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>3. The \u201c?\u201d Symbol<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Displays a space instead of insignificant zeros.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>?????<\/code>\u00a0is used for left-aligned display.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>20<\/td>\n<td>?????<\/td>\n<td>20<\/td>\n<\/tr>\n<tr>\n<td>424894<\/td>\n<td>?????<\/td>\n<td>424894<\/td>\n<\/tr>\n<tr>\n<td>166<\/td>\n<td>?????<\/td>\n<td>166<\/td>\n<\/tr>\n<tr>\n<td>326.89<\/td>\n<td>?????<\/td>\n<td>327<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>4. Displaying Text<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Displays text in the custom format, enclosed in half-width double quotes.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>0\"KM\"<\/code>\u00a0adds a unit after the number.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>20<\/td>\n<td>0&#8243;KM&#8221;<\/td>\n<td>20KM<\/td>\n<\/tr>\n<tr>\n<td>424894<\/td>\n<td>0&#8243;KM&#8221;<\/td>\n<td>424894KM<\/td>\n<\/tr>\n<tr>\n<td>166<\/td>\n<td>0&#8243;KM&#8221;<\/td>\n<td>166KM<\/td>\n<\/tr>\n<tr>\n<td>326.89<\/td>\n<td>0&#8243;KM&#8221;<\/td>\n<td>327KM<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>5. The Half-Width Comma \u201c,\u201d Symbol<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Represents a thousands separator.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>#,###.00<\/code>\u00a0displays numbers with a thousands separator.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>20<\/td>\n<td>#,###.00<\/td>\n<td>20.00<\/td>\n<\/tr>\n<tr>\n<td>424894<\/td>\n<td>#,###.01<\/td>\n<td>424,894.01<\/td>\n<\/tr>\n<tr>\n<td>166<\/td>\n<td>#,###.02<\/td>\n<td>166.02<\/td>\n<\/tr>\n<tr>\n<td>326.89<\/td>\n<td>#,###.03<\/td>\n<td>326.93<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>6. The Half-Width Exclamation Mark \u201c!\u201d or Backslash \u201c\\\u201d Symbol<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Forces the display of one character after an exclamation mark or backslash.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>0\\.0<\/code>\u00a0forces a decimal point before the last digit of the number.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>20<\/td>\n<td>0.0<\/td>\n<td>2.0<\/td>\n<\/tr>\n<tr>\n<td>424894<\/td>\n<td>0.0<\/td>\n<td>42489.4<\/td>\n<\/tr>\n<tr>\n<td>166<\/td>\n<td>0.0<\/td>\n<td>16.6<\/td>\n<\/tr>\n<tr>\n<td>326.89<\/td>\n<td>0.0<\/td>\n<td>32.7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>7. The \u201c@\u201d Symbol<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Applies format codes for text.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>@\u4e07\u6e2f\u5e63<\/code>\u00a0adds a suffix to each cell&#8217;s text.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\u4e8c\u5341<\/td>\n<td>@\u4e07\u6e2f\u5e63<\/td>\n<td>\u4e8c\u5341\u4e07\u6e2f\u5e63<\/td>\n<\/tr>\n<tr>\n<td>\u56db\u5341\u4e8c<\/td>\n<td>@\u4e07\u6e2f\u5e63<\/td>\n<td>\u56db\u5341\u4e8c\u4e07\u6e2f\u5e63<\/td>\n<\/tr>\n<tr>\n<td>\u597d\u591a<\/td>\n<td>@\u4e07\u6e2f\u5e63<\/td>\n<td>\u597d\u591a\u4e07\u6e2f\u5e63<\/td>\n<\/tr>\n<tr>\n<td>\u5187<\/td>\n<td>@\u4e07\u6e2f\u5e63<\/td>\n<td>\u5187\u4e07\u6e2f\u5e63<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>8. The \u201cM\u201d and \u201cH\u201d<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Represents hours and minutes.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>HH:MM<\/code>\u00a0displays the hour and minute portion of time.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>12:05<\/td>\n<td>HH:MM<\/td>\n<td>12:05<\/td>\n<\/tr>\n<tr>\n<td>0.1<\/td>\n<td>HH:MM<\/td>\n<td>02:24<\/td>\n<\/tr>\n<tr>\n<td>166:01:00<\/td>\n<td>HH:MM<\/td>\n<td>22:01<\/td>\n<\/tr>\n<tr>\n<td>9:09<\/td>\n<td>HH:MM<\/td>\n<td>09:09<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>9. The \u201cY\u201d, \u201cM\u201d, \u201cD\u201d<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Used to specify date formats.<\/li>\n<li><strong>Example<\/strong>: The format code\u00a0<code>DD\/MM\/YYYY<\/code>\u00a0displays dates.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>18691<\/td>\n<td>DD\/MM\/YYYY<\/td>\n<td>04\/03\/1951<\/td>\n<\/tr>\n<tr>\n<td>161661.16<\/td>\n<td>DD\/MM\/YYYY<\/td>\n<td>11\/08\/2342<\/td>\n<\/tr>\n<tr>\n<td>163<\/td>\n<td>DD\/MM\/YYYY<\/td>\n<td>11\/06\/1900<\/td>\n<\/tr>\n<tr>\n<td>11<\/td>\n<td>DD\/MM\/YYYY<\/td>\n<td>11\/01\/1900<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4>10. The Semicolon \u201c;\u201d Symbol<\/h4>\n<ul>\n<li><strong>Purpose<\/strong>: Used to separate different format sections.<\/li>\n<li><strong>Example<\/strong>: The structure of the format code is: positive format; negative format; zero format; text format.<\/li>\n<\/ul>\n<table>\n<thead>\n<tr>\n<th>Original Content<\/th>\n<th>Format<\/th>\n<th>Displayed Format<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>89<\/td>\n<td>[&lt;60]0 Not Qualified;[&lt;80]0 Not Bad;0 Good<\/td>\n<td>89 Good<\/td>\n<\/tr>\n<tr>\n<td>60<\/td>\n<td>[&lt;60]0 Not Qualified;[&lt;80]0 Not Bad;0 Good<\/td>\n<td>60 Not Bad<\/td>\n<\/tr>\n<tr>\n<td>50<\/td>\n<td>[&lt;60]0 Not Qualified;[&lt;80]0 Not Bad;0 Good<\/td>\n<td>50 Not Qualified<\/td>\n<\/tr>\n<tr>\n<td>11<\/td>\n<td>[&lt;60]0 Not Qualified;[&lt;80]0 Not Bad;0 Good<\/td>\n<td>11 Not Qualified<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3>Conclusion<\/h3>\n<p>By using these custom format codes, you can precisely control how data is displayed in Excel, enhancing the readability and professionalism of reports.<\/p>\n<p><em>A0007<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Excel&#8217;s custom format codes, special symbols c [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2445,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[8,29,20,9],"tags":[195],"class_list":["post-1929","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-format","category-onlinesupport","category-technique","tag-custom-format"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2024\/04\/Excel-new9.png?fit=2102%2C679&ssl=1","_links":{"self":[{"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/posts\/1929","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/comments?post=1929"}],"version-history":[{"count":7,"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/posts\/1929\/revisions"}],"predecessor-version":[{"id":2890,"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/posts\/1929\/revisions\/2890"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/media\/2445"}],"wp:attachment":[{"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/media?parent=1929"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/categories?post=1929"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fyndpro.com\/cn\/wp-json\/wp\/v2\/tags?post=1929"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}