Excel > Excel 2003 Intermediate > Formatting a Worksheet

Custom Number Formatting

Creating custom number formats allow you to change the way that text and numbers are displayed. For example, displaying a prefix or suffix such as "lbs" or displaying negative numbers in a different colour.

Create a Custom Number Format

  1. Click Format > Cells or press Ctrl + 1
  2. Opening the Format Cells dialogue box

  3. The Format Cells dialogue box appears. Select Custom from the Category list of the Number tab
  4. Custom category within Format Cells

  5. Select a formatting type from the scrolling Type panel
  6. Edit the format to your requirements in the type box
  7. Click Ok and the format is added to the bottom of the list. The format can then be used again
  8. Custom number formatting sections

    Custom number formats are composed of four sections separated by a semicolon (;).Sections that are not included in the format will be displayed in the default style.

    Custom number formatting sections

    Custom number format symbols

    A variety of formatting symbols are used to specify its appearance. Each character in the format code represents something.

    The table below lists the most frequently used symbols.


    The pound currency symbol


    A digit if entered, left blank if no digit entered


    Commas between thousands and millions


    A zero if no digit entered (useful for leading and trailing zeros)


    (underscore) A space the width of the next character


    Font colour for the section (Must be placed at the beginning of the section)


    Negative values in brackets

    " "

    Custom text, for example, "lbs" or "zero"


    Text as entered (only applies to the last formatting section)


Follow us on

Facebook  Twitter  You Tube