Getting Started with Spreadsheets in Microsoft Excel - IT Panel Article 2005

This article aims to help beginners start using Excel to create spreadsheets. You might want to produce a simple expense or VAT account. Or perhaps a trial date is approaching, and the value of the claim is hard to assess because it depends on a number of variable factors, so that you would like a table of best and worst case scenarios from the point of view of each side, with the ability to change any figure and have the results instantly recalculated. Each of these functions is one that an Excel spreadsheet can readily perform for you, and many of the editing routines are the same or similar to those in Word.

Opening and Formatting the Workbook

First you open Excel (for example, you click on the Excel icon that you installed on your desktop following the instructions in the previous article). You find a blank table or 'workbook', with rows numbered and columns lettered. Each resulting box is called a "cell". Name and save the workbook (F12 is a useful shortcut to "Save As" in Excel as it is in Word). The next thing to do is to format the cells to show numbers the way you would like them to appear, perhaps with a comma separating each 3 numbers, and with up to 2 decimals. Press A to select all the cells, then go to the Toolbar and click on 'Format', 'Cells', 'Number', and select what you want. You can use the same routine for 'Format', 'Cells', 'Alignment' to determine whether the display within a cell is at the top, centre, bottom, right or left of the cell, or to merge cells, and you will probably want the text to 'wrap around' to form a second line in the cell. You may want to format some columns in different ways by highlighting (or blocking) the column or other section concerned, and proceeding as above.

Entering Text and Numbers

Now you need to enter text or numbers into the cells. Text without numbers is easy. You type in what you want, formatting in a particular font or size or in bold or italic exactly as in Word. However, unlike in Word, going back to a cell to change something will cause everything you did before in that cell to be obliterated, unless you first double-click on the cell, in which case you can simply amend. An example of text you might want to enter is in the best case/worst case scenario, using column A for successive item descriptions: claim for damages; liquidated claim; VAT thereon; subtotal; interest at 8%; and so on. You can leave a row between entries to make things clearer if you wish. If you want a number that is not to be manipulated, such as a paragraph number or a date, the first thing that you type in the cell is an apostrophe ' . So after leaving some space at the top of the spreadsheet for a heading, you could start cell A10 by typing:

'1. Damages claim

and so on; this will prevent the paragraph numbers being turned into decimals, or totalled!

Formula for Manipulation

Now comes the first clever bit. In the best case/worst case scenario, you may want to add to the damages a separate liquidated claim (to be entered in cell B13) with VAT (cell B15) to provide a subtotal (cell B17). To put a formula into a cell, you start with the = sign, identify the cells concerned, and for basic arithmetic, use + for adding, - for subtracting, * for multiplying and/for dividing.

For a subtotal in cell B17 you could type in cell B17: =B11+B13 but it is just as easy to add together all the figures in a column by highlighting (or blocking) them, and then going to the Toolbar and clicking on 'S'.

Using 'Edit' to add or delete rows or columns does not disturb the formulas you have set up, because Excel automatically adjusts to accommodate the additions and deletions.

Vat Examples

With VAT calculations, therefore, one way of calculating VAT from a net figure is to type into cell B15: =B13*0.175 (because multiplying by 0.175 gives you 17.5%)

To produce a VAT inclusive subtotal, you could type: =B13*1.175 (to give 100% plus another 17.5%)

To produce from a VAT inclusive subtotal the figure net of VAT you could type: =B15/1.175

To produce from a VAT inclusive subtotal the VAT payable you could type: =B15/1175*175

Copying

As in Word, C may be used for copying part or all or a number of cells, with V for pasting (and M for cutting). But now comes the second clever bit. Assume that in the best case/worst case scenario you have completed column B, and intend to use it for the Claimant's "best case". You want to reproduce the formulas that you have already entered, so as to use Column C for the Claimant's "worst case", Column D for Defendant's "best case", and so on. You highlight (or block) the cells that you want to reproduce. Then you move the cursor to the bottom right hand corner of the bottom most right cell, and when the cross appears, click on it and drag into the next column. Changing the figures in Column C will mean that they are manipulated as they would be in Column B, and so on.

Further Help

This article ought to be enough to get you started. To solve difficulties, or find out more, you can always use 'Help'.