There are 3 techniques to round your numbers in k$ in Excel
- With the paste special
Here, we will use the option Divide
- By Formula
Divide all your number by 1000
- Create a custom Number Format
Create your own number format to your numbers in K$
In this article, you will discover 3 techniques to round your result in k$ (or m$ for the millions) in Excel. Each technique has its own benefits and disadvantages.
#1 Use the Paste Special tool
This technique uses the Paste Special option division. Its implementation is really simple. Have a look at the video to see the technique or follow each steps.
- First of all, in your spreadsheet, place in an empty cell, the value 1000
- Copy the cell (here the cell F2)
- Select the range of cells where to apply the calculation (from B2 to D13)
- Call the dialog box Paste Special with the shortcut CTRL + ALT + V or the 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
- Good 😃: Simplicity of implementation
- Bad 😟: Irreversible modifications of the raw data (not good⛔)
#2: 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
- Good 😃: The initial data are kept
- Bad 😟: Too much time to implement the formula cell by cell
#3: Customize the format number
The best technique to round your number to k$ in Excel is to create a custom format number.
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
- 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
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$"
- Good 😃: No transformation of the source
- Bad 😟: Maximum 3 conditions (not possible to have negatives and positives in the same time)