{"id":1190,"date":"2020-08-11T20:39:04","date_gmt":"2020-08-11T18:39:04","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1190"},"modified":"2024-05-04T09:05:14","modified_gmt":"2024-05-04T07:05:14","slug":"excel-number-formats-thousands-millions","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-number-formats-thousands-millions\/","title":{"rendered":"Excel Custom Number Format Millions and Thousands"},"content":{"rendered":"<h3><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Excel-Custom-Number-Format-Millions-and-Thousands.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-33517\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Excel-Custom-Number-Format-Millions-and-Thousands-1024x576.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Excel-Custom-Number-Format-Millions-and-Thousands-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Excel-Custom-Number-Format-Millions-and-Thousands-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Excel-Custom-Number-Format-Millions-and-Thousands-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Excel-Custom-Number-Format-Millions-and-Thousands.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" title=\"\"><\/a><\/h3>\n<h3>Excel custom number format Millions and Thousands<\/h3>\n<p>Many times, you might have large numbers in an <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\">Excel report<\/a> and it is hard to decipher and read the number at one glace.<\/p>\n<p>The best way is to show the numbers in Thousands (K) or Millions (M).<\/p>\n<p>Say, a number 45,200,000 will be displayed as 45.2 Million.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-474.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-18953 size-full\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-474.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"348\" height=\"281\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-474.png 348w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-474-300x242.png 300w\" sizes=\"(max-width: 348px) 100vw, 348px\" \/><\/a><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-number-formats-thousands-millions\/#Custom_Formatting\" title=\"Custom Formatting\">Custom Formatting<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-number-formats-thousands-millions\/#With_Placeholder_Pound_Sign\" title=\"With Placeholder Pound Sign (#)\">With Placeholder Pound Sign (#)<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-number-formats-thousands-millions\/#Using_Placeholder_Zero_0_Decimal_Point\" title=\"Using Placeholder Zero (0) &amp; Decimal Point\">Using Placeholder Zero (0) &amp; Decimal Point<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-number-formats-thousands-millions\/#ROUND_Function\" title=\"ROUND Function\">ROUND Function<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-number-formats-thousands-millions\/#Conclusion\" title=\"Conclusion\">Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Custom_Formatting\"><\/span><strong><a id=\"custom-formatting\"><\/a>Custom Formatting<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Fortunately, large numbers in Excel can be formatted so they can be shown in &#8220;Thousands&#8221; or &#8220;Millions&#8221;.<\/p>\n<p>By using the <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/format-cells-special-numbers-excel\/\" target=\"_blank\" rel=\"noopener\">Format Cells<\/a> dialogue box shortcuts CTRL+1<\/strong>, you will need to select CUSTOM and then enter one comma to show Thousands or two commas to show Millions. You can <strong>even add some text<\/strong> in your cells by entering any word within the quotation marks <em>&#8221; your word &#8220;.<\/em><\/p>\n<p>But before we move forward, it is important to know that <strong>certain characters in custom formatting have specific meaning<\/strong>.<\/p>\n<ul>\n<li><strong>Zero (0)<\/strong> &#8211; Display insignificant zeros<\/li>\n<li><strong>Pound Sign (#)<\/strong> &#8211; Display significant zeros<\/li>\n<li><strong>Comma (,)<\/strong> &#8211; Thousand separator<\/li>\n<li><strong>Quote (&#8221; &#8220;)<\/strong> &#8211; Add text present within the quotes<\/li>\n<\/ul>\n<p>You can create <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-custom-number-format\/\" target=\"_blank\" rel=\"noopener\">Excel Custom Number Format<\/a> Millions and Thousands using either placeholder zero or pound sign. Let&#8217;s look at both of them one-by-one.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"With_Placeholder_Pound_Sign\"><\/span><b><a id=\"with-placeholder-pound-sign\"><\/a>With Placeholder Pound Sign (#)<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>#,##0<span style=\"color: #ff0000\"><strong>,<\/strong><\/span> &#8220;ths&#8221;<\/p>\n<p>#,##0<span style=\"color: #ff0000\"><strong>,,<\/strong><\/span> &#8220;mills&#8221;<\/p>\n<p>In the example below, you have sales data with the sales amount mentioned in columns D &amp; E. Using the Excel number formatting, you need to convert <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/custom-number-formats-in-excel\/\" target=\"_blank\" rel=\"noopener\">excel format number<\/a> in millions &amp; thousands.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-458.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18935\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-458.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"838\" height=\"506\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-458.png 838w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-458-300x181.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-458-768x464.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>Follow the <strong>step-by-step tutorial<\/strong> to understand how to add <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/million-format\/\" target=\"_blank\" rel=\"noopener\">Excel custom number format millions<\/a> and thousands and make sure to <strong>download this workbook<\/strong> to follow along:<\/p>\n<p><strong><span style=\"text-decoration: underline;color: #0000ff\"><a class=\"IRPP_minimalist icon-download\" style=\"color: #0000ff\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/08\/Number-Formats-Ths-Mills.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download workbook<\/span><span class=\"postTitle px-3\">Number-Formats-Ths-Mills.xlsx<\/span><\/a><\/span><\/strong><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong><\/span> Select <strong>Column D<\/strong> in the data below.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18936\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"781\" height=\"461\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459.png 781w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459-300x177.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459-768x453.png 768w\" sizes=\"(max-width: 781px) 100vw, 781px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 2:<\/span> <\/strong>Press <strong>Ctrl + 1<\/strong> to open the Format Cells dialog box.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-460.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18937\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-460.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"531\" height=\"485\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-460.png 531w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-460-300x274.png 300w\" sizes=\"(max-width: 531px) 100vw, 531px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 3:<\/span> <\/strong>In the Format Cells dialog box, Under Number Tab select <strong>Custom.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-461.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18938\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-461.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"531\" height=\"483\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-461.png 531w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-461-300x273.png 300w\" sizes=\"(max-width: 531px) 100vw, 531px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 4:<\/span> <\/strong>Type <strong>#,##0<span style=\"color: #ff0000\">,<\/span> &#8220;ths&#8221;\u00a0<\/strong>and Click <strong>OK<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-462.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18939\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-462.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"533\" height=\"487\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-462.png 533w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-462-300x274.png 300w\" sizes=\"(max-width: 533px) 100vw, 533px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>STEP 5: <\/strong>This is how the Column D after <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/pivot-table-number-formatting\/\" target=\"_blank\" rel=\"noopener\">number formatting<\/a> will look &#8211;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-463.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18940\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-463.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"868\" height=\"465\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-463.png 868w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-463-300x161.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-463-768x411.png 768w\" sizes=\"(max-width: 868px) 100vw, 868px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 6: <\/span><\/strong>Follow the same steps for Column E as well and type <strong>#,##0<span style=\"color: #ff0000\">,,<\/span> &#8220;mills&#8221;<\/strong> under the custom section.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-464.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18941\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-464.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"851\" height=\"462\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-464.png 851w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-464-300x163.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-464-768x417.png 768w\" sizes=\"(max-width: 851px) 100vw, 851px\" \/><\/a><\/p>\n<p>The only difference between the two customer format (Thousands &amp; Millions)\u00a0 is that you have to put <strong>1 comma for Thousands and two commas for Millions<\/strong>.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Using_Placeholder_Zero_0_Decimal_Point\"><\/span><strong><a id=\"with-placeholder-zero\"><\/a>Using Placeholder Zero (0) &amp; Decimal Point<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>0.0<span style=\"color: #ff0000\"><strong>,<\/strong><\/span> &#8220;K&#8221;<\/p>\n<p>0.0<span style=\"color: #ff0000\"><strong>,,<\/strong><\/span> &#8220;M&#8221;<\/p>\n<p>&nbsp;<\/p>\n<p>Zero is used to display insignificant zeros when the number has fewer digits than the format represented using zero. For example, a custom format 0.00 will display number 5, 8.5, and 10.99 as 5.00, 8.50, and 10.99 respectively.<\/p>\n<p>Also, you can <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/10-1-ways-to-round-numbers-in-excel\/\" target=\"_blank\" rel=\"noopener\">round off the number<\/a> using decimal points symbol.<\/p>\n<p>&nbsp;<\/p>\n<p>To get this formatting done, follow the steps below:<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong><\/span> Select <strong>Column D<\/strong> in the data below.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18936\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"781\" height=\"461\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459.png 781w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459-300x177.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-459-768x453.png 768w\" sizes=\"(max-width: 781px) 100vw, 781px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 2:<\/span> <\/strong>Right-Click and then Select <strong>Format Cells<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-465.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18946\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-465.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"779\" height=\"467\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-465.png 779w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-465-300x180.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-465-768x460.png 768w\" sizes=\"(max-width: 779px) 100vw, 779px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 3:<\/span> <\/strong>In the Format Cells dialog box, Under Number Tab select <strong>Custom.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-461.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18938\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-461.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"531\" height=\"483\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-461.png 531w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-461-300x273.png 300w\" sizes=\"(max-width: 531px) 100vw, 531px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 4:<\/span> <\/strong>In the Type section, type format &#8211; <strong>0.0, &#8220;K&#8221;\u00a0<\/strong>and click <strong>OK.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-467.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18949\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-467.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"531\" height=\"486\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-467.png 531w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-467-300x275.png 300w\" sizes=\"(max-width: 531px) 100vw, 531px\" \/><\/a><\/p>\n<p><em>Follow the same process for formatting Numbers in Millions.<\/em><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 4:<\/span> <\/strong>In the type section, Enter <strong>0.0,, &#8220;M&#8221;<\/strong> and Click <strong>OK<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-468.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18950\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-468.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"532\" height=\"484\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-468.png 532w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-468-300x273.png 300w\" sizes=\"(max-width: 532px) 100vw, 532px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/display-0-5-as-a-fraction\/\" target=\"_blank\" rel=\"noopener\">Excel number format<\/a> millions &amp; thousands is now ready!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-470.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18951\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-470.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"752\" height=\"472\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-470.png 752w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-470-300x188.png 300w\" sizes=\"(max-width: 752px) 100vw, 752px\" \/><\/a><\/p>\n<p>One thing to note is that this will<strong> just format the way the number looks<\/strong> like on the Sheet. The number stored in the cell remains the same!<\/p>\n<p>You can use the ROUND function to not just change the formatting but the change the number as well.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"ROUND_Function\"><\/span><strong><a id=\"round-function\"><\/a>ROUND Function<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In this method, you have to do three things :<\/p>\n<ul>\n<li>Divide the number by 1000,000.<\/li>\n<li>Round off the decimal places.<\/li>\n<li>Use &amp; sign to add text &#8220;M&#8221;.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>In this example, you have the sales amount mentioned in Column D. Let&#8217;s use the combination of division, round, and &amp; sign to get the formatting done.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/7c1d483c454f8b13c345258f4632118d.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18958\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/7c1d483c454f8b13c345258f4632118d.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"607\" height=\"481\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/7c1d483c454f8b13c345258f4632118d.png 607w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/7c1d483c454f8b13c345258f4632118d-300x238.png 300w\" sizes=\"(max-width: 607px) 100vw, 607px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 1:<\/span> <\/strong>Select cell <strong>E7<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-479.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18959\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-479.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"626\" height=\"497\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-479.png 626w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-479-300x238.png 300w\" sizes=\"(max-width: 626px) 100vw, 626px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 2:<\/span> <\/strong>Start with the <strong>division<\/strong>. Type <strong>=D7\/1000000<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-480.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18960\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-480.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"691\" height=\"472\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-480.png 691w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-480-300x205.png 300w\" sizes=\"(max-width: 691px) 100vw, 691px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 3:<\/span> <\/strong>Add <strong>Round<\/strong> Function to this. Type <strong>=ROUND(D7\/1000000,1)<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-481.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18961\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-481.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"692\" height=\"473\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-481.png 692w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-481-300x205.png 300w\" sizes=\"(max-width: 692px) 100vw, 692px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 4:<\/span> <\/strong>Add <strong>Text<\/strong> to this formula using &amp; sign. Type <strong>=ROUND(D7\/1000000,1)&amp;&#8221; M&#8221;<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-482.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18962\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-482.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"692\" height=\"470\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-482.png 692w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-482-300x204.png 300w\" sizes=\"(max-width: 692px) 100vw, 692px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 5:<\/span> <\/strong>Copy the <strong>formula down<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-483.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18963\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-483.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"614\" height=\"470\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-483.png 614w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-483-300x230.png 300w\" sizes=\"(max-width: 614px) 100vw, 614px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 6:<\/span> <\/strong>Copy the Column and the Press <strong>Alt + E + S<\/strong> to open the <strong>Paste Special Box<\/strong> and Select <strong>Value<\/strong>. Click <strong>OK<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-484.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18966\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-484.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"398\" height=\"328\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-484.png 398w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-484-300x247.png 300w\" sizes=\"(max-width: 398px) 100vw, 398px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>The only <strong>difference between using Custom Format &amp; Round Function<\/strong> is that :<\/p>\n<ul>\n<li>In<strong> Custom Format<\/strong>, only the formatting changes but the number stored remains the same.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-486.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18964\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-486.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"754\" height=\"274\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-486.png 754w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-486-300x109.png 300w\" sizes=\"(max-width: 754px) 100vw, 754px\" \/><\/a><\/p>\n<ul>\n<li>In <strong>Round Function<\/strong>, both the formatting and number changes.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-485.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18965\" title=\"Excel Custom Number Format Millions and Thousands\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-485.png\" alt=\"Excel Custom Number Format Millions and Thousands\" width=\"692\" height=\"248\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-485.png 692w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Screenshot-485-300x108.png 300w\" sizes=\"(max-width: 692px) 100vw, 692px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span><strong><a id=\"conclusion\"><\/a>Conclusion<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In this article, you have been taught <strong>how to do <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\">Excel<\/a> custom number format millions and thousands<\/strong>. There are two ways to do so. You can either use <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/custom-date-formats-in-excel\/\" target=\"_blank\" rel=\"noopener\">custom format options available in Excel<\/a> or use a<strong> combination of division, round, and &amp; sign<\/strong>.<\/p>\n<p>There are various analytical tools inside Excel, like <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/conditionally-formatting-a-drop-down-list\/\" target=\"_blank\" rel=\"noopener\">Conditional Formatting<\/a>, Data Validation, Tables, Sort &amp; Filter plus MORE!\u00a0 <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/analysis\/\" target=\"_blank\">Click here to learn more<\/a><\/strong>!<\/p>\n<p>You can learn more about Excel with <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/\" target=\"_blank\" rel=\"noopener\">Formulas<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/pivot-tables\/\" target=\"_blank\" rel=\"noopener\">Pivot Tables<\/a>, and <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/macros\/\" target=\"_blank\" rel=\"noopener\">Macros<\/a> here!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Excel custom number format Millions and Thousands Many times, you might have large numbers in an Excel report and it is hard to decipher and read the number at one glace. The best way is to show the numbers in Thousands (K) or Millions (M). Say, a number 45,200,000 will be displayed as 45.2 Million. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":33517,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Excel Custom Number Format Millions and Thousands","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[6,41],"tags":[88,76],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1190"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=1190"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1190\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/33517"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=1190"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=1190"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=1190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}