«

»

How to round in k$ without formula?

In many companies, it is difficult to present in a worksheet, big numbers. So, to avoid that, it is better to present  your figure rounded in kilo dollar (k$) or million (m$). But how to round in k$?

Round_K$_1

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

 

Paste Special

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

Round_K$_21. 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)

Round_K$_34. 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

Round_K$_4

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

Round_K$_5

Benefit: Simplicity of implementation
Drawback: Irreversible modifications of the raw data
Impossible to add the sign K$ ou M$
My opinion: Don’t use this technique because it is never allow to change the raw data.

Division by 1000

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

Round_K$_6To carry out this method, you simply integrate into each cell, a division by 1000

=value/1000

 

 

Benefit: The initial data are kept
Drawback: Too 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

There is a trick that allows you to change the display of your format numbers in kilo or million.

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

  • or 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

[>=1000]# ##0.0, ” K€”
  • The parameter [>=1000] indicates that only the values greater than 1000 will apply this format.
  • # ##0.0, The coma means that we round the value to thousands and the dot means that we keep on figure for the decimal
  • ” K$” indicates the symbol to add after the figure; don’t forget the double-quote

Round_K$_8

Now, if you want both present your values for thousands (kilo) and the millions, you go in the text box to the layout window numbers write the following formula

[>=1000000] # ## 0.0,, “M$”; [>=1000] # ## 0.0, “k$”; # ## 0.0

Do not forget to put two comas after the final 0 for million

 

Round_K$_10

You can put 3 conditions maximum in your custom format number

Leave a Reply