This option covers all the options to change the look of the data on your worksheet, it also carries one part of the two-part protection process to prevent data being changed on the worksheet. (The other part is detailed here).

Formatting cells alters the 'presentation' of cells on the worksheet, it does not alter the underlying values.  For example you could have the underlying value of 1.55499999 which you format to two decimal places so it shows as 1.55, but any calculations used on the cell will still use the full Excel precision of 15 decimals. You should be aware of this as if, for example, you have another cell with the same value (1.55499999) formatted to two decimals.  You now have two cells that look like they will produce a sum of 1.55+1.55=3.10, but with Excel using the full precision of the underlying data you get 3.11! To overcome this you can either use the ROUND function, e.g.. =ROUND(A1,2)+ROUND(A2,2), in your calculations or set the 'Precision as displayed' setting on the 'Calculation' tab under Tools>Options... (I wouldn't recommend this with anything other than sheets holding simple currency data).

The menu presents a dialog with six tabs, (as below). The explanation and detail of each follows.

Number Tab

This first tab has all the settings to format numbers, dates, times and text.  If none of the 'built-in' options, which are pretty self-explanatory and user-friendly,  give your data the look you need, then, as in the screenshot below, you can use a 'custom' format.

A custom format can have up to four parts, separated by semi-colons, (;). These parts are the formats for:

1. Positive Numbers
2. Negative Numbers
3. Zeroes
4. Text.

These formats are made up of special 'placeholders' which display data a certain way or reserve space for certain data. Here is not the place to go into every placeholder, as there are many, but these can easily be found in help under Custom Number Formats.

Here is the building of one example.

£#,##0.00 - This has a symbol (£) followed a placeholder for a number (#). The # placeholder displays a number or nothing if it is an insignificant zero. We then have the thousand separator, (,), followed by two further placeholders for numbers which will not show insignificant zeros, (##) The next placeholder is a zero (0), this will show either the number entered or an insignificant zero.  We have a further two place holders (00), that will show two decimal places or insignificant zeros if there is no entry. As there is only one element (positive numbers) to this format, positive/negative numbers and zeroes will be displayed the same. Some example of numbers using this format are:

If we now add to this a second section for negative numbers, like so £#,##0.00;[Red](£#,##0.00). We get the following results.

The difference here is that by adding a second part to the format, we are specifying what to do with negative numbers. In my case make them red ([Red]), there are several colours you can use but they are pretty basic.  We also added brackets to signify negative.  We could equally have added a leading or trailing minus sign.

Now lets add a third level to handle zeroes.

£#,##0.00;[Red](£#,##0.00);[Blue]£00.00

Our numbers will now look like so:

Note we added the colour blue and a further leading zero, which will now show as a zero even though it is insignificant. Lastly we will add a placeholder for text.

£#,##0.00;[Red](£#,##0.00);[Blue]£00.00;"Enter Numbers!"

You can see now that every time we enter any text, the text in the text portion of the format is displayed.

Custom formats are also a good way for example with weights of adding the measure but still being able to do calculations on the data, such as 0.00"kg", if you entered 1 in a cell would show as 1.00kg.

Formats, in this area also have the ability to use criteria so 'values over x are red and below y blue', etc, but you are better in these cases to use 'Conditional Formatting'.

A common request is for items such as part no's which have leading zeroes, such as 01234. When entered in Excel, Excel naturally sees the input as a number and dumps insignificant zeroes and you end up with 1234. Providing all part numbers are say five digits in length, a custom format of 00000 would show any leading zeroes up to a total of 5, so a part number of 00123 could be entered as 123 and still show the desired result. (00123). If they are multiple lengths you would need to pre-format the cells as text and this would hold any leading zeroes or prefix the entry with a single apostrophe ('). This would not show but would enter the data as text. Remember any data entered or formatted as text, will be 'naturally' left aligned and cannot have calculations performed on it, other than ones which work with text such as COUNTA, LEN, RIGHT, MID, LEFT, etc.

Dates and times

Dates and times in Excel can be confusing. In truth they are not, they just take an explanation and they are then pretty easy. Formatting is all that makes dates and times appear as such in Excel.  Dates are actually whole numbers from January 1st 1900, day 1 (providing you are using the default date system and not the 1904 date system under Tools>Options>Calculation). Today, (1st December 2004), is 38322! That is the 38,322nd day since 1st January 1900. Excel will format dates automatically providing they are entered in a format it recognises, with dashes or slashes in between. (01/01/2000 or 01-01-2000). Once you have entered a date, to prove the above theory, format the cell as 'general' and you will see the resultant date 'serial number'. Times are decimals of a day so, for example, 12 noon is half a day or .5! so midday today was 38322.5.

