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

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

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. If you really want to use this method, please check out this article.

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).

You want to know more about custom format codes in Excel? Great – we have summarized everything you need to know here!

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

## Format codes for thousands and millions in Excel

• Thousands number format:
`#,##0,;-#,##0,`
• Millions number format:
`#,##0,,;-#,##0,,`
• Billions number format:
`#,##0,,,;-#,##0,,,`

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

## 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:

• Select the cells you have formatted in thousands and millions and you want to get back to normal numbers.
• Press Ctrl + 1 on the keyboard. The Format Cells window opens.
• Go to the Number tab and select Number on the left-hand side. Define the desired number format.

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

• either not a number (text, e.g.)
• or not in thousands.

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 k, dividing or multiplying by 1000 might help.

## Expert tip: Number formatting with Professor Excel Tools

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.

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

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’

More than 35,000 users can’t be wrong.

Image by Eak K. from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

1. Aanchal says:

I am still unable to get million, billion format in excel. Under custom when i type above given formats, the number replaces to 1.

The only format that I can convert in custom is like this 10,00,000. I want to convert this to million format but can’t do it

2. Ayesha Mushtaq says:

2008-12
2.064B
213.00M
2.277B
3.784B
969.00M
4.683B
how to convert this data set into standard numbers i.e. in its original form in excel e.g. 2.064B into 2064000000

1. Henrik Schiffner says:

Hi Ayesha,
Thanks for the question. I’ve updated the article to include it. Does that help?
Best regards,
Henrik

3. murat says:

I’m looking for the exact answer, how could it be back to normal?

1. Henrik Schiffner says:

Hi murat,
Just select back the default number format. Select the cell and press Ctrl + 1 on the keyboard to open the Format Cells window. Go to the Number tab and select number on the left (second entry). Define your desired format here, it should revert back to absolute numbers.
Best regards,
Henrik

2. Henrik Schiffner says:

One more comment: I’ve updated the article and included a new section with a screenshot. Does that help?

4. nazia says:

5. vasanth says:

If not changing million to thousand format please go to control panel and search Region setting and Format and select ” English (India)” automaticly changing in excel sheet.

6. Saloni says:

Thank you so much for easy to understand article.

1. Henrik Schiffner says:

Hi Saloni,
Best regards,
Henrik

7. Leif LaWhite says:

So far so good on displaying thousands and millions, but what about a similar strategy for displaying thousandths (milli) and millionths (micro)? Any pointers?

8. Jai says:

can i display a number stored in millions to full value using formatting? For e.g. if the cell contains 0.01, using formatting, can I show it as 100,000?

9. i have a value in million and its show as 79.4 million not just “m” how i convert this value in thousand in excel.

10. Jennifer Huff says:

How can you add the currency to this custom number format? For example, I need to show in USD.