How to round in k$ without formula?

Write your numbers in K$

In Excel, you can easily display your numbers in kilo dollars K$ or million dollars (M$) with 3 methods.

In this article you will find 3 techniques to display a result in K$ (or m$ for the millions]. Each technique have their own benefits and disadvantages.

The last one is my favorite 😃😍

Paste Special

This method uses the Paste Special option division. Its implementation is really simple.

1. First of all, in your spreadsheet, place in an empty cell, the value 1000

2. Copy this cell

3. Select the range of cells to be transformed (from B2 to D13)

4. Call the dialog box Paste Special with the shortcut CTRL + ALT + V or by the calling this option in the menu HOME > Paste Special

5. Select the option Divide

6. The result appears immediately in your document and all the figures have been divided by 1000

GoodSimplicity of implementation
BadIrreversible modifications of the raw data
Impossible to add the symbol K$ ou M$
My opinion:Don't use this technique because it will change your raw data.

Formula division by 1000

This is the most used method to transform your value in k€ or k$ but it is far from the best method.

To carry out this method, you simply integrate into each cell, a division by 1000

=value/1000

GoodThe initial data are kept
BadToo much time to implement the formula cell by cell.
My opinion:Even if this technique is the most popular, it takes to much time to apply this formula to your all cells and also there is a risk to forget to apply the formula in some cells.

Customize the format number

Custom format

You can also create a custom format number to display your values in K$

To apply this method, you just have to select a cell containing the value to be changed and change the format number of this cell.

Call the numbers formatting window

  • By using the shortcut CTRL + 1
  • Or by selecting the Home Menu> Number> More Number Formats ...

In the dialog box, select in the left part of Custom and type in the text box, you enter the code with a COMMA after the last 0 ⚠

[>=1000]# ##0.0," K$"

Now, if you want both present your values for thousands (kilo) and the millions, this time you put 2 commas after the last 0.

[>=1000000] # ## 0.0,,"M€"; [>=1000] # ## 0.0,"K€"; # ## 0.0

Explanation

  • The parameter [>=1000] indicates that only the values greater than 1000 will apply this format.
  • # ##0.0, The comma after the 0 means that we round the value to thousands
  • " K$" indicates the symbol to add after the figure; don't forget the double-quote

You can have maximum 3 conditions in your custom format number

Local setting

If you don't work with the same settings than US, the sign to round for thousand is not a comma but a space. For instance, for Spanish, you must write this code.

[>=1000]# ##0.0 " K$"

Conclusion

GoodNo transformation of the data
BadMaximum 3 conditions (not possible to have negatives and positives in the same time).
My opinion:That is the technique to use 😍😎.

Related posts


Permanent link to this article: https://www.excel-exercise.com/how-to-round-in-k-without-formula/


1 comment

    • Glenroy Carty on 28/03/2018 at 15:00
    • Reply

    Thank you. This was very helpful.
    However, I am struggling with negative values.
    Say your result is -250000, how do I show it as (250)?

Leave a Reply

Your email address will not be published.