Professor Excel

Thousands or Millions in Excel: How to Change the Number Unit

thousands, millions, excel, display, cell, format

When you calculate with large numbers in Excel, you might want to only show the values as thousands or millions. 2,000 should be shown as 2. Unfortunately, Excel doesn’t offer such option with a single click. In this article you learn how to display numbers as thousands or millions by creating a custom number format.

In a hurry? Copy this for thousands #,##0,;-#,##0,  and this for millions #,##0,,;-#,##0,, and paste it into the custom number format field. 

Ways of displaying thousands and millions

Display values as thousands or millions with just some simple clicks.

There are different ways for achieving your desired number format:

The first option would be dividing the results by thousands. This solution is easy to handle, but prone to errors.

A better way is not changing the values, but only displaying them as thousands. Therefore, you have to define a custom number format (the numbers are corresponding with the picture):

  1. Select the cells which you want to display in thousands.
  2. Open the format cell dialogue by pressing Ctrl + 1 or right-click on the cell and select “Format Cells”. On the “Number” tab, click on “Custom” on the left hand side.
  3. For “Type” write: #,##0,;-#,##0,  and confirm with OK.
    # and 0 are placeholders for numbers (0 is always shown, no matter if there is a digit to display, whereas # is only shown when there is a number) and , (comma) stands for thousand separators. If you want to add a decimal separator, add a . (dot).

Once you’ve set up your format, you can also use the increase and decrease decimal buttons for displaying more or less digits (4). To display millions, use this code: #,##0,,;-#,##0,,  (just one more comma).


Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...

Boost your Excel skills: Learn the best Excel tricks and tutorials!


Format codes for thousands and millions in Excel

If you work on a computer with a point as thousands separator: Replace the 3 commas of the billions number format with points.


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Revert thousands and millions back to normal numbers

Because this was asked multiple times in the comments, let me quickly show how to revert thousands and millions back to normal numbers. It’s actually quite straight-forward:

If this doesn’t work it probably means that the underlying number is

If it’s not a numeric value, you’ll probably need to work more on it (please see this article). If it’s just not in thousands, dividing or multiplying by 1000 might help.

Expert tip: Number formatting with Professor Excel Tools

Display number as thousands or millions with just one click.

Another way of quickly formatting number is provided with ‘Professor Excel Tools‘. The Excel add-in offers three buttons for one-click-formatting options:

A: You can either define your favorite number preferences within the settings or use the two pre-formatted buttons. That way, you don’t have to remember any codes but just select your cell ranges and click the button for the desired cell format.

Apply the ‘Thousands’ number format with just one click

B: Format as thousands with just one click. Select the numbers to format and click this button.

Apply the ‘Millions’ number format with just one click

C: Format as thousands with just one click. Select the numbers to format and click this button.

Try ‘Professor Excel Tools‘ for free and get to know all the amazing features. Download it by clicking the button below (no sign-up, no installation).


This function is included in our Excel Add-In 'Professor Excel Tools'

Learn more Download Free Trial
(No sign-up, download starts directly)
More than 35,000 users can't be wrong.


Exit mobile version