Spreadsheet

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

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

Figure 3

Figure 4

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

Figure 6

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

Figure 8

Figure 9

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

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

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

Figure 13

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!

     = B2 * $C$9. 

     = B2 * C$9

Spreadsheet terms

Spreadsheets - glossary of terms

Lets start by giving you some definitions of what you will see when you start an Open Office spreadsheet. It helps if you know the correct name for each tool and function.

Spreadsheet Active Cell	The Active Cell is the cell that you have selected. To work in a spreadsheet you need to have a cell active. This tells the computer where you want to work. The active cell is surrounded by a thick black rectangle, with a square in the bottom right hand corner. (The reference for the active cell is displayed at the left end of the formula bar).
Column Headings	The Column Headings are the grey lettered boxes near the top of the screen. You can use the column headings to select an entire column.
Formatting Toolbar	The Formatting Toolbar contains a row of icons starting with the font box that allow you to change how the workbook looks. This includes: Font Size and Style Bold, Italic and Underline Text alignment Number and Currency formatting Increase and Decrease Indents Insert Frame Fill and Font Colour
Formula Bar	The Formula Bar helps you to enter text onto the worksheet. You can also enter text directly onto active cells. However, using the formula bar will allow you to edit your work more easily. When you click onto the formula bar the following functions become active: allows you to cancel what you have input allows you to enter what you have input =   allows you to edit what you have input You can also use the formula bar to edit information that you have input. You would click onto the cell in question. Then if you move up to the formula bar, your pointer would change to an I you would then be able to click to set the insertion point in order for you to delete or add individual characters.
The Menu Bar	The menu bar allows you to maximise, minimise, restore or close the worksheet.

Mouse Cursor	In Open Office-Spreadsheets the Mouse Cursor or pointer, appears in three forms. The shape of the cursor changes when it is positioned in different places on the window. The Arrow Pointer is activated a. when the cursor is moved over cells in the worksheet. You use the cell pointer to select any cell in the worksheet. b. when the cursor is moved over any menu. This allows you to point to the function that you wish to select. The arrow is also activated when you point it around the outside edge of an active cell, or range of cells, you can use the arrow to move the cells to a different location. The I-beam is activated when you move the pointer to the formula bar. The pointer changes to the shape of an I. You can click here to set an insertion point and edit text. The Cross is activated when you position the cursor over the small square box on the bottom right-hand corner of the active cell box. The cross allows you to copy the contents of a cell into the adjoining cells, row or column.
Range	A Range is a group of cells that you have selected. This can be a row a column, or any combination of cells, columns or rows. The range is identified by the Range Co-ordinates. The first element in the range co-ordinates is the cell reference of the top left cell, the second element is the bottom right cell. The two references are separated by a colon :
Row Headings	Row Headings are the grey numbered boxes down the left hand side of the screen. If you click on a row heading you select the entire row.
Scroll Bar/Arrows	The Scroll Bar and Arrow Keys allow you to move around your worksheet. They are located around the right bottom hand corner of the window. The arrow keys allow you to move by single rows or columns and should be used if you only need to across a few rows or columns. The bar allows you to quickly move around large segments of the worksheet. As you move Spreadsheets displays the row or column reference that you are currently on. This allows you to move quickly and accurately around your worksheet.
Select All Box	The Select All Box, does just what it says, it allows you to select the entire worksheet just by clicking it. It is a small grey box to the left of column A and above row 1 towards the top left-hand corner of the screen.

Sheet Tabs	The Sheet Tabs allow you to move between worksheets. Each worksheet is part of a workbook. A new workbook contains three sheets,at the bottom left-hand corner of a worksheet, named Sheet1 - Sheet3. You can insert a new worksheet by clicking on the green cross or through the Insert menu (Insert/Worksheet).
Standard Toolbar	The Standard Toolbar contains icons for tools that are used frequently. This includes; New, Open, Save Print, Print Preview, Cut, Copy, Paste, Undo Redo, AutoSum, Paste Function, Chart, Zoom and Help.
Tab Scrolling Buttons	Tab Scrolling Buttons are used for scrolling between sheets. Choose the direction you want to go and click. Alternatively you can point and the tab scrolling bars with your cursor, it will be in the shape of an arrow, then click your right mouse button. This will display a menu of all the worksheets, allowing you to select one.
Title Bar	The Title Bar allows you to minimise, maximise, restore or close Spreadsheets.