Enhance your spreadsheets in Excel

When it comes to keeping track of anything involving tables of information, Microsoft’s Excel spreadsheet is hard to beat. Some of these tasks do not require an attractive design. However you dress them up, tax calculations will not be fun, ­ but if you are sharing a spreadsheet with others for a fun purpose, such as a fantasy football league table, then you might want to brighten it up a little.

Fortunately, Excel can transform a bland screen of figures into something more attractive with different fonts and pictures, even colours that change as the figures do. In this article, we will show how to apply these formatting tips in Excel 2003 and 2007. The instructions for Excel 2003 should work with earlier versions and the free Openoffice Calc spreadsheet, too.

Excel 2007 has a new Live preview tool that is particularly handy. Formatting options are applied as soon as you move the mouse over them; move the mouse away from the option without clicking if you don’t like how it looks.

Plan ahead
When possible, it is best to try and set as much of the formatting and design of a spreadsheet as possible before you start entering text, numbers or other data. This is because some changes can alter the data held in cells or may delete it.

If you are working with an existing spreadsheet, it is best to make any changes to a copy: right-click on the worksheet tab at the bottom of the Excel window and left-click on Move or Copy. Select the option Create a copy and click on OK.

Excel 2003 has a Formatting toolbar that will be instantly familiar if you have used Word. There are dropdown menus for selecting font and text size as well as the usual bold, italic and underline icons.

The Merge cell function icon is not in the Word Formatting toolbar, but can be found in Word’s Table toolbar. It has an ‘a’ with an arrow pointing outwards on each side. This is very useful if a title needs to span several columns.

Click and drag a row or column of cells to select them and then click on this icon to turn them into one cell. Click on the icon again to remove the merge. Be careful not to use this on cells that are already full, as the contents of all but the leftmost or top cell will be lost.

A very useful tool when creating a spreadsheet is the Format Painter. This works just like copy and paste but it copies the way a cell looks rather than what is in it. Select the cell or range of cells with the formatting you want to copy, click on the icon of a paintbrush in the toolbar and then select either an individual cell or range of cells to apply the same formatting to them.

These are helpful shortcuts, but they only cover a small fraction of the formatting options. To see far more, select a sheet or cell, click on the Format menu and then choose Cells. Click on the Font tab to find all the font formatting options.

Excel 2007 has all the important formatting options in the Home tab of the Ribbon, which should be the first tab on view when you start Excel. The two icons next to the font size dropdown menu can be used to increase and decrease the font size in the selected cells. More options can be found in the Format Cells window that can be opened by pressing the Ctrl and 1 keys together.