This allows fairly simply operations, such as adding days, etc. (e.g. 01/01/2001+7=08/01/2001). In time card applications it is also necessary to get Excel times to whole numbers to multiply them, for example, by an hourly rate, to do this you simply multiply the hour entered in Excel by 24, so  a finish time of 17:00 and a start time of 09:00 when subtracted from each other in Excel would give a time of 08:00 which is correct, if you then want to multiply that 8 hours worked by the hourly rate of £10.00 would naturally leave you with a result, when formatted as currency of £3.33 for the days work. (= unhappy workers!). To arrive at the correct answer, simply get the decimal 'hours' up to whole numbers.  There are 24 hours in a day, so =(Time_Result*24)*Hourly_Rate, would equal the correct £80.00. The same can be said of minutes, (need to be multiplied by 60 and then 24 (1440)) or seconds (Seconds*60*60*24 (86400))

 Formatting of times in Excel is also automatic if you enter them with colons(:) between, e.g. to enter thirteen minutes and thirteen seconds past three in the morning, you would enter 3:13:13 and Excel would understand it without any further formatting. If you wish to enter a time after noon, you can either enter in military, (24hr) time, e.g. 15:13:13 or post-fix the time with a space and then A or P (AM/PM).

Excels auto entry of dates and times will be based on the date settings in your windows control panel. But you can overcome this again, either by looking under the date/time options or by type a 'custom format', e.g. to display 01/01/2000 as Monday 1 January 2001, we would use dddd d mmmm yyyy. Experimenting will help and as you build any formula the dialog will show you an example of how the formula will look.

Lastly on times. You will occasionally find you are summing times and if hours go over 24 they will actually make a day so adding 12:00 (which Excel is holding as .5) and 12:00 will give you an answer of 1. One we remember is 1st January 1900, so adding those two times together will result in 01/01/00!!! To overcome this you use a custom format, either [h] or [hh]:mm:ss.00 (The last two zeroes actually show 10ths of a second!).  It's the [] that will stop Excel from incrementing hours over 24 to days. Likewise, you can use [m] or [mm] to stop minutes over 60 going to hours and [s] or [ss] to stop seconds incrementing to minutes.

Alignment Tab

From here you have the ability to line up your cell entries in Excel. Control in here improved dramatically in version 97, with the addition of 'Orientation'.

Horizontal alignment can be set to 'general'. This means the default alignment of right for numbers and left for text. You also have left, right, justified, and distributed, each of which can be customised by using the 'indent' spinner.  Each positive increment will move the text right and if using 'distributed' will increase the spacing between letters. 'Fill' will fill the current cell width with the text in the cell.

Vertical alignment allows you to move text to the top, centre, bottom, etc of the cell. This is obviously only effective if the row width is greater that the height of the font.

The 'Text control' allows you to 'wrap' text onto several lines, Excel will normally size the rows to fit when using this option. You can 'shrink' the text to fit the current cell width and 'merge' several cells together. This is great for 'heading' several columns but should be used with care as it renders a lot of features unusable, such as insert and also looses the intuitive view of what cell the actual entry is in. (That is, it may appear that the text is in E1 when actually it is in A1).

'Orientation' allows you to rotate the text through 180 with an increment of 1°.  Here is an example of the features in use.

Font Tab

This allows you to set the 'typeface' of the selected text/cell/cells. Most of this is self-explanatory, you can check  the 'Normal font' checkbox to quickly return to Excel's default font.

Border Tab

A confusion often reigns between borders and gridlines. Excels gridlines are only visible if the cell has no 'fill'. As soon as you do this the 'gridlines' will not show and the only way of imitating this is to apply borders. These are often also used in financial spreadsheets to make separator lines between totals. There are several 'frequently used' presets and a wysiwyg box allowing you to customise the borders of the cell(s) that have lines. (Top, bottom, left, right, diagonal, etc.)

You have control over the style, weight, colour and position of the line.

Patterns Tab

Sets the 'fill' for a cell or range of cells. You are very restricted currently as to the number of colours you can display in Excel (56), but these can be extended by use of 'patterns' in the drop-down.

Protection Tab

Protection in Excel is a two-stage process in Excel. All cells are 'locked' by default. If you then protect a sheet via 'Tools>Protection...', you will 'lock' users from editing all cells. If you uncheck this box on any cells in a sheet and then protect the worksheet, you will be able to edit these cells. Equally you will be able to navigate unlocked cells only using the tab key.

Selecting 'Hidden' on any cells will hide the formula from the formula bar. This is handy if:

1. You don't want users to see the underlying formula

2. You have a huge formula which when the cell is selected shows in the formula bar and obscures much of the worksheet.