SoSLUG Archive

Spreadsheets

Working with Spreadsheets, cells and formulas

Recent experience with spreadsheets made me think it might be useful to explain to non users just how easy it is to use the features that are available with spreadsheets to log whatever data you wish. To show how brilliant spreadsheets can be I propose to explain how they can be used to keep track of say your bank account with a fully automated balance.

However, I should mention at the outset that I am using Ubuntu 10.10 and spreadsheets within Open Office, both of which are open source. Nevertheless, this workshop will apply to those of you using Open Office with a Windows OS.

OK, to get started, from the Ubuntu desktop, select Applications, Office and then Open Office.org – Spreadsheets. This will produce the spreadsheet shown as Figure 1 in which there are provided multiple cells in columns denoted by the letters A,B,C……. and rows denoted by number 1,2,3…. . The cell A1 is selected by default as shown by the bold line surrounding the cell. You will see this for any chosen cell, try clicking the cursor in any cell.

Figure 1

spreadsheet-Fig1

Above the column headings there are two boxes and in the first of these you will see A1 indicating the identity of the cell which is highlighted. The second, much longer, box is the where you may edit the wording you wish to appear in a selected cell and also where you can generate any formula you wish to apply to a cell.

It is possible to type directly into a cell but when you wish to edit an error you may well have to retype what you have written or, for example, when moving the cursor using the up, down, left, right keys you simply select adjacent cells. Therefore, having selected a cell, the second box is where you enter what you require to be in that cell. I will call this box the Edit Box for the purpose of this workshop.

With cell A1 selected type into the Edit box the name of the account (for example Simple Simon Account). Typing this wording causes the wording to extend into column B but before making any adjustment highlight the wording and using the Tool Bar above the Edit box make the wording show in Bold and, if you wish, increase the size of the text. The result is that the wording now extends into column C. To overcome this place the cursor on the line separating the headings A and B and when the cursor changes to a horizontal line with an arrowhead at each end hold down the left mouse button and pull the line to the right until the wording is cleared. Let go of the mouse button and you have a wider A cell column than the other columns.

One alternative to the method just described is to click on cell A1to highlight the cell, hold the left mouse button down and drag it across to the H1 cell (because this is the full width of the cell range used in this workshop).Release the mouse button and type the title (what will become the file name) into the Edit box, make the text Bold and centralize the text if you wish.

Now select A3 and insert the words Account No. while in cell B3 you type in the actual number of the account. Select A4 and insert Sort Code. In B4 insert the actual sort code. In A5 insert the word Branch while in B5 insert the actual name of your branch. If any of these entries extend into column C simply widen column B as explained above for column A .

Now perhaps select A10 and insert there Account Activity. It is suggested you put this in bold type with an increased text size so that it stands out. Then in cells C11 toH11 type in the details for the individual cells as shown in Figure 2. To make the individual headings in bold type and centralized within their individual cells, either click in the individual cell and then click the Centred button followed by the Bold button in the Tool Bar above the Edit Box. However, where all info in a column can be centred highlight the column by clicking it’s heading, then the Centred button, Figures 3 & 4.

Figure 2

spreadsheet-Fig2

Figure 3

spreadsheet-Fig3

Figure 4

spreadsheet-Fig4

Alternatively, where you wish to type in the information shown in Figure 2 for cells C11 to H11, first type the information in the cells, highlight cell C11 and drag the highlight along row 11 to cell H11. You can then apply, the Centred and Bold buttons as well as increase/decrease the text size to all the highlighted cells for consistency and all selected cells will change to the form shown in Figure 2 at the same time.

Now we turn to the more interesting part of the process and that is formatting the cells to do what you want. Columns A and B simply list information and the cells in these columns do not need to be formulated other than to align the text to the left for column A, and centred for column B. To do this simply click in the space where the column letter is found to highlight the column. Then select the appropriate alignment from the Tool Bar above, Figure 3. The Date column C can have a centralised date so highlight column C and click the Centred button from the Tool Bar above, Figure 4.

However, we all tend to write the date in a different way so what is needed is to standardize the date form irrespective as to how we type it into a cell. This can be achieved by right clicking in the cell column heading where C appears. Two things happen, firstly the C column is highlighted and secondly a sub-menu appears in which you select “Format cells” , Figure 5. You will then see a Format Cells window Figure 6 in which ,under Category, you select Date. Under Format you select the particular format which suits you and then click OK. Now, however you enter the date, as soon as you select another cell, the date will appear in the format and cell you have chosen.

Figure 5

spreadsheet-Fig5

Figure 6

spreadsheet-Fig6

Moving on to the D and E columns it is necessary to have the description start at the left hand side of the cells, so highlight cell D12 beneath the wording Payment Type and drag the highlight across to E12 and down for as many cells as you feel is necessary for your purposes and click on the left alignment button in the Tool Bar, Figure 7. Again repeat for columns F,G,and H, although in this case have the text align from the right hand side of the cells because these cells will display monetary values.

Rather than set the columns F,G and H individually, it is possible to highlight all the relevant cells by highlighting F12 and dragging the highlight across to H12 and then downwards for as many rows as you require. Then click the Align Right button, see Figure 8. With these cells highlighted it is also possible to set the cells so that whatever number you enter into the In and Out cells, once you press Enter the appropriate currency sign will appear. To achieve this right click the highlighted cells and select from the sub menu that appears Format Cells, Figure above. From the resultant Format Cells window, Figure 9, select say the £ Currency under the Category Heading and under the Format heading select the particular form in which you want the currency to be displayed.

Figure 7

