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.

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

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

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

 
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

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

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

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

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