How to round in k$ with Excel?

How to round in k$ with Excel?
Last Updated on 06/09/2023
Reading time: 3 minutes

There are 3 techniques to round your numbers in k$ in Excel

  1. With the paste special

    Here, we will use the option Divide

  2. By Formula

    Divide all your number by 1000

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

  1. First of all, in your spreadsheet, place in an empty cell, the value 1000
  2. Copy the cell (here the cell F2)
  1. Select the range of cells where to apply the calculation (from B2 to D13)
  2. 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 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

custom format number to round your data in k$ in Excel
  • 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
Custom number format to round to the next thousandth

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)

Related articles

7 Comments

  1. person
    26/05/2022 @ 14:08

    Edited slightly to be better looking:
    [>=1000000]$ # ## 0.0,,"M";[>=1000] $ # ## #0.0,"K";$ # ## ##0.0

    Reply

  2. zoritoler imol
    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.

    Reply

  3. gdementen
    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

    Reply

  4. Dan
    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

    Reply

    • Frédéric LE GUEN
      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

      Reply

  5. Glenroy Carty
    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)?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to round in k$ with Excel?

Reading time: 3 minutes
Last Updated on 06/09/2023

There are 3 techniques to round your numbers in k$ in Excel

  1. With the paste special

    Here, we will use the option Divide

  2. By Formula

    Divide all your number by 1000

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

  1. First of all, in your spreadsheet, place in an empty cell, the value 1000
  2. Copy the cell (here the cell F2)
  1. Select the range of cells where to apply the calculation (from B2 to D13)
  2. 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 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

custom format number to round your data in k$ in Excel
  • 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
Custom number format to round to the next thousandth

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)

Related articles

7 Comments

  1. person
    26/05/2022 @ 14:08

    Edited slightly to be better looking:
    [>=1000000]$ # ## 0.0,,"M";[>=1000] $ # ## #0.0,"K";$ # ## ##0.0

    Reply

  2. zoritoler imol
    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.

    Reply

  3. gdementen
    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

    Reply

  4. Dan
    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

    Reply

    • Frédéric LE GUEN
      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

      Reply

  5. Glenroy Carty
    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)?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *