How to use Custom formatting in Excel

We use symbols to custom format numbers in Microsoft Excel.  We can use formats like “#”, “0” (zero), Comma “,”, “k”, etc.

0 (zero):- Zero is used to provide the number sequence in custom formatting as we cannot format each number. Microsoft Excel has declared that if we want to provide our own formatting to numbers we can create it using 0 (zero).

# (Symbol): - # symbol is used to format a number. A # symbol is used to place it instead of a number while using a custom format.

, (comma): - (comma) is used to separates the thousands in the number. The comma has a second use - if you place a comma at the end of the digits, the displayed number is shown in thousands.

“TEXT”: - We use the text format in the custom formatting to show text along with the numbers. For Example, if we want to show 4596 with one digit and represent the thousands using the word (k), we can use the “k” text in the number formatting.

Let’s take an example to understand the number formatting.

We have some data showing the income in which column D contains City Name, column E contains Gender, and column F contains Income in $. We want to format the income in thousands.

img1
 

To Round the income in thousands, follow the below given steps:-

  • Select the range F6:F16 and copy by pressing the key Ctrl+C  and paste in the range G6:G16 by pressing the key Ctrl+V on your keyboard

img2
 

  • Select the range G6:G16 and press the key Ctrl+1 on your keyboard.
  • The Format Cells dialog box will appear.

img3
 

  • In the Number Tab, Click on Custom.
  • After clicking on Custom, the related options will appear.

img4
 

  • In the type box write the format to round the number in thousands #,##0,
  • The function will return the rounded figure in thousands.

img5
 
To convert the Excel format in millions, follow below given steps:-
 

  • Select the range F6:F16 and copy by pressing the key Ctrl+C and paste in the range G6:G16 by pressing the key Ctrl+V on your keyboard

img6
 

  • Select the range G6:G16 and press the key Ctrl+1 on your keyboard.
  • The Format Cells dialog box will appear.
  • In the Number Tab, Click on Custom.
  • After clicking on Custom, the related options will appear.

img7
 

  • In the type box, write the format to round the number in millions$#.##,,"M";
  • The function will return the rounded figures in Millions.

img8
This is how we can use the symbols to create some custom formatting.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

Comments

  1. Sunny @ PNR Status

    It's my pleasure bro. If someone is doing great job, i think it's our responsibility to appreciate his work. I hope, i am not wrong.

  2. Sunny @ PNR Status

    I was trying to add zero in my excel but it’s not happening. I was quite frustrated and i start searching the way to do it and luckily i got this page. I have did according to your described way and it’s working. Thanks for sharing such a valuable resource.

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.