Leading the way in Microsoft Office Development

Excel

Do you really need Formatting ?

Applying formatting to your worksheets will make them easier to read and interpret your data.

You can apply formatting to cells very quickly by selecting the cells or range of cells and choosing the appropriate commands

The (Format > Cells) dialog box gives you access to most of the formatting options including changing fonts, character formatting, cell alignment and applying borders and shading.

Borders are an easy way to separate and define different areas on a worksheet.

There are an enormous number of formatting options on the Formatting toolbar and the Format Painter is also very useful.

Using the Format Painter

This button can be found on the Standard toolbar and can be used to quickly copy the formatting from one part of a worksheet to another.

 Format Painter - This button copies formats from the selected cells, so they can be copied to other parts of the worksheet or workbook.

Just select the cells you want to copy and press the Format Painter button.

Select the cells or drag the painter over the cells you want to apply the formatting to.

It is possible to apply the formatting to non adjacent cells by double clicking on the Format Painter button.

Double click on the paint brush to copy a format to a selection of non-contiguous cells. Single click it afterwards to cancel.

Select the cells you want to copy the formatting from. Select the Format Painter button. The mouse pointer will change to a small paintbrush icon. Select the cells you want to copy the formatting to.

If you copy formats from a range of cells and then select a single cell before pasting, the Format Painter will paste the format from the entire range, with the selected cell being the top left cell.

If you select a range of cells before pasting, the Format Painter will paste the format into the highlighted area, repeating or truncating the format where necessary.

(Format > Cells) dialog box

You can apply formatting changes to a cell, a range of cells or even to a selection of characters within a cell.

This dialog box displays the options for formatting both text and numbers.

Number tab - This is discussed in detail in the Formatting Numbers section.

Alignment tab - Text on the left, numbers on the right.

Font tab - Font, Font style and colour.

Border tab - Borders can be used to separate and define different sections on your worksheet. You can specify different thicknesses and colours.

Patterns tab - Using shading and patterns can make your data dramatically easier to read.

Protection tab - This allows you to lock and prevent users from either changing values or viewing formulas. This is discussed in detail in the Protection section.

Formatting Individual Characters

If you select a cell any formatting will be applied to the whole cell and all its contents.

You can apply formatting to cells, ranges and even Font formatting to individual characters within cells.

To format characters within a cell, double click the cell (to enter edit mode). Highlight the characters you want to format. You will only be able to use the formatting options from the Font tab.

You can select individual characters or words and apply specific formatting.

Formatting as you type

It is possible to have some formatting applied automatically when you enter data although this is only applicable to numeric data.

This type of formatting is possible by using custom number formats.

When you enter numeric characters that represent one of the number formats that Excel recognises, the format is applied automatically.

This is discussed in more detail in the Formatting Numbers section.

Formatting toolbar

Allows you to alter the appearance and alignment of the data on a worksheet.

For more details on this toolbar, please refer to the Toolbar page.

Removing the Formats

You can quickly remove all the formatting associated with a range of cells by selecting the cells and selecting (Edit > Clear > Formats).

To quickly remove all the formatting from a cell or range of cells, highlight the cells and select (Edit > Clear > Formats).

To also remove the values, choose (Edit > Clear > All).

Things to Remember

When you copy or cut a cell, the formatting applied to that cell is also copied.

A cell that has been formatted will remain formatted until the formatting is either removed or a different format is applied.

You can toggle bold on the current selection by using the shortcut key (Ctrl + B).

You can toggle italics on the current selection by using the shortcut key (Ctrl + I).

The Format Painter lets you copy the formatting attributes from cells you have previously formatted to other cells.