Excel > Excel 2007 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 negative numbers in a different colour.

Create a Custom Number Format

  1. Click the Home tab on the Ribbon
  2. Click the Dialogue Box Launcher for the Number group or press Ctrl + 1
  3. Opening the Format cells dialogue box

  4. The Format Cells dialogue box appears. Select Custom from the Category list of the Number tab
  5. Create a custom format
  6. Select a formatting type from the scrolling Type panel
  7. Edit the format to your requirements in the type box
  8. Click Ok and the format is added to the bottom of the list. The format can then be used again
  9. 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.

    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 

Microsoft tutorials