An Introduction to Microsoft Excel – Part 3

Now that we can open Excel and input data, let’s have a look at formatting and in particular number formats.

Excel has quite a wide range of number formats available, but there are some limitations. Accordingly, you may need to create custom formats to suit your own needs.


Custom Formats

Custom formats can be viewed by pressing Ctrl+1, then select the Number tab and choose Custom from the list.



Let’s now look at the constituent parts of a format and what the various symbols mean.

There are four sections to a format – positive numbers, negative numbers, zero values and number+text. The separator between each section is a semi-colon (;).


The following symbols are used to create a custom format:

0 (zero) – this displays any digit in a cell, including 0.

For example, 0.00 will display a number rounded to 2 places after the point. In the following example, cell A1 has a custom format of 0.00 while cell B1 does not.


In both cases the value 1.558 was entered. You can see how the custom format has rounded up the value to 1.56.


# (hash) – this displays significant digits but does not display insignificant zeros.

For example, in each of the following cells, the input was .5. The format style of each cell is shown underneath.



, (comma) – this is used as a separator.

For example, in each of the following cells, the input was 12345. The format style of each cell is shown underneath.

You can also add a comma at the end of the digits. This will have the effect of dividing the number by 1000 for each comma. In each of the following cells, the input was 125,000,000.



* (Asterisk) – this fills in empty characters, up to the beginning of the number.

For example, 12345 is displayed with a pound sign (£) on the left and the numbers on the right.


You can also use colour in your formats – but note that there are only 8 colours available for this. This is done by adding the colour name in square brackets to your format.

For example, this format shows values greater than 2000 in Blue, positive values less than 2000 in black, and negative values in Red.



While this article cannot possible cover all the available formatting options, it has hopefully given you some ideas upon which you can build and extend your own knowledge and thus create custom formats to suit your own needs.