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 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 step.
- 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
=value/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 you can select 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)
26/05/2022 @ 14:08
Edited slightly to be better looking:
[>=1000000]$ # ## 0.0,,"M";[>=1000] $ # ## #0.0,"K";$ # ## ##0.0
09/05/2022 @ 13:23
I will right away grab your rss feed as I can not find your e-mail subscription link or e-newsletter service. Do you have any? Please let me know so that I could subscribe. Thanks.
09/05/2022 @ 22:27
Hi, the link to connect to the rss feed is https://excel-tutorial.com/feed/
19/01/2022 @ 16:28
Small remark : international convention for kilo is k lowercase. So your formula should be :
[>=1000000] # ## 0.0,,"M€"; [>=1000] # ## 0.0,"k€"; # ## 0.0
30/06/2020 @ 10:32
Bonjour,
Parametres regionaux UK
Si j'entre 36 987 456 j'obtiens "3 7.0M€"
Si j'entre 256 987 j'obtiens "25 7.0K€"
Une idee ? les parametres regionaux US et UK ne doivent pas etre si different ?
PS : j'utilise un clavier QWERTY
30/06/2020 @ 13:04
Pour moi, il n'y a pas d'erreur. Les chiffres sont bien arrondis aux millions et aux milliers.
Votre paramètre de mise en forme des nombres est juste
28/03/2018 @ 15:00
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)?