Home » Basic-Excel » Excel-Formatting

Search this site: Custom Search The use of Excel formatting can give your spreadsheet a professional look and can provide essential information about your data to the user of your spreadsheet. However, when using Excel functions, it is important to fully understand Excel formatting, because the way you see data in an Excel spreadsheet, is not necessarily the way Excel 'sees' the data. For example, in Excel, dates and times are stored as numbers, but when they are displayed in the cells of a spreadsheet, they have generally been formatted to look like dates.

Similarly, if you see a currency amount of $1,500.00 displayed in an Excel spreadsheet, this is generally stored in Excel as the number "1500". It is only the formatting of the cells that displays this number with the currency sign ($), the thousands separating comma, and the decimal places.

Accessing the Cell Formatting Options

There are several ways to access the Excel formatting options for a spreadsheet cell or range of cells. For all methods, click on the cell (or range) and then either:

• Use the Format Cells control box, which is accessed by any of the following 3 methods:
  
  
  • right-click on the selected cell or range and select the Format Cells ... option from the drop-down menu
    
    
  • Press CTRL-1 (ie. Select the CONTROL key and while this is depressed, select the "1" (one) key)
    
    
  • In Excel 2003: Select the Format menu from the top of the spreadsheet and then select the Cells option
    
    OR
    
    
  • In Excel 2007: Press CTRL-SHIFT-F (ie. Select the CONTROL key and while this is depressed, select SHIFT and "F")
    
    
• Use the buttons on the 'Format' toolbar. These are available for some Excel formatting options, such as text colour, background colour, applying Bold, Italics or Underlining to cell contents, or horizontal alignment of cell contents. However, this toolbar does not include all Excel formatting options.

Excel Data Types

Excel recognises the value of a cell as one of the following types:

• text
• a logical value
• a number

Text values will always have the same appearance inside a cell, because Excel recognises them as pure text, with no meaning. Logical values are displayed as TRUE or FALSE

Numbers, however, can be displayed in many different ways in an Excel Cell. For example, a value that is stored as a number within Excel may be represented as a date, a percentage, a currency, etc.

Although it appears to the user that there are several different types of data in your spreadsheet, it is only the Excel formatting of your number, that makes this single data type appear to have different attributes.

The spreadsheet below gives examples of different ways of formatting Excel cells containing numerical values.

Changing Cell Formatting

To change the Excel formatting of a cell's contents, enter the Format Cells control box, (using any of the 3 methods described above), and ensure the first tab, Number is selected.

From within the Number tab, select the Excel formatting Category that you want to apply to your cell. For most of the categories, this will cause further options to appear on the right hand side of the control box, which you can then select from.

Note that the listed Excel formatting types will only work with numbers, so if you apply a date format to a cell containing text, the appearance of the cell will remain unchanged.

Define Your Own Formatting Style

Within the Number tab of the Format Cells control box, you will notice the last formatting category is Custom. This option has a number of Excel formatting styles that you can select and either use as they are, or edit to produce your own formatting style.

To define your own formatting style select the Custom category and then, select one of the types from the menu on the right of the control box. The formatting definition of the selected type then appears in the 'Type' box, which you can then edit.

The following sections discuss each of the different Excel formatting options that can be used for displaying numbers:

Integer, Decimal & Currency Formatting

Formatting a number as an integer or decimal uses the following characters:

Other characters, such as the ",", "$", "£", "+", "-", "(" and ")" can be displayed at the start, middle and/or end of numbers, to make the number more readable, denote currency, or denote positive or negative values, etc. You can even replace the number completely with your chosen characters (eg. replacing a zero value with the text "NIL")

You can define one, two or three basic Excel formats for any one cell. If the cell contains a number, these formats are applied depending on whether the number is positive, negative or zero, as follows:

• If a single text format is supplied, this format is applied to all numbers;
• If two text formats are supplied, and separated by a semi-colon, the first format is applied to positive numbers (and zeros) and the second format is applied to negative numbers
• If three text formats are supplied, separated by semi-colons, the first format is applied to positive numbers, the second format is applied to negative numbers and the third format is applied to zero values.

Examples

The following examples show the effect of different Excel formatting definitions on the numbers 5198.34, -98.66667 and 0

Formatting Percentages

The formatting of percentages in Excel uses the "0", "#" and "." characters in the same way as the formatting of integers and decimals. However, when using the percentage format, Excel displays the number multiplied by 100 and followed by the % sign.

Examples

Some examples of the numbers 0.55555, -0.5 and 0, formatted as percentages are shown below:

Date & Time Formatting

When formatting a cell as a date, time or date and time, use the following characters:

Examples

The example below shows different Excel formatting definitions for the date and time '06:00AM on 25th August 2008':

Other Types of Excel Formatting

The Format Cells control box, contains a number of other cell formatting options, allowing you to define the alignment of cell contents, the size, style & colour of text, the cell borders and the colour and pattern of the cell background. These formatting options are self-explanatory and are best learned by experimentation.

Some Excel formatting options of particular use are described below:

Wrap Text

This option is controlled by a tick-box in the Alignment tab of the Format Cells control box. When ticked, this option forces the contents of the selected cells to 'wrap around', instead of being hidden or writing over adjacent cells. This is shown in the example spreadsheet below:

Merge Cells

The Merge Cells option is also controlled by a tick-box in the Alignment tab of the Format Cells control box. When ticked, this option forces the selected cells to merge together and be treated as one larger cell. This is shown in the example spreadsheet on the left.