spreadsheet-Fig7

Figure 8

spreadsheet-Fig8

Figure 9

spreadsheet-Fig9

Any formulae to be applied will be applied to cells H12 and H13 in the Balance column. So what are the basic requirements? The first is to ensure that whatever is entered into the Out or In columns the correct amount will appear in the Balance column. The second is to make the balance automatically change as more entries are placed in either the In or Out columns. More particularly, we want whatever is paid out to be subtracted from whatever is paid in.

Therefore, click in cell H12 and then move to the Edit Box. Firstly click on the = sign just to the left of the Edit Box and this sign will appear within the Edit box. Alternatively, you could just type in the = sign. Then type in SUM because that is what the product is in the Balance column. Now we need to specify the cells that are going to give us that product i.e. G12-F12 and we enclose those in brackets because (a) they are the two cells to be summed and (b) if we wanted to do anything further the product (G12-F12)K1 say, is different to G12-F12xK1. Therefore, the formula in the Edit Box should read =SUM(G12-F12), see Figure 10. Now click the green tick to the left of the Edit Box to accept the formula that will apply to cell H12, although the formula is not show in the cell in the normal course of events unless you specifically click on the cell.

Figure 10

spreadsheet-Fig10

Now if you wanted that formula to apply to say the next 10 cells H13 to H23 you would need to select cell H12 and move the cursor to the bottom right hand corner of the box surrounding the cell, grab it with your mouse and drag it down until cell H23 is selected. However, the disadvantage here is that any balance cell will only show the product of it’s two corresponding in and out cells. To obtain an automatically corrected balance you need to add say a balance in cell H13 to the previous balance in cell H12. You can do this by selecting cell H13 and amend the previous formula to read =SUM(G13-F13)+H12 , in the Edit Box. Click the green tick and the formula is applied to cell H13. Highlight the cell H13, grab the bottom right hand corner and drag it down until H23 is selected. Now you have a corrected last balance although whatever the final or last balance will appear also in all remaining balance cells down to H23.

One way to stop this and to show only a corrected balance following your last entry is to limit the balance to appear only once a date has been entered for your last entry. So to achieve this amend the formula for H13 to read
=IF(C13,SUM((G13-F13)+H12),””)
where C13 is the cell in which the date is entered, Figure 11. The commas in the formula separate the various parts while the quotes provide spaces/blanks before the application of the final bracket. Again click the green tick and this formula will be applied to cell H13. Highlight cell H13, grab the bottom right hand corner and drag it down until cell H23 is highlighted and press enter. Now you will have an automated running balance depending upon the entries in corresponding In and Out cells, and the corresponding Date cell.

Figure 11

spreadsheet-Fig11

At this point can I draw your attention back to the formula or program for the cell H13 mentioned above, namely, =IF(C13,SUM((G13-F13)+H12),””). This formula is effectively saying that IF one set of data is entered into the cells a corrected balance will appear in cell H13, or alternatively, if a different set of data is entered nothing will be shown in the appropriate Balance column cell. I have explained in the previous paragraph what will happen when a date is entered in cell C13. However, the formula also reacts when no date is entered in cell C13 and some data is entered in the In (G13) or Out (F13) cells,completing the calculation SUM((G13-F13)+H12). A balance would then be expected to appear in cell H13 and all the remaining H cells ad-infinitum. However, the presence of the quotes (spaces or blanks) now becomes effective to instruct that no data be applied to H13 and hence all remaining H cells. So you see the reason for the quotes at the end of the formula, when no date is entered, no balance appears.

Accordingly, Figure 12 illustrates what a sheet tracking your bank account might begin to look like with entries in both the In and Out columns, and with an automated Balance column. This figure also illustrates the feature that when no date is entered there is no balance. Figure 13 shows the situation after the date has been entered.

Figure 12

spreadsheet-Fig12

Figure 13

spreadsheet-Fig13

Please note the formulae discussed above and indeed any formula that requires dependence upon cells in a previous row requires that any entries you make must be in the next following row. Spaces between the rows will interrupt the working of the formula.

If you require greater spacing between your entries then it will be necessary to increase the depth of each row. This can be achieved for one row by placing your cursor on the line between say row numbers 1 and 2 and when the cursor changes to a vertical line with an arrowhead at each end hold the left mouse button down and drag the line downward until you have the depth you require. Alternatively, click Format in the Main Menu and from the sub-menu that appears select Row then Height and a Row Height window appears in which you can amend the height of all rows.

It is recommended that having achieved the page you require you keep it as a copy so that you can take a copy of it for any future pages you require. Note that at the bottom left hand corner of your spreadsheet a number of pages are listed and you can move between these by just clicking the one you want. Therefore keep your original as page 1 and take a copy and paste it into the next page. All the formulae and formatting will be transferred. The simplest way to do this is select Edit, Select All and copy. Select the next page, Edit and then Paste.

Although this workshop has been specifically directed towards tracking a banking account, the principles of manipulating the cells is substantially the same for any application, only the formulae and style will change according to your own needs.

We humans can and do forget things over long periods of time and therefore it is imperative that you keep clear documentation concerning the spreadsheet you have generated. Start out with a good descriptive title which will become the file name, and add notes to say page 1 to explain what the spreadsheet is for and how it works.

Finally,for those of you wishing to learn more about spreadsheets please see the four articles that follow this workshop in the Sub-Menu to Spreadsheets and also see the on-line help which will provide you with much more information as to further functions to be obtained. Enjoy!

Author: Peter Horwood - Page reference: 4203
Last modified: Alan Campion - 2015-01-27