Enhance your spreadsheets in Excel

Drawing the borders
Borders are a useful way of separating titles from data, or highlighting totals. Anyone who has worked in a finance department will probably want to use double underlines to indicate a total in a column of figures.

Excel 2003 has a combined icon and dropdown menu that sits third from the right in the Formatting toolbar. Use the dropdown menu to select a border type, and then click on the icon to apply it to any cells that are selected.

The same icon appears in Excel 2007 in the middle of the bottom row of icons in the Font section of the Home tab. The Border tab, found in the Format Cells window has all the available border options.

Although you will always know exactly what kind of information is held in a cell – a price, perhaps, or a percentage, or a phone number – Excel sometimes needs a little help.

Telephone numbers are a good example. We use these as text, but Excel assumes they are numbers, which means it will often remove any zeros from the beginning of the number. Sometimes it will even display the number in mathematical shorthand (how do you dial 1.2E+9?). The solution is to mark the cell’s contents as text by typing an apostrophe (‘) before the number.

Not all numbers are the same, of course. Marking a cell’s contents as Currency will add a pound symbol at the beginning of the cell entry and fix it to two decimal points. Choose Accountancy and it will line up all the cells in a column by the decimal point. Both can be found by right-clicking a cell then clicking Format Cells.

There are some shortcuts for cell contents in the Excel 2003 Formatting toolbar. The icon with a bank note will format the text as currency, the percentage icon does what you would expect and the comma adds commas for thousands and millions. The two icons to the right of the comma can be used to increase or decrease the number of decimal places shown in the cell.

The same icons can be found in the Number section of the Home tab in Excel 2007. This has a dropdown menu with more options, including currency and date. There is no real-time preview on this menu. Click on the More number formats at the bottom to see the Number tab in the Format Cells window that has a complete list of number types.

Percentages require a little more care, and it is best to format a cell’s content as a percentage before entering any data. Normally, changing the cell format will not change the contents, but this is not true of percentages. This is because 20 per cent as a decimal figure is 0.2. If you enter 20 in a cell to denote 20 per cent and format it as a percentage it will appear as 2,000 per cent.