Whether you've used Excel to show lists containing numbers, or to perform calculations, sooner or later you may want to change the exact way the numbers are displayed in cells. By default Excel aligns numbers to the right, but this may not always suit you. Custom number formatting allows you to choose very precisely how the numbers are displayed.

For example if you want to display a list of numbers showing two digits even though some only have one, such as 55, 73, 6 and 5, custom number formatting lets you show the numbers as 55, 73, 06 and 05.

Or you might want to show various numbers in a column with different decimal places, but all lined up on the decimal point. For example you want to show 12.1, 1.123 and 0.5 lined up vertically under the decimal point. Custom number formatting allows you to do this.

Another example might be if you may want to show positive numbers as normal, but negative numbers in brackets rather than with the usual minus sign. Custom number formatting lets you specify this.

To start using custom number formatting, highlight some cells with numbers, right click on the highlight , left click on Format Cells and choose the Number tab. Then in the Format Cells panel under category click on Custom. To the right, under Type click on the word General and delete it. Now you're all ready to add your own custom formatting to this box..

There are three symbols we are going to use to set our own custom number formatting, hash, #, the number 0, and the question mark, ?. Basically # specifies the maximum number of decimal places to show, the 0 specifies how many numbers to always show before and after the decimal point, and ? is used to add spaces to ensure numbers line up. We'll look at the # symbol first.

Type in these four numbers in a column, 5678, 123.1, 35.12 and 7.456. Now highlight the cells, right click, choose Format Cells and click on Custom as described above. Add the format #.## then click OK to complete.

This format means "show all the numbers before the decimal point, and up to two decimal places after the point, rounded up if necessary". Note: no decimals are shown if the number doesn't have any. So the numbers are displayed with correct digits before the point and up to two digits after the point.

Next we'll look at the zero symbol.

Start with the same four numbers but in new cells. This time after highlighting the cells, apply the custom formatting 0.00 and click OK to complete. This format means "always show at least one number before the point, and always show two decimal places after the point, rounded up to two places if necessary".

Note: all numbers are shown to two decimal places, and if they didn't have any Excel adds zeros. This results in a neat list with the numbers always showing the same number of digits after the decimal point.

This is actually the same as selecting two decimal places as you might do normally, so notice that Excel assumes this and switches the formatting to number, two places, in the formatting panel.

If you select the same cells again and change the custom formatting to 0000.00 then click OK to finish (because we remember one of the numbers has four digits before the point), zeros are added to the start of all numbers where necessary to make up to four digits before the point and two places always show after the point. Result is a neat list with all numbers showing the same number of digits before and after the decimal point.

In the example at the start of this article, to display 55, 73, 6 and 5 as two digits numbers, highlight the cells and apply the custom format 00 (with no decimal point). This formatting will display all numbers as two digits, with a lead zero added to single digit numbers. Again this is useful in organising neat displays.

The last symbol we'll look at is the question mark ?.

Start once again with the same four numbers in new cells. Highlight and apply the custom format 0.?? and click OK to complete. This means "show at least one number before the point and up to two decimal places after the point".

Note: if there aren't two numbers after the point Excel adds up to two spaces. The numbers then show with up to two decimal places or spaces to match, and the effect is that all numbers are lined up on the decimal point.


So far we have seen how to apply custom formats to positive numbers in Excel. We can in fact specify up to four different kinds of number formats separated by semicolons in the custom formatting box. The four parts are for positive numbers, negative numbers, zeros and text.

If we only specify formats for positive numbers, as we have done here so far, Excel assumes the same formats for negative numbers and zeros and the general format for text.

To show an example of specifying custom format for positive and negative numbers, add these new example numbers into new cells, 2.1, -6.3, 5.7, -2.6 and then highlight them.

Now create the custom format 0.00;(0.00) and click OK to complete. This format means "show at least one number before the point, and all numbers to two decimal places after the point for positive numbers, and the same but with brackets for negative numbers". So now all numbers are displayed to two places and negative numbers are displayed in brackets.

Before we finish, remember that when you delete cell contents, formatting is not deleted, so if you apply custom formatting then delete some numbers, and later add some more numbers, custom formatting will still be applied. This is why we place numbers in new cells in these exercises, to ensure we start with the default general formatting.

In future if you are faced with strange or complicated looking cell formatting in Excel, right click on one of the cells in question and select Format Cell to reveal any applied custom formatting.

Attending an Excel training course would be a good way to learn more about custom number formatting and help you demystify this topic further.