Cloud Training (Online)




Custom Number Format in Excel

In this article, you’ll get to know how to apply Custom format cells in Microsoft Excel with few particular features like

  • Numbers with leading zeroes.
  • Control number of decimal places.
  • Add a string of characters.
  • Large numbers: Use one comma for thousands and use two commas to display millions.
  • Repeat characters.
  • Date and time.

Let’s take a look at them in detail. 

How to Custom format numbers with leading zeroes in Excel?

  • Select the cell you wish to format.
  • Open the Format dialogue box by using Ctrl+1 keys or right-click on the cell and select the Format cells option.
  • Once the dialogue opens up go to the Text option under Categories in the Number tab.
Custom number format in Excel
  • Click on OK.

When you enter a number starting with zero after formatting the cell Excel does not discard it.


Custom number format in Excel

You can format it in a different way as shown below too.

  • Select the cell.
Custom number format in Excel
  • Go to Custom option under the Categories select Special in the Number tab.
  • Select the format you want, it has special formats like Zipcode, Phone numbers, and Security numbers.
Custom number format in Excel
  • After selecting the format. Click on OK.
Custom number format in Excel

How to custom format to control the number of decimal places in Excel?

  • Select the cell.
Custom number format in Excel
  • Open format dialogue box.
  • Select Custom under Categories in the Number tab.
Custom number format in Excel
  • Select this format. Here the number of zeros after the decimal point means the number of digits you want after the decimal point.
Custom number format in Excel

For example, here we’ve entered three zeroes which means three decimal places after the point.

  • After selecting the format, Click on OK.
Custom number format in Excel

How to custom format to add a string after a number in Excel?

  • Select the cell.
  • Open format dialogue box.
  • Select Custom under Categories of the Number tab.
  • Select a format of your wish and include the ” ” after the number if you want to include a string of words.

Anything inside the ” ” gets displayed.

So here for example we’ve included “ft” after the format of the number.

Custom number format in Excel
  • After finalizing the format click on OK.
Custom number format in Excel

How to custom format to display Large numbers with commas in Excel?

  • Select a cell.
  • Open Format dialogue box.
  • Go to Custom under Categories in the Number tab.
Custom number format in Excel
  • Select this format to display large numbers like Thousands and Millions with commas.
Custom number format in Excel
  • Click on OK after selecting the format.
Custom number format in Excel

How to custom format to repeat characters in Excel?

  • Select a cell.
  • Open Format Dialogue box.
  • Go to Custom under Categories in the Number tab.
Custom number format in Excel
  • Select a format and use the asterisk * symbol to repeat characters. For example #*$ will be displayed as the number followed by the symbol $ on repeat.
  • Click on OK after setting the format right.
Custom number format in Excel

How to Custom Format Date and Time format in Excel?

  • Select a cell.
  • Open Format Dialogue box.
  • Go to Custom under Categories in the Number tab.
Custom number format in Excel
  • Select a format and click on OK.
Custom number format in Excel
  • Again open the format dialogue box and go to the custom option.
Custom number format in Excel
  • Select a format. and click on OK.
Custom number format in Excel

This is how you can apply custom format your cells in Microsoft Excel.